> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sqd.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# typeorm-store

> Reference for @subsquid/typeorm-store — the optimized TypeORM-based Postgres store that powers Squid SDK indexers with batched upserts and inserts.

# `@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](/en/sdk/squid-sdk/resources/persisting-data/typeorm) page.

## `TypeormDatabase` constructor arguments

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

* `stateSchema: string`: the name of the [database schema](https://www.postgresql.org/docs/current/sql-createschema.html) 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`](#db_schema) environment variable.
* `isolationLevel: 'SERIALIZABLE' | 'READ COMMITTED' | 'REPEATABLE READ'`: sets the [transaction isolation level](https://www.postgresql.org/docs/current/transaction-iso.html) 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](https://en.wikipedia.org/wiki/Fork_\(blockchain\)). 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

<h4 id="upsert">
  **`upsert(e: E | E[])`**
</h4>

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

```ts theme={"system"}
await ctx.store.upsert([new User({id: 'Bob'}), new User({id: 'Alice'})])
```

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

Alias for [`upsert()`](#upsert).

<h4 id="insert">
  **`insert(e: E | E[])`**
</h4>

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**.

```ts theme={"system"}
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**.

```ts theme={"system"}
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.

```ts theme={"system"}
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.

```ts theme={"system"}
await ctx.store.count(User, {
  where: {
    firstName: "Timber",
  },
});
```

#### **`countBy`**

Count the number of entities matching a filter.

```ts theme={"system"}
await ctx.store.countBy(User, { firstName: "Timber" })
```

#### **`find`**

Return a list matching a where filter.

```ts theme={"system"}
await ctx.store.find(User, {
  where: {
    firstName: "Timber",
  },
});
```

#### **`findBy`**

Return a list matching a filter.

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

#### **`findOne`**

Return the first entity matching a where filter.

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

#### **`findOneBy`**

Return the first entity matching a filter.

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

#### **`findOneOrFail`**

Throws if nothing is found.

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

#### **`findOneByOrFail`**

Throws if nothing is found.

```ts theme={"system"}
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](https://typeorm.io/find-options#advanced-options).

#### Example

```ts theme={"system"}
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:

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

See the [TypeORM docs](https://typeorm.io/find-options) 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_url))
* `DB_SCHEMA` (default `undefined`, see the [DB\_SCHEMA section](#db_schema))
* `DB_SCHEMA_INCLUDE_PUBLIC` (default `false`, see the [DB\_SCHEMA section](#db_schema))

<Info>
  When deploying to [Cloud](/en/cloud) with the [Postgres addon](/en/cloud/reference/pg) enabled in the [manifest](/en/cloud/reference/manifest), any user-supplied values are overwritten for most of these variables. See [Variable shadowing](/en/cloud/reference/pg#variable-shadowing).
</Info>

`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

<Tip>
  In case you're deploying to [Cloud](/en/cloud) you can set [secrets](/en/cloud/resources/env-variables#secrets) to the contents of any given file via stdin:

  ```bash theme={"system"}
  sqd secrets set DB_SSL_CA < ca.crt
  ```
</Tip>

### `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](https://www.postgresql.org/docs/current/ddl-schemas.html) 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`](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-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](/en/sdk/squid-sdk/resources/persisting-data/typeorm#database-migrations), and the migrations ledger lands in it too — so no manual setup is required.

`DB_SCHEMA` is independent of the [`stateSchema`](#typeormdatabase-constructor-arguments) 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](/en/sdk/squid-sdk/reference/openreader-server/overview) serves the entities straight from the same schema.

<Tip>
  `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.
</Tip>