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

> A loop that queries per row turns one page load into hundreds of round trips. Fetch relations in the parent query, batch by id, and aggregate in the database.

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

```ts
// 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:

```ts
// 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:

```ts
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:

```ts
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.

```ts
// 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:

```ts
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`:

```ts
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:

```bash
PRISMA_LOG_QUERIES=1 bun run dev
```

Then count. If a page produces a number of `SELECT`s 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.

---

Agentic Boilerplate: A Next.js repo your agent already knows. Free during launch, then $99 once.

- Site map for agents: https://agenticboilerplate.com/llms.txt
- Public API: https://agenticboilerplate.com/openapi.json
- Contact: agenticstudio@gmail.com