Drizzle Relations v2

Every detail page wants an invoice with its line items and tags, handed over as one typed object you can render straight into the page. So you reach for the call that should give you exactly that:

const invoice = await db.query.invoices.findFirst({
  with: { lineItems: true, tags: true },
});

It doesn’t work yet. You’ve already declared the foreign keys: invoiceLineItems.invoiceId points at invoices.id, and the invoiceTags junction points at both invoices and tags. The links exist in the database, so why can’t the query builder follow them?

Because a foreign key and a traversal are two different things, declared for two different audiences. In this lesson you’ll write db/relations.ts, the file that turns those inert links into a graph the query builder can walk. You write the map, not the queries that read it; those come next chapter.

A foreign key guards writes; a relation enables reads

A foreign key is a rule Postgres enforces on every write. When you wrote .references(() => organizations.id, { onDelete: 'restrict' }), you told it to reject any invoice whose organizationId doesn’t match a real organization, and to govern what happens on delete. It guards writes and says nothing about how to read across the link.

The relational query builder, the db.query.… API you reached for above, needs that second thing: a TypeScript declaration of which edges it may walk and how. The foreign key lives in the database, the query builder in your Node process; neither sees the other’s world, so you declare the traversal separately.

That gives the data layer two files that look like they overlap but don’t:

• db/schema.ts: what’s in the database. Tables, columns, foreign keys. This is the only file Postgres ever sees, and every constraint you’ve written lives here.
• db/relations.ts: how the query builder walks it. A pure-TypeScript graph that never touches Postgres and emits no SQL. Its one consumer is the db.query builder.

db/schema.ts → Postgres

invoices

FOREIGN KEY ON DELETE

organizations

A write-time rule. Postgres enforces it on every insert and delete.

db/relations.ts → db.query

invoices

relation with: { … }

organizations

A read-time map. Only the query builder ever reads it; Postgres never sees it.

The same two tables, joined by two different edges for two different readers. The foreign key (left) governs writes; the relation (right) is a separate declaration the query builder reads. Declaring one never declares the other.

The foreign key and the relation are two declarations of the same edge, for two different audiences, and declaring one never declares the other. This is the most common Drizzle mistake: db.query.invoices.findFirst({ with: { tags: true } }) returns tags: undefined while the foreign keys sit right there in the junction table. Nothing is wrong with them; the relation was simply never declared.

The relations file and defineRelations

The db/ folder already holds schema.ts (your tables), columns.ts (the shared column helpers), and index.ts (the client). Add a fourth file beside them: db/relations.ts. Its shape is small and fixed.

Import the tables from ./schema, pass them into defineRelations , and export what it returns as a single relations const:

import { defineRelations } from 'drizzle-orm';
import * as schema from './schema';

export const relations = defineRelations(schema, (r) => ({
  // one entry per table, each listing that table's relations
}));

defineRelations takes two arguments.

The first is the schema object — every table passed together: organizations, invoices, invoiceLineItems, users, tags, invoiceTags, and memberships. Handing over the whole module gives the builder its knowledge of every table and column, and is where autocomplete comes from next.

The second is a callback, (r) => ({ … }), returning a map keyed by table name where each value describes that table’s relations. The r parameter is the relation builder, used three ways: r.one.<table>(…) for a relation that resolves to a single row, r.many.<table>(…) for one that resolves to an array, and r.<table>.<column> to point at a specific column when you spell out the join.

The relations feed the relational query API . You hand them to the client when you create it:

export const db = drizzle(client, { relations });

That option carries the graph into the db.query builder; without it, every with comes back empty. The rest of the client setup is a later chapter’s job. Now fill the callback in, starting with the simplest edge.

One-to-many: an organization and its invoices

One organization, many invoices, the relationship behind most of a real schema. The foreign key already exists: invoices.organizationId references organizations.id. A relationship has two ends, so you declare it on both tables, one entry per side.

From the organization’s side it has many invoices; from an invoice’s side it belongs to one organization. The code transcribes those two sentences:

export const relations = defineRelations(schema, (r) => ({
  organizations: {
    invoices: r.many.invoices(),
  },
  invoices: {
    organization: r.one.organizations({
      from: r.invoices.organizationId,
      to: r.organizations.id,
    }),
  },
}));

The organizations key lists one relation: invoices: r.many.invoices(). r.many resolves to an array, since an org has many invoices, and the call names the target table. That’s the whole declaration on this side, no columns.

export const relations = defineRelations(schema, (r) => ({
  organizations: {
    invoices: r.many.invoices(),
  },
  invoices: {
    organization: r.one.organizations({
      from: r.invoices.organizationId,
      to: r.organizations.id,
    }),
  },
}));

