Design your schema

The ponder.schema.ts file defines your application's database schema, and the autogenerated GraphQL API schema. Like Zod and Drizzle, the schema definition API uses TypeScript for static validation and editor autocompletion throughout your app.

Tables

To create a table, add the table name as a property of 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, hex, 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, hex, 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.hex(),
    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(),
    favoriteNumbers: p.int().list(),
    colors: p.enum("Color").list(),
  }),
}));

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

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.hex(),
    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"
// }

Many-to-many

To add a many-to-many relationship to the GraphQL schema, create a "join table" that contains a foriegn key column referencing the two tables you want to connect.

To create a relationship, insert a new record into the join table referencing the two related records.

import { createSchema } from "@ponder/core";
 
export default createSchema((p) => ({
  Person: p.createTable({
    id: p.string(),
    age: p.int(),
    personDogs: p.many("PersonDog.personId"),
  }),
  Dog: p.createTable({
    id: p.string(),
    personDogs: p.many("PersonDog.dogId"),
  }),
  PersonDog: p.createTable({
    id: p.string(),
    personId: p.string().references("Person.id"),
    dogId: p.string().references("Dog.id"),
    person: p.one("personId"),
    dog: p.one("dogId"),
  }),
}));

type Person {
  id: String!
  age: Int!
  personDogs: [PersonDog!]!
}
 
type Dog {
  id: String!
  personDogs: [PersonDog!]!
}
 
type PersonDog {
  id: String!
  personId: String!
  dogId: String!
  person: Person!
  dog: Dog!
}

Then, you can write GraphQL queries for one-to-many data on either side of the relationship. In this example, you could query for all of a person's dogs, or all of a dog's people.

query {
  person(id: "Max") {
    id
    personDogs {
      dog {
        id
      }
    }
  }
  dog(id: "Chip") {
    id
    personDogs {
      person {
        id
      }
    }
  }
}

{
  "person": {
    "id": "Max",
    "personDogs": [
      { "dog": { "id": "Chip" } },
      { "dog": { "id": "Spike" } },
      { "dog": { "id": "Lettuce" } },
    ],
  },
  "dog": {
    "id": "Chip",
    "personDogs": [
      { "person": { "id": "Max" } },
    ],
  },
}

Indexes

To create a database index, include a second argument to the p.createTable() function, and use p.index() to specify which column(s) to include in the index.

Single column

To create a index on a single column, pass the column name to p.index().

import { createSchema } from "@ponder/core";
 
export default createSchema((p) => ({
  Person: p.createTable({
    id: p.string(),
    name: p.string(),
    dogs: p.many("Dog.ownerId"),
  }, {
    // Index the `name` column to speed up search queries.
    nameIndex: p.index("name"),
  }),
  Dog: p.createTable({
    id: p.string(),
    ownerId: p.string().references("Person.id"),
  }, {
    // Index the `ownerId` column to speed up relational queries (the `Person.dogs` field).
    ownerIdIndex: p.index("ownerId"),
  }),
}));

For single-column indexes, you can also specify the direction of the index using .asc() or .desc(), and the null ordering behavior using .nullsFirst() or .nullsLast(). These options can improve query performance if you use the column in an ORDER BY clause using the same options.

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

Multiple columns

To create a multi-column index, pass an array of column names to p.index(). The index will be created using the same column order that you specify.

import { createSchema } from "@ponder/core";
 
export default createSchema((p) => ({
  Person: p.createTable({
    id: p.string(),
    age: p.int(),
    salary: p.bigint(),
  }, {
    seniorityIndex: p.index(["age", "salary"]),
  }),
}));

The .asc(), .desc(), .nullsFirst() and .nullsLast() modifiers are not currently supported for multi-column indexes.

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