N+1 Queries: The Hidden Performance Killer in ORMs

1. What Is N+1: 1 List Query + N Related Queries

A classic scenario: you write an endpoint that returns a list of orders, with a username attached to each order. The code looks fine and runs fine, until the data volume grows and P99 latency goes through the roof.

Open the SQL logs and you see:

SELECT * FROM orders;                  -- 1 query
SELECT * FROM users WHERE id = 1;      -- N queries begin
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;
...

1 list query + N related-record queries = N+1 queries. Something that should take 1-2 queries now takes dozens or hundreds. That is N+1.

It is not a bug; it is a side effect of the design. In ORMs especially, it is often the default behavior rather than an exception. The worst part is that you will not notice it with a small development dataset, but requests start timing out as soon as production data grows.

2. How It Happens: ORM Lazy Loading's "Helpful" Trap

For the sake of convenience, ORMs often default to lazy loading. SQL is issued only when you access order.user.name, which looks elegant until you do it inside a loop.

Bad code (TypeScript, Prisma style):

const orders = await prisma.order.findMany();  // 1 query
for (const o of orders) {
  console.log(o.user.name);  // Each access to .user triggers a SELECT
}

Accessing .user triggers lazy loading. Looping N times means N queries.

Manually querying related records inside a loop is just as deadly, and even more common:

const orders = await prisma.order.findMany();
for (const o of orders) {
  const user = await prisma.user.findUnique({ where: { id: o.userId } });
  console.log(user.name);
}

The code "looks normal," but every await is a database round trip. You will not notice it with 10 records; with 10,000 records, it blows up.

Lazy loading is a double-edged sword: convenient for a single record, disastrous for batch operations.

3. The Impact: Database Round Trips Are the Most Expensive Operation

Remember this: The cost of one database round trip is far greater than the cost of retrieving more data.

A single RTT (round-trip time) is usually 1-10ms, and it is even higher across availability zones or regions. The cost of N round trips is not merely "querying data N times." It is N accumulated RTTs + N connection overheads + N query parsing operations.

For example, retrieve the usernames for 100 orders:

• N+1 approach: 1 + 100 = 101 queries, assuming a 3ms RTT per query = 303ms
• Optimized: 2 queries (orders + users WHERE id IN (...)) = 6ms

That is 50x slower. And this is within the same data center. Across regions, the difference can exceed 100x.

N+1 is dangerous because of its "boiling frog" effect: load tests usually focus on QPS, not the number of SQL queries per request. It therefore sits unnoticed until operations exports 10,000 records one day and the endpoint immediately returns a 504.

4. How to Detect It: Three Ways to Find N+1

First: enable SQL logging. Set log: ['query'] in Prisma, logger: true in Drizzle, or logging: true in TypeORM. Look for repeating patterns of similar SQL statements in the logs. If one API request floods the log, it is almost certainly N+1.

Second: use the ORM's explain or analysis tools. Prisma provides prisma.debug, while Drizzle can print a query with .toSQL(). If one request triggers dozens of SQL statements, investigate immediately.

Third: use an APM performance profile. Datadog, New Relic, and Sentry can all aggregate SQL traces by request. If one HTTP request contains dozens of DB spans, the problem is obvious at a glance.

Practical recommendation: add a gate to CI that raises an alert when the number of SQL queries in one request exceeds a threshold, such as 10. You can catch N+1 during testing instead of waiting for production.

5. How to Fix It: Eager Loading / Join / Manual Aggregation

First choice: eager loading. Have the ORM retrieve all related records at once.

Prisma's include:

const orders = await prisma.order.findMany({
  include: { user: true },  // Retrieve all related users at once
});

Drizzle's with:

const orders = await db.query.orders.findMany({
  with: { user: true },
});

Under the hood, both approaches first query orders, then retrieve users with a single WHERE id IN (...) query and join them in memory. Two queries are enough, no matter how large N becomes.

Second choice: retrieve everything with one JOIN. When you need custom fields, join directly:

const rows = await db.select({
  orderId: orders.id,
  userName: users.name,
})
.from(orders)
.leftJoin(users, eq(orders.userId, users.id));

