The N+1 Problem and Batching with DataLoader

You go to a shop for ten products. You can make ten trips, one product per trip, or you can write a list and buy everything in one go. The resolvers from the last lesson go to the database "one product at a time": a separate query for the comments of every post. This is the famous N+1 problem. In this lesson you will see it in the Prisma log and fix it with DataLoader, a small library that gathers the queries of one "tick" into one.

How N+1 appears

Take the query { posts { title comments { text } } }. Query.posts runs one SQL query and returns N posts. Then Post.comments is called for each post, and every call sends its own query WHERE "postId" = …. That is 1 + N queries in total, hence the name. With 10 posts it is 11 queries, with 100 posts it is 101.

This is bad for two reasons. Every query is a trip over the network to the database, and the connection pool is limited. And the levels multiply: if every comment also has an author, the number of queries becomes 1 + N + N·M. The client sent just one request.

Seeing N+1 in the Prisma log

The client was created with log: ['query'] (last lesson). Run the query in Sandbox and look at the server console (the output is shortened):

prisma:query SELECT ... FROM "public"."Post" ... ORDER BY ... DESC
prisma:query SELECT ... FROM "public"."Comment" WHERE ... "postId" = $1 ...
prisma:query SELECT ... FROM "public"."Comment" WHERE ... "postId" = $1 ...
prisma:query SELECT ... FROM "public"."Comment" WHERE ... "postId" = $1 ...
... 7 more queries, one for each remaining post

There is one query to Post and one query to Comment for every post. Get used to counting queries in the log: it is the easiest way to find N+1.

💡 Prisma can merge findUnique calls made in one tick into a single query with IN by itself. That is why Post.author written with findUniqueOrThrow may look better in the log than Post.comments: that one uses findMany, which Prisma does not merge. You cannot rely on it, because the trick works only for findUnique. DataLoader suits any source (another REST API, Redis, a service) and also caches results for the duration of the request.

The idea: collect the keys and ask once