Write to the database

Insert, update, and delete rows

There are two ways to write to the database in a Ponder app.

Store API: The recommended way to write to the database. 100-1000x faster than raw SQL.
Raw SQL: A useful escape hatch for logic that's too complex for the Store API.

Store API

The Store API is a SQL-like query builder optimized for common indexing workloads.

The examples below use this ponder.schema.ts to demonstrate the core concepts.

ponder.schema.ts

import { onchainTable, primaryKey } from "ponder";
 
export const accounts = onchainTable("accounts", (t) => ({
  address: t.hex().primaryKey(),
  balance: t.bigint().notNull(),
  nickname: t.text(),
}));
 
export const allowances = onchainTable(
  "allowances",
  (t) => ({
    owner: t.hex().notNull(),
    spender: t.hex().notNull(),
    value: t.bigint().notNull(),
  }),
  (table) => ({
    pk: primaryKey({ columns: [table.owner, table.spender] }),
  })
);

Insert

Insert one or many rows into the database. Returns the inserted rows, including any default values that were generated.

src/index.ts

import { accounts } from "ponder:schema";
 
// Insert a single row
const row = await db.insert(accounts).values({
  address: "0x7Df1", 
  balance: 0n
});
 
// Insert multiple rows
const rows = await db.insert(accounts).values([
  { address: "0x7Df2", balance: -50n },
  { address: "0x7Df3", balance: 100n },
]);

Errors

If you insert a row that's missing a required column value (not null constraint violation), insert will reject with an error.

src/index.ts

import { accounts } from "ponder:schema";
 
const row = await db.insert(accounts).values({
  address: "0x7Df1",
});
 
// Error: Column "balance" is required but not present in the values object.

If you insert a duplicate row (unique constraint violation), insert will reject with an error.

src/index.ts

import { accounts } from "ponder:schema";
 
const row = await db.insert(accounts).values({
  address: "0x7Df1",
});
 
// Error: Column "balance" is required but not present in the values object.

Use conflict resolution to ignore unique constraint violations with onConflictDoNothing or achieve upsert behavior with onConflictDoUpdate.

Find

Find a single row by primary key. Returns the row, or null if not found.

The second argument is an object that specifies the primary key value to search for.

src/index.ts

import { accounts } from "ponder:schema";
 
const row = await db.find(accounts, { address: "0x7Df1" });

If the table has a composite primary key, the object must include a value for each column in the primary key.

src/index.ts

import { allowances } from "ponder:schema";
 
const row = await db.find(allowances, {
  owner: "0x7Df1",
  spender: "0x7Df2"
});

Update

Update a row by primary key. Returns the updated row.

src/index.ts

import { accounts } from "ponder:schema";
 
const row = await db
  .update(accounts, { address: "0x7Df1" })
  .set({ balance: 100n });

You can also pass a function to set, which receives the existing row and returns the update object.

src/index.ts

import { accounts } from "ponder:schema";
 
const row = await db
  .update(accounts, { address: "0x7Df1" })
  .set((row) => ({ balance: row.balance + 100n }));

Errors

If the target row is not found, update will reject with an error.

src/index.ts

import { tokens } from "ponder:schema";
 
const row = await db
  .update(accounts, { address: "0xa4F0" })
  .set({ balance: 200n });
 
// Error: No row found for address "0xa4F0".

If the new row violates a not null constraint, update will reject with an error.

src/index.ts

import { tokens } from "ponder:schema";
 
const row = await db
  .update(accounts, { address: "0x7Df1" })
  .set({ balance: null });
 
// Error: Column "balance" is required but not present in the object.

Delete

Delete a row by primary key. Returns true if the row was deleted, or false if not found.

src/index.ts

import { accounts } from "ponder:schema";
 
const deleted = await db.delete(accounts, { address: "0x7Df1" });

Conflict resolution

The insert method supports conflict resolution.

`onConflictDoNothing`

Use onConflictDoNothing to skip the insert if the specified row already exists. This avoids unique constraint violation errors.

src/index.ts

import { accounts } from "ponder:schema";
 
const row = await db
  .insert(accounts)
  .values({ address: "0x7Df1", balance: 0n })
  .onConflictDoNothing();

`onConflictDoUpdate`

Use onConflictDoUpdate to achieve "upsert" behavior.

If the row does not exist, it will be inserted using the specified values. Otherwise, the existing row will be updated with the values passed to onConflictDoUpdate.

src/index.ts

import { accounts } from "ponder:schema";
 
const row = await db
  .insert(accounts)
  .values({ address: "0x7Df1", balance: 0n })
  .onConflictDoUpdate({ value: 200n });

Just like with update, you can pass a function to onConflictDoUpdate that receives the existing row and returns the update object.

src/index.ts

import { accounts } from "ponder:schema";
 
const row = await db
  .insert(accounts)
  .values({ address: "0x7Df1", balance: 0n })
  .onConflictDoUpdate((row) => ({ 
    balance: row.balance + 50n
  }));

Raw SQL

Query builder

Use db.sql to access the raw Drizzle PostgreSQL query builder. This is useful for complex queries that join multiple tables or use advanced SQL features.

src/index.ts

import { accounts, tradeEvents } from "ponder:schema";
import { eq, and, gte, inArray, sql } from "drizzle-orm";
 
// Add 100 points to accounts with recent trades
await db.sql
  .update(accounts)
  .set({ points: sql`${accounts.points} + 100` })
  .where(
    inArray(
      accounts.address,
      db.sql
        .select({ address: tradeEvents.from })
        .from(tradeEvents)
        .where(
          gte(tradeEvents.timestamp, event.block.timestamp - 24 * 60 * 60)
        )
    )
  );

Relational queries

Use db.sql.query to access Drizzle's relational query builder. This provides a type-safe way to write complex SELECT queries that join multiple tables.

src/index.ts

import { accounts, tradeEvents } from "ponder:schema";
 
// Find recent large trades with account details
const trades = await db.sql.query.tradeEvents.findMany({
  where: (table, { gt, gte, and }) =>
    and(
      gt(table.amount, 1_000n),
      gte(table.timestamp, Date.now() - 1000 * 60 * 60)
    ),
  limit: 10,
  with: { account: true },
});

Visit the Drizzle documentation for more details on writing raw SQL queries.