Fix: Prisma N+1 Query Problem — include, select, and Relation Loading

The Problem

A Prisma query that looks simple generates dozens of database queries:

const posts = await prisma.post.findMany();

for (const post of posts) {
  // Each iteration runs a separate query — N+1 problem
  const author = await prisma.user.findUnique({
    where: { id: post.authorId },
  });
  console.log(`${post.title} by ${author.name}`);
}

With 100 posts, this runs 101 queries: 1 for the posts + 100 for each author.

Or the fluent API causes unexpected queries:

const users = await prisma.user.findMany();

for (const user of users) {
  // Fluent API — each .posts() call runs a separate query
  const posts = await prisma.user.findUnique({ where: { id: user.id } }).posts();
  // 1 (users) + N (each user's posts) = N+1
}

Or in a GraphQL resolver:

// Resolver for the `author` field on Post type
// Called once per post — N queries for N posts
async author(post: Post) {
  return prisma.user.findUnique({ where: { id: post.authorId } });
}

Why This Happens

The N+1 problem in Prisma arises when you fetch a list of records and then query related records one at a time in a loop. The “1” is the initial list query, and the “N” is one query per item in that list to load its relation.

Prisma does not lazy-load relations. When you call prisma.post.findMany(), the returned objects contain only the scalar fields of the Post model — no author, no comments, no tags. The relation fields simply do not exist on the result unless you explicitly request them with include or select. This is by design: Prisma avoids implicit queries that could surprise you. But it means that any code that accesses relations after the initial query must make additional database calls.

The problem is invisible in development with small datasets. Ten posts and ten author lookups take a few milliseconds total. In production, when the post count reaches hundreds or thousands, the accumulated round trips to the database cause response times to spike. Database connection pools fill up. API endpoints that worked fine in staging suddenly time out under real traffic.

Common patterns that trigger it:

• Loop + findUnique/findFirst — the most direct cause. Fetching the parent list, then querying each child separately.
• Fluent API — prisma.user.findUnique(...).posts() is convenient but executes a new database query each time it’s called.
• GraphQL resolvers without DataLoader — each field resolver runs independently. Without batching, an author resolver fetches one user per post.
• Missing include or select — Prisma doesn’t automatically eager-load relations. You must explicitly request them.

Diagnostic Timeline

The first instinct when you hear “N+1” is to add include: { author: true } to the query. But which query? And which relation? A real codebase may have several N+1 patterns stacked in the same request. Walk through these steps to find and fix all of them.

Minute 0 — Enable Prisma query logging. Add log: [{ emit: 'event', level: 'query' }] to your PrismaClient constructor and attach a listener that counts queries. Hit the slow endpoint. If the count is higher than you expect (say, 50 queries for a page that shows 20 items), you have at least one N+1.

Minute 2 — Count SELECT statements per request. In your query log handler, filter for lines starting with SELECT. Group them by table name. If you see 20 SELECT statements against the User table in a single request that loads 20 posts, you found the N+1: each post triggers a separate author lookup.

Minute 4 — Find the loop. Search the handler code for any for, forEach, map, or Promise.all that contains a Prisma call inside the loop body. This is the code generating the N queries. The fix is to move the data fetch out of the loop by using include on the original query or batching the IDs into a single findMany with where: { id: { in: ids } }.

Minute 6 — Check GraphQL resolvers. If the API is GraphQL, field resolvers are the hidden loops. Each resolver runs once per parent object. Check resolvers that call prisma.*.findUnique() — each one is a potential N+1. The fix is DataLoader (see Fix 4).

Minute 8 — Choose include vs select vs raw SQL. For simple one-to-one or one-to-many relations, include is the right tool. For queries that need only a few fields from a large related table, select reduces data transfer. For complex aggregations or joins across many tables, a raw SQL query with prisma.$queryRaw may outperform multiple Prisma calls.

Minute 10 — Measure after the fix. Re-run the query counter. The count should drop from N+1 to 2 (one for the parent, one for the batched relation) or 1 (if using a raw SQL JOIN). If it didn’t drop, another N+1 is still present — repeat from Minute 2.

Fix 1: Use include to Eager Load Relations

include fetches related records in the same query using a JOIN or a batched query:

// BEFORE — N+1 pattern
const posts = await prisma.post.findMany();
for (const post of posts) {
  const author = await prisma.user.findUnique({ where: { id: post.authorId } });
  // 1 + N queries
}

