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.

ERC20 example

Here's a schema for a simple ERC20 app.

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

Tables

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

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

ID column

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

import { createSchema } from "@ponder/core";
 
export default createSchema((p) => ({
  Book: p.createTable({
    id: p.string(),
    title: p.string(),
  }),
  Page: p.createTable({
    id: p.bigint(),
    bookId: p.string().references("Book.id"),
  }),
}));

Column types

Primitives

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

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

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

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

Enum

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.

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

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

List

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.

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

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

Optional

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

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

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

Foreign key

Foreign key columns are used to define relationships between records. To define a foreign key column:

1. Add .references("OtherTable.id") to the column type.
2. Be sure to use the same column type as the referenced column.
3. By convention, the foreign key column name should have an Id suffix, like userId or tokenId.

Foreign key columns are just like other primitive columns. They are persisted to the database, and if they are not marked as optional, they must included when creating new records.

import { createSchema } from "@ponder/core";
 
export default createSchema((p) => ({
  Author: p.createTable({
    id: p.bigint(),
    name: p.string(),
  }),
  Book: p.createTable({
    id: p.string(),
    title: p.string(),
    authorId: p.bigint().references("Author.id"),
  }),
  Page: p.createTable({
    id: p.bigint(),
    contents: p.string(),
    bookId: p.string().references("Book.id"),
  }),
}));

Relationships

To create relationships between records, just define foreign key columns and create records accordingly.

However, to enrich the schema of the autogenerated GraphQL API, you can also define virtual relationships using the p.one() and p.many() column types. These columns are not persisted to the database, are not present in indexing function code, but are included as fields in the autogenerated GraphQL schema.

One-to-one

To add a one-to-one relationship to the GraphQL schema, add a new column using the p.one() column type passing the name of a foreign key column from the same table. This creates a field in the GraphQL schema that resolves to the referenced entity type.

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

type Person {
  id: String!
  age: Int!
}
 
type Dog {
  id: String!
  ownerId: String!
  owner: Person!
}

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

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

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

One-to-many

To add a one-to-many relationship to the GraphQL schema, add a new column using the p.many() column type passing the name of a foreign key column from the other table that references the current table (also known as a derived field or reverse mapping). This creates a field in the GraphQL schema that resolves to a list of other other entity type.

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

type Person {
  id: String!
  age: Int!
  dogs: [Dog!]!
}
 
type Dog {
  id: String!
  ownerId: String!
}

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

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

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

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

As a reminder, you cannot directly get or set the dogs field on a Person record. Columns defined using p.one() and p.many() don't exist in the database. They are only present when querying data from the GraphQL API.

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

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