Prisma Query Optimization: Stop N+1 Problems and Write Faster Queries

Why Prisma Queries Go Wrong

Prisma's fluent API makes database queries feel trivial. But that ease of use can mask N+1 problems, missing indexes, and over-fetching that destroy performance at scale. A query that runs fine with 100 rows can take 30 seconds with 10,000.

Enable Query Logging First

Before optimizing, see what Prisma is actually sending to your database:

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

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

db.$on('query', (e) => {
  console.log('Query: ' + e.query);
  console.log('Params: ' + e.params);
  console.log('Duration: ' + e.duration + 'ms');
});

You'll often be shocked at how many queries a single API route generates.

The N+1 Problem and How to Fix It

// BAD: N+1 — 1 query for posts + N queries for authors
const posts = await db.post.findMany();
for (const post of posts) {
  const author = await db.user.findUnique({ // N separate queries!
    where: { id: post.authorId }
  });
  console.log(post.title, author.name);
}

// GOOD: 2 queries with include (join under the hood)
const posts = await db.post.findMany({
  include: {
    author: {
      select: { id: true, name: true, avatar: true } // only what you need
    }
  }
});

Select Only What You Need

Over-fetching large text fields (like article content) on list views is a common mistake:

// BAD: fetches all columns including large content field
const posts = await db.post.findMany();

// GOOD: select only columns needed for the list view
const posts = await db.post.findMany({
  select: {
    id: true,
    title: true,
    slug: true,
    summary: true,
    publishedAt: true,
    author: {
      select: { name: true, avatar: true }
    },
    _count: {
      select: { comments: true }
    }
  },
  where: { status: 'PUBLISHED' },
  orderBy: { publishedAt: 'desc' },
  take: 20,
});

Batching with findMany vs Multiple findUnique

Prisma automatically batches multiple findUnique calls within the same tick using dataloader-style batching. But explicit findMany with in is more predictable:

// Explicit batch lookup
const userIds = posts.map(p => p.authorId);
const authors = await db.user.findMany({
  where: { id: { in: userIds } },
  select: { id: true, name: true }
});
const authorMap = new Map(authors.map(a => [a.id, a]));
const enriched = posts.map(p => ({ ...p, author: authorMap.get(p.authorId) }));

Using Raw Queries for Complex Cases

Sometimes Prisma's abstraction generates suboptimal SQL. Use $queryRaw for complex aggregations:

const stats = await db.$queryRaw`
  SELECT 
    category_id,
    COUNT(*) as post_count,
    AVG(view_count) as avg_views
  FROM blog_posts
  WHERE status = 'PUBLISHED'
  GROUP BY category_id
  ORDER BY post_count DESC
`;

Add Indexes for Your Query Patterns

model Post {
  id          String   @id @default(cuid())
  slug        String   @unique
  status      String
  publishedAt DateTime
  authorId    String
  categoryId  String

  // Composite index for the most common query pattern
  @@index([status, publishedAt(sort: Desc)])
  @@index([categoryId, status])
  @@index([authorId])
}

After adding indexes, run EXPLAIN ANALYZE on your slowest queries to confirm they're using them. Prisma Studio shows query plans in the latest versions.

Key Takeaways

• Always log queries in development to catch N+1 issues early
• Use include with select to join and narrow in one query
• Never fetch all columns for list views
• Add composite indexes matching your where + orderBy patterns
• Use $queryRaw when Prisma's abstraction generates bad SQL