Chapter 38Lesson 3

Nested reads with the relational API

Read an entity with its related rows as one nested object using Drizzle's relational query builder, instead of folding flat joins by hand.

You can already join two tables. An invoice detail page, one of the most ordinary screens in the app, needs three of them: the invoice, its line items as a list, and the name of the organization it belongs to. In the previous lesson you’d write that read as a leftJoin. It returns every column, but in the wrong shape for the page, and folding it into the right shape by hand is the chore this lesson removes.

When a join gives you the wrong shape

Here is the join you’d write for that detail page with the SQL builder from the last lesson.

const rows = await db
  .select({
    invoice: invoices,
    lineItem: invoiceLineItems,
    organization: organizations,
  })
  .from(invoices)
  .leftJoin(invoiceLineItems, eq(invoiceLineItems.invoiceId, invoices.id))
  .leftJoin(organizations, eq(organizations.id, invoices.organizationId))
  .where(eq(invoices.id, invoiceId));

It runs, but the result carries two costs.

First, it is flat and duplicated. SQL returns a rectangle of rows, so an invoice with three line items comes back as three rows, the invoice and organization columns copied onto each. The page wants one invoice with an array of three line items inside it, so the app folds the rectangle back by hand: group rows by invoice id, collect the line items into an array, peel one copy of the organization off the top. That regrouping reduce is boilerplate you rewrite for every detail screen, and where off-by-one bugs hide.

Second, left-join nullability. Every column on the right side of a leftJoin is typed T | null, because the join must allow for an invoice with no line items. So organization.name, always present for a real invoice, arrives as string | null, and you thread null checks through code that should just render a name.

You asked for a rectangle when you wanted a tree.

What the join returns

invoice.id invoice.total lineItem.description org.name

inv_1 $300.00 Design Acme

inv_1 $300.00 Build Acme

inv_1 $300.00 QA Acme

1 invoice × 3 line items = 3 rows invoice + org repeated

What the page wants

invoice {

id: "inv_1"

total: "$300.00"

organization { name: "Acme" }

lineItems: [

{ description: "Design" }

{ description: "Build" }

{ description: "QA" }

]

}

Same invoice, two shapes. The join returns a flat rectangle, copying the invoice and organization columns onto every line-item row, so the app must regroup it by hand. The page wants the tree on the right, where each value appears once. RQB returns that tree directly.

For a tree, one entity plus its related rows, you want to describe the tree directly and let Drizzle build the SQL that returns it. That is what the relational query builder (RQB) is for: it reads the relations graph you declared with defineRelations in the schema chapter. The SQL builder is operation-first, you compose the join, grouping, and reshaping yourself; RQB is shape-first, you declare the entity and its relations and Drizzle plans the SQL.

findMany, findFirst, and the `with` traversal

RQB has two entry points, carrying the single-vs-many split from this chapter’s first lesson.

const all = await db.query.invoices.findMany(); // Invoice[]
const one = await db.query.invoices.findFirst(); // Invoice | undefined

findMany returns an array; with no options it reads the same rows as db.select().from(invoices). findFirst adds a LIMIT 1 and returns the matching row, or undefined (not null, and it does not throw) when nothing matches. And db.query.<table> exists for every table in your schema, so db.query.organizations, db.query.users, and the rest are all there.

You reach for RQB for its next option, with, which loads related rows into the result.

const invoice = await db.query.invoices.findFirst({
  where: { id: invoiceId },
  with: { lineItems: true, organization: true },
});

findFirst on invoices: one invoice, or undefined if no row has that id.

const invoice = await db.query.invoices.findFirst({
  where: { id: invoiceId },
  with: { lineItems: true, organization: true },
});

A plain object filter: keys are columns, a bare value means equals, so this reads as WHERE id = invoiceId. The full where object comes two sections down.

const invoice = await db.query.invoices.findFirst({
  where: { id: invoiceId },
  with: { lineItems: true, organization: true },
});

