Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 37 ยท Performance and Patterns

Finding and Fixing N+1 Queries

What the N+1 query problem is, how to count queries to catch it, and how to fix it with joins, batched IN queries and the DataLoader pattern.

The N+1 problem is the most common performance bug in ORM code. You run one query to load a list of N records (the "1"), and then your code runs one more query per record to fetch something related (the "N"). A page that lists 50 posts with their authors quietly makes 51 round trips to the database. Each query is fast, so nothing looks wrong on your laptop, but in production with network latency and real traffic the page becomes slow.

In this lesson you will count queries, reproduce the problem and meet the four standard fixes. The runnable demos use node:sqlite with a small query counter. TypeORM and Sequelize code is read-only.

How it hides in ORM code

TypeScript
// TypeORM: looks innocent
const posts = await postRepo.find();               // 1 query
for (const post of posts) {
  const author = await userRepo.findOneBy({ id: post.authorId }); // N queries
  console.log(post.title, author.name);
}
JavaScript
// Sequelize: lazy loading in a loop is the same trap
const posts = await Post.findAll();                // 1 query
for (const post of posts) {
  const author = await post.getAuthor();           // N queries
}

Lazy-loaded relations (TypeORM lazy: true or Sequelize getX() methods) make it worse, because the extra query is hidden behind a property access.

Count the queries

Before you optimize, measure. This helper wraps the database so every statement increments a counter. Real projects get the same effect from the ORM's logger: TypeORM's logging: true and Sequelize's logging: console.log print every statement.

Node.js Reproduce N+1 and count queries

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.

Twenty-one queries for twenty posts. Now the fixes.

Fix 1: join in one query

Ask for posts and authors together. This is the right answer for to-one relations such as post to author.

Node.js One join query

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.

In the ORMs:

TypeScript
// TypeORM
postRepo.find({ relations: { author: true } });
// or with the query builder
postRepo.createQueryBuilder('post').leftJoinAndSelect('post.author', 'author').getMany();
JavaScript
// Sequelize
Post.findAll({ include: [{ model: User, as: 'author' }] });

Fix 2: two queries with IN (batching)

For to-many relations (a user with many posts), a join repeats the parent columns on every child row, which wastes bandwidth and can multiply rows if you join two collections at once. A better plan is two queries: load the parents, then load all children with WHERE parent_id IN (...) and group them in memory.

Node.js Batched IN query

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.

Sequelize does this for you with include: [{ model: Post, separate: true }]. TypeORM's relations option with find loads each relation with joins by default; the relationLoadStrategy: 'query' option switches to separate queries.

Fix 3: select only what you need

Fewer rows and columns also help. In TypeORM use select: { id: true, title: true }; in Sequelize attributes: ['id', 'title']. See also Pagination Patterns.

Fix 4: the DataLoader pattern

In GraphQL and some REST resolvers you cannot know all the ids up front: each resolver asks for "the author of this post" separately. A loader collects every request made in the same tick of the event loop and answers them with one batched query. The dataloader npm package does this; here is the idea in about twenty lines.

Node.js A tiny batching loader (stand-in)

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.

Five lookups, one query. A real loader also caches per request.

Preventing regressions

  • Turn on query logging in development and read it.
  • In tests, count queries for list endpoints and assert an upper bound (the counter above works for that).
  • Beware of eager relations that load everything everywhere. Load relations where you need them.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A page lists 50 posts and loads each author separately. How many queries run?

    One query for the list plus one per post: 1 + N.
  2. 2. Which fix suits a to-many relation best when joins would repeat parent rows?

    Batching avoids row multiplication and keeps the number of queries constant.
  3. 3. What does a DataLoader do?

    It turns many by-id requests into a single IN query.
  4. 4. What is the first step before optimizing a slow endpoint?

    Logging reveals the real number and shape of queries.

Summary

  • N+1 means one query for a list plus one per item; it hides behind lazy relations.
  • Count queries to find it.
  • Fix with a join for to-one relations, a batched IN query for to-many ones, or a loader when ids arrive one at a time.
  • Select only needed columns and assert query counts in tests.

Next: Indexes and Reading Query Plans