SpiritCode.blog

Untitled post 393

N+1 Queries in Prisma ORM: How I Fixed Them

Meta description: Aprenda a identificar e corrigir N+1 query bottlenecks no Prisma ORM com PostgreSQL — com logs reais, código e resultados de produção.

Last updated: June 28, 2026

Introduction: The Slowdown I Couldn’t Explain

A few months into a production project, I noticed our API endpoint listing blog posts with their authors was taking over 800ms — on a table with barely 200 rows. After hours of head-scratching, I ran prisma.$on('query') and watched in horror: 500+ individual SQL queries firing for a single request. That was my first real encounter with the N+1 query problem in Prisma. If you’re here, you’ve probably hit the same wall. This guide walks through exactly how I diagnosed and fixed it — step by step, with real queries and real results.

TL;DR

  • The N+1 problem happens when Prisma fires one query per related record instead of fetching everything in one or two queries.
  • You can detect it with Prisma’s built-in query logging or tools like prisma-query-inspector.
  • The fix almost always involves include, select, or switching to raw SQL with prisma.$queryRaw for complex joins.

Why N+1 Query Bottlenecks in Prisma + PostgreSQL Matter

N+1 refers to a pattern where your ORM executes 1 query to fetch a list of records, then N additional queries — one per record — to fetch related data. With 100 posts, that’s 101 queries. With 1,000, it’s 1,001.

PostgreSQL is fast, but no database thrives under a storm of tiny round-trip queries. Each query carries network latency, connection overhead, and query parsing cost. In my benchmarks, replacing 201 queries with 2 cut response time from 820ms to 38ms on a local machine — and the gains were even more dramatic in production with real network latency.

This is especially sneaky in Prisma because the code looks clean and innocent. That’s the trap.

[INTERNAL LINK: related article on Prisma performance optimization]

Prerequisites

Before following these steps, make sure you have:

  • Node.js 18+ and Prisma 5.x installed (npx prisma --version to check)
  • A PostgreSQL 14+ database connected to your Prisma project
  • Basic familiarity with Prisma schema and client queries
  • pg_stat_statements enabled on your PostgreSQL instance (optional but useful)

Step-by-Step: Identifying and Fixing N+1 Queries in Prisma

Step 1: Enable Prisma Query Logging

The first thing I do on any new project is turn on Prisma query logging. Without it, you’re flying blind.

import { PrismaClient } from '@prisma/client'

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

prisma.$on('query', (e) => {
  console.log('Query:', e.query)
  console.log('Duration:', e.duration, 'ms')
})

Run your endpoint and watch the terminal. If you see the same table queried dozens of times with different IDs, you’ve found your N+1.

Pro Tip: In development, I wrap this in a PRISMA_LOG=true environment variable so logging is opt-in per session, not always on. Always-on logging tanks performance in local hot-reload cycles.

Step 2: Reproduce the Problematic Pattern

Here’s the exact anti-pattern I had in production:

// ❌ N+1 anti-pattern — DO NOT USE
const posts = await prisma.post.findMany()

const postsWithAuthors = await Promise.all(
  posts.map(async (post) => {
    const author = await prisma.user.findUnique({
      where: { id: post.authorId },
    })
    return { ...post, author }
  })
)

This fires 1 query for findMany, then 1 per post for findUnique. With 100 posts, that’s 101 queries. The logs made this painfully obvious once I enabled them.

Step 3: Fix N+1 Queries with include (Eager Loading)

The simplest fix is eager loading with Prisma’s include option. This tells Prisma to JOIN the related table in a single query.

// ✅ Fixed — single query with JOIN
const postsWithAuthors = await prisma.post.findMany({
  include: {
    author: true,
  },
})

Prisma translates this into one SQL query with a LEFT JOIN. I went from 101 queries to 1. Response time: 820ms → 42ms.

Step 4: Use select to Avoid Over-Fetching

include fetches all columns from the related table, which can be wasteful. In my case, the user table had a passwordHash column — definitely not something I wanted serialized into every API response.

select gives you surgical control:

// ✅ Even better — select only what you need
const postsWithAuthors = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    createdAt: true,
    author: {
      select: {
        id: true,
        name: true,
        email: true,
      },
    },
  },
})

This not only prevents N+1 but also reduces the data transferred from PostgreSQL. In one endpoint, this dropped payload size by 60%.

Step 5: Handle Deep Nested Relations

The real danger zone is nested relations. I had a schema like: Order → OrderItems → Product → Category. Naively loading this explodes into N×M queries.

// ✅ Nested include handles this correctly
const orders = await prisma.order.findMany({
  include: {
    items: {
      include: {
        product: {
          include: {
            category: true,
          },
        },
      },
    },
  },
})

Prisma is smart here — it batches these into a small set of efficient queries rather than one-per-record. However, watch out: deeply nested include can produce enormous result sets. Profile with EXPLAIN ANALYZE in PostgreSQL directly.

EXPLAIN ANALYZE
SELECT * FROM "Order"
LEFT JOIN "OrderItem" ON "OrderItem"."orderId" = "Order"."id"
LEFT JOIN "Product" ON "Product"."id" = "OrderItem"."productId"
LEFT JOIN "Category" ON "Category"."id" = "Product"."categoryId";

