Prisma in Production: N+1, include, Batching and Connection Pools

You already know the Prisma, GraphQL and Hasura tools from the frontend side. This lesson shows the backend side in production. You will see what SQL your code really creates, and which settings decide if your API survives heavy traffic.

Words used in this lesson

• Prisma is a Node.js library (an ORM) that lets you read and write a database with TypeScript code instead of hand-written SQL. An ORM (object-relational mapper) is a tool that turns database rows into objects in your language.
• Query is one request you send to the database, for example "give me all users".
• Relation is a link between two tables, such as a user who has many posts.
• Eager loading means loading related data at the same time as the main data. Lazy loading means loading it later, only when your code asks for it.
• Round trip is one trip of a request from your app to the database and back. Each trip costs time.
• Connection pool is a small group of already-open database connections that your app shares, so each request does not have to open a new one.
• Serverless is a way to run code where the cloud company starts and stops your function instances for you. You do not manage a server.
• Resolver is a function in a GraphQL server that finds the data for one field.
• Transaction is a group of database steps that either all succeed or all fail together.
• Migration is a saved, versioned change to the database structure (for example "add a column").

The N+1 problem

N+1 means this: 1 query gets a list of N items. Then N more queries get something for each item. For 100 users, that is 101 queries.

Think of a shopping trip. A smart shopper makes one list and buys everything in one trip. N+1 is like driving to the shop again for every single item.

// ❌ 1 query for users + N queries for their postsconst users = await prisma.user.findMany({ take: 100 });for (const user of users) {  user.posts = await prisma.post.findMany({ where: { authorId: user.id } }); // runs 100 times}

Each query alone is fast (2 ms). So N+1 stays hidden in development with 5 rows. Then it becomes a big problem in production with 5,000 rows. The cost is latency multiplied by N, plus pressure on the connection pool.

Where it hides

• Loops with await inside (as above).
• map with Promise.all. It is still N queries. They just run at the same time, and now they rush your pool all at once.
• GraphQL resolvers. The posts resolver on User runs once for each user in the list (Lesson 09).
• Lazy loading in other ORMs. The ORM fetches related data only when you touch it, so a loop quietly makes one query per row. Prisma does not lazy-load, which is good. But your own helper functions can bring the problem back.
• Serialisation or "to DTO" mappers (a DTO, data transfer object, is a plain object you send out as the response) that call the database for each item.

How to detect it

• Log queries in development:

const prisma = new PrismaClient({ adapter, log: [{ emit: "event", level: "query" }] }); // `adapter`: see "Connection pools" belowprisma.$on("query", (e) => console.log(`${e.duration}ms ${e.query}`));

• Count queries per request in tests. For example, check that an endpoint uses "at most 3 queries".
• Tracing or APM tools (APM means application performance monitoring: tools that record how long each step of a request takes) show a "waterfall" of the same query repeated many times.
• In PostgreSQL, pg_stat_statements is an extension that counts how often each SQL statement runs. It shows statements with a huge number of calls.
• Look for an endpoint whose speed gets worse as the page size grows.

Fix 1: eager loading with include / select

Eager loading means you tell Prisma up front which relations you want. It then loads them together. include adds a relation to the result. select picks exactly which fields to return.

const users = await prisma.user.findMany({  take: 100,  include: { posts: { take: 5, orderBy: { createdAt: "desc" } } },});

With the query load strategy, Prisma runs one query for the users and one WHERE authorId IN (…) query for all their posts. Then it joins the results in memory. That is 2 queries, not 101. Which strategy is the default depends on your Prisma version and database, so set relationLoadStrategy yourself when it matters.

Join strategy

Prisma can ask the database to do the join in one SQL query. In PostgreSQL this uses lateral joins and JSON aggregation.

