Design your schema

Ponder's schema definition API is built on Drizzle, a modern TypeScript ORM.

To create a table, use the onchainTable function exported by ponder and include column definitions.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const pets = onchainTable("pets", (t) => ({
  name: t.text().primaryKey(),
  age: t.integer().notNull(),
}));

Remember to export table objects using export const. Ponder will ignore tables that are not exported.

Columns

Column types

The schema definition API supports most PostgreSQL data types. Here's a quick reference for the most commonly used data types. For a complete list, see the Drizzle documentation.

name	description	TypeScript type	SQL data type
`text`	UTF‐8 character sequence	`string`	`TEXT`
`integer`	Signed 4‐byte integer	`number`	`INTEGER`
`real`	Signed 4-byte floating‐point value	`number`	`REAL`
`boolean`	`true` or `false`	`boolean`	`BOOLEAN`
`timestamp`	Date and time value (no time zone)	`Date`	`TIMESTAMP`
`json`	JSON object	`any` or custom	`JSON`

Ponder also includes a few extra column types built specifically for EVM indexing.

name	description	TypeScript type	SQL data type
`bigint`	Large integer (holds `uint256` and `int256`)	`bigint`	`NUMERIC(78,0)`
`hex`	UTF‐8 character sequence with `0x` prefix	`0x${string}`	`TEXT`

`bigint`

The bigint column type can hold any EVM uint256 or int256 value.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const accounts = onchainTable("accounts", (t) => ({
  address: t.hex().primaryKey(),
  balance: t.bigint().notNull(),
}));

Ponder's bigint type takes precedence over the Drizzle bigint type, which is an 8-byte integer (too small for EVM uint256 or int256 values). To create an 8-byte integer column, use the int8 alias.

`hex`

The hex column type is useful for EVM address, bytes, or any other hex-encoded value.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const accounts = onchainTable("accounts", (t) => ({
  address: t.hex().primaryKey(),
  balance: t.bigint().notNull(),
}));

Enums

To define an enum, use the onchainEnum function exported by ponder. Then, use the value returned by onchainEnum as a column type. Under the hood, onchainEnum uses a PostgreSQL enumerated type.

ponder.schema.ts

import { onchainEnum, onchainTable } from "ponder";
 
export const color = onchainEnum("color", ["ORANGE", "BLACK"]);
 
export const cats = onchainTable("cats", (t) => ({
  name: t.text().primaryKey(),
  color: color("color"),
}));

Arrays

To define an array column, use the .array() modifier. Arrays are a good fit for small one-dimensional collections, not relationships between records.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const cats = onchainTable("cats", (t) => ({
  name: t.text().primaryKey(),
  vaccinations: t.text().array(), // ["rabies", "distemper", "parvo"]
}));

Not null

To mark a column as not null, use the .notNull() modifier. If you attempt to insert a row that does not include a value for a NOT NULL column, the database will throw an error.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const cats = onchainTable("cats", (t) => ({
  name: t.text().primaryKey(),
  age: t.integer().notNull(),
}));

Default value

To set a default value for a column, use the .default() modifier and pass a string, number, boolean, or null.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const cats = onchainTable("cats", (t) => ({
  name: t.text().primaryKey(),
  livesRemaining: t.integer().default(9),
}));

Alternatively, use the .$default() modifier to specify a JavaScript function that returns the default value. With this approach, the database driver calls the function before inserting a row into this table that does not include a value for this column.

ponder.schema.ts

import { onchainTable } from "ponder";
import { generateId } from "../utils";
 
export const cats = onchainTable("cats", (t) => ({
  name: t.text().primaryKey(),
  age: t.integer().$default(() => generateId()),
}));

Relationships

Ponder uses Drizzle Relations to define relationships between tables. Here are some examples of how to define one-to-one, one-to-many, and many-to-many relationships. For more information, see the Drizzle Relations documentation.

