# db.query relations or an explicit join: choosing in Drizzle without an N+1

> The relational API returns nested objects and one round trip; the core builder returns flat rows and total control. Which to reach for, and the loop that quietly becomes N+1.

Drizzle gives you two ways to read related data, and they are not two styles of
the same thing: they produce different SQL and different shapes.

```ts
// Relational API: nested objects, one query
const posts = await db.query.posts.findMany({
  where: eq(posts.published, true),
  with: { author: true, comments: { limit: 3 } },
});
// posts[0].author.name, posts[0].comments[0].body

// Core builder: flat rows, one query, your join
const rows = await db
  .select({ postId: posts.id, title: posts.title, authorName: users.name })
  .from(posts)
  .innerJoin(users, eq(users.id, posts.authorId))
  .where(eq(posts.published, true));
// rows[0].authorName
```

## The failure both of them exist to prevent

```ts
// don't
const list = await db.select().from(posts).limit(50);
for (const post of list) {
  post.author = await db.select().from(users).where(eq(users.id, post.authorId));
}
```

Fifty-one queries. Locally, against Postgres on the same machine, this takes 12
milliseconds and nobody notices. In production, against a database 40 ms away,
it is two seconds of pure round-trip latency, and it scales with the page size,
so it gets worse exactly when the page gets popular.

This is the N+1, and it is the single most common performance bug in any ORM.
It never shows up in a code review as "a performance problem"; it shows up as a
`for` loop with an `await` in it. That shape (`await` inside a loop over rows)
is the thing to grep for.

## When to use the relational API

`db.query` is the right default for **reading a tree you are going to render**:
a post with its author and comments, an order with its line items, a user with
their team memberships.

```ts
const order = await db.query.orders.findFirst({
  where: eq(orders.id, orderId),
  columns: { id: true, total: true, createdAt: true },
  with: {
    customer: { columns: { id: true, email: true } },
    items: {
      columns: { id: true, quantity: true },
      with: { product: { columns: { name: true, sku: true } } },
    },
  },
});
```

What you get:

- **One round trip.** Drizzle builds a single statement with lateral joins and
  JSON aggregation, so nesting does not multiply queries.
- **The shape you actually want.** No manual regrouping of flat rows into
  objects, which is where hand-written join code accumulates bugs.
- **No row multiplication.** A post with 30 comments comes back as one post with
  an array, not 30 rows repeating the post body 30 times.

Requirements: the relations must be declared with `relations()` in your schema
file, and the tables must be exported to the `drizzle()` call as its schema. If
`db.query.posts` does not exist in your editor's autocomplete, one of those two
is missing.

Always pass `columns` and always bound nested collections with `limit`. `with:
{ comments: true }` on a post with 40,000 comments will happily fetch all of
them.

## When to use the core builder

Reach for `select().from().join()` when the query is not a tree:

**Aggregates.**

```ts
const stats = await db
  .select({
    authorId: posts.authorId,
    total: count(posts.id),
    lastPublished: max(posts.publishedAt),
  })
  .from(posts)
  .where(eq(posts.published, true))
  .groupBy(posts.authorId)
  .having(gt(count(posts.id), 5));
```

**Anti-joins and existence checks.** "Users with no posts" is a `LEFT JOIN ...
WHERE posts.id IS NULL`, or a `NOT EXISTS`. The relational API has no way to say
it.

**Window functions, CTEs, `DISTINCT ON`, set operations.** Anything where the SQL
shape *is* the answer.

**Reports and exports**, where a flat row is exactly what you want to stream to
a CSV.

And when even the core builder is fighting you, drop to SQL: it is a first-class
option, not a defeat:

```ts
const rows = await db.execute(sql`
  select date_trunc('day', created_at) as day, count(*) as signups
  from users
  where created_at > now() - interval '30 days'
  group by 1 order by 1
`);
```

Keep every interpolated value inside the `sql` template so it is parameterised.
Never build a query by concatenating strings.

## The trap in the middle: joining a one-to-many with the core builder

```ts
const rows = await db
  .select()
  .from(posts)
  .leftJoin(comments, eq(comments.postId, posts.id))
  .limit(20);
```

`limit(20)` limits **rows**, not posts. Twenty rows might be three posts. This is
the classic pagination bug: page one shows three items, page two shows seven,
and users report that results are missing.

Either group in application code after fetching a bounded set of post ids, or
use `db.query` with `with`, which handles it correctly by construction. This
specific mistake is a good reason to prefer the relational API for anything
paginated.

## Measuring instead of guessing

```ts
const query = db.select().from(posts).innerJoin(users, eq(users.id, posts.authorId));
console.log(query.toSQL()); // { sql, params }
```

`toSQL()` gives you the exact statement, which you can then `EXPLAIN`:

```sql
explain analyze select ...;
```

Read the plan for two things: a `Seq Scan` on a table that should be using an
index (usually a foreign key with no index on the referencing side, Postgres
never creates one for you), and row-estimate errors of an order of magnitude,
which usually mean stale statistics or a filter Postgres cannot use.

## The rules that hold up

1. No `await` inside a loop over rows. Ever. If you need related data, join it
   or use `with`.
2. `db.query` for trees you render; the core builder for aggregates, anti-joins
   and anything set-shaped; raw `sql` when the SQL is the point.
3. Select columns explicitly. `select()` with no argument fetches every column of
   every joined table, including the `jsonb` blob nobody wanted.
4. Bound every collection: `limit` on nested `with`, `limit` on the outer query.
5. Index the referencing side of every foreign key you filter or join on.

---

Agentic Boilerplate: A Next.js repo your agent already knows. Free during launch, then $99 once.

- Site map for agents: https://agenticboilerplate.com/llms.txt
- Public API: https://agenticboilerplate.com/openapi.json
- Contact: agenticstudio@gmail.com