Nested reads with the relational API

You can already join two tables. Consider one of the most ordinary screens in the whole invoicing app, an invoice detail page. It needs the invoice itself, its line items rendered as a list, and the name of the organization it belongs to, which is three tables on one page. In the previous lesson you wrote that read as a leftJoin, and it worked: all the columns came back. But the shape of what came back was wrong for the page, and fixing that 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 you learned in 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 when you look at what comes back, two costs show up at once.

The first is that the result is flat and duplicated. SQL only knows how to return a rectangle of rows. An invoice with three line items comes back as three rows: the same invoice columns and the same organization columns are copied onto each one, and only the line-item part differs. The page wants one invoice with an array of three line items inside it. So the app has to take that rectangle and fold it back into the nested object by hand: group the rows by invoice id, collect the line items into an array, and peel one copy of the organization off the top. That regrouping reduce is boilerplate you rewrite for every detail screen, and it’s where off-by-one bugs hide.

The second cost is left-join nullability. Every column on the right side of a leftJoin is typed T | null, because the join has to account for an invoice with no line items at all. So even organization.name, a column you know is always there for a real invoice, arrives as string | null, and you end up threading null checks through code that should simply render a name.

Neither cost is the join’s fault. A join’s job is to return columns from more than one table, and it does that. The mismatch is that you asked for a rectangle when what you actually wanted was 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

RQB returns the
right shape directly

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 — the invoice and organization columns copied onto every line-item row, which the app must regroup by hand. The detail page wants the tree on the right, where each value appears exactly once. RQB returns that tree directly.

When the read is a tree, meaning one entity plus its related rows, you don’t want to describe a join and then reshape the result. 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 doesn’t invent the relationships out of thin air. It reads the relations graph you declared with defineRelations back in the schema chapter, and this is where that declaration pays off.

One idea ties the rest of this lesson together: describe the tree, get the tree. With the SQL builder you compose the operation, choosing these columns, this join predicate, this grouping, and you shape the result yourself. With RQB you declare the shape, this entity with these relations filtered like this, and Drizzle plans the SQL. So the SQL builder is the imperative, operation-first read, and RQB is the declarative, shape-first read. Every “which one do I use?” decision later in this lesson follows from that distinction.

findMany, findFirst, and the with traversal

RQB has two entry points, and you already know their semantics from the single-vs-many reads in the first lesson of this chapter.

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

findMany returns an array of rows. With no options it returns the same set of invoices db.select().from(invoices) would give you, just through the relational entry point. findFirst adds a LIMIT 1 and returns a single row, or undefined when nothing matches. Note that it returns undefined, not null, and it does not throw. This is the relational-API version of the const [row] = await db.select()…limit(1) idiom from the first lesson: the same single-row read, except you don’t destructure an array, you get the row directly. And db.query.<table> exists for every table in your schema. The entry is generated from the schema the same way db.select is, so db.query.organizations, db.query.users, and the rest are all there too.

That covers the plain read. The reason you reach for RQB is the 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 },
});

The RQB entry on invoices, with findFirst. You get one invoice back, or undefined if no row has that id.

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

The filter is a plain object: keys are columns, and a bare value means equals. We’ll do the full where object two sections down; for now read it as WHERE id = invoiceId.

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

This is the traversal. Each key is a relation you declared with defineRelations in the schema chapter, and true means load it. lineItems is a many relation, so it comes back as an array. organization is a one relation, so it comes back as a single object.

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

No type was written by hand, yet invoice is fully typed: Invoice & { lineItems: LineItem[]; organization: Organization }. The nested type is inferred from the relations and the with keys, and the end of the lesson builds directly on that.

← Previous 1 / 1 Next →

Two rules from that block carry the whole API.

The key inside with is the relation name, exactly as you declared it. When you wrote defineRelations in the schema chapter, you named each relation: lineItems, organization, tags. Those names are the keys you put in with. A many relation resolves to an array, and a one relation resolves to a single object. This is why it mattered back then to name relations so they read like the data: the name you chose is the name you type here, and the result reads like the sentence “the invoice, with its line items and its organization.”