This takes one query, but the result is flat and must be mapped manually. It is suitable when you only need a few fields rather than complete related objects.

Third choice: manually aggregate into a DTO. In complex scenarios, the ORM abstraction can become a burden. Writing SQL directly and aggregating only what you need can be clearer:

const orders = await sql`SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'`;
const userIds = [...new Set(orders.map(o => o.userId))];
const users = await sql`SELECT * FROM users WHERE id = ANY(${userIds})`;
const userMap = new Map(users.map(u => [u.id, u]));
return orders.map(o => ({ ...o, user: userMap.get(o.userId) }));

This is a "manual DataLoader." It is simple, explicit, free of magic, and gives you maximum control. I prefer this approach for complex reporting endpoints because ORM magic often gets in the way of complex queries.

6. DataLoader: Batch N Individual Queries

N+1 is particularly severe in GraphQL because every field can trigger an independent query. Facebook's DataLoader is the classic solution and deserves its own section.

The principle in one sentence: Collect all individual queries made during the same event loop tick, combine them into one IN query, and then distribute the results.

const userLoader = new DataLoader(async (userIds: number[]) => {
  const users = await db.user.findMany({ where: { id: { in: userIds } } });
  const map = new Map(users.map(u => [u.id, u]));
  return userIds.map(id => map.get(id));  // Return results in input order
});

// In application code, regardless of how many times it is called:
for (const o of orders) {
  const user = await userLoader.load(o.userId);  // Batched automatically
}

The key is that load internally uses process.nextTick to collect every call made during the same tick, combines them into one WHERE id IN (...) query, and then distributes the results to each caller in order.

DataLoader is a general pattern, not something limited to GraphQL. It applies anywhere you need to retrieve related records by ID inside a loop. Combine it with a request-scoped cache, so the same ID is queried only once per request, to maximize the benefit. In the TypeScript ecosystem, you can install the dataloader library directly from npm.

7. Prevent It at the Source: Eager by Default + CI Query Reviews

Fixing one N+1 problem is easy. The hard part is preventing it from happening again. Use two lines of defense:

Defense 1: make ORM relationships eager by default, or require explicit includes. Some teams disable lazy loading outright: accessing a relationship that was not preloaded immediately throws an error. Prisma is explicit by default, so relationship fields are unavailable unless you include them, and Drizzle works the same way. This is a healthy default. Do not enable lazy loading; doing so leaves a back door open for N+1. For ORMs that support lazy loading, such as TypeORM's eager: false plus relationship proxies, teams without mature engineering practices should disable it consistently.

Defense 2: check query counts in CI. Exercise the endpoint in a test and assert the number of SQL queries:

test('The order list endpoint should execute fewer than 5 SQL queries', async () => {
  const queries: string[] = [];
  prisma.$on('query', e => queries.push(e.query));
  await GET('/api/orders');
  expect(queries.length).toBeLessThan(5);
});

As soon as someone introduces N+1, CI fails. This turns "performance" into a "testable contract," which is far more reliable than relying on verbal code review guidance.

Summary

N+1 is not a minor detail; it is an engineering discipline problem. Key points:

1. N+1 = 1 list query + N related-record queries. Lazy loading and manually querying the database inside loops are the two main culprits
2. Database round trips are the most expensive operation. N round trips can be 10-100x slower than one, with an even larger gap across regions
3. Fix priority: eager loading > JOIN > DataLoader > manual aggregation; choose according to the scenario
4. DataLoader is a general pattern. It works just as well for batch-query scenarios outside GraphQL
5. Prevention requires discipline: disable lazy loading + check query counts in CI to stop N+1 before merge

Rule to remember: Do not query the database inside a loop. This habit alone will help you avoid 90% of N+1 problems. A query made by your ORM is still a query, and so is one you write as await db.xxx. A database call inside a loop is a performance landmine. Build the reflex that whenever you see await db.xxx inside a for loop, you pause for a second and ask, "Can this be moved outside the loop? Can it be batched?" Once that question becomes instinctive, N+1 will rarely affect you.