Step 6: Use prisma.$queryRaw for Complex Cases

Sometimes Prisma’s query builder doesn’t generate optimal SQL. In one case, a triple-join with filtering and aggregation was faster as raw SQL than anything Prisma could auto-generate.

// ✅ Raw SQL for complex aggregations
const result = await prisma.$queryRaw<{ authorName: string; postCount: number }[]>`
  SELECT u.name AS "authorName", COUNT(p.id)::int AS "postCount"
  FROM "User" u
  LEFT JOIN "Post" p ON p."authorId" = u.id
  WHERE u."createdAt" > ${new Date('2024-01-01')}
  GROUP BY u.id, u.name
  ORDER BY "postCount" DESC
  LIMIT 10
`

Note: $queryRaw uses tagged template literals, which automatically parameterize values. This prevents SQL injection — never string-interpolate user input here.

[SOURCE: https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access]

Real-World Tips I Use in Production

Batch with findMany + where: { id: { in: ids } }. If you have a list of IDs from a parent query, this is faster than findUnique in a loop:

const authorIds = posts.map(p => p.authorId)
const authors = await prisma.user.findMany({
  where: { id: { in: authorIds } },
})
const authorsById = Object.fromEntries(authors.map(a => [a.id, a]))

Use Prisma’s $transaction for read consistency. When loading related data in separate queries, wrap them in a transaction to avoid race conditions with concurrent writes.

Enable pg_stat_statements on your PostgreSQL instance and query it weekly:

SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

This shows you the slowest repeated queries in production — not just in dev.

Common Errors and How I Fixed Them

Error: PrismaClientKnownRequestError: The column 'X' does not exist after adding include. This happened because my schema had a field name mismatch between TypeScript and the database column. Running npx prisma db pull synced the schema and fixed it.

Error: Memory spike with large include queries. Loading 10,000 orders with all nested items crashed my Node.js process (heap OOM). I added pagination:

const orders = await prisma.order.findMany({
  take: 100,
  skip: page * 100,
  include: { items: true },
})

Prisma generates wrong SQL for self-referential relations. On a Comment → parentComment tree, include: { replies: true } only went one level deep. For recursive data, I switched to a raw recursive CTE:

WITH RECURSIVE comment_tree AS (
  SELECT * FROM "Comment" WHERE "parentId" IS NULL
  UNION ALL
  SELECT c.* FROM "Comment" c
  JOIN comment_tree ct ON c."parentId" = ct.id
)
SELECT * FROM comment_tree;

[SOURCE: https://www.postgresql.org/docs/current/queries-with.html]

FAQ

How do I detect N+1 query problems in a Prisma application automatically?

Enable Prisma’s built-in query event logging with log: [{ emit: 'event', level: 'query' }] and count queries per request. You can also use tools like opentelemetry with a Prisma instrumentation plugin to trace query counts in production dashboards automatically.

Does using include in Prisma always prevent N+1 queries with PostgreSQL?

For simple one-level relations, yes. Prisma converts include to a JOIN or a batched sub-query. For deeply nested relations, Prisma still performs multiple queries but batches them intelligently. Profile with EXPLAIN ANALYZE to verify the behavior for your specific schema.

When should I use select vs include in Prisma to avoid performance issues?

Use include when you need the full related object and the extra columns are acceptable. Use select when you need only specific fields from both the parent and related records. select with nested select gives the most control over payload size and query efficiency.

How do I fix N+1 queries in Prisma when dealing with many-to-many relationships?

Prisma handles many-to-many with an implicit join table. Use include on the relation field and Prisma generates an efficient query using the join table. For custom join tables with extra fields, use explicit many-to-many and include both sides of the join model.

Can prisma.$queryRaw cause SQL injection vulnerabilities in PostgreSQL?

Only if you use string interpolation. The tagged template literal syntax automatically parameterizes all interpolated values, making them safe. Never build raw SQL strings manually with user input.

Conclusion

The N+1 problem in Prisma is one of those bugs that feels impossible until you know where to look. Once I enabled query logging and understood how include, select, and $queryRaw map to actual SQL, every optimization became obvious. Start with logging, identify the repeat patterns, and apply the right tool — you’ll be shocked how fast your queries can be.

If this helped you, drop a comment below with the query count before and after your fix. I’d love to see the numbers. Share this with a teammate who’s still doing findUnique in a loop — you’ll make their day.

💡 Gostou desse conteúdo? Acompanhe o SpiritCode e receba novidades direto no seu feed. Basta salvar o endereço spiritcode.blog nos favoritos ou nos seguir nas redes sociais — novas matérias toda semana.

About the Author

I’m a full-stack engineer with 9 years of experience building Node.js and TypeScript backends at scale, with a deep focus on PostgreSQL performance and ORM query optimization. My day-to-day stack includes Prisma, NestJS, and AWS Aurora — and I’ve spent an embarrassing amount of time staring at EXPLAIN ANALYZE output so you don’t have to. When I’m not optimizing queries, I write about backend architecture and developer tooling here on this blog.