A with key that isn’t a declared relation does nothing, and it doesn’t error. This catches people often, so it’s worth pinning down. If you write with: { tags: true } but never declared the tags relation, tags simply won’t appear in the result and won’t appear in the inferred type, with no runtime error. Your real signal is the editor: TypeScript autocompletes only the relations you declared, so a typo or a missing relation shows up as “this key isn’t allowed” instead of a wrong result at runtime. This rests on a distinction from the schema chapter. A foreign key is a write-time guarantee that the reference is valid, while the relation is a separate, read-time declaration that tells RQB how to walk it. Having the foreign key does not give you the with key; you declare the relation for that.

Nesting deeper and picking columns

Depth and width are two independent knobs on a tree read. with controls depth: how far down the relations you walk. columns controls width: how many fields you carry at each level. The next two examples add one knob, then the other.

Relations chain, so with nests. Starting from a line item, you can walk back to the invoice it belongs to, and from that invoice on to its organization:

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

Postgres assembles that entire 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 fully typed all the way down. You declare the shape, and the depth is just how deeply you nest with.

Now for width. Most of the time you don’t want every column, only the handful the screen renders. The columns option narrows the selection at the top level:

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. And this isn’t only a type trick: Drizzle does the partial select in the SQL, so the unselected columns never travel from the database. There’s an exclude form too, where columns: { internalNote: false } keeps everything except internalNote. Include-mode and exclude-mode don’t mix, though: the moment you mark one column true, you’re in include-mode and only the true columns come back.

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

Whole row

with: {
  organization: true,
},

Everything. Loads all of the organization’s columns, which is fine when you genuinely use them and wasteful when you don’t.

Just what the card needs

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

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

Depth + projection

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

Every level, shaped independently. Depth (with) and width (columns) compose freely: each relation in the tree picks its own columns and its own children.

The pattern to hold onto is that a tree read is a tree of { columns, with } choices, one per node.

Projecting columns isn’t premature optimization for its own sake. It’s the difference between shipping an invoice’s twenty columns to render a dropdown label and shipping two. On a nested read the saving compounds, because you pay it once per child row. So for any read that feeds a specific piece of UI, projecting down to the columns that UI actually renders is the default, not an advanced tuning step.

Filtering and ordering, the v2 way

So far the only filter you’ve seen is where: { id: invoiceId }. It’s worth looking at the where object more closely, because in Drizzle’s current relational builder it is an object, and that’s the part most likely to trip you up if you’ve read older tutorials.

Note

If you’ve seen where: (invoices, { eq, and }) => and(eq(invoices.id, x)), a callback that receives the table and operator helpers, that’s the v1 form of the relational builder. It still exists, relocated to db._query and marked old. The course uses the current builder, db.query, which takes a filter object instead of a callback. It has the same expressive power with far less ceremony. The callback form turns up only in pre-2026 tutorials, so you can leave it alone.

The filter object has a small number of shapes, and they stack. Here they are one at a time.

A bare value is equality. The common case, column = value, is just a key and a value:

where: { status: 'sent' }

An operator object compares. When you need more than equality, the value becomes an object whose keys are operators:

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

The operators are the ones you’d expect: eq, ne, gt, gte, lt, lte, in, notIn, like, ilike, isNull, isNotNull, and the array forms. Note that 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. This is the same two-worlds rule from the schema chapter: money stays a string from the database all the way to your comparison.

Several keys are AND-ed. Put more than one key in the object and they combine with AND, which covers the everyday “this and that” filter:

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

Boolean combinators are keys too. When you need OR, or an explicit AND, or a negation, they’re 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 the rare predicate the object syntax can’t express, such as a Postgres operator the builder doesn’t model or a function call, drop to a sql fragment under the RAW key:

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

The RAW callback hands you the table so you can reference its columns. The sql template parameterizes exactly like everywhere else: every ${value} becomes a $1 that the driver binds separately, so there’s no injection surface even here. Reach for RAW last, only when the object genuinely can’t say what you mean.

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

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

Multiple keys sort by multiple columns, in the order you write them. Notice this one pairs createdAt with id, the tiebreaker habit from the first lesson, in object form. createdAt isn’t unique, since two invoices can land in the same millisecond, so on its own the order of ties is undefined and can shuffle between runs. Adding the primary key as a final sort key makes the order total and stable. Cursor pagination will lean on that stable total order a few lessons from now, so it’s worth starting the habit here.

limit and offset round it out, with the same meaning as the SQL builder: limit: 20, offset: 40 means “twenty rows starting after the first forty.” (Whether offset is the right paging tool is its own discussion, a few lessons ahead; here it’s just an option.)

