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.

ponder.schema.ts

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.

ponder.schema.ts

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

name	description	TypeScript type	JSON data type
`p.string()`	A UTF‐8 character sequence	`string`	`string`
`p.hex()`	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 large integer (holds `uint256` and `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 { 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(),
  }),
}));

src/index.ts

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.

ponder.schema.ts

import { createSchema } from "@ponder/core";
 
export default createSchema((p) => ({
  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",
  },
});

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.

ponder.schema.ts

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

src/index.ts

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.

ponder.schema.ts

import { createSchema } from "@ponder/core";
 
export default createSchema((p) => ({
  User: p.createTable({
    id: p.hex(),
    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",
  },
});

Foreign key

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

Add .references("OtherTable.id") to the column type.
Be sure to use the same column type as the referenced column.
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.

ponder.schema.ts

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

Foreign key columns can only reference primary key columns of other tables. This is because the schema definition does not yet support marking columns other than the primary key as DISTINCT/UNIQUE. We may add this feature in a future release - please get in touch if you want it.

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.

ponder.schema.ts

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

GraphQL schema

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

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

Result

{
  "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.

ponder.schema.ts

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

GraphQL schema

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.

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

Query

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

Result

{
  "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.

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"
// }

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.

ponder.schema.ts

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

GraphQL schema

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

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

Result

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

ERC20 example

Here's a schema for a simple ERC20 app.

ponder.schema.ts

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

Add contracts Create & update records

Design your schema

Tables

ID column

Column types

Primitives

Enum

List

Optional

Foreign key

Relationships

One-to-one

One-to-many

Many-to-many

ERC20 example

Resources

More

Connect