Advanced Access to the SubQuery Store

The SubQuery SDK will generate classes for your entities with the codegen step. These classes provide a simpler, better typed interface for interacting with the store. However there are times where you may need to access the store directly such as performing bulk operations.

The SubQuery store is an injected class that allows users to interact with records in the database from within mapping functions. This will come handy when user demands using multiple entity records as the parameters in the mapping function, or create/update multiple records in a single place.

Following is a summary of the Store interface:

export type GetOptions<T> = {
  limit: number;
  offset?: number;
  /* Order fields are only available in SDK versions >= 4.0.0 */
  orderBy?: keyof T;
  orderDirection?: "ASC" | "DESC";
};

export interface Store {
  get(entity: string, id: string): Promise<Entity | null>;

  getByFields<T extends Entity>(
    entity: string,
    filter: [
      field: keyof T,
      operator: "=" | "!=" | "in" | "!in",
      value: T[keyof T] | Array<T[keyof T]>,
    ][],
    options: GetOptions<T>,
  ): Promise<T[]>;

  getByField(
    entity: string,
    field: string,
    value: any,
    options: GetOptions<T>,
  ): Promise<Entity[]>;

  getOneByField(
    entity: string,
    field: string,
    value: any,
  ): Promise<Entity | null>;

  set(entity: string, id: string, data: Entity): Promise<void>;

  bulkCreate(entity: string, data: Entity[]): Promise<void>;

  bulkUpdate(entity: string, data: Entity[], fields?: string[]): Promise<void>;

  remove(entity: string, id: string): Promise<void>;
}

Get Record by ID

get(entity: string, id: string): Promise<Entity | null>;

This allows you to get a record of the entity with its id.

const id = block.block.header.hash.toString();
await store.get(`TransactionEntity`, id);

Filtering Records

To filter records by fields other than the ID, you NEED to set an @index on the field in your GraphQl schema otherwise there will be a runtime error when querying the data. Once the index has been added, you can run codegen and it will create convenience methods on your entity classes to filter by fields.

Get Records by Fields

export type GetOptions<T> = {
  limit: number;
  offset?: number;
  /* Order fields are only available in SDK versions >= 4.0.0 */
  orderBy?: keyof T;
  orderDirection?: "ASC" | "DESC";
};

export interface Store {
  getByFields<T extends Entity>(
    entity: string,
    filter: [
      field: keyof T,
      operator: "=" | "!=" | "in" | "!in",
      value: T[keyof T] | Array<T[keyof T]>,
    ][],
    options: GetOptions<T>,
  ): Promise<T[]>;
}

This returns all matching records for the specific entity that matches the given filter(s). Each entry in the filter is an AND operation. By default it will return the first 100 results.

The number of results can be changed via the query-limit flag for the node or via the options field. If you need more than the number of results provided, we recommend you specify an offset and paginate through your results.

The store has a cache layer in order to increase performance, because of this the returned results will always return data that is in the cache followed by data that is in the database. This is required to ensure pagination works.

Only fields with an index can be filtered on and an error will be thrown if the fields are not indexed. To add an index the projects graphql schema will need to be updated to include @index decorators.

Ordering

Note

Ordering is only available in SDK versions >= 4.0.0

By default ordering is done by id in ascending order.

Examples

Using the store directly:

// Get the first 100 records with ChainID == 50 AND AccountID == '0xSomeAddress'
await store.getByFields(
  `TransactionEntity`,
  [
    ["ChainID", "=", 50],
    ["AccountID", "=", "0xSomeAddress"],
  ],
  { limit: 100 },
);

Using an entity, this will provide better type safety:

// Get the first 100 records with ChainID == 50 AND AccountID == '0xSomeAddress'
await TransactionEntity.getByFields(
  [
    ["ChainID", "=", 50],
    ["AccountID", "=", "0xSomeAddress"],
  ],
  { limit: 100 },
);

It's also possible to match multiple values to a field (in this case an OR operation is applied):

// Get the first 100 records with ChainID == 50 OR ChainID == 51
await TransactionEntity.getByFields([["ChainID", "in", [50, 51]]], {
  limit: 100,
});

Get Records by a Single Field

export type GetOptions<T> = {
  limit: number;
  offset?: number;
  /* Order fields are only available in SDK versions >= 4.0.0 */
  orderBy?: keyof T;
  orderDirection?: "ASC" | "DESC";
};

export interface Store {
  getByField(
    entity: string,
    field: string,
    value: any,
    options: GetOptions,
  ): Promise<Entity[]>;
}

This is a convenient wrapper for getByFields but only accepts a single filter and uses the = or in operator.

// Get the first 100 records with ChainID == 50
await store.getByField(`TransactionEntity`, "ChainID", 50, { limit: 100 });

Please note, the third parameter also accepts array, you can consider this similar like bulkGet with OR search. To get a list of records with ChainID equal to 50, 100 or 150:

// Get the first 100 records with ChainID == 50 OR ChainID == 100 OR ChainID == 150
await store.getByField("TransactionEntity", "ChainID", [50, 100, 150], {
  limit: 100,
});

Get First Record by Field

getOneByField(entity: string, field: string, value: any): Promise<Entity | undefined>;

This returns the first matching record for the specific entity that matches a given search. This is a convenient wrapper for getByFields that allows filtering by a field and returns a single result.

const ChainIDValue = 50;
await store.getOneByField(`TransactionEntity`, `ChainID`, 50);

Upsert (Create and Update) Record

set(entity: string, id: string, data: Entity): Promise<void>;

This allows user to create a single record, if the record already exist this will overwrite its record.

const id = block.block.header.hash.toString();
await store.set(`TransactionEntity`,id, {ChainID: 50, ...})

Remove Record

remove(entity: string, id: string): Promise<void>;

This allows to remove a single record of the entity with its id.

const id = block.block.header.hash.toString();
await store.remove(`TransactionEntity`, id);

Bulk Operations

These methods don't offer much in the way for performance improvements by being bulk operations, but they may provide some convenience.

Bulk Create Records

bulkCreate(entity: string, data: Entity[]): Promise<void>;

This allows to create multiple records for specified entity, but it will not overwrite existing records.

await store.bulkCreate(`TransactionEntity`,[
    {id: 1, ChainID: 50, ...},
    {id: 2, ChainID: 100, ...},
    {id: 3, ChainID: 150, ...}
])

Bulk Upsert (Create and Update) Records

bulkUpdate(entity: string, data: Entity[], fields?: string[]): Promise<void>;

This allows to update multiple records for specified entity, it will create the records if they are not exist.

await store.bulkUpdate(`TransactionEntity`,[
    {id: 1, ChainID: 99, ...},
    {id: 2, ChainID: 199, ...},
    {id: 3, ChainID: 299, ...}
])

The 3rd parameter is optional, and allows user to provide a list of fields they wish to be updated, and other fields will be ignored.

For example, only the Success property will be updated.

await store.bulkUpdate(`TransactionEntity`,[
    {id: 1, ChainID: 99, Success: true, ...},
    {id: 2, ChainID: 199, Success: false,...},
    ['Success']
])

Please note, this fields feature is not working currently with any automated historical indexing. It will overwrite all attributes. To disable automated historical indexing, please enable --disable-historical=true parameter on subql-node.

Bulk Remove Record

bulkRemove(entity: string, ids: string[]): Promise<void>;

This allows to remove a number of entities with their ids.

const ids = ["1", "2", "3"];
await store.remove(`TransactionEntity`, ids);