// AFTER — single query with eager-loaded relation
const posts = await prisma.post.findMany({
  include: {
    author: true,   // Fetches author for each post in one operation
  },
});

for (const post of posts) {
  console.log(`${post.title} by ${post.author.name}`);
  // No additional queries — author already loaded
}

Nested includes:

const posts = await prisma.post.findMany({
  include: {
    author: {
      include: {
        profile: true,    // Also include the author's profile
      },
    },
    comments: {
      include: {
        author: true,     // Each comment's author
      },
      orderBy: { createdAt: 'desc' },
      take: 5,            // Only last 5 comments
    },
    tags: true,
  },
});

Note: Prisma’s include uses separate SELECT queries per relation (not JOINs), batching them efficiently. For 100 posts with include: { author: true }, Prisma runs 2 queries total: one for posts and one batched query for all unique authors.

Fix 2: Use select to Fetch Only Needed Fields

include: true fetches all fields of the related record. select fetches only what you need — reducing data transfer:

// include: true — fetches ALL user fields (id, name, email, password, createdAt, ...)
const posts = await prisma.post.findMany({
  include: { author: true },
});

// select — fetches only id and name from author
const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    publishedAt: true,
    author: {
      select: {
        id: true,
        name: true,
        // email, password, etc. not fetched
      },
    },
  },
});

select vs include on the root query:

// Can't use both select and include at the top level — pick one
// select: controls which fields of the parent AND relations are returned
// include: returns all parent fields + the specified relations

// If using select, relations are also specified inside select
const post = await prisma.post.findUnique({
  where: { id: 1 },
  select: {
    title: true,
    author: {         // Relation inside select
      select: { name: true },
    },
  },
});

// If using include, you get all post fields + the relation
const post = await prisma.post.findUnique({
  where: { id: 1 },
  include: {
    author: true,     // Gets all author fields
  },
});

Fix 3: Batch Related Queries with findMany + In

When you need to load relations for a known set of IDs — such as in a GraphQL DataLoader batch function — use a single findMany with IN:

// BEFORE — N individual queries (typical in resolvers without DataLoader)
async function getAuthors(authorIds: number[]) {
  return Promise.all(
    authorIds.map(id => prisma.user.findUnique({ where: { id } }))
  );
  // N queries for N IDs
}

// AFTER — single query for all IDs
async function getAuthors(authorIds: number[]) {
  const users = await prisma.user.findMany({
    where: { id: { in: authorIds } },
  });

  // Map results back to original order (important for DataLoader)
  const userMap = new Map(users.map(u => [u.id, u]));
  return authorIds.map(id => userMap.get(id) ?? null);
  // 1 query for all IDs
}

Fix 4: Use DataLoader for GraphQL Resolvers

In GraphQL, field resolvers run per-object. Without DataLoader, each resolver runs a separate Prisma query:

// graphql/resolvers.ts

// WRONG — N+1 in author resolver
const resolvers = {
  Post: {
    author: (post) => prisma.user.findUnique({ where: { id: post.authorId } }),
    // Runs once per post — 100 posts = 100 queries
  },
};

// CORRECT — DataLoader batches all author lookups into one query
import DataLoader from 'dataloader';

function createLoaders() {
  return {
    user: new DataLoader<number, User | null>(async (ids) => {
      const users = await prisma.user.findMany({
        where: { id: { in: [...ids] } },
      });
      const map = new Map(users.map(u => [u.id, u]));
      return ids.map(id => map.get(id) ?? null);
    }),
  };
}

// Apollo Server context — fresh loaders per request
context: async ({ req }) => ({
  prisma,
  loaders: createLoaders(),
}),

// Resolver uses the loader
const resolvers = {
  Post: {
    author: (post, _, context) => context.loaders.user.load(post.authorId),
    // All author lookups in a request are batched into ONE Prisma query
  },
};

For a deeper dive into DataLoader patterns (including Prisma-specific examples), see Fix: GraphQL N+1 Query Problem.

Fix 5: Enable Prisma Query Logging to Detect N+1

Make N+1 patterns visible by logging all queries in development:

// prisma/client.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'stdout', level: 'error' },
    { emit: 'stdout', level: 'warn' },
  ],
});

// Count queries per operation
let queryCount = 0;

prisma.$on('query', (e) => {
  queryCount++;
  if (process.env.LOG_QUERIES === 'true') {
    console.log(`Query #${queryCount}: ${e.query}`);
    console.log(`Duration: ${e.duration}ms`);
  }
});

