Drizzle Relations v2

Every detail page in this app wants the same thing: an invoice, but not the bare row. It wants the invoice with its line items and its 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 does not work yet, and that should bother you. You’ve already declared the foreign keys that connect every one of these tables: 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. This lesson is about the second one. By the end you’ll have written a new file, db/relations.ts, that turns the inert links from the last few lessons into a graph the query builder can walk. You won’t write the queries themselves here; those come in the next chapter. You’ll build the map they read.

Why the foreign key isn’t enough

Go back to what a foreign key actually is. When you wrote .references(() => organizations.id, { onDelete: 'restrict' }), you handed Postgres a rule it enforces on every write: reject any invoice whose organizationId doesn’t match a real organization. That’s the whole job of the constraint. It’s a promise the database keeps, but the promise is about writes. It guards what can be inserted and what happens on delete. It 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 declaration of which edges it’s allowed to walk and how to walk them, written in TypeScript, where the builder lives. The foreign key is in the database; the query builder is in your Node process. They never see each 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: it becomes migrations in a later chapter, and every constraint you’ve written lives here.
• db/relations.ts: how the query builder walks it. A pure-TypeScript graph. It never touches Postgres, emits no SQL, and generates no migration. Its one and only 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.

Carry this idea through the rest of the lesson. The foreign key and the relation are two declarations of the same edge, for two different audiences: the database’s integrity engine and Drizzle’s query builder. Declaring one never declares the other. This is the most common beginner mistake with Drizzle. A call like db.query.invoices.findFirst({ with: { tags: true } }) comes back with tags: undefined, the developer stares at the foreign keys sitting right there in the junction table, and can’t see what’s wrong. Nothing is wrong with the foreign keys. 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 from earlier), and index.ts (the client, wired up properly in a later chapter). Add a fourth file beside them: db/relations.ts. Its shape is small and fixed.

You import the tables from ./schema, pass them as one object 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
}));

It takes two arguments, and each is worth being precise about, because everything you write goes inside the second one.

The first argument is the schema object: every table, passed together. That’s organizations, invoices, invoiceLineItems, users, tags, invoiceTags, and memberships. Importing the whole module as schema and handing it over in one go is what gives the builder its knowledge of every table and every column. That’s where autocomplete comes from in the next part.

The second argument is a callback, (r) => ({ … }). It returns a map keyed by table name, and each value is an object describing that table’s relations. The r parameter is the relation builder, and you’ll use it 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 need to spell out the join.

Where does this file plug in? Into the relational query API . When the client gets created, the relations are handed to it on the side:

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

That one option is what carries the graph into the db.query builder. Without it, db.query has no map and every with comes back empty. The rest of the client setup, the connection, the pool, and reading the URL from the environment, is a later chapter’s job; here, that single { relations } line is the only part that matters. Now you can fill the callback in, starting with the simplest edge.

One-to-many: an organization and its invoices

Start with the relationship that makes up most of every real schema: one organization, many invoices. The foreign key already exists, since invoices.organizationId references organizations.id, and a relationship like this has two ends, so you declare both, one on each table.

From the organization’s side, it has many invoices. From an invoice’s side, it belongs to one organization. Read those two English sentences and the code is almost a transcription of them:

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, listing one relation: invoices: r.many.invoices(). r.many says this resolves to an array, since an org has many invoices, and the call names the target table. That’s the entire declaration on this side; no columns yet.

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 says this resolves to a single object, not an array, because an invoice belongs to exactly one org. The braces hold the part 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 points outward, the one that holds the foreign key. It’s invoices.organizationId, the same column you wrote .references() on. Whenever you see from, read it 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 exact same direction as the foreign key itself: organizationId → organizations.id. The relation just restates that arrow for the query builder.

← Previous 1 / 1 Next →

Notice the names. The one side is singular, organization, and the many side is plural, invoices. That’s not decoration: the relation key is the exact word you’ll type inside with: { … } when you finally write the query. You want with: { organization: true } and with: { invoices: true } to read like the data they return, so a singular key returns one object and a plural key returns an array. Name them to match what comes back.

Which side carries from and to

Look again at where from and to actually appeared: only on the invoices side, the r.one relation, the one holding the foreign-key column. The organizations side was a bare r.many.invoices() with no from and no to.

That’s deliberate, and the rule is worth remembering: from/to go on the side that holds the foreign key. A foreign key lives on exactly one of the two tables (here, organizationId is on invoices), so the explicit join belongs there. The other side, the reverse direction, doesn’t need it, because Drizzle can infer it. That’s the next section’s whole point, and it’s the main reason this course teaches the v2 API rather than the old one.

One declaration, both directions