Relations are useful to connect tables in the Query API (findMany and findFirst) and the GraphQL API. However, relations do not create foreign key constraints and won't stop you from inserting rows that violate referential integrity.

One-to-one

Use the relations function exported by ponder to define the relationships for a table.

To define a one-to-one relationship, use the one() operator and specify which columns relate the two tables. In this example, each user has a profile and each profile belongs to one user.

ponder.schema.ts

import { onchainTable, relations } from "ponder";
 
export const users = onchainTable("users", (t) => ({
  id: t.text().primaryKey(),
}));
 
export const usersRelations = relations(users, ({ one }) => ({
  profile: one(profiles, { fields: [users.id], references: [profiles.userId] }),
}));
 
export const profiles = onchainTable("profiles", (t) => ({
  id: t.text().primaryKey(),
  userId: t.text().notNull(),
  age: t.integer().notNull(),
}));

Now that you've defined the relationship, the profile field will become available in the Query API (findMany and findFirst) using the with option.

src/index.ts

import { users, profiles } from "ponder:schema";
 
await db.insert(users).values({ id: "hunter42" });
await db.insert(profiles).values({ userId: "hunter42", age: 29 });
 
const user = await db.sql.query.users.findFirst({
  where: eq(users.id, "hunter42"),
  with: { profile: true },
});
 
console.log(user.profile.age);
//          ^? { id: string; profile: { id: string; userId: string; age: number } }

One-to-many

To define a one-to-many relationship, use the one() and many() operators to define both sides of the relationship. In this example, each dog has one owner and each person can own many dogs.

ponder.schema.ts

import { onchainTable, relations } from "ponder";
 
export const persons = onchainTable("persons", (t) => ({
  name: t.text().primaryKey(),
}));
 
export const personsRelations = relations(persons, ({ many }) => ({
  dogs: many(dogs),
}));
 
export const dogs = onchainTable("dogs", (t) => ({
  petId: t.text().primaryKey(),
  ownerName: t.text().notNull(),
}));
 
export const dogsRelations = relations(dogs, ({ one }) => ({
  owner: one(persons, { fields: [dogs.ownerName], references: [persons.name] }),
}));

Now, any row inserted into the dogs table with ownerName: "Bob" will become available in Bob's dogs field.

src/index.ts

import { persons, dogs } from "ponder:schema";
 
await db.insert(persons).values({ name: "Bob" });
await db.insert(dogs).values([
  { petId: "Chip", ownerName: "Bob" },
  { petId: "Spike", ownerName: "Bob" },
]);
 
const bob = await db.sql.query.persons.findFirst({
  where: eq(persons.id, "Bob"),
  with: { dogs: true },
});
 
console.log(bob.dogs);
//          ^? { name: string; dogs: { petId: string; age: number }[] }

Note that in a one-to-many relationship, you cannot directly set the value of the many field. Instead, you must insert or update the related rows individually.

Many-to-many

To define a many-to-many relationship, create a "join table" that relates the two tables you want to connect using two one-to-many relationships.

ponder.schema.ts

import { onchainTable, relations } from "ponder";
 
export const users = onchainTable("users", (t) => ({
  id: t.text().primaryKey(),
}));
 
export const usersRelations = relations(users, ({ many }) => ({
  userTeams: many(userTeams),
}));
 
export const teams = onchainTable("teams", (t) => ({
  id: t.text().primaryKey(),
  mascot: t.text().notNull(),
}));
 
export const teamsRelations = relations(teams, ({ many }) => ({
  userTeams: many(userTeams),
}));
 
export const userTeams = onchainTable(
  "user_teams",
  (t) => ({
    userId: t.text().notNull(),
    teamId: t.text().notNull(),
  }),
  // A composite primary key is often a good choice for a join table.
  (table) => ({ pk: primaryKey({ columns: [table.userId, table.teamId] }) })
);
 
