Prisma include vs select: Fetching Relations Without the N+1 Trap

The problem: one query becomes dozens

You need a list of posts with their authors and the first three comments on each. The naive loop looks clean:

const posts = await prisma.post.findMany();
for (const post of posts) {
  post.author = await prisma.user.findUnique({ where: { id: post.authorId } });
  post.comments = await prisma.comment.findMany({ where: { postId: post.id }, take: 3 });
}

In development the latency is invisible. In production that loop issues 1 + N + N queries — one for posts, one per author, one per comment batch. With 50 posts you've sent 101 round trips to the database. Prisma's include and select options collapse that into a single query per top-level call, but only if you structure the request correctly.

How include resolves relations in one round trip

When you pass include: { author: true, comments: { take: 3 } } to findMany, Prisma builds a single SQL statement that joins Post, User, and Comment (or issues a batched subquery depending on the connector). The generated query returns duplicated parent rows — one per comment — and Prisma's result merger reconstructs the nested objects in memory. This behavior is consistent across PostgreSQL, MySQL, and SQLite in Prisma 5.x and 6.x; MongoDB uses a different execution path but the client API remains the same.

The schema must declare the relation on both sides:

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  comments  Comment[]
}

model User {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

model Comment {
  id     Int    @id @default(autoincrement())
  text   String
  postId Int
  post   Post   @relation(fields: [postId], references: [id])
}

Run npx prisma generate after editing schema.prisma so the client knows the relation fields exist. Missing or mismatched relation definitions cause client generation errors or runtime P2023 errors.

include vs select: the over-fetching trade-off

include: { author: true } loads every scalar column of the related model — id, name, email, createdAt, and any future columns you add. If you only need the author's name, that's wasted bandwidth and memory. select projects only the columns you ask for:

const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    author: { select: { name: true } },
    comments: { take: 3, select: { text: true } }
  }
});

The generated SQL now projects only Post.id, Post.title, User.name, and Comment.text. Use include when you genuinely need the full related object (e.g., passing it to a serializer that expects all fields). Use select for API responses, DTOs, or any case where the payload size matters.

Worked example: posts with authors and limited comments

Goal: fetch 20 most recent posts, each with its author's name and up to three latest comments, in one database round trip.

1. Ensure the schema has the bidirectional relations shown above.
2. Run npx prisma generate (requires write access to node_modules/.prisma/client).
3. Execute the query with select to bound the payload:

const posts = await prisma.post.findMany({
  take: 20,
  orderBy: { createdAt: 'desc' },
  select: {
    id: true,
    title: true,
    createdAt: true,
    author: { select: { id: true, name: true } },
    comments: {
      take: 3,
      orderBy: { createdAt: 'desc' },
      select: { id: true, text: true, createdAt: true }
    }
  }
});

Enable query logging to verify a single statement:

const prisma = new PrismaClient({
  log: [{ level: 'query', emit: 'event' }]
});
prisma.$on('query', (e) => console.log(e.query));

Expected check: the log shows one SELECT with joins (or a batched subquery) rather than 21+ separate queries. Risk: if you omit take on comments, a post with thousands of comments will duplicate the parent row thousands of times, inflating memory and network transfer.

Limitations and when to reach for something else

• Deep nesting: include: { a: { include: { b: { include: { c: true } } } } } works but can produce massive Cartesian products. Pair with take at each level or switch to multiple queries with Promise.all if the depth exceeds three levels.
• Many-to-many via implicit relations: Prisma creates a join table automatically. include still issues one query, but the join table columns are not exposed unless you define an explicit model.
• MongoDB connector: Nested include/select uses aggregation pipelines; performance characteristics differ from relational connectors. Test with realistic data volumes.
• Filtering on related fields: You cannot where on a relation inside include. Use where at the top level with relation filters (e.g., where: { author: { name: { contains: 'Alice' } } }) or fetch the relation separately.

Verify it yourself

Add log: ['query'] to your PrismaClient options, run the select query above, and count the emitted queries. Then comment out the select and replace it with the naive loop; the query count will jump from 1 to 1 + N + N. That single comparison is the most reliable proof that the N+1 problem is solved.