The traversal. Each key is a relation you declared with defineRelations, and true loads it. lineItems is a many relation, so it returns an array; organization is a one relation, so it returns a single object.

const invoice = await db.query.invoices.findFirst({
  where: { id: invoiceId },
  with: { lineItems: true, organization: true },
});

You wrote no type, yet invoice is fully typed Invoice & { lineItems: LineItem[]; organization: Organization }, inferred from the relations and the with keys.

1 / 1

The keys inside with are relation names, exactly as you declared them in defineRelations. A many relation resolves to an array, a one relation to a single object. A key that isn’t a declared relation does nothing and raises no runtime error, but TypeScript autocompletes only real relations, so a typo surfaces in the editor. Note that a foreign key and a relation are separate: the foreign key validates the reference at write time, while the relation is what you declare to tell RQB how to walk it at read time.

Nesting deeper and picking columns

A tree read has two independent knobs. with controls depth, how far down the relations you walk; columns controls width, how many fields you carry at each level.

Relations chain, so with nests. From a line item you can walk back to its invoice, and on to that invoice’s organization:

const lineItem = await db.query.invoiceLineItems.findFirst({
  where: { id: lineItemId },
  with: {
    invoice: {
      with: { organization: true },
    },
  },
});

Postgres assembles the whole tree (the line item, its invoice, and the invoice’s organization) in one statement, and the inferred type nests to match: lineItem.invoice.organization is typed all the way down.

For width, you rarely want every column, only the handful the screen renders. The columns option narrows the selection:

const invoice = await db.query.invoices.findFirst({
  where: { id: invoiceId },
  columns: { id: true, amountDue: true, status: true },
});

The result type narrows to exactly those three keys, the same “the type follows the projection” rule from the first lesson, now on RQB. It isn’t only a type trick: Drizzle does the partial select in SQL, so unselected columns never leave the database. There’s also an exclude form, columns: { internalNote: false }, which keeps everything except internalNote. The modes don’t mix: mark one column true and you’re in include-mode, where only the true columns come back.

The same columns option lives on a relation inside with, which is where it earns its keep. To render a card you need an organization’s id and name, not its full row:

with: {
  organization: true,
},

Everything. Loads every column: fine when you use them, wasteful when you don’t.

with: {
  organization: { columns: { id: true, name: true } },
},

Two columns. The same narrowing on the child: organization is typed { id: string; name: string }, and only those two columns leave the database.

with: {
  lineItems: {
    columns: { id: true, description: true, amountDue: true },
    with: { invoice: { columns: { id: true } } },
  },
},

Every level, shaped independently. Depth and width compose freely: each relation picks its own columns and its own children.

For any read that feeds a specific piece of UI, project down to the columns that UI renders. The saving (two columns instead of twenty for a dropdown label) compounds on a nested read, paid once per child row.

Filtering and ordering with the filter object

So far the only filter you’ve seen is where: { id: invoiceId }. In Drizzle’s relational builder, where is an object, and that object trips up readers of older tutorials.

The filter object has a few shapes, and they stack.

A bare value means equality. The common case, column = value:

where: { status: 'sent' }

An operator object compares. For anything beyond equality, the value becomes an object keyed by operators:

where: { amountDue: { gt: '0' } }

The operators are eq, ne, gt, gte, lt, lte, in, notIn, like, ilike, isNull, isNotNull, and their array forms. The money value is the string '0', not the number 0: amountDue is a numeric column, which Drizzle represents as a string end to end, so its operands are strings too.

Several keys are AND-ed. Multiple keys in one object combine with AND:

where: { organizationId: orgId, status: 'sent' }

Boolean combinators are keys too. OR, an explicit AND, or a NOT are capitalized keys whose values are arrays (or, for NOT, a nested filter):

where: { OR: [{ status: 'sent' }, { status: 'paid' }] }

AND: [...] and NOT: {...} work the same way.

RAW is the escape hatch. For a predicate the object can’t express, such as a Postgres operator the builder doesn’t model, drop to a sql fragment under the RAW key:

