08 - The N+1 Problem

What is N+1?

The N+1 problem happens when your code runs one query to fetch a list, then one more query per row to fetch related data. If you load 50 users and then fetch posts for each one, that's 51 queries. It's the most common performance mistake in database-backed apps.

The Problem in Action

Here's how it typically shows up. You fetch all users, then loop through them to get their posts.

// 1 query: fetch all users
const allUsers = await db.select().from(users);
// SELECT * FROM users  (1 query)

// N queries: fetch posts for each user
for (const user of allUsers) {
  const userPosts = await db.select().from(posts)
    .where(eq(posts.authorId, user.id));
  // SELECT * FROM posts WHERE author_id = 1  (query 2)
  // SELECT * FROM posts WHERE author_id = 2  (query 3)
  // SELECT * FROM posts WHERE author_id = 3  (query 4)
  // ... one query per user
}

With 100 users, that's 101 database round trips. Each query is fast on its own, but the total latency adds up quickly. On a remote database, this can turn a 5ms page load into a 500ms one.

Counting Queries

A simple way to see the problem:

// ❌ N+1: 1 + N queries
const allUsers = await db.select().from(users);  // 1 query
for (const user of allUsers) {
  await db.select().from(posts).where(eq(posts.authorId, user.id));
}
// Total: 1 + 100 = 101 queries

// ✅ Fixed: 1 query
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true }
});
// Total: 1 query (Drizzle handles the join internally)

Fix 1: Relational Queries

Drizzle's relational query API solves N+1 automatically. Use db.query with with to load related data in a single round trip.

This requires relations defined in your schema (covered in lesson 06).

import { relations } from 'drizzle-orm';

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts)
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id]
  })
}));

Now fetch users with their posts:

const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true
  }
});
// [
//   { id: 1, name: 'Alice', posts: [{ id: 1, title: 'Hello World', authorId: 1 }] },
//   { id: 2, name: 'Bob', posts: [{ id: 2, title: 'My First Post', authorId: 2 }] },
//   { id: 3, name: 'Carol', posts: [] }
// ]

One call. Drizzle builds the SQL for you. Users with no posts get an empty array.

You can filter and select specific columns too:

const result = await db.query.users.findMany({
  columns: { id: true, name: true },
  with: {
    posts: {
      columns: { title: true },
      where: eq(posts.published, true)
    }
  }
});
// [{ id: 1, name: 'Alice', posts: [{ title: 'Hello World' }] }]

Fix 2: Left Join

If you don't need nested objects, a leftJoin also solves N+1 in a single query.

const rows = await db.select({
  userId: users.id,
  userName: users.name,
  postTitle: posts.title
}).from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));
// [
//   { userId: 1, userName: 'Alice', postTitle: 'Hello World' },
//   { userId: 1, userName: 'Alice', postTitle: 'Second Post' },
//   { userId: 2, userName: 'Bob', postTitle: 'My First Post' },
//   { userId: 3, userName: 'Carol', postTitle: null }
// ]

This returns flat rows. Users with multiple posts appear multiple times. Users with no posts have null for post columns.

Relational Queries vs Joins

Both solve N+1, but the output shape is different.

// Relational: nested objects, clean structure
const nested = await db.query.users.findMany({
  with: { posts: true }
});
// { id: 1, name: 'Alice', posts: [{ title: 'Hello World' }, { title: 'Second Post' }] }

// Join: flat rows, duplicated parent data
const flat = await db.select().from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));
// { users: { id: 1, name: 'Alice' }, posts: { title: 'Hello World' } }
// { users: { id: 1, name: 'Alice' }, posts: { title: 'Second Post' } }

Use relational queries when you want nested data for an API response or UI rendering. Use joins when you need aggregations, custom grouping, or complex conditions the relational API doesn't support.

When Each Approach Fits

Relational queries (db.query with with):

Fetching a resource with its children (user + posts, order + items)
API endpoints that return nested JSON
Simple, readable code

SQL joins (.leftJoin(), .innerJoin()):

Aggregations like counting posts per user
Filtering parents by child conditions (users who have published posts)
Complex multi-table queries with custom select shapes

// Example: count posts per user (needs a join + groupBy)
import { count } from 'drizzle-orm';

const postCounts = await db.select({
  userName: users.name,
  postCount: count(posts.id)
}).from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id, users.name);
// [{ userName: 'Alice', postCount: 2 }, { userName: 'Bob', postCount: 1 }, { userName: 'Carol', postCount: 0 }]

You can't do this with the relational API. Aggregations require SQL joins.

Key Takeaways

N+1 means 1 query for the list + 1 query per row for related data, causing performance problems that scale linearly with row count
db.query.table.findMany({ with: { ... } }) solves N+1 with nested output in a single round trip
.leftJoin() solves N+1 with flat rows, useful for aggregations and complex conditions
Relational queries need relations() defined in your schema
Use relational queries for nested JSON responses, use joins for aggregations and grouping
Always check your query count: if it grows with your data, you have an N+1 problem