Prisma Performance Optimization Tips

Prisma Performance Optimization Tips

Prisma makes database access simple, but that simplicity can hide performance problems. The most common issues — N+1 queries, over-fetching, and connection exhaustion — are avoidable with the right patterns.

Fixing N+1 Queries

The N+1 problem happens when you fetch a list and then query related data for each item individually. Prisma's include solves this with a single query:

// Bad: N+1 — one query for posts, then one per post for author
const posts = await prisma.post.findMany();
for (const post of posts) {
  const author = await prisma.user.findUnique({
    where: { id: post.authorId },
  });
}

// Good: Eager loading with include
const posts = await prisma.post.findMany({
  include: { author: true },
});

Use select to Reduce Payload

By default, Prisma returns all columns. Use select to fetch only what you need:

// Fetches every column — wasteful for a list page
const users = await prisma.user.findMany();

// Fetches only what the UI needs
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    avatarUrl: true,
  },
});

Rule of thumb: Use select for list views and API responses. Use full objects only when you need every field.

Combine include and select

You can nest select inside include to control what related data is fetched:

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

Connection Pooling

Each Prisma Client instance creates its own connection pool. In serverless environments, connections can exhaust your database:

// lib/prisma.ts — singleton pattern
import { PrismaClient } from "@prisma/client";

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

if (process.env.NODE_ENV !== "production") {
  globalForPrisma.prisma = prisma;
}

For serverless, consider Prisma Accelerate or PgBouncer to pool connections across function invocations.

Batch Operations

Use createMany, updateMany, and deleteMany instead of looping:

// Bad: 100 individual INSERT statements
for (const item of items) {
  await prisma.product.create({ data: item });
}

// Good: Single batched INSERT
await prisma.product.createMany({
  data: items,
  skipDuplicates: true,
});

Raw Queries for Complex Operations

When Prisma's query builder is not enough, drop to raw SQL:

const topAuthors = await prisma.$queryRaw
  SELECT u.id, u.name, COUNT(p.id) as post_count
  FROM "User" u
  JOIN "Post" p ON p."authorId" = u.id
  WHERE p."createdAt" > \${thirtyDaysAgo}
  GROUP BY u.id, u.name
  ORDER BY post_count DESC
  LIMIT 10
;

Indexing Strategy

Add indexes in your Prisma schema for columns you filter, sort, or join on:

model Post {
  id        String   @id @default(cuid())
  title     String
  slug      String   @unique
  authorId  String
  createdAt DateTime @default(now())

  @@index([authorId])
  @@index([createdAt])
  @@index([authorId, createdAt])
}

Logging Slow Queries

Enable query logging to find bottlenecks:

const prisma = new PrismaClient({
  log: [
    { level: "query", emit: "event" },
  ],
});

prisma.$on("query", (e) => {
  if (e.duration > 100) {
    console.warn(Slow query (\${e.duration}ms): \${e.query});
  }
});

Conclusion

Most Prisma performance issues come from N+1 queries and over-fetching. Use include for eager loading, select to limit fields, batch operations for bulk writes, and raw queries for complex analytics. Add indexes based on your actual query patterns and monitor slow queries in development.