`@subsquid/typeorm-store`

This page describes the interface of the classes from the @subsquid/typeorm-store NPM package. If you’re looking for a guide on saving squid data to databases and the related workflows, check out the Saving to PostgreSQL page.

`TypeormDatabase` constructor arguments

The argument of the TypeormDatabase class constructor may have the following fields:

stateSchema: string: the name of the database schema that the processor uses to persist its status (hash + height of the highest reached block). Useful for making sure that each processor uses its own state schema when running multiple processors against the same database (e.g. in a multichain setting). Default: 'squid_processor'. This affects only the processor’s own status tables; to relocate the entity (data) tables, use the DB_SCHEMA environment variable.
isolationLevel: 'SERIALIZABLE' | 'READ COMMITTED' | 'REPEATABLE READ': sets the transaction isolation level of processor transactions. Default: 'SERIALIZABLE'.
supportHotBlocks: boolean: controls the support for hot blocks. Necessary in all squids that must be able to handle short-lived blockchain forks. That includes all squids that index chain data in near-real time using RPC endpoints. Default: true.
projectDir: string: the folder where TypeormDatabase will look for the TypeORM model definition and migrations. It resolves src/model under ts-node and lib/model in compiled runs, plus db/migrations. Default: process.cwd().

`Store` interface

Processor transactions retry PostgreSQL serialization failures with code 40001 up to three times after the first attempt. The data handler can therefore run more than once for the same batch. Keep external side effects idempotent or perform them outside the handler.

Batch access methods

`upsert(e: E | E[])`

Upsert a single or multiple entities to the database. Does not cascade the upsert to the relations.

await ctx.store.upsert([new User({id: 'Bob'}), new User({id: 'Alice'})])

`save(e: E | E[])`

Alias for upsert().

`insert(e: E | E[])`

Inserts a given entity or entities into the database. Does not check if the entity(s) exist in the database and will fail if a duplicate is inserted. Executes a primitive INSERT operation without cascading to the relations.

await ctx.store.insert([new User({ id: "Bob" }), new User({ id: "Alice" })]);

Array inserts and upserts are split into batches of 1,000 rows. Upserts that include foreign-key fields are also grouped by which foreign keys are defined, so every statement has a consistent column set.

`remove(e: E | E[] | EntityClass<E>, id?: string | string[])`

Deletes a given entity or entities from the database. Accepts either an object or an entity ID(s). Does not cascade the deletion.

await ctx.store.remove(User, ["Alice", "Bob"]);

TypeORM methods

The store intentionally exposes a restricted subset of TypeORM’s EntityManager. Its find options support where, relations, order, and comment, plus skip and take for multi-row methods. findOne(), findOneBy(), and get() return undefined instead of TypeORM’s null when nothing matches.

`get`

Get an entity by ID or with the restricted FindOneOptions accepted by findOne(). It returns undefined when no row matches.

await ctx.store.get(User, "Bob");

await ctx.store.get(User, {
  where: {firstName: "Timber"},
  relations: {project: true},
});

`count`

Count the number of entities matching a where filter.

await ctx.store.count(User, {
  where: {
    firstName: "Timber",
  },
});

`countBy`

Count the number of entities matching a filter.

await ctx.store.countBy(User, { firstName: "Timber" })

`find`

Return a list matching a where filter.

await ctx.store.find(User, {
  where: {
    firstName: "Timber",
  },
});

`findBy`

Return a list matching a filter.

let accounts = await ctx.store.findBy(Account, {id: In([...accountIds])})

`findOne`

Return the first entity matching a where filter.

const timber = await ctx.store.findOne(User, {
  where: {
    firstName: "Timber",
  },
});

`findOneBy`

Return the first entity matching a filter.

const timber = await ctx.store.findOneBy(User, { firstName: "Timber" })

`findOneOrFail`

Throws if nothing is found.

const timber = await ctx.store.findOneOrFail(User, {
  where: {
    firstName: "Timber",
  },
});

`findOneByOrFail`

Throws if nothing is found.

const timber = await ctx.store.findOneByOrFail(User, { firstName: "Timber" })

Find Operators

find() and findXXX() methods support the following operators:

In (contains in array)
Not
LessThan
LessThanOrEqual
MoreThan
MoreThanOrEqual
Like
ILike
Between
Any
IsNull
Raw (raw SQL fragments)

See the details and examples in the TypeORM FindOption docs.

Example

let accounts = await ctx.store.findBy(Account, {id: In([...accountIds])})

Joining relations

To load an entity with relations, use relations field on the find options and specify which relations should be joined:

await ctx.store.find(User, {
  relations: {
    project: true,
  },
  where: {
    project: {
      name: "TypeORM",
      initials: "TORM",
    },
  },
});

See the TypeORM docs sections for details.

Database connection parameters

Database credentials must be supplied via the environment variables:

DB_HOST (default localhost)
DB_PORT (default 5432)
DB_NAME (default postgres)
DB_USER (default postgres)
DB_PASS (default postgres)
DB_SSL (default false)
DB_SSL_REJECT_UNAUTHORIZED (default true)
DB_URL (default undefined, see the DB_URL section)
DB_SCHEMA (default undefined, see the DB_SCHEMA section)
DB_SCHEMA_INCLUDE_PUBLIC (default false, see the DB_SCHEMA section)