where: { RAW: (t) => sql`${t.amountDue} > ${threshold}` }

The callback hands you the table, and the sql template parameterizes as always: every ${value} becomes a bound $1, so there’s no injection surface. Reach for RAW last, only when the object can’t say what you mean.

Ordering is an object too: each key a column, each value a direction.

orderBy: { createdAt: 'desc', id: 'desc' }

Keys sort in the order you write them. This pairs createdAt with id as a tiebreaker: createdAt isn’t unique, so on its own the order of ties is undefined and can shuffle between runs. Adding the primary key makes the order total and stable, which cursor pagination will lean on later.

limit and offset mean what they do in the SQL builder: limit: 20, offset: 40 returns twenty rows starting after the first forty.

Here’s a realistic read using the whole vocabulary at once: every invoice that’s been sent and still owes money, newest first, with a lightweight organization attached for the row label.

const sent = await db.query.invoices.findMany({
  where: { organizationId: orgId, status: 'sent', amountDue: { gt: '0' } },
  orderBy: { createdAt: 'desc', id: 'desc' },
  limit: 20,
  with: { organization: { columns: { name: true } } },
});

Three keys, all AND-ed: scope to this tenant’s organization, status equals 'sent' (bare value), and amountDue greater than zero (operator object). The tenant scope is carried by hand on every multi-tenant read for now.

const sent = await db.query.invoices.findMany({
  where: { organizationId: orgId, status: 'sent', amountDue: { gt: '0' } },
  orderBy: { createdAt: 'desc', id: 'desc' },
  limit: 20,
  with: { organization: { columns: { name: true } } },
});

The operator object up close: operators are keys, and the value is a string because amountDue is numeric.

const sent = await db.query.invoices.findMany({
  where: { organizationId: orgId, status: 'sent', amountDue: { gt: '0' } },
  orderBy: { createdAt: 'desc', id: 'desc' },
  limit: 20,
  with: { organization: { columns: { name: true } } },
});

Newest first, with the primary key as a tiebreaker so equal createdAt values never shuffle.

const sent = await db.query.invoices.findMany({
  where: { organizationId: orgId, status: 'sent', amountDue: { gt: '0' } },
  orderBy: { createdAt: 'desc', id: 'desc' },
  limit: 20,
  with: { organization: { columns: { name: true } } },
});

Page size, plus a child projected down to one column: exactly what a row label needs and nothing more.

1 / 1

A quick drill on the object shape. Fill each blank so the read returns this organization’s paid invoices, largest first.

Fill the filter-object and order slots so the read returns this org's paid invoices, largest amount first. Pick the right option from each dropdown, then press Check.

const paid = await db.query.invoices.findMany({
  where: {
    organizationId: orgId,
    status: ___,
    amountDue: { ___: '0' },
  },
  orderBy: { amountDue: ___, id: 'desc' },
});

Filtering the children, filtering by the children

The same where object does two jobs, depending on where you put it.

Put where inside a with and it filters which children load; it never drops the parent. Say you want every invoice, but on each one only its line items with a non-zero quantity:

with: {
  lineItems: { where: { quantity: { gt: 0 } } },
},

An invoice whose line items are all quantity zero still comes back, just with lineItems: []. It’s the same where object as the top level, scoped to the relation, and it composes with the relation’s orderBy and limit: with: { comments: { orderBy: { createdAt: 'desc' }, limit: 3 } } reads as “the three most recent comments per post.”

Put a relation as a key in the parent’s where and it filters which parents survive, based on their children. v1 couldn’t express “invoices that have a line item over $100” in the relational where at all; you dropped to the SQL builder and an exists() subquery. The current builder takes the parent-by-child filter directly:

// invoices that have at least one line item over $100
where: { lineItems: { amountDue: { gt: '100' } } }

// invoices that have any line items at all
where: { lineItems: true }

A relation name in where asks a question about existence: the { … } form constrains what counts as a match, the bare true form asks whether any related row exists at all.

