You've shipped a feature that lists orders with customer details, and it works fine in testing. Then production hits 100K orders and your API response time climbs to 8 seconds. Welcome to N+1 queries with Prisma—a performance trap that feels innocent until it runs in a loop, silently multiplying database round trips and crushing your throughput.
The N+1 problem explained
N+1 queries happen when you fetch a list of records (N queries), then loop through those results and fetch related data for each one (1 query per item). With Prisma, it's easy to write code that looks clean but executes a catastrophic number of database calls.
Here's the innocent-looking culprit:
// DON'T do this
const orders = await prisma.order.findMany({
take: 100,
});
const ordersWithCustomer = await Promise.all(
orders.map((order) =>
prisma.order.findUnique({
where: { id: order.id },
include: { customer: true },
})
)
);
You fetch 100 orders (1 query), then loop and fetch each one again with its customer (100 more queries). That's 101 database round trips for data you could have loaded in a single query. At scale, this causes timeouts, connection pool exhaustion, and angry oncall pages at 2 AM.
Fetch relations in one query with include or select
Prisma gives you two powerful ways to load related data eagerly: include and select. Both execute in a single database query, eliminating the N+1 trap entirely.
Using include:
const orders = await prisma.order.findMany({
take: 100,
include: {
customer: true,
items: true,
},
});
// Now every order has customer and items populated
// Total: 1 query (or 2-3 depending on join strategy, but still O(1))
orders.forEach((order) => {
console.log(order.customer.name); // No extra query
console.log(order.items.length); // No extra query
});
Include loads the entire related record. It's simple and handles nested relations well. Under the hood, Prisma uses a smart fetching strategy—it might execute 2–3 queries in total, but it's deterministic and won't multiply based on result count.
Using select for more control:
const orders = await prisma.order.findMany({
take: 100,
select: {
id: true,
status: true,
createdAt: true,
customer: {
select: {
id: true,
name: true,
email: true,
},
},
},
});
// Same single query, but you only fetch the columns you need
// Reduces payload size and improves transfer speed
Select is explicit and helps you think about what data flows across the network. In a ride-hailing app, when listing trips, you don't need every driver field—just ID, name, and rating. Select keeps your queries lean.
Never query inside a map over results
The most common pattern that triggers N+1 is mapping over results and fetching extra data:
// ANTI-PATTERN: N+1 disaster
const users = await prisma.user.findMany();
const enriched = await Promise.all(
users.map(async (user) => {
const profile = await prisma.profile.findUnique({
where: { userId: user.id },
});
return { ...user, profile };
})
);
Even with Promise.all, you're still firing N separate queries. It doesn't matter if they run in parallel—each one hits the database independently.
The fix: Use include or select before the map:
// CORRECT: Single query
const users = await prisma.user.findMany({
include: {
profile: true,
},
});
// Now map over fully hydrated data
const enriched = users.map((user) => ({
...user,
profileSummary: `${user.profile.bio}`,
}));
In a trading platform, I once debugged a portfolio summary endpoint that listed 50 users and fetched their trade history in a loop. One query became 2,500. Switching to a single include query dropped response time from 12s to 150ms. The code looked identical from the outside, but the database impact was night and day.
Use select to fetch only needed columns
Select does two things: it prevents N+1 by eager-loading relations, and it shrinks your result set by fetching only the columns you actually need.
Consider a paginated user list in an admin dashboard:
// Overfetching: you get everything
const users = await prisma.user.findMany({
take: 20,
skip: 0,
include: { profile: true },
});
// Result: returns user.passwordHash, user.internalNotes, etc.
// These fields are never displayed; they bloat the JSON response
Better approach with select:
const users = await prisma.user.findMany({
take: 20,
skip: 0,
select: {
id: true,
email: true,
createdAt: true,
profile: {
select: {
firstName: true,
lastName: true,
avatar: true,
},
},
},
});
// Result: lean payload, only fields needed for UI
// Response size reduced by 60%, faster network transfer
This is especially important in mobile apps or high-traffic APIs. Every byte counts. I've seen e-commerce checkouts where listing products with unneeded fields caused 40KB payloads instead of 5KB. Mobile users on 4G felt that pain.
Select also helps catch over-engineering: if you're selecting a field, you're being intentional. It becomes obvious when you're loading data you don't use.
Log queries in development to catch it early
The best way to spot N+1 queries is to see them happening. Prisma has built-in logging that shows every query hitting your database.
Enable query logging:
const prisma = new PrismaClient({
log: ['query', 'info', 'warn', 'error'],
});
// Or conditionally in development:
const prisma = new PrismaClient({
log:
process.env.NODE_ENV === 'development'
? ['query', 'info', 'warn']
: ['warn', 'error'],
});
Now run your code and watch the logs:
prisma:query SELECT "public"."Order"."id", "public"."Order"."customerId" FROM "public"."Order" LIMIT 100 OFFSET 0
prisma:query SELECT "public"."Customer"."id", "public"."Customer"."name" FROM "public"."Customer" WHERE "id" = 1
prisma:query SELECT "public"."Customer"."id", "public"."Customer"."name" FROM "public"."Customer" WHERE "id" = 2
prisma:query SELECT "public"."Customer"."id", "public"."Customer"."name" FROM "public"."Customer" WHERE "id" = 3
... (97 more queries)
See that pattern? 1 + 100. That's your N+1. Now fix it, re-run, and watch it collapse to 1 query:
prisma:query SELECT "public"."Order"."id", "public"."Order"."customerId", "public"."Customer"."id", "public"."Customer"."name" FROM "public"."Order" LEFT JOIN "public"."Customer" ON ... LIMIT 100 OFFSET 0
One query. Done.
Pro tip: In local development, I keep query logging on all the time. It's visual feedback that teaches you Prisma's behavior. When you see a scary query count, you fix it immediately rather than discovering it in production metrics.
Common mistakes
- Assuming Promise.all prevents N+1: It doesn't. Parallel execution is faster than serial, but you're still issuing N separate queries. Use include or select instead.
- Using include with nested maps:
include: { items: true }loads items, but if you then map over those items and fetch extra data per item, you've broken the optimization. Think end-to-end. - Forgetting to test with realistic data: Features work with 10 test records but explode at 10K. Always run query logging on realistic datasets in development.
- Including everything "just in case":
include: { *: true }(if it existed) would bloat responses and hurt caching. Include and select only what you need, when you need it. - Not considering nested relations:
include: { order: { include: { items: true } } }is valid, but if items have their own relations, you can still trigger N+1 at deeper levels. Review your entire query tree.
Key takeaways
- N+1 queries kill performance silently—always use
includeorselectto load relations in a single operation, never in a loop. - Prefer
selectoverincludewhen you need only specific columns; it reduces payload size and teaches you to be intentional about what data flows. - Enable Prisma query logging in development—seeing your queries teaches you how to write efficient code faster than any doc.
- Test with realistic data volumes in development. 10 records won't reveal the N+1 trap; 10K will.
- Code review with an eye for loops that query the database. If a map or forEach fires a Prisma call, refactor to eager loading.
Comments
Loading comments…