Diagnosing Unexpected N+1 Queries in Prisma Client Relation Loading

Recognizable Condition

The application shows a higher-than‑expected number of database queries after a single findMany or findUnique call. For example, loading a list of users and later accessing each user’s posts results in one query for the users plus an additional SELECT for every user’s posts.

Likely Cause

Prisma Client does not perform lazy loading of relations. If the original query omits an explicit include or select for a relation, accessing that relation later triggers separate queries—one per parent record—creating an N+1 pattern.

Diagnostic Steps

1. Inspect the Prisma Client call – verify whether the relation you later use appears in the include or select options.
2. Enable Prisma query logging – add log: ['query', 'info'] to the PrismaClient constructor and run the request that triggers the suspected endpoint.
3. Count executed queries – attach a simple middleware or use a tracing tool to increment a counter for each SQL statement observed in the logs.

Cause / Diagnostic Table

Observed Symptom	Typical Cause	Check to Perform
Multiple SELECT statements for the same relation after one findMany	Missing include or select for that relation	Review the original query; enable logging and look for separate SELECTs with WHERE IN or WHERE = id patterns
Query count grows linearly with number of parent records	Relation accessed in a loop after the initial query	Add a query‑counting middleware; verify count matches parent count + 1 after fix

Ordered Checks

1. Review the Prisma Client call

   Locate the code that fetches the parent records. Example:

   // Missing include → causes N+1 when accessing user.posts later
   const users = await prisma.user.findMany({ where: { active: true } });
   

   If you later do user.posts inside a loop, Prisma will issue a separate query for each user.

2. Enable query logging

   Instantiate the client with logging:

   const prisma = new PrismaClient({
     log: ['query', 'info'],
   });
   

   Run the endpoint (e.g., via curl or a test suite) and capture the console output. Look for patterns like:

   query: SELECT * FROM "User" WHERE "active" = true
   query: SELECT * FROM "Post" WHERE "authorId" = 1
   query: SELECT * FROM "Post" WHERE "authorId" = 2
   ...

3. Count queries per request

   Add a minimal middleware (Express example):

   let queryCount = 0;
   prisma.$on('query', e => {
     queryCount++;
   });
   app.use((req, res, next) => {
     queryCount = 0;
     res.on('finish', () => console.log(`Queries for ${req.path}: ${queryCount}`));
     next();
   });
   

   Run the request and note the printed count. Compare it to the expected count (parent query + 1 for the relation if loaded via JOIN or WHERE IN).

Fixes Tied to Findings

1. Add the missing relation to include or select

   If you need the whole relation:

   const users = await prisma.user.findMany({
     where: { active: true },
     include: { posts: true },
   });
   

   If you only need specific fields from the relation, use select:

   const users = await prisma.user.findMany({
     where: { active: true },
     select: { id: true, name: true, posts: { select: { id: true, title: true } } },
   });
   

2. Ensure the relation is loaded before accessing it

   Avoid conditional access that might skip the include/select in some code paths. Load the relation upfront, then use the data.

3. Verify the generated SQL

   After applying the fix, the logs should show either a single query with a JOIN (if the database supports it) or two queries: one for parents and one using a WHERE IN clause for all related records.

   query: SELECT "User".*, "Post".* FROM "User" LEFT JOIN "Post" ON "Post"."authorId" = "User"."id" WHERE "User"."active" = true
   

   or

   query: SELECT * FROM "User" WHERE "active" = true
   query: SELECT * FROM "Post" WHERE "authorId" IN (1, 2, 3, ...)
   

Limitations and Practical Verification

• Large include or select payloads increase response size and memory usage. Only select the fields you truly need.
• If the relation is optional or can be null, Prisma will still generate a LEFT JOIN or WHERE IN that returns empty rows for missing relations—this is expected and does not cause extra queries.
• Missing indexes on foreign‑key columns can make the WHERE IN query slow even though the query count is correct. Verify index existence with SHOW INDEX FROM Post; in your database.

To confirm the fix works in practice:

1. Run the request with query logging enabled.
2. Check that the number of SQL statements matches the expected count (e.g., 2 statements for a list of users with posts).
3. Ensure no additional SELECT statements appear when iterating over the relation in your application code.

Escalation Criteria

If after adding the proper include or select you still observe an elevated query count:

• Inspect the schema for circular relations that may cause Prisma to generate multiple queries.
• Confirm that relevant foreign‑key columns are indexed.
• Consider loading data in a single transaction using prisma.$transaction or resorting to raw SQL for bulk fetching when the relation set is very large.
• Collect the query logs, schema snippet, and the Prisma Client call, then open a GitHub issue in the Prisma repository with this information for further investigation.