Fixing N+1 queries in Prisma with include, select and groupBy

A list page that loads in 40ms locally takes four seconds in production. The query in your code looks fine. The database CPU is idle. The slow part is that you are making 300 round trips instead of one.

This is the N+1 problem: one query for the list, then N more, one per row. Locally, with the database on the same machine, each round trip costs a fraction of a millisecond and the bug is invisible. In production, with 20ms between your function and your database, 300 round trips is six seconds of waiting on the network.

The shape of the bug

// 1 query for the projects...
const projects = await prisma.project.findMany({ where: { ownerId } });

// ...then one query per project. This is the N.
const rows = [];
for (const project of projects) {
  const owner = await prisma.user.findUnique({ where: { id: project.ownerId } });
  const taskCount = await prisma.task.count({ where: { projectId: project.id } });
  rows.push({ ...project, owner, taskCount });
}

Fifty projects: 101 queries.

Promise.all is the disguised version. It is faster, because the round trips overlap, and it is still 101 queries hitting your connection pool at once, which on a pool of 3 connections means most of them are queued anyway:

// still N+1, just concurrent
const rows = await Promise.all(
  projects.map(async (project) => ({
    ...project,
    owner: await prisma.user.findUnique({ where: { id: project.ownerId } }),
  })),
);

The third disguise hides inside a helper. getProjectOwner(project) looks like a pure function at the call site; it contains a query. Rendering a list of them in a React Server Component produces the same N+1 with nothing in the loop body that looks like a database call.

Fix 1: fetch relations in the parent query

Prisma resolves relations declared in include or select in the same call:

const projects = await prisma.project.findMany({
  where: { ownerId },
  select: {
    id: true,
    name: true,
    createdAt: true,
    owner: { select: { id: true, name: true, avatarUrl: true } },
    _count: { select: { tasks: true } },
  },
  orderBy: { createdAt: "desc" },
  take: 50,
});

One call. _count does the counting in the database instead of loading task rows to call .length on them.

Note select, not include. include gives you the relation *plus every scalar column of the parent*, including the ones you did not think about, like a password hash or a large JSON blob, which then get serialised across the server/client boundary. select states what you want. Prefer it, and remember you cannot use both at the same level.

Fix 2: batch by id when the relation is not declared

Sometimes there is no relation to traverse: you have a list of ids from somewhere else. Fetch them in one query and index them in memory:

const ownerIds = [...new Set(projects.map((p) => p.ownerId))];

const owners = await prisma.user.findMany({
  where: { id: { in: ownerIds } },
  select: { id: true, name: true },
});

const ownersById = new Map(owners.map((owner) => [owner.id, owner]));
const rows = projects.map((project) => ({
  ...project,
  owner: ownersById.get(project.ownerId) ?? null,
}));

Two queries, constant in the number of rows. The Set matters, without it you send duplicate ids and the in list grows with your page size. Keep an eye on the size of that list anyway: Postgres handles thousands of parameters, but a 100k-element in is a sign you should be joining instead.

Fix 3: aggregate in the database

Loading rows to count, sum or group them in JavaScript is N+1's quieter cousin: one query, but it transfers a table over the wire.

// don't: pulls every task to compute per-status counts
const tasks = await prisma.task.findMany({ where: { projectId } });
const done = tasks.filter((t) => t.status === "DONE").length;

// do
const byStatus = await prisma.task.groupBy({
  by: ["status"],
  where: { projectId },
  _count: { _all: true },
});

aggregate covers _sum, _avg, _min, _max. For anything more shaped than that (a window function, a lateral join, a recursive CTE) drop to raw SQL with the tagged template, where ${} values are bind parameters:

import { sql } from "@/db/orm";

const rows = await sql<{ projectId: string; lastActivity: Date }>`
  select p.id as "projectId", max(t.updated_at) as "lastActivity"
  from project p
  join task t on t.project_id = p.id
  where p.owner_id = ${ownerId}
  group by p.id
`;

Nested relations and the join strategy

Prisma resolves nested relations with separate queries per relation level by default, then stitches the results together in the client, so a two-level select is a small constant number of queries, not N. On Postgres you can ask for a single SQL statement with a lateral join instead. It is still a preview feature in Prisma 7, so switch it on in the generator block first (previewFeatures = ["relationJoins"]) and run bun run db:generate:

const projects = await prisma.project.findMany({
  relationLoadStrategy: "join",
  select: { id: true, tasks: { select: { id: true, title: true } } },
});

Measure before switching. join wins when the round trip dominates; the default query strategy wins when a wide parent row would be duplicated across many children.

Finding the ones you already have

Turn on query logging for a single session and load the page:

PRISMA_LOG_QUERIES=1 bun run dev

Then count. If a page produces a number of SELECTs proportional to the number of items on it, you have found one. In production the same signal shows up in your tracing as a flat wall of identical short spans.

A cheap review heuristic: any await on a prisma. call inside a for, a .map(async ...), or a helper called from either, needs a reason to exist.