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

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

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.

You should always add an index on the id column. This column is used every time the indexer handles a chain reorganization or a new pending block.

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