const users = await prisma.user.findMany({  relationLoadStrategy: "join",       // one query, joined in the database  include: { posts: true },});

A lateral join is a SQL join where the right side can use values from each left row. In Prisma 7.10 the relationLoadStrategy option is typed only when the generator has previewFeatures = ["relationJoins"]. Without it, TypeScript reports the option as never. Check the documentation for the version you use.

Neither one is always better. Measure with EXPLAIN ANALYZE (a PostgreSQL command that runs a query and shows how long each step took) and real data.

select to trim the payload

const users = await prisma.user.findMany({  select: { id: true, name: true, posts: { select: { id: true, title: true } } },});

Fetch only the columns you return. A narrow select means less data to move and less memory use. If all selected columns are in one index, the database may also read only the index (an index-only scan). include returns every simple field of the relation. So select is often the better habit for API endpoints.

Fix 2: batch it yourself with in

Use this when the relation is not in the schema, or when you want more control. in is a filter that matches any value from a list (SQL IN):

const users = await prisma.user.findMany({ take: 100 });const posts = await prisma.post.findMany({ where: { authorId: { in: users.map((u) => u.id) } } });const byAuthor = Map.groupBy(posts, (p) => p.authorId);const result = users.map((u) => ({ ...u, posts: byAuthor.get(u.id) ?? [] }));

This is two queries, no matter how big N is. (Map.groupBy needs Node 21 or newer. For the types you also need lib: ["ES2024"] in your tsconfig. Otherwise, a small reduce works.)

Fix 3: DataLoader (and Prisma's findUnique batching)

DataLoader is a small library that collects many single lookups and turns them into one batched lookup. It gathers all the keys that are asked for in the same tick of the event loop (one short turn of Node.js's work cycle, where it runs one piece of code before moving to the next). Then it runs one batched query, and it caches results by key:

import DataLoader from "dataloader";export const postsByAuthorLoader = () =>  new DataLoader<string, Post[]>(async (authorIds) => {    const posts = await prisma.post.findMany({ where: { authorId: { in: [...authorIds] } } });    const grouped = Map.groupBy(posts, (p) => p.authorId);    return authorIds.map((id) => grouped.get(id) ?? []);   // MUST return results in the same order as the keys  });// GraphQL: create loaders per request (never share across users) and put them in contextconst resolvers = { User: { posts: (user, _args, ctx) => ctx.loaders.postsByAuthor.load(user.id) } };

Prisma also has its own dataloader for findUnique. If you call findUnique many times on the same model in the same tick, Prisma joins them into one WHERE id IN (…) query. So a resolver that calls prisma.user.findUnique({ where: { id } }) for each post does not break. This works only if the calls are findUnique (not findFirst or findMany) and happen in the same tick.

Hasura is a server that reads your PostgreSQL database and builds a GraphQL API for you (Lesson 09). Hasura (and similar GraphQL-to-SQL tools) avoids N+1 by design. It turns the whole GraphQL query into a single SQL statement and joins nested relations in the database. This is a big reason people choose it.

Other Prisma performance habits

• Paginate every list, which means returning it in small pages. Use take, and use cursor-based pagination ("give me the 20 rows after this row") when you can (Practical Design Problems, Lesson 03).
• Index the columns you filter and sort on, including foreign keys. An index is a sorted lookup structure that lets the database find rows without scanning the whole table. A foreign key is a column that points to a row in another table (Lesson 07). Prisma's @@index writes them in the schema.
• Use _count, which asks the database to count related rows, instead of loading rows just to count them: include: { _count: { select: { posts: true } } }.
• Use createMany / updateMany / deleteMany instead of loops of single writes. Use $transaction([...]) for batches that must succeed together.
• Avoid fetching large JSON or text columns unless you need them. Use select.
• Use raw SQL ($queryRaw with the tagged-template form, which keeps values safe) for reports, aggregates and window functions that the Prisma client cannot do well.
• Interactive transactions ($transaction(async tx => …)) hold a connection. Keep them short and do not call outside services inside them. They also have a default timeout (5 seconds in Prisma).
• Cache (keep a copy of a result so you do not compute it again) data that is read a lot and rarely changes (Redis, or memory inside the process). Clear the cache on purpose when the data changes (Scenario Debugging, Lesson 02).

Connection pools

Each Prisma Client instance keeps a pool of database connections. Opening a connection is slow, so a pool keeps some connections open and lends one to each request. When the request ends, the connection goes back to the pool. Where the pool lives depends on your Prisma version:

Prisma 7 (released November 2025) made driver adapters the default. So the connection-string limits from older tutorials no longer work there. Read the pool section of the documentation for your version.

Reuse one PrismaClient

If you create a new PrismaClient for each request, or each time a module reloads, you open a new pool every time. You will quickly reach the database's connection limit.

// lib/prisma.ts — one instance per processimport { PrismaPg } from "@prisma/adapter-pg";import { PrismaClient } from "../generated/prisma/client"; // path depends on the `output` of your generatorconst globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };// Prisma 7: the client takes a driver adapter, and the pool size (`max`) is set on the adapterconst adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL, max: 5 });export const prisma = globalForPrisma.prisma ?? new PrismaClient({ adapter });if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma; // survives hot reload in dev

The globalThis trick matters in Next.js development. Hot reloading (the page updates when you save a file) runs your modules again. Without the trick, it would create a new client on every change.

The serverless connection problem

Scenario: your API runs on serverless functions (Vercel, AWS Lambda). Each function instance has its own pool. Under heavy load, hundreds of instances open connections at the same time. A normal PostgreSQL server allows only about 100 connections. You get "too many connections", requests fail, and the database wastes its CPU on handling connections.

Fixes:

1. Use an external connection pooler (a separate service that holds the real database connections) between the app and the database. Options are PgBouncer (a small proxy program that sits in front of PostgreSQL and shares a few real connections among many clients; "transaction mode" lends a real connection only for the length of one transaction), your cloud provider's pooler (RDS Proxy, Supabase's pooler, Neon's pooler) or Prisma Accelerate (Prisma's hosted pooler). Your instances connect to the pooler with many cheap connections. The pooler shares them over a few real database connections.
2. Set a small pool for each instance. For serverless, 1 to 5 is common. Use connection_limit in Prisma 6 and earlier, or the adapter's max in Prisma 7. This way many instances do not each open a big pool.
3. When you use PgBouncer in transaction mode, follow the guide for your Prisma version. Prisma 6 uses the pgbouncer=true connection-string flag. With a Prisma 7 driver adapter, set up the driver for transaction pooling. Also use a direct (not pooled) URL for migrations.
4. Reuse the client across calls with a module-level singleton (one shared object). Then warm functions do not reconnect.
5. Use a long-running server (a container) for work that needs many connections, if your design allows it.

(PgBouncer's transaction mode means some session-level features need care. Examples are advisory locks (locks your own code takes by number), and prepared statements (a query the database parses once and runs many times) that are used across several statements.)

Migrations in production

• Use prisma migrate deploy in CI/CD (not migrate dev).
• Make migrations backward-compatible. Old code and new code should both work with the same schema during a rolling deploy (a deploy that replaces servers one by one, so old and new code run side by side for a while). This is the expand and contract pattern:
  1. Add a new nullable column.
  2. Deploy code that writes to both columns.
  3. Backfill the old rows, which means filling in the new column for rows that already exist.
  4. Switch the reads to the new column.
  5. Drop the old column later.
• For big tables, add an index with CREATE INDEX CONCURRENTLY in a hand-edited migration. This builds the index without blocking writes to the table. (It cannot run inside a transaction.)

Common mistakes

1. await inside loops (and Promise.all(users.map(...query))) over large lists.
2. include everywhere. It pulls whole relations and wide rows when select would do.
3. Using findFirst or findMany in resolvers. You lose the automatic batching.
4. Global DataLoaders that leak data across users.
5. A new PrismaClient per request or per hot reload.
6. Serverless without a pooler. Then you see "too many connections."
7. List endpoints without pagination.
8. Not logging or counting queries. N+1 stays hidden until production.
9. Interactive transactions that call external services.

Interview questions

Key takeaways

• N+1 means one query for each row. Fix it with include/select (or the join strategy), an IN query, or DataLoader.
• Prisma batches findUnique calls in the same tick automatically. GraphQL tools like Hasura turn nested queries into one SQL statement.
• Prefer select for API responses. Paginate everything. Index the filter and sort columns and the foreign keys.
• Use one PrismaClient per process. Use a pooler (and a small pool for each instance) for serverless.
• Make migrations backward-compatible, and watch your query count.

What's next

Lesson 09: REST vs GraphQL (vs Hasura). N+1 shows up most strongly in GraphQL. Next you will see when each API style fits, and how to protect a GraphQL endpoint.