Drizzle with PostgreSQL

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

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:

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

We recommend using Drizzle Kit to manage the database schema.

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.

npm install @electric-sql/pglite

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.

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.

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"),
});

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.

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.

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

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!

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

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.

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

const database = drizzle({ schema, connectionString });

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

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

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

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.

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",
      },
    }),
  ],
  // ...
});