Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 36 ยท Performance and Patterns

Solving the N+1 Query Problem

Spot and fix the N+1 query problem in Prisma with include, select, relationLoadStrategy and batching, and learn to count queries so you can prove an improvement.

The N+1 problem is the most common ORM performance bug. You run one query to fetch a list (the "1"), then one extra query for each item to fetch something related (the "N"). With 10 rows it is invisible. With 1,000 rows it is 1,001 round trips to the database, and the page takes seconds.

Prisma code is read-only here (it needs the npm package and a database). The TryIts are labelled stand-ins on node:sqlite that count queries so you can see the difference.

What N+1 looks like

TypeScript
const authors = await prisma.user.findMany();           // 1 query

for (const author of authors) {
  const posts = await prisma.post.findMany({            // N queries
    where: { authorId: author.id },
  });
  console.log(author.name, posts.length);
}

Nothing looks wrong: each line is a normal Prisma call. The cost hides in the loop. Databases are fast at set operations and slow at many tiny round trips, because each trip pays network latency.

Node.js Stand-in: count the queries of an N+1 loop

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Fix 1: include (or select) the relation

Ask Prisma for the relation in the same call and it fetches everything in a fixed number of queries, however many rows there are:

TypeScript
const authors = await prisma.user.findMany({
  include: { posts: true },
});

By default Prisma runs one query per relation level and joins the results in memory (relationLoadStrategy: 'query'). It first loads the users, then loads all posts for those user IDs with a single WHERE authorId IN (...). On PostgreSQL and MySQL you can ask the database to do the join instead:

TypeScript
const authors = await prisma.user.findMany({
  relationLoadStrategy: 'join',
  include: { posts: true },
});

join uses one SQL statement with lateral joins and JSON aggregation. It is often faster for small result sets and deep nesting; query can win when the join would repeat large parent rows. Measure with your data.

Fix 2: fetch less with select

include: { posts: true } loads every post column. If you only need titles, say so:

TypeScript
const authors = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    posts: { select: { title: true }, where: { published: true }, take: 5 },
  },
});

Fewer columns means less data over the wire and less memory. You cannot use include and select at the same level, but you can nest select inside include and the reverse. Counting is its own trap: use _count instead of loading rows just to take .length:

TypeScript
const authors = await prisma.user.findMany({
  select: { name: true, _count: { select: { posts: true } } },
});

Fix 3: batch by hand with IN

Sometimes a loop is unavoidable, for example when resolving a GraphQL field. Batch the lookups yourself: collect the IDs, run one query, group in memory:

TypeScript
const posts = await prisma.post.findMany({ where: { authorId: { in: authorIds } } });
const byAuthor = Map.groupBy(posts, (p) => p.authorId);

GraphQL servers use a DataLoader to do this automatically: it collects every load(id) made in one tick and flushes them as one batched query. Prisma also batches findUnique calls made in the same tick on its own, which is why a DataLoader around findUnique often needs nothing more.

Node.js Stand-in: N+1 vs include-style vs one JOIN

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

All three return the same data; the number of round trips is 51, 2 and 1.

Proving it: count and log queries

Do not guess; look. Turn on query logging (covered in connection pooling and logging) and watch the console while you load the page. A list page that prints dozens of near-identical SELECT ... WHERE authorId = ? lines has N+1. In tests, count queries with the query event and assert an upper bound, so a future change cannot quietly reintroduce the problem (see testing Prisma code).

flowchart LR
  A["1 query: list of users"] --> B{"Loop over users?"}
  B -- "query per user" --> C["N more queries (slow)"]
  B -- "include / select / IN batch" --> D["1-2 more queries (fast)"]

The fix is never a faster loop; it is removing the loop.

Other hidden N+1 sources

  • Calling findUnique inside .map() over an array of IDs: use findMany({ where: { id: { in: ids } } }).
  • Creating rows in a loop with await prisma.x.create: use createMany or a nested write.
  • Checking existence per item before inserting: use upsert or skipDuplicates.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the N+1 problem?

    The loop turns a single list query into N+1 round trips, which gets slow as N grows.
  2. 2. How many queries does Prisma's default include run for users with their posts?

    By default Prisma loads the parents, then all children for those parent IDs in one IN query.
  3. 3. Which option asks the database to do the join in a single SQL statement?

    relationLoadStrategy: 'join' uses lateral joins and JSON aggregation instead of separate queries.
  4. 4. How should you get only the number of posts per user?

    _count asks the database to count, so no post rows are transferred.

Summary

  • N+1 is one list query plus one query per item; the hidden cost is round trips.
  • include and select fetch relations in a fixed number of queries; relationLoadStrategy picks query or join.
  • Use select to fetch only needed columns and _count to count.
  • Batch by hand with in (or a DataLoader) when a loop is unavoidable.
  • Prove the fix by logging or counting queries.

Next: Pagination: Offset and Cursor