# Source: https://www.apibara.com/docs/storage/drizzle-pg

# Drizzle with PostgreSQL

The Apibara Indexer SDK supports Drizzle ORM for storing data to PostgreSQL.

## Installation

### Using the CLI

You can add an indexer that uses Drizzle for storage by selecting "PostgreSQL" in the "Storage" section when creating an indexer.

The CLI automatically updates your `package.json` to add all necessary dependencies.

### Manually

To use Drizzle with PostgreSQL, you need to install the following dependencies:

```bash
npm install drizzle-orm pg @apibara/plugin-drizzle@next
```

We recommend using Drizzle Kit to manage the database schema.

```bash
npm install --save-dev drizzle-kit
```

Additionally, if you want to use PGLite to run a Postgres compatible database without a full Postgres installation, you should install that package too.

```bash
npm install @electric-sql/pglite
```

## Persisting the indexer's state

The Drizzle plugin automatically persists the indexer's state to the database. You can explicitly configure this option with the `persistState` flag.

Read more [about state persistence in the internals page](/docs/storage/drizzle-pg/internals#state-persistence).

## Adding the plugin to your indexer

Add the `drizzleStorage` plugin to your indexer's `plugins`. Notice the following:

- Use the `drizzle` helper exported by `@apibara/plugin-drizzle` to create a drizzle instance. This method supports creating an in-memory database (powered by PgLite) by specifying the `memory:` connection string.
- Always specify the database schema. This schema is used by the indexer to know which tables it needs to protect against chain reorganizations.
- By default, the connection string is read from the `POSTGRES_CONNECTION_STRING` environment variable. If left empty, a local PGLite database will be created. This is great because it means you don't need to start Postgres on your machine to develop locally!

```ts
import { drizzle, drizzleStorage, useDrizzleStorage } from "@apibara/plugin-drizzle";

import { transfers } from "@/lib/schema";

const db = drizzle({
  schema: {
    transfers,
  },
});

export default defineIndexer(EvmStream)({
  // ...
  plugins: [
    drizzleStorage({
      db,
      idColumn: "_id",
    }),
  ],
  // ...
});
```

## Schema configuration

You can use the `pgTable` function from `drizzle-orm/pg-core` to define the schema, no changes required.

The only important thing to notice is that your table **must have an `id` column (name configurable)** that uniquely identifies each row. This requirement is necessary to handle chain reorganizations. Read more how the plugin handles chain reorganizations [on the internals page](/docs/storage/drizzle-pg/internals).

```ts
import { bigint, pgTable, text, uuid } from "drizzle-orm/pg-core";

export const transfers = pgTable("transfers", {
  id: uuid("id").primaryKey().defaultRandom(),
  amount: bigint("amount", { mode: "number" }),
  transactionHash: text("transaction_hash"),
});
```

## Specifying the id column

As mentioned in the previous section, the id column is required by the plugin to handle chain reorganizations.

The plugin allows you to specify the id column name for each table in the schema. You can do this by passing the `idColumn` option to the `drizzleStorage` plugin. This option accepts either a string value or a record mapping table names to column names. You can use the special `*` table name to define the default id column name for all tables.

### Example

This example uses the same id column name (`_id`) for all tables.

```ts
export default defineIndexer(EvmStream)({
  // ...
  plugins: [
    drizzleStorage({
      db,
      idColumn: "_id",
    }),
  ],
  // ...
});
```

This example uses different id column names for each table. The `transfers` table will use `transfer_id` as the id column, while all other tables will use `_id`.

```ts
export default defineIndexer(EvmStream)({
  // ...
  plugins: [
    drizzleStorage({
      db,
      idColumn: {
        transfers: "transfer_id",
        "*": "_id",
      },
    }),
  ],
  // ...
});
```

## Writing and reading data from within the indexer

Use the `useDrizzleStorage` hook to access the current database transaction. This transaction behaves exactly like a regular Drizzle ORM transaction because it is. Thanks to the way the plugin works and handles chain reorganizations, it can expose the full Drizzle ORM API without any limitations.

```ts
export default defineIndexer(EvmStream)({
  // ...
  async transform({ endCursor, block, context, finality }) {
    const { db } = useDrizzleStorage();

    for (const event of block.events) {
      await db.insert(transfers).values(decodeEvent(event));
    }
  },
});
```

You are not limited to inserting data, you can also update and delete rows.

### Drizzle query

Using the [Drizzle Query interface](https://orm.drizzle.team/docs/rqb) is easy. Pass the database instance to `useDrizzleStorage`: in this case the database type is used to automatically deduce the database schema.

**Note**: the database instance is not used to query data but only for type inference.

```ts
const database = drizzle({ schema });

export default defineIndexer(EvmStream)({
  // ...
  async transform({ endCursor, block, context, finality }) {
    const { db } = useDrizzleStorage(database);

    const existingToken = await db.query.tokens.findFirst({ address });
  },
});
```

## Querying data from outside the indexer

You can query data from your application like you always do, using the standard Drizzle ORM library.

## Database migrations

There are two strategies you can adopt for database migrations:

- run migrations separately, for example using the drizzle-kit CLI.
- run migrations automatically upon starting the indexer.

If you decide to adopt the latter strategy, use the `migrate` option. Notice that the `migrationsFolder` path is relative from the project's root.

```ts
import { drizzle } from "@apibara/plugin-drizzle";

const database = drizzle({ schema });

export default defineIndexer(EvmStream)({
  // ...
  plugins: [
    drizzleStorage({
      db,
      migrate: {
        // Path relative to the project's root.
        migrationsFolder: "./migrations",
      },
    }),
  ],
  // ...
});
```