Design your schema

The ponder.schema.ts file defines your application's database schema, and the autogenerated GraphQL API schema. Like Zod (opens in a new tab) and Drizzle (opens in a new tab), the schema definition API uses TypeScript to offer static validation and editor autocompletion throughout your app.

Here's an example ponder.schema.ts file for a simple ERC20 app.

ponder.schema.ts

import { p } from "@ponder/core";
 
export default p.createSchema({
  Account: p.createTable({
    id: p.string(),
    balance: p.bigint(),
    isOwner: p.boolean(),
    approvals: p.virtual("Approval.ownerId"),
    transferFromEvents: p.virtual("TransferEvent.fromId"),
    transferToEvents: p.virtual("TransferEvent.toId"),
  }),
  Approval: p.createTable({
    id: p.string(),
    amount: p.bigint(),
    ownerId: p.string().references("Account.id"),
    spender: p.string(),
  }),
  TransferEvent: p.createTable({
    id: p.string(),
    amount: p.bigint(),
    fromId: p.string().references("Account.id"),
    toId: p.string().references("Account.id"),
    timestamp: p.int(),
  }),
});

Column types

To create a table, add the table name as a property in the object passed to p.createSchema(). Then, use p.createTable() and include column definitions following the same pattern.

Every table must have an id column that is a string, bytes, int, or bigint.

Primitive types

Every column is a string, bytes, int, float, bigint, or boolean. Each of these primitive types corresponds to a TypeScript type (used in indexing function code) and a JSON data type (returned by the GraphQL API).

name	description	TypeScript type	JSON data type
`p.string()`	A UTF‐8 character sequence	`string`	`string`
`p.bytes()`	A UTF‐8 character sequence with `0x` prefix	`0x${string}`	`string`
`p.int()`	A signed 32‐bit integer	`number`	`number`
`p.float()`	A signed floating-point value	`number`	`number`
`p.bigint()`	A signed integer (solidity `int256`)	`bigint`	`string`
`p.boolean()`	`true` or `false`	`boolean`	`boolean`

Here's an example Account table that has a column of every type, and a function that inserts an Account record.

ponder.schema.ts

import { p } from "@ponder/core";
 
export default p.createSchema({
  Account: p.createTable({
    id: p.bytes(),
    daiBalance: p.bigint(),
    totalUsdValue: p.float(),
    lastActiveAt: p.int(),
    isAdmin: p.boolean(),
    graffiti: p.string(),
  }),
});

src/index.ts

const { Account } = context.db;
 
await Account.create({
  id: "0xabc",
  data: {
    daiBalance: 7770000000000000000n,
    totalUsdValue: 17.38,
    lastActiveAt: 1679337733,
    isAdmin: true,
    graffiti: "LGTM",
  },
});

Enums

To define a enum, pass a list of allowable values to p.createEnum() (similar to p.createTable()). Then use p.enum() as a column type, passing the enum name as an argument. Enums use the same database and JSON types as string columns.

ponder.schema.ts

import { p } from "@ponder/core";
 
export default p.createSchema({
  Color: p.createEnum(["ORANGE", "BLACK"]),
  Cat: p.createTable({
    id: p.string(),
    color: p.enum("Color"),
  }),
});

src/index.ts

const { Cat } = context.db;
 
await Cat.create({
  id: "Fluffy",
  data: {
    color: "ORANGE",
  },
});

Lists

To define a list, add .list() to any primitive or enum column. Lists should only be used for small one-dimenional collections, not relationships between records.

ponder.schema.ts

import { p } from "@ponder/core";
 
export default p.createSchema({
  Color: p.createEnum(["ORANGE", "BLACK"]),
  FancyCat: p.createTable({
    id: p.string(),
    colors: p.enum("Color").list(),
    favoriteNumbers: p.int().list(),
  }),
});

src/index.ts

const { FancyCat } = context.db;
 
await FancyCat.create({
  id: "Fluffy",
  data: {
    colors: ["ORANGE", "BLACK"],
    favoriteNumbers: [7, 420, 69],
  },
});

Foreign keys (`references`)

To define a relationship between two tables, create a foreign key column. To create a foreign key column:

Use an Id suffix in the column name, like userId or tokenId.
Add .references("OtherTable.id") to the column type, passing the name of the referenced table.column.
Be sure that the column type matches the type of the id column of the referenced table (string, bytes, int, or bigint).

Suppose every Dog belongs to a Person. When you insert a Dog record, set the ownerId field to the id of a Person record to establish the relationship.

ponder.schema.ts

import { p } from "@ponder/core";
 
export default p.createSchema({
  Person: p.createTable({
    id: p.string(),
    age: p.int(),
  }),
  Dog: p.createTable({
    id: p.string(),
    ownerId: p.string().references("Person.id"),
  }),
});

src/index.ts

const { Person, Dog } = context.db;
 
await Person.create({
  id: "Bob",
  data: { age: 22 },
});
 
await Dog.create({
  id: "Chip",
  data: { ownerId: "Bob" },
});

Now, you can query for information about the owner of a Dog using the GraphQL API.

Query

query {
  dog(id: "Chip") {
    id
    owner {
      age
    }
  }
}

Result

{
  "dog": {
    "id": "Chip",
    "owner": {
      "age": 22,
    },
  },
}

Virtual columns

Virtual columns are similar to Graph Protocol derivedFrom or reverse-lookup fields.

To create a virtual column, use p.virtual() as the column type passing the name of a column that references the current table.

ponder.schema.ts

import { p } from "@ponder/core";
 
export default p.createSchema({
  Person: p.createTable({
    id: p.string(),
    age: p.int(),
    dogs: p.virtual("Dog.ownerId"),
  }),
  Dog: p.createTable({
    id: p.string(),
    ownerId: p.string().references("Person.id"),
  }),
});

src/index.ts

const { Person, Dog } = context.db;
 
await Person.create({
  id: "Bob",
});
 
await Dog.create({
  id: "Chip",
  data: { ownerId: "Bob" },
});
 
await Dog.create({
  id: "Spike",
  data: { ownerId: "Bob" },
});

Now, any Dog record with ownerId: "Bob" will be present in Bob's dogs field.

Query

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

Result

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

You can't directly get or set the dogs field on a Person record. Virtual columns don't exist in the database. They are only present when querying data from the GraphQL API.

src/index.ts

const { Person } = context.db;
 
await Person.create({
  id: "Bob",
  // Error, can't set a virtual column.
  data: { dogs: ["Chip", "Bob"] },
});

src/index.ts

const { Person } = context.db;
 
const bob = await Person.get("Bob");
// `dogs` field is NOT present.
// {
//   id: "Bob"
// }

Optional

All columns are required, e.g. NOT NULL by default. To mark a column as optional/nullable, add .optional() to the primitive type.

ponder.schema.ts

import { p } from "@ponder/core";
 
export default p.createSchema({
  User: p.createTable({
    id: p.bytes(),
    ens: p.string(),
    github: p.string().optional(),
  }),
});

src/index.ts

const { User } = context.db;
 
await User.create({
  id: "0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045",
  data: {
    ens: "vitalik.eth",
    github: "https://github.com/vbuterin",
  },
});
 
await User.create({
  id: "0xD7029BDEa1c17493893AAfE29AAD69EF892B8ff2",
  data: {
    ens: "dwr.eth",
  },
});

Add contracts Create & update records

Design your schema

Column types

Primitive types

Enums

Lists

Foreign keys (references)

Virtual columns

Optional

Foreign keys (`references`)