The two reads share a predicate and look almost identical. The position is the only difference:

Filter the children
Filter the parents

const invoices = await db.query.invoices.findMany({
  where: { organizationId: orgId },
  with: {
    lineItems: { where: { amountDue: { gt: '100' } } },
  },
});

Keeps every invoice. The where lives inside with, trimming each invoice’s lineItems to the over-$100 ones. An invoice with none still returns, just with lineItems: [].

const invoices = await db.query.invoices.findMany({
  where: {
    organizationId: orgId,
    lineItems: { amountDue: { gt: '100' } },
  },
});

Drops invoices. The relation is a key in the top-level where, so only invoices that have a matching line item come back.

Relation filters cover the common “has a matching child” case but have a ceiling: predicates correlated across several relations, or aggregate thresholds like “invoices with more than five line items,” still need the SQL builder’s exists() or a having clause. Counting children is the next lesson.

Many-to-many without naming the junction

Tags are a many-to-many: an invoice has many tags, and a tag is on many invoices. The two are wired through the invoiceTags junction table, which holds two foreign keys and a composite primary key and no columns of its own. In the schema chapter you declared invoices.tags as a many relation that walks through that junction.

const invoice = await db.query.invoices.findFirst({
  where: { id: invoiceId },
  with: { tags: true },
});
// invoice.tags is Tag[] — invoiceTags never appears

You never name invoiceTags, never write the two-hop join through it, and never see it in the result; the through declaration you wrote once does all of that. A many-to-many traversal now costs one with key.

In the previous lesson the same read meant two explicit innerJoins, invoices to invoiceTags and invoiceTags to tags, producing a flat, duplicated result you regrouped by hand. RQB collapses both joins and the regroup into with: { tags: true }, the shape it improves most.

The through-walk is only for a pure junction. Add a column, like a membership’s role, and the junction becomes a real entity: you no longer walk through it invisibly, you relate to it and traverse it as a first-class relation. A pure junction you walk through; a junction with data you walk to.

One statement, no N+1

RQB is the default for tree reads because of how it gets there: a findMany with with, no matter how deeply nested, compiles to one SQL statement. Drizzle uses correlated subqueries that aggregate each relation’s rows into JSON, then assembles the nested object from that single result. There is no round trip per parent row.

Building the same list by hand looks reasonable:

const rows = await db.select().from(invoices); // 1 query
const detail = await Promise.all(
  rows.map(async (invoice) => ({
    ...invoice,
    lineItems: await db
      .select()
      .from(invoiceLineItems)
      .where(eq(invoiceLineItems.invoiceId, invoice.id)), // 1 query — per invoice
  })),
);

One query for the invoices, then one more for each invoice’s line items. For one hundred invoices that’s 1 + N round trips, each with its own latency. This is the N+1 problem , a common reason a list endpoint that felt fine at ten rows crawls at a thousand. RQB does the identical read in one round trip, and the N+1 never arises because you never wrote the loop.

Turn on the query logger from the schema chapter, drizzle({ client, logger: true, … }), run the with query, and you’ll see a single statement with subqueries, not a burst of one-per-parent selects.

Your server db.select()

select … from invoices

Postgres 100 invoices

1 round trip so far

Naive read, step one: one query fetches all the invoices. Nothing looks wrong yet.

Your server rows.map(…)

one query per invoice × N

Postgres line items

1 + N round trips

Step two: the loop. Each invoice gets its own query for line items, N more round trips. This is the N+1.

Your server db.query … with

one statement, nested JSON

Postgres invoices + items

1 round trip

RQB: describe the tree and Drizzle emits one statement whose subqueries aggregate each relation into JSON. One round trip at any depth, so the N+1 never arises.

One caveat: because RQB plans its own SQL, a deeply nested with over wide tables can emit a heavy JSON-aggregating query, so one statement isn’t always a cheap one. The answer isn’t to hand-roll the loop, which trades the heavy query back for the N+1 — it’s to measure: logger: true to read the SQL, and the query-plan tools in the next chapter to see what it costs.

