Queries, N+1 and Performance
15. Find the N+1 problem and fix it.
const users = await prisma.user.findMany({ where: { active: true } });
for (const user of users) {
user.posts = await prisma.post.findMany({ where: { authorId: user.id } });
}
Problem: one query for users plus one query per user. With 500 active users, that is 501 round trips.
Fixes, from the Prisma query optimization docs:
include: findMany({ where: { active: true }, include: { posts: true } }). With the default application-level strategy this is two SQL queries total.
- An
in filter: fetch posts with where: { authorId: { in: userIds } } and group them in memory, also two queries.
- Database joins:
relationLoadStrategy: "join" runs one query (question 17).
The GraphQL version of the problem is resolvers calling findUnique per parent.
Prisma's dataloader batches findUnique() calls made in the same tick with the same parameters and selection, so the fluent form prisma.user.findUnique({ where: { id } }).posts() batches.
The same docs state that findMany() is not batched, which is the detail most candidates miss.
16. When do you use select, include and omit?
include adds relations to the default scalar set. select lists exactly which fields and relations come back, and nested select trims relations too. omit removes named fields from the default set, which is the clean way to keep passwordHash out of every user query; it can be set per query or globally on the client.
const prisma = new PrismaClient({
adapter,
omit: { user: { passwordHash: true } },
});
const list = await prisma.user.findMany({
select: { id: true, email: true, _count: { select: { posts: true } } },
});
A strong candidate selects narrowly on list endpoints (wide rows and big JSON columns cost network and memory) and uses _count instead of loading a relation to count it.
A weak candidate returns Prisma models straight from an API handler and leaks internal columns.
17. What does relationLoadStrategy do, and when would you pick "query" over "join"?
It chooses how Prisma loads relations.
Per the Prisma 7 relation queries docs, "join" uses a LATERAL JOIN on PostgreSQL or correlated subqueries on MySQL and aggregates related rows as JSON in the database, so one query comes back. "query" sends one query per table and merges in the application.
In Prisma 7 the option still requires the relationJoins preview flag and works on PostgreSQL, CockroachDB and MySQL.
const users = await prisma.user.findMany({
relationLoadStrategy: "join",
include: { posts: { take: 5, orderBy: { createdAt: "desc" } } },
});
Pick "query" when the database is the bottleneck and app servers have headroom, or when JSON aggregation of a wide relation costs more than two plain queries.
The docs recommend profiling, and a senior candidate would compare EXPLAIN ANALYZE output for both before changing a default.
18. Offset or cursor pagination? Write the cursor version.
Offset pagination (skip plus take) makes the database read and discard every skipped row, so page 5,000 is slow, and inserts shift rows between pages. Cursor pagination starts after a known unique value and stays fast at any depth.
async function nextPage(cursorId?: number) {
return prisma.post.findMany({
take: 20,
...(cursorId ? { skip: 1, cursor: { id: cursorId } } : {}),
orderBy: { id: "asc" },
});
}
skip: 1 skips the cursor row itself. The cursor must be unique and the orderBy must match it; ordering by createdAt alone breaks on ties, so order by [{ createdAt: "desc" }, { id: "desc" }] and use a compound cursor.
Cursor pagination cannot jump to page 37, which is a product question worth raising.
19. What does this code do?
async function purgeTenant(tenantId?: string) {
const { count } = await prisma.auditLog.deleteMany({
where: { tenantId },
});
return count;
}
await purgeTenant(req.body.tenantId); // body was {"tenant_id": "t_42"}
Answer: it deletes every audit log row for every tenant. The request used tenant_id, so req.body.tenantId is undefined, and Prisma treats undefined in a filter as "leave this condition out". The where becomes empty.
Fix: validate input at the boundary (Zod or similar) so tenantId is a required string before it reaches Prisma, and throw if it is missing.
Prisma also added a strictUndefinedChecks preview feature in 5.20 that throws on explicit undefined and offers Prisma.skip for intentional omission.
Candidates who have run multi-tenant systems tend to know this bug from experience; it is one of the highest-signal questions on the list.
20. Your API's p99 latency rose after upgrading to Prisma 7 under high concurrency. Where do you look?
Start with the event loop. In Prisma 7 the query compiler runs synchronously on the main thread. Prisma's 7.4.0 notes put compilation at 0.1 ms to 1 ms per query, which adds up when thousands of distinct query shapes compete.
7.4.0 added an LRU plan cache that normalizes values into placeholders, so findUnique({ where: { email } }) compiles once for every email. 7.8.0 added queryPlanCacheMaxSize to the constructor (0 disables the cache).
Then check the usual suspects: pool size per instance versus concurrency, slow SQL in the database logs, and the type of relation loading.
A strong candidate measures event loop delay (for example with perf_hooks.monitorEventLoopDelay), enables query events or OpenTelemetry tracing, and tags queries with SQL comments, which Prisma 7.1.0 added through sqlcommenter plugins, so slow statements in pg_stat_statements trace back to a route.