Here’s a realistic read that uses the whole vocabulary at once. It returns 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 in one object, 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 explicit here, carried by hand on every multi-tenant read until a scoped client arrives later in the course.

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. The order is total and deterministic.

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 a single column: exactly the shape a row label needs and nothing more.

← Previous 1 / 1 Next →

Here’s 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' },
});

Check Reset

Filtering the children, filtering by the children

This is where the same small where object does two genuinely different jobs depending on where you put it. Holding this distinction clearly is most of what makes RQB filtering feel comfortable rather than fiddly.

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

That filters the line-item array per invoice. An invoice whose every line item has quantity zero still comes back, just with lineItems: []. The parent is untouched; you’ve only shaped its child list. The where here is the exact same object syntax as the top level, scoped to the relation. It also composes with the relation’s own orderBy and limit: with: { comments: { orderBy: { createdAt: 'desc' }, limit: 3 } } reads as “the three most recent comments per post,” all applied per parent.

Put a relation as a key in the parent’s where and it filters which parents survive, based on their children. This capability is new, and worth a closer look, because it used to require dropping out of the relational API entirely.

In v1, the filter “give me invoices that have a line item over $100” couldn’t be expressed in the relational where at all. You’d reach for the SQL builder and an exists() subquery, or a join with grouping. The current builder lets you write 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 }

Read a relation name in where as a question about existence: does a related row matching this exist? The { … } form constrains what counts as a match, while the bare true form just asks whether any related row exists at all. This answers what the previous lesson had to set aside, dropping a parent based on a fact about its children, and for the common existence case it replaces the old exists() reach entirely.

The two reads share a predicate and look almost identical, which is the easy thing to get wrong. Putting them side by side makes the position the only difference.

Filter the children

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

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

Filter the parents

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; the rest vanish entirely.

Same predicate, two positions, two meanings: inside with it shapes the child list and keeps the parent, and in the parent’s where it decides whether the parent comes back at all. Keep those two apart and the rest of RQB filtering is just those two moves.

There’s still a ceiling. The hardest parent predicates go beyond what relation filters express: ones correlated across several relations, or aggregate thresholds like “invoices with more than five line items.” Those still drop to the SQL builder with exists() or a having clause. Relation filters cover the common “has a matching child” case cleanly, but counting how many children there are is a job for the aggregation lesson coming up next.

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 together through the invoiceTags junction table, which holds two foreign keys and a composite primary key and no columns of its own. Back in the schema chapter you declared invoices.tags as a many relation that walks through that junction. The junction was inert when you declared it, a table nothing had read yet. This is where something finally reads it.

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

with: { tags: true } returns tags: Tag[]. You never name invoiceTags, you never write the two-hop join through it, and you never see the junction in the result. The through declaration you wrote once does all of that work, and from here a many-to-many traversal costs exactly one with key, the same true you’d use for any other relation.

Contrast that with the previous lesson, where the same tags read meant two explicit innerJoins, invoices to invoiceTags and invoiceTags to tags, producing a flat, duplicated result you then regrouped by hand. RQB collapses both joins and the regroup into with: { tags: true }. Of every shape in the last lesson, the many-to-many is the one RQB improves the most, and the clearest illustration of “describe the tree, get the tree.”

One thing the through-walk is not for is a junction that grew columns of its own. A membership, a user in an organization with a role, isn’t a pure junction anymore; the role makes it a real entity. So you don’t walk through it invisibly. Instead you relate to it and traverse it as a first-class relation in its own right: an organization’s memberships, and each membership’s user. This is the read consequence of the rule of thumb from the schema chapter, that pure junctions disappear and junctions-with-data become entities. A pure junction you walk through; a junction with data you walk to. The membership read gets its full treatment when organizations arrive later in the course.

One statement, no N+1

You now know what RQB returns. The reason it’s the default for tree reads is 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.

To see why one statement matters so much, look at the problem it sidesteps. The naive way to build the same list by hand looks perfectly 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
  })),
);

It fetches the invoices in one query, then fetches each invoice’s line items in its own query. For one hundred invoices that’s one query plus one hundred more: 1 + N round trips to the database, each with its own latency. This is the N+1 problem , and it’s one of the most common reasons a list endpoint that felt fine with 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.