The result type is your prop type

A nested read needs a nested type, and you should never hand-write one. Recall from the schema chapter that typeof invoices.$inferSelect is flat: Invoice has organizationId: string and nothing relational, no organization, no lineItems, no tags. Pass a with query result into a Server Component and $inferSelect is the wrong type, because it describes the row, not the tree.

Filling the gap by hand is tempting:

type InvoiceDetail = {
  id: string;
  amountDue: string;
  organization: { id: string; name: string };
  lineItems: { id: string; description: string }[];
  // …and every field, kept in sync by hand, forever
};

This restates fields the schema already knows, exactly the hand-typed interface the schema chapter worked to eliminate. Rename a column or change one with key and it silently goes stale.

The RQB call’s return type already is the nested shape, so derive your type from the query instead of describing it twice:

const getInvoiceDetail = (id: string) =>
  db.query.invoices.findFirst({
    where: { id },
    with: { lineItems: true, organization: true },
  });

type InvoiceDetail = NonNullable<Awaited<ReturnType<typeof getInvoiceDetail>>>;

The three wrappers compose to the fully-typed nested shape, generated rather than written. Change the with or rename a column and the type updates on its own, so the component prop, the function return, and the query can never drift apart.

The rule is derive, don’t declare: the schema is the source of truth for a row’s type, the query for its own result type. These read helpers later move into a db/queries/ folder; here the point is only the type extraction.

When to reach for the SQL builder instead

One test draws the boundary between the two APIs: is the read a tree?

Reach for RQB, db.query.…({ with }), when the result is an entity plus its related rows as a nested object: detail pages, lists with their children, anything you’d draw as a tree. It’s the N+1-safe default, and as of this lesson it also filters parents by a fact about their children.

Drop to the SQL builder, db.select(...).leftJoin(...) from the previous lesson, when the read is not a tree:

Aggregates: a COUNT, SUM, or AVG with GROUP BY. RQB returns rows and relations, not rolled-up totals. That’s the next lesson.
Flat, irregular projections: a report row pulling a few columns from three tables into one record.
Aggregate-existence predicates: “invoices with more than five line items.” Relation filters answer has a matching child, not has this many; counting is having on the SQL builder.
Set operations, window functions, CTEs: layered SQL a few lessons ahead.

The two mix freely within one feature: an invoice detail page can fetch the invoice with its line items and tags through RQB and compute “total billed this year” with a db.select aggregate, on the same screen.

Match each read to the API whose result shape fits it. Click an item on the left, then its match on the right. Press Check when done.

An invoice with its line items and its tags

RQB — with

Revenue per month across all invoices

SQL builder — aggregate with GROUP BY

Each post with only its 3 newest comments

RQB — with plus per-relation orderBy and limit

Invoices that have at least one paid line item

RQB — relation filter in the parent where

A flat report: invoice id, org name, line-item count

SQL builder — join with a projected/aggregated row

Check your understanding

Return invoice 1 with its line items, ordered by line-item id.

Return invoice 1 together with its line items, ordered by line-item id. The select and the join are written for you — finish the where so only invoice 1's rows come back, then order by the line-item id. Invoice 2's line item must not appear.

View schema & seed rows

Schema (Drizzle)

export const organizations = pgTable('organizations', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
});

export const invoices = pgTable('invoices', {
  id: integer('id').primaryKey(),
  organizationId: integer('organization_id')
    .notNull()
    .references(() => organizations.id),
  status: text('status').notNull(),
  amountDue: numeric('amount_due').notNull(),
});

export const invoiceLineItems = pgTable('invoice_line_items', {
  id: integer('id').primaryKey(),
  invoiceId: integer('invoice_id')
    .notNull()
    .references(() => invoices.id),
  description: text('description').notNull(),
  amountDue: numeric('amount_due').notNull(),
});

Seed rows (SQL)

INSERT INTO organizations (id, name) VALUES (1, 'Acme');