// Reset and check count per request (in Express middleware)
app.use((req, res, next) => {
  queryCount = 0;
  res.on('finish', () => {
    if (queryCount > 10) {
      console.warn(`[N+1 Warning] ${req.method} ${req.path} — ${queryCount} queries`);
    }
  });
  next();
});

Prisma’s built-in query logging in schema.prisma:

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["metrics"]  // Enable query metrics
}

// Get metrics snapshot
const metrics = await prisma.$metrics.json();
console.log('Query count:', metrics.counters.find(c => c.key === 'prisma_client_queries_total')?.value);

Fix 6: Avoid Fluent API in Loops

The Prisma fluent API (prisma.model.findUnique(...).relation()) is convenient but executes a new query each call:

// WRONG — fluent API in a loop = N+1
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.user.findUnique({ where: { id: user.id } }).posts();
  // Each .posts() call = 1 query — N users = N queries
}

// CORRECT option 1 — include in the initial query
const users = await prisma.user.findMany({
  include: { posts: true },
});
for (const user of users) {
  console.log(user.posts);  // Already loaded — no additional queries
}

// CORRECT option 2 — one query for all posts
const userIds = users.map(u => u.id);
const posts = await prisma.post.findMany({
  where: { authorId: { in: userIds } },
});

// Group posts by author
const postsByAuthor = new Map<number, Post[]>();
for (const post of posts) {
  const existing = postsByAuthor.get(post.authorId) ?? [];
  postsByAuthor.set(post.authorId, [...existing, post]);
}

Fluent API is fine for single record lookups — just not in loops:

// OK — fluent API for a single record
const userWithPosts = await prisma.user.findUnique({ where: { id: userId } }).posts();
// One query, one record — no N+1 risk

Fix 7: Use Prisma’s findMany with Cursor-Based Pagination

Fetching large datasets page by page with OFFSET is slow and creates N+1 risks when loading relations per page. Use cursor-based pagination instead:

// Cursor-based pagination — efficient for large datasets
async function getPaginatedPosts(cursor?: number, take = 20) {
  return prisma.post.findMany({
    take,
    skip: cursor ? 1 : 0,         // Skip the cursor itself
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { id: 'asc' },
    include: {
      author: {
        select: { id: true, name: true },
      },
      _count: {
        select: { comments: true },   // Comment count without loading comments
      },
    },
  });
}

// First page
const firstPage = await getPaginatedPosts();
const lastId = firstPage.at(-1)?.id;

// Next page — starts after the last item
const secondPage = await getPaginatedPosts(lastId);

_count for aggregate counts without loading relations:

// Get post with comment count — no need to load all comments
const post = await prisma.post.findUnique({
  where: { id: postId },
  include: {
    _count: {
      select: { comments: true, likes: true },
    },
  },
});

console.log('Comment count:', post._count.comments);  // No N+1 — computed in SQL

Still Not Working?

Verify the query count with logging — add the query counter middleware above and check how many queries a route generates. If it’s still N+1 after adding include, the relation may not be configured correctly in schema.prisma.

Check schema.prisma for correct relation definitions:

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  authorId Int
  author   User   @relation(fields: [authorId], references: [id])
  // ↑ This must exist for 'include: { author: true }' to work
}

model User {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]  // ← Back-relation — required on both sides
}

findMany with include still runs 2 queries — Prisma always uses at least 2 queries for include (one for the parent, one for the related records). This is correct behavior, not N+1. N+1 is when queries scale with the number of parent records (N queries for N parents).

Middleware or hooks re-fetching relations — if you have Prisma middleware or $use() hooks that call additional Prisma queries per record (e.g., enriching results with extra data), that middleware itself introduces N+1. Move the enrichment logic into an include or a single batched query.

relationMode = "prisma" (Prisma-level relations without foreign keys) — when using relationMode = "prisma" in schema.prisma (common with PlanetScale), Prisma emulates relations in the client layer, which may generate more queries than database-level foreign key relations. Verify the query count is acceptable, and consider adding database indexes on the foreign key columns manually.

Nested N+1 across multiple relation levels — if your query includes author but the template also accesses author.posts (a reverse relation), that second level triggers another N+1 unless you nested the include: include: { author: { include: { posts: true } } }. Each level of relation access needs its own include.

For related database issues, see Fix: Prisma Unique Constraint Failed, Fix: Prisma Transaction Error, Fix: Prisma Connection Pool Exhausted, and Fix: GraphQL N+1 Query Problem.