Relations

Define relationships between database tables

Ponder uses Drizzle Relations to define relationships between tables. This guide describes each kind of relationship and how to use them.

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 }[] }

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

Each row in the userTeams table represents a relationship between a user and team row. 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
//              }
//            }[]
//          }

GraphQL API

Every relationship you define in ponder.schema.ts automatically becomes 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" }
    ]
  }
}