You don’t have to take that on faith. Turn on the query logger you met in the schema chapter, drizzle({ client, logger: true, … }), and run the with query. You’ll see Drizzle emit a single statement with subqueries, not a burst of one-per-parent selects. That log is the proof.

The contrast is easier to see as a sequence than to read as a claim, so step through it below.

Your server db.select()

select … from invoices

Postgres 100 invoices

1 round trip so far

Naive read, step one: fetch the parent rows. One query for all the invoices — nothing looks wrong yet.

Your server rows.map(…)

one query per invoice ··· × N

Postgres line items

1 + N round trips

Naive read, step two: the loop. For every invoice, a separate query for its line items — N more round trips, each with its own latency. 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 a single statement with subqueries that aggregate each relation into JSON. One round trip, at any depth — the N+1 never arises.

Prev

Next

One honest caveat, so you don’t over-trust this. Because RQB plans its own SQL, a deeply nested with over wide tables can emit a heavier JSON-aggregating query than you’d guess: one statement doesn’t always mean a cheap statement. The fix is never to abandon RQB and hand-roll the loop, since that just trades a heavy query for the N+1 you were avoiding. The fix is to measure when something feels slow: logger: true to read the SQL it generated, and the query-plan tools coming in the next chapter to see what that SQL actually costs. Spotting and fixing N+1 in code that bypasses RQB, along with reading the plans, are jobs for the next chapter. The habit this lesson plants is simply that you reach for RQB and the N+1 doesn’t arise.

The result type is your prop type

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

The tempting wrong move is to fill the gap by hand:

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
};

That restates fields the schema already knows, the kind of hand-typed interface the whole schema chapter worked to eliminate. Rename a column or change one with key and this interface silently goes stale.

The right move is to recognize that the RQB call’s return type already is the nested shape. 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>>>;

Read the type right to left: ReturnType<typeof getInvoiceDetail> is the Promise the function returns, Awaited<…> unwraps it to the resolved value, and NonNullable<…> strips the | undefined that findFirst adds. What’s left, InvoiceDetail, is the fully-typed nested shape, generated rather than written. Change the with or rename a column and the type updates with no edits, so the component prop, the function return, and the query can never drift apart.

This is the chapter’s “one shape, one source” principle, now reaching reads. The schema is the source of truth for a row’s type, and the query is the source of truth for its own result type. You no more describe the shape of a read in two places than you describe a table in two places. You derive the second from the first.

(Where these read helpers live, as verb-led functions in a db/queries/ folder closing over a tenant-scoped client, is a structure for later in the course. Here the point is only the type extraction: the query owns its result type.)

When to reach for the SQL builder instead

Once you know RQB, the common mistake is forcing every read through it. One test draws the boundary: 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 naturally draw as a tree. It’s the N+1-safe default, and as of this lesson it also handles filtering 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 over related rows, anything with GROUP BY. RQB returns rows and their relations; it doesn’t roll them up into computed totals. That’s the next lesson’s job.
• Flat, irregular projections: a report row that pulls a few columns from three different tables into one flat record. That’s a rectangle by design, not a tree.
• Aggregate-existence predicates: “invoices with more than five line items.” Relation filters answer has a matching child, not has this many children; counting is having on the SQL builder.
• Set operations, window functions, CTEs: the layered-SQL territory a few lessons ahead.

What drives the decision is the shape you want back, not what each API can technically do, since both return Drizzle-typed results either way. A tree goes to RQB; flat, aggregated, or irregular goes to the SQL builder. The two also mix freely within one feature. An invoice detail page might 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, and that’s normal rather than a compromise.

The exercise below matches each read to the tool that fits its shape.

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 Reset

Check your understanding

Start by writing one. The schema below seeds an organization, two invoices, and several line items. Return the invoice with id 1 together with its line items, ordered by line-item id.

Note

This sandbox runs the SQL builder, so db.query and its relations graph aren’t wired up here. Finish the equivalent leftJoin that returns the same three rows. The shape it returns is flat, exactly the “rectangle, not a tree” cost from the top of the lesson: invoice 1’s status repeats on every line-item row. RQB would hand you the same data already folded into { ...invoice, lineItems: [...] }.

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');

Run query Feedback Reset

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 concept that turns 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.

Finally, a fast round on the parts most likely to trip you.

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

False True

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.