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:

• 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 with include or select.

Prisma does NOT automatically detect or prevent N+1 — unlike some ORMs, it executes exactly the queries you write.

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).

For related database issues, see Fix: Prisma Unique Constraint Failed and Fix: TypeORM QueryFailedError.