Skip to main content

Advanced Access to the SubQuery Store

SubQuery TeamAbout 3 min

Advanced Access to the SubQuery Store

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.

Note

Note that there are additional methods autogenerated with your entities that also interact with the store. Most users will find those methods sufficient for their projects.

Following is a summary of the Store interface:

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

  getByField(
    entity: string,
    field: string,
    value: any,
    options?: { limit?: number; offset?: number },
  ): Promise<Entity[]>;

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

  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>;

  count(
    entity: string,
    field?: string,
    value?: any,
    options?: { distinct?: boolean; col?: string },
  ): Promise<number>;
}

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);

Get Records by a Single Field

getByField(entity: string, field: string, value: any, options?: { limit?: number; offset?: number }): Promise<Entity[]>;

This returns matching records for the specific entity that matches an equality comparison. 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 you can also specify an offset and page your results.

// Get all records with ChainID == 50
await store.getByField(`TransactionEntity`, "ChainID", 50);

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 all records with ChainID == 50 OR ChainID == 100 OR ChainID == 150
await store.getByField("TransactionEntity", "ChainID", [50, 100, 150]);

Get Records by Field

getByFields<T extends Entity>(
    entity: string,
    filter: [field: keyof T, operator: '=' | '!=' | 'in' | '!in', value: T[keyof T] | Array<T[keyof T]>][],
    options?: {offset?: number; limit?: number}
  ): 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.

Using the store directly:

// Get all records with ChainID == 50 AND AccountID == '0xSomeAddress'
await store.getByFields(`TransactionEntity`, [
  ["ChainID", "=", 50],
  ["AccountID", "=", "0xSomeAddress"],
]);

Using an entity, this will provide better type safety:

// Get all records with ChainID == 50 AND AccountID == '0xSomeAddress'
await TransactionEntity.getByFields([
  ["ChainID", "=", 50],
  ["AccountID", "=", "0xSomeAddress"],
]);

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

// Get all records with ChainID == 50 OR ChainID == 51
await TransactionEntity.getByFields([["ChainID", "=", [50, 51]]]);

Get First Record by Field

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

This returns the first matching record for the specific entity that matches a given search.

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, ...})

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.

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 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);