CodePark/ questions / ORMs & the N+1 problem
START PRACTICINGPRACTICE
Backend question · Databases & SQL › ORMs & the N+1 problem

What is the N+1 query problem in this TypeORM code, and how do you fix it?

const users = await userRepo.find();           // 1 query
for (const user of users) {
  user.posts = await postRepo.find({           // N queries
    where: { authorId: user.id },
  });
}

The code runs one query for the list, then one more query for each user. So 100 users means 101 round trips to the database. Each query is fast, but the waiting time adds up, and it grows with the data. ORMs hide this problem, especially when you access lazy-loaded relations (relations fetched only when you touch them) inside a loop.

Fixes:

  • Load the relation in the same request. Use relations: { posts: true } in TypeORM or include in Prisma. The ORM then uses a join or one extra batched query.
  • Batch it yourself. Collect the user ids. Run one WHERE author_id = ANY($1) (or IN (...)) query. Then group the posts in memory.
  • In GraphQL resolvers, use DataLoader. It collects lookups made in the same tick and sends them as one query.

Be careful: joining several one-to-many relations at once multiplies the rows. In that case, separate batched queries can be better. Logging queries in development makes N+1 easy to spot.

  • N+1 means 1 query for the list plus 1 query for each row.
  • Eager load relations with relations, include or a JOIN.
  • Batch with IN or ANY, then group the results in memory.
  • Use DataLoader to batch lookups in GraphQL.
  • Log queries in development to detect N+1.