Cut N+1 Reads in Prisma List Endpoints with Projected Select and Batched Queries

Start with the symptom, not the ORM

A list endpoint that returns 50 records with nested relations can quietly turn into hundreds of database statements. You add include: { author: true, comments: true } because it reads cleanly, then watch response times climb as the page size grows. The useful takeaway is that you usually do not need a deeper include; you need narrower projections and one batched query per child relation.

What actually happens under a deep include

Prisma's include fetches related rows, but the execution plan is not guaranteed to be a single JOIN. Depending on your Prisma version, database connector, and relationMode setting in schema.prisma, nested reads may be executed as separate statements per parent record. Deep includes can multiply round trips. The only reliable way to know is to capture the SQL your client emits rather than assuming database behavior.

Project the parent with select

select tells Prisma to return only the scalar fields you name. You can nest select for relations, which shrinks the payload and the number of columns the database returns. Because Prisma generates TypeScript types from your query, omitting a field you later try to read becomes a compile error instead of a runtime surprise. After changing queries, run npx prisma generate from your project root so the client types match; it requires no database permissions but must run before TypeScript compilation. Note that combining select and include at the same level follows version-specific rules, so confirm the exact combination compiles against your generated client before relying on it.

Batch child reads manually

Instead of traversing relations per parent, run one findMany on the child table filtered by the parent IDs you already fetched, then group the rows in memory.

// Run in your route handler or service layer.
// Requires the generated Prisma client; no elevated DB permissions beyond normal query rights.

const posts = await prisma.post.findMany({
  where: { published: true },
  select: {
    id: true,
    title: true,
    authorId: true,
    createdAt: true,
  },
  take: 50,
  orderBy: { createdAt: "desc" },
});

const postIds = posts.map((p) => p.id);
const authorIds = [...new Set(posts.map((p) => p.authorId))];

const [authors, comments] = await Promise.all([
  prisma.user.findMany({
    where: { id: { in: authorIds } },
    select: { id: true, name: true },
  }),
  prisma.comment.findMany({
    where: { postId: { in: postIds } },
    select: { postId: true, body: true, createdAt: true },
    orderBy: { createdAt: "desc" },
  }),
]);

const authorMap = new Map(authors.map((a) => [a.id, a]));
const commentsByPost = new Map();
for (const c of comments) {
  const list = commentsByPost.get(c.postId) ?? [];
  list.push(c);
  commentsByPost.set(c.postId, list);
}

const result = posts.map((post) => ({
  ...post,
  author: authorMap.get(post.authorId) ?? null,
  comments: (commentsByPost.get(post.id) ?? []).slice(0, 3),
}));

This trades a few lines of application code for a predictable query count: one statement for the parent, one for authors, one for comments. The database sees three round trips instead of a potentially unbounded number.

The trade-off: large IN lists and pagination boundaries

Batching with in is not free. If the parent set grows to thousands of IDs, the IN clause can exceed database limits or slow down planning. Chunk the IDs or paginate the parent query. Also, cursor pagination using take plus orderBy on a unique column is generally steadier than large skip offsets, but it is a separate concern from over-fetching. Ordering by a non-unique column can duplicate or skip rows across pages, so verify page boundaries with tests.

Verify with query logs

Prove the change by counting statements. Enable query logging in the client constructor or through the DEBUG environment variable, then hit the endpoint and count the SQL statements.

// Gate this behind an environment flag; query logging adds overhead
// and may expose parameter values, so avoid enabling it broadly in production.
const prisma = new PrismaClient({
  log: process.env.PRISMA_LOG_QUERIES === "1" ? ["query"] : [],
});

Expected check: the endpoint should emit a small, fixed number of statements regardless of page size, and the response payload should contain only the selected fields. Seed a known dataset, capture the SQL before and after the change, and compare counts and payload sizes. Add a test that asserts the response shape and, where practical, the number of queries, running it in CI with the same Prisma version and connector used in production.

Actionable checklist

1. Open schema.prisma and note the datasource provider and any relationMode setting.
2. Check the Prisma version in package.json; nested read behavior differs across versions.
3. Replace broad include with select projections and run npx prisma generate.
4. Manually batch child relations with findMany and group in memory.
5. Enable query logging locally, count statements per request, and confirm the payload shrank.
6. Add a regression test for response shape and query count.

This keeps the endpoint fast as the page grows and turns silent over-fetching into a compile-time or test-time failure.