export const userTeamsRelations = relations(userTeams, ({ one }) => ({
  user: one(users, { fields: [userTeams.userId], references: [users.id] }),
  team: one(teams, { fields: [userTeams.teamId], references: [teams.id] }),
}));

Now, any row inserted into the userTeams table will create a relationship between the user and team records. You can query for the relationship by nesting the with option in the Query API.

src/index.ts

import { users, teams, userTeams } from "ponder:schema";
 
await db.insert(users).values([
  { id: "ron" }, { id: "harry" }, { id: "hermione" }
]);
await db.insert(teams).values([
  { id: "muggle", mascot: "dudley" },
  { id: "wizard", mascot: "hagrid" },
]);
await db.insert(userTeams).values([
  { userId: "ron", teamId: "wizard" },
  { userId: "harry", teamId: "wizard" },
  { userId: "hermione", teamId: "muggle" },
  { userId: "hermione", teamId: "wizard" },
]);
 
const hermione = await db.sql.query.users.findFirst({
  where: eq(users.id, "hermione"),
  with: { userTeams: { with: { team: true } } },
});
 
console.log(hermione.userTeams);
//          ^? {
//            id: string;
//            userTeams: {
//              userId: string;
//              teamId: string;
//              team: {
//                id: string;
//                mascot: string
//              }
//            }[]
//          }

Relationships in GraphQL

Every relationship you define in ponder.schema.ts will automatically become available in the GraphQL API, with one relations creating singular fields and many relations creating plural/connection fields.

The one-to-many example above corresponds to the following GraphQL query and result.

Query

query {
  person(id: "Bob") {
    id
    dogs {
      id
    }
  }
}

Result

{
  "person": {
    "id": "Bob",
    "dogs": [
      { "id": "Chip" },
      { "id": "Spike" }
    ]
  }
}

Tables

Primary key

Every table must have a primary key. To create a single-column primary key, use the .primaryKey() modifier.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const tokens = onchainTable("tokens", (t) => ({
  id: t.bigint().primaryKey(),
}));

To create a composite primary key, use the primaryKey() function exported by ponder. All components of the primary key constraint must be unique and not null.

ponder.schema.ts

import { onchainTable, primaryKey } from "ponder";
 
export const poolStates = onchainTable(
  "pool_states",
  (t) => ({
    poolId: t.bigint().notNull(),
    address: t.hex().notNull(),
    balance: t.bigint().notNull(),
  }),
  (table) => ({
    pk: primaryKey({ columns: [table.poolId, table.address] }),
  })
);

Indexes

To create a database index, use the index() function exported by ponder. The following example creates an index on the persons.name column to speed up search queries, and an index on the dogs.ownerId column to speed up the persons.dogs relational query.

ponder.schema.ts

import { onchainTable, relations, index } from "ponder";
 
export const persons = onchainTable(
  "persons",
  (t) => ({
    id: t.text().primaryKey(),
    name: t.text(),
  }),
  (table) => ({
    nameIdx: index().on(table.name),
  })
);
 
export const personsRelations = relations(persons, ({ many }) => ({
  dogs: many(dogs),
}));
 
export const dogs = onchainTable(
  "dogs",
  (t) => ({
    id: t.text().primaryKey(),
    ownerId: t.text().notNull(),
  }),
  (table) => ({
    ownerIdx: index().on(table.ownerId),
  })
);
 
export const dogsRelations = relations(dogs, ({ one }) => ({
  owner: one(persons, { fields: [dogs.ownerId], references: [persons.id] }),
}));

The index() function supports specifying multiple columns, ordering, and custom index types like GIN and GIST. Read more in the Drizzle and PostgreSQL documention.

To improve performance, database indexes are created after historical indexing is complete, just before the app becomes healthy.

Best practices

Composite primary keys

If a table has two or more columns that together form a unique identifier for a row, use a composite primary key.

Consider an allowances table storing ERC20 token allowances. Each row in this table represents the allowance granted by one owner to one spender.

ponder.schema.ts