The invoices key holds its organization relation. r.one resolves to a single object, not an array, because an invoice belongs to exactly one org. The braces hold what the many side didn’t need: the explicit join.

export const relations = defineRelations(schema, (r) => ({
  organizations: {
    invoices: r.many.invoices(),
  },
  invoices: {
    organization: r.one.organizations({
      from: r.invoices.organizationId,
      to: r.organizations.id,
    }),
  },
}));

from is the column on this table that holds the foreign key, invoices.organizationId, the same column you wrote .references() on. Read from as “the FK-holding side.”

export const relations = defineRelations(schema, (r) => ({
  organizations: {
    invoices: r.many.invoices(),
  },
  invoices: {
    organization: r.one.organizations({
      from: r.invoices.organizationId,
      to: r.organizations.id,
    }),
  },
}));

to is the column it points at, the referenced primary key organizations.id. from → to runs in the same direction as the foreign key itself: organizationId → organizations.id. The relation just restates that arrow for the query builder.

← Previous 1 / 1 Next →

The one side is singular, organization; the many side is plural, invoices. Each key is the exact word you type inside with: { … }, so with: { organization: true } returns one object and with: { invoices: true } returns an array. Name them to match what comes back.

from and to appear only on the r.one side, the table that holds the foreign key; Drizzle infers the reverse direction, so the r.many side needs no columns.

Pin the join once, declare both sides

The previous API made you spell the join on both ends, the same two columns named twice in opposite order. v2 drops that: you pin the edge once, on the foreign-key side with from/to, and declare the reverse bare. Drizzle matches the two by table and infers the reverse direction, so the reverse side is just a name and a target:

Bare reverse

organizations: {
  invoices: r.many.invoices(),
},

The common case. With no from/to, Drizzle reads the forward invoices.organization declaration and infers this reverse join from it.

Explicit reverse

organizations: {
  invoices: r.many.invoices({
    from: r.organizations.id,
    to: r.invoices.organizationId,
  }),
},

Legal but redundant once the other side pins the edge. Reach for it only on real ambiguity, like two foreign keys between the same pair of tables (see below).

The bare reverse is still a declaration: omit it and organizations has no invoices relation. That isn’t a compile error, just a silent gap. Someone later writes org.invoices in a with, gets nothing back, and can’t see why.

When one name isn’t enough: alias

Matching by table breaks when two foreign keys connect the same pair of tables, and your schema has a case of it. An invoice points at users through assignedToId, and as the app grows, potentially again through createdById. With two relations to users, table alone can’t say which relation owns which column.

You disambiguate with alias, a label on each relation:

invoices: {
  assignee: r.one.users({
    from: r.invoices.assignedToId,
    to: r.users.id,
    alias: 'assignee',
  }),
  creator: r.one.users({
    from: r.invoices.createdById,
    to: r.users.id,
    alias: 'creator',
  }),
},

Each alias keeps the with keys distinct (with: { assignee: true, creator: true }).

Many-to-many with .through() on both sides

The invoiceTags junction you built last lesson records that this invoice links to this tag, but nothing yet walks those links.

There is no top-level through: option. Reaching an invoice’s tags is two hops, into the junction and back out, so you name a junction column on each hop by chaining .through() onto both from and to:

invoices: {
  tags: r.many.tags({
    from: r.invoices.id.through(r.invoiceTags.invoiceId),
    to: r.tags.id.through(r.invoiceTags.tagId),
  }),
},

Still r.many: an invoice has many tags, and the array shape is unchanged. What’s new is inside the braces, where each side gains a .through() because the join now passes through a third table.

invoices: {
  tags: r.many.tags({
    from: r.invoices.id.through(r.invoiceTags.invoiceId),
    to: r.tags.id.through(r.invoiceTags.tagId),
  }),
},

The entry hop. Read it as: from invoices.id, into the junction via invoiceTags.invoiceId.

invoices: {
  tags: r.many.tags({
    from: r.invoices.id.through(r.invoiceTags.invoiceId),
    to: r.tags.id.through(r.invoiceTags.tagId),
  }),
},

The exit hop. Arrive at tags.id, coming out through invoiceTags.tagId. Both sides chain .through(), each naming its own foreign-key column in the junction: invoiceId in, tagId out.

← Previous 1 / 1 Next →

You declare nothing on invoiceTags itself; the .through() calls reach into it from the outside. The reverse side, an invoice list on each tag, is the mirror image with the two hops swapped: tags: { invoices: r.many.invoices({ … }) }.

Note

