N+1 isn't a database problem. It's a shape.
An admin page lists every API key, and next to each key, the roles it has. The data model is many-to-many: one key can hold several roles, and one role can belong to several keys, so the pairing lives in a junction table links.
api_keys(api_key, name)
links(api_key, admin_role_id) ← junction, many-to-many
roles(id, name, description)
api_keys ──< links >── rolesTo get one key's roles, you go to links for its role ids, then to roles to read those roles. All code below uses Prisma with PostgreSQL. Assume one database round-trip takes 5ms and there are 5 keys.
The obvious version: 11 queries
const keys = await prisma.apiKey.findMany(); // 1 query
for (const key of keys) {
const links = await prisma.link.findMany({
where: { apiKey: key.apiKey },
}); // 1 per key
key.roles = await prisma.role.findMany({
where: { id: { in: links.map(l => l.adminRoleId) } },
}); // 1 more per key
}Count the queries:
1 × SELECT api_keys
5 × SELECT links WHERE api_key = ? ← once per key
5 × SELECT roles WHERE id IN (...) ← once per key
= 11 queries, one after another, about 55msWith 100 keys, it's 1 + 2 × 100 = 201. The query count grows with the number of keys. That's N+1. Each key here crosses two tables, so it works out to 1 + 2N.
Every single query runs fine on the database. The loop sends those 11 queries one iteration at a time. If each iteration called another service's HTTP API, did a Redis GET, or read a file, it would still be N+1. Only the cost of each round-trip changes. So instead of "don't query the database inside a loop," remember: don't cross a boundary inside a loop.
Send them at once with Promise.all: still 11
In the loop, each key waits for the previous key to finish. Send them all at once instead:
const keys = await prisma.apiKey.findMany();
await Promise.all(keys.map(async key => {
const links = await prisma.link.findMany({ where: { apiKey: key.apiKey } });
key.roles = await prisma.role.findMany({
where: { id: { in: links.map(l => l.adminRoleId) } },
});
}));The 5 keys query links at the same time, and each then queries roles. With 5 or more connections in the pool, the total wait is three round-trips (api_keys, links, roles), about 15ms.
The page is faster, but the database still receives 11 queries. With 100 keys, 100 links queries want a connection at the same time, and if the pool is smaller than that, the rest wait in line.
Collect the keys, query once with IN: 3
Each loop iteration queries with a single key. Collect all the keys first, and each table needs only one query:
const keys = await prisma.apiKey.findMany(); // 1 query
const links = await prisma.link.findMany({
where: { apiKey: { in: keys.map(k => k.apiKey) } },
}); // 1 query
const roleIds = [...new Set(links.map(l => l.adminRoleId))];
const roles = await prisma.role.findMany({
where: { id: { in: roleIds } },
}); // 1 query
// put each role back under its key
const roleMap = new Map(roles.map(r => [r.id, r]));
const keyMap = new Map(keys.map(k => [k.apiKey, { ...k, roles: [] }]));
for (const l of links) keyMap.get(l.apiKey).roles.push(roleMap.get(l.adminRoleId));1 × SELECT api_keys
1 × SELECT links WHERE api_key IN (5 keys)
1 × SELECT roles WHERE id IN (3 roles)
= 3 queriesLike Promise.all, it's three round-trips, about 15ms. But the database receives only 3 queries, and it stays 3 whether there are 5 keys or 100. The new Set on the second hop removes duplicate role ids: several of the 5 keys share a role, so only 3 roles need querying.
This works because when keys.map(k => k.apiKey) runs, keys already holds all 5 keys.
Hand it to Prisma's include: still 3
Collecting keys, querying with IN, and putting results back: Prisma's include does all three. As long as the schema defines the relations (ApiKey has links, Link has role), it's one call:
const keys = await prisma.apiKey.findMany({
include: { links: { include: { role: true } } },
});According to the Prisma docs, without the relationJoins preview feature, Prisma queries each table once and joins the results in the application, so it's still 3 queries. You just don't write the IN and the Map yourself.
Let the database JOIN: 1
When all three tables live in the same database, the database can connect them itself:
SELECT k.api_key, k.name, r.id AS role_id, r.name AS role_name
FROM api_keys k
LEFT JOIN links l ON l.api_key = k.api_key
LEFT JOIN roles r ON r.id = l.admin_role_id;1 query, 1 round-trip, about 5ms. If you run this SQL with $queryRaw, a key with 3 roles comes back as 3 rows, and you group by api_key yourself. The other option is to enable relationJoins in schema.prisma and add relationLoadStrategy next to include:
// schema.prisma: generator client { previewFeatures = ["relationJoins"] }
const keys = await prisma.apiKey.findMany({
relationLoadStrategy: 'join',
include: { links: { include: { role: true } } },
});On PostgreSQL, Prisma then uses a LATERAL JOIN to fetch everything in one query, and the result is still grouped objects.
A JOIN only works inside one database. If roles live in another service, you're back to IN: query each side and combine.
Switch to GraphQL resolvers: back to 11
Every step so far relied on one thing: a variable holding all the keys at once. In GraphQL, that variable is no longer in the code you write.
query {
apiKeys {
name
adminRoles { id, name }
}
}adminRoles is a field resolver that runs once per key:
@ResolveField(() => [RoleDto], { name: 'adminRoles' })
async adminRoles(@Parent() apiKey: ApiKeyDto) {
const links = await this.prisma.link.findMany({
where: { apiKey: apiKey.apiKey },
}); // hop 1
return this.prisma.role.findMany({
where: { id: { in: links.map(l => l.adminRoleId) } },
}); // hop 2
}After the GraphQL engine gets 5 keys, it calls adminRoles once for each, and it calls resolvers on the same level at the same time. There's no loop in the code, yet it runs like the first step's loop plus the second step's Promise.all. Back to 11 queries.
Each resolver call receives a single apiKey, so it can't collect the keys for an IN query by itself.
Collect the resolver calls with DataLoader: back to 3
dataloader is an npm package Facebook open-sourced. Each resolver now calls .load(key). GraphQL calls the 5 resolvers at the same time, so the 5 .load() calls land in the same round of the event loop. DataLoader waits for that round to end, then hands all 5 keys to the batch function at once. The batch function holds the same IN queries as before.
import DataLoader from 'dataloader';
@Injectable({ scope: Scope.REQUEST }) // one independent instance per request
export class ApiKeyRoleLoader {
constructor(private readonly prisma: PrismaService) {}
// called once, after every .load() key from the same round is collected
private readonly loader = new DataLoader<string, Role[]>(async (apiKeys) => {
const links = await this.prisma.link.findMany({ where: { apiKey: { in: [...apiKeys] } } });
const roleIds = [...new Set(links.map(l => l.adminRoleId))];
const roles = await this.prisma.role.findMany({ where: { id: { in: roleIds } } });
const roleMap = new Map(roles.map(r => [r.id, r]));
const grouped = new Map(apiKeys.map(k => [k, [] as Role[]]));
for (const l of links) grouped.get(l.apiKey)!.push(roleMap.get(l.adminRoleId)!);
return apiKeys.map(k => grouped.get(k)!); // same order as the incoming keys
});
load(apiKey: string) { return this.loader.load(apiKey); }
}The resolver becomes one line:
@ResolveField(() => [RoleDto], { name: 'adminRoles' })
adminRoles(@Parent() apiKey: ApiKeyDto) {
return this.loader.load(apiKey.apiKey);
}1 × SELECT api_keys
1 × SELECT links WHERE api_key IN (5 keys)
1 × SELECT roles WHERE id IN (3 roles)
= 3 queries (11 → 3)The queries are identical to the hand-written IN step. There, the keys were collected on the keys.map(...) line. Here, DataLoader collects them from 5 .load() calls. The batch function must return its array in the same order as the incoming keys, or each resolver gets another key's roles.
How DataLoader "collects automatically"
Resolvers call .load() one at a time. How does DataLoader know when to send them together?
.load(key) only adds the key to a batch and immediately returns a pending promise. When the current round of the event loop ends, DataLoader calls the batch function once. When the query results come back, it fills each promise by index.
So when does a "round" end? Take await Promise.all(ids.map(id => loader.load(id))). In order:
1. map runs load(id1): the key joins the batch, DataLoader schedules a dispatch (sending the batch)
2. map keeps going, load(id2) … load(id100): every key joins the same batch
3. map finishes, the synchronous code ends
4. promise callbacks run until the microtask queue is empty, including ones queued along the way; any .load() inside them also joins this batch
5. the dispatch runs: the batch function is called once with every keyStep 4 is common in GraphQL. A child resolver only starts after its parent resolver's promise resolves, so the child's .load() calls happen inside exactly this kind of callback.
The dispatch scheduled in step 1 is Promise.resolve().then(() => process.nextTick(dispatch)). It is wrapped twice: .then waits out step 2, and process.nextTick waits out step 4.
.then: its callback can't run until step 3 is over, so every key from step 2 makes it in.process.nextTick:Promise.resolve().then().then().then(() => loader.load(x))calls.load()three hops later, and x still lands in the same batch as the keys from step 2. Once V8 starts running promise callbacks, it keeps going until the microtask queue is empty, including callbacks queued along the way, before handing control back to Node. DataLoader callsprocess.nextTickinside one of those.thencallbacks, so Node runs the dispatch only after the queue is empty and control is back. Callprocess.nextTick(dispatch)directly in step 1 instead, and the dispatch runs before those step 4 promise callbacks. The next GraphQL level's keys would miss this batch.
What breaks batching
Put a real await (waiting on real I/O) between two .load() calls and the batch is sent early at that point. If a loop awaits on every iteration, 100 loads become 100 batches:
// ✓ same round: 100 loads in one batch → 1 query
await Promise.all(ids.map(id => loader.load(id)));
// ✗ sequential loop: each await yields, dispatch runs early, each its own batch → 100 queries
for (const id of ids) await loader.load(id);In the second version, each await loader.load(id) waits for its query to finish before the next iteration. So the first key goes out as a batch of one. Only after it returns does the loop call .load() a second time, and DataLoader opens a new batch. 100 keys means 100 batches, and with DataLoader in place the code is back at N+1.
GraphQL resolvers also each await load(), but they don't hit this. The engine calls resolvers on the same level at the same time, so all 5 .load() calls are made before any await finishes, and they land in the same round.
p-limit caps how many run at once
p-limit is an npm package that caps how many async tasks run at the same time. Hand 100 queries to pLimit(10) and all 100 still go out, but at most 10 run at once while the rest wait in a queue.
| DataLoader | p-limit | |
|---|---|---|
| Solves | N+1 (too many queries) | pool exhaustion (too many connections at once) |
| 100 calls | merged into 1 query | still 100 runs, at most 10 at once |
| Fits | rows that merge into WHERE IN (...) | tasks that each do something different (read → decide → update) |
DataLoader can still run the pool short. If one level has three fields, each with its own loader, one request needs three connections at once; ten concurrent requests need thirty. Then you can enlarge the pool, or use p-limit to cap how many requests are handled at once. As for how long a query waits for a connection, the Prisma docs say: in Prisma v6, pool_timeout defaults to 10 seconds; in v7, Postgres goes through the pg adapter, whose connectionTimeoutMillis defaults to 0, meaning no limit.
Back to the Promise.all step. If each task does something different and can't merge into one IN, you have to send N queries, and that's where p-limit keeps them from using up every connection in the pool at once.
To recap, with the same 5 keys:
for loop 11 queries ~55ms
→ Promise.all 11 queries ~15ms
→ collect keys, IN 3 queries ~15ms
→ Prisma include 3 queries ~15ms
→ JOIN 1 query ~5ms
GraphQL resolver 11 queries ~15ms
→ DataLoader 3 queries ~15msPromise.all only shortens the wait. The query count drops only when queries are merged. From IN onward, every step merges N queries into one; the merge just happens in a different place each time: your own code, Prisma, the database, and DataLoader. A GraphQL resolver only ever receives one key, so DataLoader uses Promise.then plus process.nextTick to time the dispatch and collect every .load() from the same round.