In the previous version of Drizzle’s relations API, you had to spell out the join on both ends. fields and references were repeated on the organization side and the invoice side, the same two columns named twice in opposite order. It was easy to get the direction backwards on one of them, and the redundancy bought you nothing.

The v2 API drops that. You pin the edge once, on the foreign-key-holding side (with from/to), and declare the reverse side bare. Drizzle matches them by table and infers the reverse direction for you. So in practice the reverse side is just a name and a target:

Bare reverse

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

The common case. No from/to: Drizzle reads the forward invoices.organization declaration and infers this reverse join from it. This is what you’ll write almost every time.

Explicit reverse

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

Legal, occasionally clearer, but redundant when the other side already pins the edge. Reach for it only when there’s genuine ambiguity to resolve; see the note below on two foreign keys between the same pair of tables.

The bare reverse is the default, but notice that you did declare it: you didn’t leave organizations without its invoices relation just because the forward side already pinned the join. Declare both ends, even when one is a bare line. Omitting the reverse isn’t an error you’ll catch at compile time; it’s a silent gap, a relation that simply doesn’t exist. Weeks later someone writes org.invoices in a with, gets nothing back, and spends an afternoon wondering why the link “isn’t working” when the real answer is that this direction of it was never declared. Declaring both ends keeps the graph walkable whichever way the next reader comes at it.

When one name isn’t enough: alias

There’s one case where Drizzle genuinely can’t sort the relations out by table, and your own schema already has an example of it. An invoice points at users twice: once through assignedToId, and, when the app grows, potentially again through a createdById. That’s two foreign keys between the same pair of tables. If you declare two relations to users, matching by table is ambiguous: which relation owns which column?

You disambiguate by hand with alias, a label on each relation so they stay distinct:

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',
  }),
},

That’s the entire mechanic: when two relations connect the same two tables, give each an alias so the with keys stay unambiguous (with: { assignee: true, creator: true }). This one example covers it. Modeling tangled multi-edge graphs is a topic of its own, and you only need to recognize the escape hatch when you trip over it.

Many-to-many: walking the junction with through

The junction table you built last lesson, invoiceTags, with its two foreign keys and a composite primary key, was inert. It records that this invoice is linked to this tag, but nothing yet walks those links. That’s what this section adds.

There’s a natural guess here, and it’s worth seeing why it’s wrong. You might expect a top-level option, something like r.many.tags({ through: invoiceTags }), read as “go to tags, through the junction.” That’s not the shape. The junction isn’t a single waypoint; getting from an invoice to its tags is two hops, into the junction and then out of it, so you name the junction column on each hop. .through() chains onto both the from and the 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, since an invoice has many tags and the array shape is unchanged. What’s new is entirely inside the braces: the join now goes through a third table, so each side gains a .through().

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

The entry hop. Start at this table’s primary key, invoices.id, and go through its column in the junction, invoiceTags.invoiceId. 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 the target’s primary key, tags.id, coming out through the other junction column, invoiceTags.tagId. The symmetry is the point: both sides chain .through(), each naming its own foreign-key column in the junction, invoiceId on the way in and tagId on the way out.

← Previous 1 / 1 Next →

Read the whole thing aloud and it narrates the path: from invoices.id, hop through invoiceTags.invoiceId into the junction; then to tags.id, arriving through invoiceTags.tagId out the other side. The junction gets named twice because the builder traverses it as two joins. And notice what you didn’t write: nothing on invoiceTags itself. For this traversal the junction needs no relations of its own, because the .through() calls reach into it from the outside. (The reverse side, an invoice list hanging off each tag, is the mirror image: tags: { invoices: r.many.invoices({ … }) } with the two hops swapped.)

Note

This is the main reason the course standardizes on the v2 API. In the old version, walking a junction meant declaring relations on the junction table pointing at each parent, then chaining two separate with clauses to hop through it. v2’s .through() collapses that into one direct invoices.tags relation, so the junction stays invisible at the call site. You’ll meet v1 only in older tutorials and codebases; recognize the double-hop shape so it doesn’t confuse you, and write v2.

Here’s what this one declaration buys. In the next chapter, db.query.invoices.findFirst({ with: { tags: true } }) returns the invoice with a tags: Tag[] array hanging off it, the junction table nowhere in sight and the two hops folded away by the builder. You don’t write that query in this lesson, but that’s the payoff the .through() chain exists to enable, and it’s why the junction stopped being inert the moment you declared this relation.

The junction that became an entity

Last lesson drew a line between two kinds of linking table, and that line decides how you query each one. A pure junction like invoiceTags, nothing but two foreign keys, is what .through() is for: you walk through it because it carries no data you’d ever want on its own.