import { onchainTable, primaryKey } from "ponder";
 
export const allowances = onchainTable(
  "allowances",
  (t) => ({
    owner: t.hex(),
    spender: t.hex(),
    amount: t.bigint(),
  }),
  (table) => ({ pk: primaryKey({ columns: [table.owner, table.spender] }) })
);

Timestamps

Use the bigint column type to store block timestamps using their EVM-native Unix timestamp representation. This maintains consistency with Viem's approach, and avoids error-prone timezone manipulation code.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const events = onchainTable("events", (t) => ({
  id: t.text().primaryKey(),
  timestamp: t.bigint(), // Unix timestamp in seconds
}));

If you strongly prefer working with JavaScript Date objects, you can also use the timestamp column type, but we recommend doing this conversion in the view layer.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const events = onchainTable("events", (t) => ({
  id: t.text().primaryKey(),
  timestamp: t.timestamp(), // JavaScript Date object
}));

Custom types

Use the .$type() modifier to customize the TypeScript type for a column. Note that the .$type() modifier does not validate data at runtime or in the database, it only enforces a TypeScript type.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const tokens = onchainTable("tokens", (t) => ({
  id: t.bigint().primaryKey(),
  metadata: t.json().$type<{ name: string; symbol: string; decimals: number }>(),
}));

`camelCase` vs `snake_case`

Use camelCase for TypeScript names and snake_case for SQL names. This guideline applies to all database objects and properties, including tables, columns, relations, and indexes.

ponder.schema.ts

import { onchainTable } from "ponder";
 
export const registrationEvents = onchainTable(
  "registration_events", // Use snake_case for the SQL table name
  (t) => ({
    createdAt: t.bigint(), // Drizzle automatically converts this to `created_at`
    invitedBy: t.text("invited_by"), // Avoid manual case conversion for columns
    // ...
  })
);

Examples

ERC20

Here's a schema for a simple ERC20 app.

ponder.schema.ts

import { index, onchainTable, primaryKey } from "ponder";
 
export const account = onchainTable("account", (t) => ({
  address: t.hex().primaryKey(),
  balance: t.bigint().notNull(),
  isOwner: t.boolean().notNull(),
}));
 
export const allowance = onchainTable(
  "allowance",
  (t) => ({
    owner: t.hex(),
    spender: t.hex(),
    amount: t.bigint().notNull(),
  }),
  (table) => ({
    pk: primaryKey({ columns: [table.owner, table.spender] }),
  })
);
 
export const transferEvent = onchainTable(
  "transfer_event",
  (t) => ({
    id: t.text().primaryKey(),
    amount: t.bigint().notNull(),
    timestamp: t.integer().notNull(),
    from: t.hex().notNull(),
    to: t.hex().notNull(),
  }),
  (table) => ({
    fromIdx: index().on(table.from),
  })
);
 
export const approvalEvent = onchainTable("approval_event", (t) => ({
  id: t.text().primaryKey(),
  amount: t.bigint().notNull(),
  timestamp: t.integer().notNull(),
  owner: t.hex().notNull(),
  spender: t.hex().notNull(),
}));

Call traces Write to the database

Design your schema

Columns

Column types

`bigint`

`hex`

Enums

Arrays

Not null

Default value

Relationships

One-to-one

One-to-many

Many-to-many

Relationships in GraphQL

Tables

Primary key

Indexes

Best practices

Composite primary keys

Timestamps

Custom types

`camelCase` vs `snake_case`

Examples

ERC20

Resources

More

Connect

Design your schema

Columns

Column types

bigint

hex

Enums

Arrays

Not null

Default value

Relationships

One-to-one

One-to-many

Many-to-many

Relationships in GraphQL

Tables

Primary key

Indexes

Best practices

Composite primary keys

Timestamps

Custom types

camelCase vs snake_case

Examples

ERC20

`bigint`

`hex`

`camelCase` vs `snake_case`