This is why the course uses the v2 API. v1 made you declare relations on the junction table and chain two with clauses to hop through it; v2’s .through() collapses that into one direct invoices.tags relation, keeping the junction invisible at the call site. You’ll meet v1 in older codebases: recognize the double-hop shape, and write v2.

The payoff comes next chapter: db.query.invoices.findFirst({ with: { tags: true } }) returns the invoice with a tags: Tag[] array, the junction nowhere in sight.

The junction that became an entity

How you query a linking table depends on which kind it is. A pure junction like invoiceTags is nothing but two foreign keys, so you reach through it with .through(): it carries no data you’d want on its own.

memberships is a promoted entity. It grew a role, a surrogate id, and its own timestamps, so you relate to it directly like any table: one-to-many from each parent into memberships, then r.one back to each parent. You query the membership itself, because its role is data you want. Pure junction, reach through; promoted entity, relate to, the querying half of the decision you made when you gave it an id.

Your relations as a graph

The edges you wrote pair by pair form a graph: tables are nodes, relations are edges, and the query builder walks it just as the explorer below does. Click a table or edge to read it, then press a walk button to trace a nested read the way db.query would.

The domain as a traversal graph

tip Click any node or labelled edge

Click a table or an edge label to read it — or press a walk button below to trace a nested read the way db.query would.

Self-referential relations

A table can relate to itself: a comment that replies to another comment, a category nested under a parent. The same table sits on both ends of the edge.

You declare it with the same from/to you already know, except both columns live on one table:

comments: {
  parent: r.one.comments({
    from: r.comments.parentId,
    to: r.comments.id,
  }),
  replies: r.many.comments({
    from: r.comments.id,
    to: r.comments.parentId,
  }),
},

With a parentId pointing at comments.id, parent walks up to a comment’s one parent and replies walks down to its many children. Both connect comments to comments, so this is the two-edges-same-pair case from earlier: a second self-edge needs an alias. Fetching a whole tree to arbitrary depth is recursion, a query concern that belongs with the next chapter.

When to skip the relations layer

Now that nested reads are effortless, it’s worth saying plainly when not to reach for them.

The relational API earns its keep when the read is a tree: an entity and its children, shaped as a nested object. “An invoice with its line items and tags,” “an organization with its members” — that’s the shape defineRelations and db.query.…({ with }) exist for, and the safe default when a read fans out across related tables.

It is not the tool for reads that aren’t trees. Aggregates (a COUNT of invoices per org), filters that select on a joined table, and projections pulling a few columns from several tables are hand-written joins (db.select(...).leftJoin(...)), coming in the next chapter. The relational builder plans its SQL toward nested objects, not arbitrary result sets.

One caveat: a deeply nested with can emit a heavier query than the short call suggests. The logger: true option from the earlier pgTable and snake_case lesson prints the generated SQL; read it, and later run EXPLAIN ANALYZE, to catch a with whose cost grew out of hand.

Check your understanding

You run db.query.invoices.findFirst({ with: { tags: true } }) and get back an invoice where tags is undefined — even though invoiceTags has both foreign keys, both with onDelete: 'cascade'. What’s the cause?

No tags relation was declared in db/relations.ts. The foreign keys guarantee writes; they don’t tell the query builder how to walk the join.

The two foreign keys need an index before with can follow them.

with only resolves one-to-one relations; many-to-many has to be a hand-written join.

The junction needs a surrogate id column before it can be traversed.

Finally, fill the four blanks in this many-to-many declaration: the two .through() hops and the columns they connect.

Pick the right option from each dropdown, then press Check.

invoices: {
  tags: r.many.tags({
    from: ___.through(___),
    to: ___.through(___),
  }),
},

Check Reset

v2 versus the legacy v1 API

Everything in this lesson is the v2 relations API: defineRelations, the single-call graph, from/to/through, shipping in drizzle-orm@1.0.0-beta.

You’ll still meet v1 in older repos. Recognize its shape: a per-table relations(invoices, ({ one, many }) => …) helper imported from drizzle-orm/_relations, queried through db._query instead of db.query, with the _ prefixes marking it legacy. You write none of it.

Watch video

Nev the Dev migrates a real project from v1 to the v2 defineRelations API live (14 min), collapsing the per-table v1 shape into the single-call graph this lesson teaches.

External resources

Drizzle Relations v2

orm.drizzle.team

The canonical from / to / through reference for defineRelations.

Drizzle relational queries (RQB v2)

orm.drizzle.team

What these relations unlock — the db.query.…({ with }) API, covered next chapter.

Migrating relations v1 → v2

orm.drizzle.team

For when you hit the older per-table relations() shape in the wild.