db.query relations or an explicit join: choosing in Drizzle without an 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.

// 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

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

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.

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:

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

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

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:

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.