INSERT INTO invoices (id, organization_id, status, amount_due) VALUES
  (1, 1, 'sent', '600.00'),
  (2, 1, 'sent', '90.00');

INSERT INTO invoice_line_items (id, invoice_id, description, amount_due) VALUES
  (1, 1, 'Design', '200.00'),
  (2, 1, 'Build',  '300.00'),
  (3, 1, 'QA',     '100.00'),
  (4, 2, 'Hosting','90.00');

Reference solution

return await db
  .select({
    invoiceId: invoices.id,
    status: invoices.status,
    lineItemId: invoiceLineItems.id,
    description: invoiceLineItems.description,
  })
  .from(invoices)
  .leftJoin(invoiceLineItems, eq(invoiceLineItems.invoiceId, invoices.id))
  .where(eq(invoices.id, 1))
  .orderBy(invoiceLineItems.id);

This returns three rows, with invoice 1’s status copied onto each: the flat rectangle you’d fold back into one nested invoice by hand. The RQB form, db.query.invoices.findFirst({ where: { id: 1 }, with: { lineItems: { orderBy: { id: 'asc' } } } }), returns that tree directly.

Next, a question that hinges on the silent-relation rule.

You write db.query.invoices.findFirst({ with: { tags: true } }). The invoice comes back, but tags is on neither the result nor the inferred type — no error, no warning. The invoiceTags junction is in your schema with both foreign keys in place, and editor autocomplete didn’t offer tags when you typed it. Which fix makes tags appear?

Add invoices.tags to defineRelations — the foreign keys are in the schema, but the relation that tells RQB how to traverse them isn’t.

Swap findFirst for findMany, since with only works on the many-row entry point.

List the junction explicitly: with: { invoiceTags: { with: { tags: true } } }.

Drop RQB for this read and walk the junction with two innerJoins on the SQL builder.

The foreign keys are a write-time guarantee that the reference is valid; they don’t tell RQB how to walk it. The relation is a separate, read-time declaration — without invoices.tags in defineRelations, there’s nothing for with to traverse, so the key is silently absent instead of an error. That silence is why the tell was the missing autocomplete: the editor lists only declared relations, so a relation you never declared simply never appears.

Finally, a pass over the parts most likely to catch you out.

Each claim is about where a relation lives in an RQB query — and what that position does. Mark each statement True or False.

with: { lineItems: { where: { amountDue: { gt: '100' } } } } can drop an invoice that has no matching line item from the result.

A where inside with filters the child array, never the parent. An invoice with no matching line item still comes back — with lineItems: [].

where: { lineItems: { amountDue: { gt: '100' } } } at the top level drops invoices that have no matching line item.

A relation as a key in the parent’s where is an existence filter — only invoices that have a matching line item survive; the rest vanish.

A single findMany with nested with still compiles to one SQL statement.

RQB uses correlated subqueries that aggregate each relation’s rows into JSON, so any depth of with is one statement — that’s what makes it N+1-safe by construction.

The current db.query where takes a (table, operators) => … callback.

That’s the v1 form, now relocated to db._query. The current builder takes a filter object — { column: value | { op }, AND, OR, NOT, RAW }.

External resources

Drizzle — Relational queries (v2)

orm.drizzle.team

The canonical reference for findMany / findFirst, the object where and orderBy, with, and columns.

Drizzle — Relations v1 to v2

orm.drizzle.team

If a tutorial shows the callback where, this documents the move to the object syntax this lesson uses.

Drizzle — Relations (v2)

orm.drizzle.team

The relations graph these queries consume, including predefined relation filters.

Nested reads with the relational API

When a join gives you the wrong shape

findMany, findFirst, and the with traversal

Nesting deeper and picking columns

Filtering and ordering with the filter object

Filtering the children, filtering by the children

Many-to-many without naming the junction

One statement, no N+1

The result type is your prop type

When to reach for the SQL builder instead

Check your understanding

External resources

findMany, findFirst, and the `with` traversal