When deploying to Cloud with the Postgres addon enabled in the manifest, any user-supplied values are overwritten for most of these variables. See Variable shadowing.

typeorm-store also supports the following variables for connecting to databases that require client-side SSL:

DB_SSL_CA - the root certificate in plain text
DB_SSL_CA_FILE - path to a root certificate file
DB_SSL_CERT - client certificate in plain text
DB_SSL_CERT_FILE - path to client certificate in plain text
DB_SSL_KEY - client key in plain text
DB_SSL_KEY_FILE - path to client key in plain text

In case you’re deploying to Cloud you can set secrets to the contents of any given file via stdin:

sqd secrets set DB_SSL_CA < ca.crt

`DB_URL`

When set, DB_URL takes precedence over all individual variables. Its format is as follows:

postgres[ql]://[username[:password]@][host[:port]]/database[?parameter_list]

where parameter_list is an &-separated list of assignments of SSL connection parameters:

ssl=(0|1|true|false)
sslmode=(disable|disabled|no-verify|prefer|require|verify-ca|verify-full)
sslcert=<path_to_cert_file>
sslkey=<path_to_key_file>
sslrootcert=<path_to_root_cert_file>

When DB_URL is set, its host, port, and database are authoritative; DB_HOST, DB_PORT, and DB_NAME are ignored. DB_USER and DB_PASS fill in only a username or password omitted from the URL. SSL values in the URL take precedence over SSL environment variables.

`DB_SCHEMA`

By default a squid keeps its entity tables in the connection’s default Postgres schema (usually public). Set DB_SCHEMA to place them in a named Postgres schema instead:

DB_SCHEMA (default: undefined) — name of the Postgres schema that holds the entity (data) tables. When set, the store connects with a search_path pointing at this schema, so all entity reads and writes resolve to it without any change to the generated SQL. The name must be a plain SQL identifier (matching [A-Za-z_][A-Za-z0-9_]*), and its case is preserved.
DB_SCHEMA_INCLUDE_PUBLIC (default: false) — when true, public is appended to the search_path after DB_SCHEMA. Enable it only if your squid references objects that live in public, such as Postgres extensions; otherwise the data schema stays fully isolated.

The schema is created automatically — squid-typeorm-migration apply issues a CREATE SCHEMA IF NOT EXISTS before running the migrations, and the migrations ledger lands in it too — so no manual setup is required. DB_SCHEMA is independent of the stateSchema constructor argument: DB_SCHEMA selects where the entity data lives, while stateSchema selects where the processor keeps its status. The two may be set to the same value, in which case a chain’s data and processor state share a single schema. If you use such an arrangement, please take care to avoid collisions with system table names: hot_block, hot_change_log, migrations, status, template_registry. The GraphQL server reads DB_SCHEMA too, so OpenReader serves the entities straight from the same schema.

DB_SCHEMA makes it possible to index several networks into one database from a single chain-agnostic codebase: run one processor per network, each with its own DB_SCHEMA (and a matching stateSchema), and their tables will never collide. This is an alternative to the classic approach of writing all the data to the public schema and separating it using a chain column or chain-specific tables.

Overview

Pipes SDK

Squid SDK

Alternative clients

typeorm-store

`@subsquid/typeorm-store`

`TypeormDatabase` constructor arguments

`Store` interface

Batch access methods

`upsert(e: E | E[])`

`save(e: E | E[])`

`insert(e: E | E[])`

`remove(e: E | E[] | EntityClass<E>, id?: string | string[])`

TypeORM methods

`get`

`count`

`countBy`

`find`

`findBy`

`findOne`

`findOneBy`

`findOneOrFail`

`findOneByOrFail`

Find Operators

Example

Joining relations

Database connection parameters

`DB_URL`

`DB_SCHEMA`

​@subsquid/typeorm-store

​TypeormDatabase constructor arguments

​Store interface

​Batch access methods

​upsert(e: E | E[])

​save(e: E | E[])

​insert(e: E | E[])

​remove(e: E | E[] | EntityClass<E>, id?: string | string[])

​TypeORM methods

​get

​count

​countBy

​find

​findBy

​findOne

​findOneBy

​findOneOrFail

​findOneByOrFail

​Find Operators

​Example

​Joining relations

​Database connection parameters

​DB_URL

​DB_SCHEMA

`@subsquid/typeorm-store`

`TypeormDatabase` constructor arguments

`Store` interface

Batch access methods

`upsert(e: E | E[])`

`save(e: E | E[])`

`insert(e: E | E[])`

`remove(e: E | E[] | EntityClass<E>, id?: string | string[])`

TypeORM methods

`get`

`count`

`countBy`

`find`

`findBy`

`findOne`

`findOneBy`

`findOneOrFail`

`findOneByOrFail`

Find Operators

Example

Joining relations

Database connection parameters

`DB_URL`

`DB_SCHEMA`