But memberships was different. It started as a user-to-organization link and grew a role, a surrogate id, and its own timestamps, so it became a first-class entity, a thing in the domain rather than a wire between two things. So you don’t reach through it. You relate to it directly, the same way you’d relate to any table: one-to-many from each parent into memberships (an org has many memberships, a user has many memberships), then r.one from memberships back to each parent. You query the membership as itself, because its role is data you want, instead of treating it as a hidden hop. Pure junction, reach through; promoted entity, relate to. That’s the querying half of the decision you made when you gave it an id.

The whole graph, as a graph

You’ve now written the edges one pair at a time. Step back and see them together. The file you’re building is this graph, with tables for nodes and relations for edges, and the query builder walks it exactly the way the explorer below is about to.

In the explorer below, click any table to see what it holds, and click an edge label to read the relation that connects two tables and which direction it runs. Then press a traversal button: each one walks a nested read the way db.query would, lighting up the path from a starting table out to its related rows. The first walks an invoice to its line items, a plain one-to-many. The second walks an invoice to its tags, through the invoice_tags junction, so you can watch the two hops the .through() chain folds together.

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.

That traversal, invoice into the junction and out to tags, is the .through() chain animated. The graph and the defineRelations file say the same thing in two notations; you just wrote the second one.

Self-referential relations

There’s one more shape to cover, and it looks unusual the first time even though it turns out to be nothing new. Sometimes a table relates to itself: a comment that replies to another comment, a category nested under a parent category, an org chart where each employee reports to another employee. The same table sits on both ends of the edge.

You declare it with the same from/to you already know. The only difference is that 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 column that references comments.id, parent walks up (a comment’s one parent) and replies walks down (a comment’s many children). Because both relations connect comments to comments, this is exactly the two-edges-same-pair case from earlier, so if you add a second self-edge, reach for alias to keep them apart. What this does not cover is fetching a whole tree to arbitrary depth; recursion is a query concern, not a relations-declaration one, and it belongs with the queries in the next chapter.

When to skip the relations layer

You’ve just learned a tool that makes nested reads effortless, so it’s worth saying plainly when not to reach for it, before you start forcing every read through it in the next chapter.

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 exact shape defineRelations and db.query.…({ with }) exist for, and it’s the safe default for reads that fan out across related tables.

It is not the tool for reads that aren’t trees. Aggregates (a COUNT of invoices per org, a SUM of line-item totals), filters that select on a joined table, and projections that pull a handful of columns from several tables at once are hand-written joins (db.select(...).leftJoin(...)), and you’ll meet them in the next chapter too. The relational builder plans its own SQL toward assembling nested objects; it isn’t built to shape arbitrary result sets.

One caveat is worth naming, even though this lesson won’t solve it: because the relational API plans its own SQL, a deeply nested with can emit a heavier query than the short call would suggest. The flag that lets you see the SQL it generates is the logger: true option from the earlier pgTable lesson. Reading the emitted query, and later running EXPLAIN ANALYZE on it, is how you catch a with whose cost grew out of hand. For now, just know the relational API is one tool with a clear job: nested tree reads. Use it there, and drop to joins elsewhere.

Check your understanding

These two checks aim at the two places this topic most often trips people: the cause of a silent undefined, and the direction of the through hops.

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.

Now test the through syntax. 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

Finally, a quick vocabulary check. Match each piece of the relations API to what it means.

Match each part of the Relations v2 API to what it does. Click an item on the left, then its match on the right. Press Check when done.

r.one

Relation resolves to a single object

r.many

Relation resolves to an array

.through()

Walk a junction table on the way across

from

The column on the foreign-key-holding side

to

The referenced primary key it points at

alias

Disambiguates two relations between the same pair of tables

Check Reset

A version note worth keeping straight

Everything in this lesson is the v2 relations API: defineRelations, the single-call graph, and from/to/through. It ships in drizzle-orm@1.0.0-beta, the forward-looking version this chapter committed to from the start.

The reason to mention it at all is so older code doesn’t confuse you. The v1 API is now labelled [OLD] in Drizzle’s docs: it used a per-table relations(invoices, ({ one, many }) => …) helper, its import has moved to drizzle-orm/_relations, and its query builder is now db._query rather than db.query. You’ll write none of it, but you’ll see it in tutorials and existing repos, and recognizing the per-table shape (and the _ prefixes that mark it as legacy) is enough to not get thrown. v2 is what you write, for the reasons this lesson showed: one declaration per edge instead of two, native junction traversal, and cleaner type inference.

Watch video

Nev the Dev migrates a real project from the v1 relations helper to the v2 defineRelations API live (14 min). Watch the per-table v1 shape collapse 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.