Optimize Prisma ORM for Performance and Reliability

Prisma Expert covers schema design, migrations, query optimization, relation modeling, and connection/transaction handling for Prisma ORM across PostgreSQL, MySQL, and SQLite. It explicitly hands off to other specialists for raw SQL optimization, database server configuration, or infrastructure-level connection pooling rather than covering those itself, and starts by checking the environment (npx prisma --version, the configured provider, existing migrations, and client generation status).

Use it across five problem playbooks, each with a progressive minimal-to-complete fix strategy. Schema design issues (bad relation definitions, missing indexes, enum drift) are diagnosed with npx prisma validate/migrate diff/format, fixed by adding @relation directives, then @@index, then full normalization. Migration issues (team conflicts, failed migrations, shadow-database problems) use prisma migrate status, fixed by resetting dev databases, then prisma migrate resolve, then squashing/baselining - critically, production always uses prisma migrate deploy, never migrate dev. Query optimization targets N+1 problems and over-fetching:

// BAD: N+1 problem
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

// GOOD: Include relations
const users = await prisma.user.findMany({
  include: { posts: true }
});

// BETTER: Select only needed fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    posts: {
      select: { id: true, title: true }
    }
  }
});

// BEST for complex queries: Use $queryRaw
const result = await prisma.$queryRaw`
  SELECT u.id, u.email, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  GROUP BY u.id
`;

Connection management addresses pool exhaustion and serverless connection leaks: configure connection_limit/pool_timeout in DATABASE_URL, reuse a single global PrismaClient instance in serverless environments, and disconnect gracefully on beforeExit - escalating to PgBouncer for high-traffic apps. Transaction patterns cover sequential auto-transactions (prisma.$transaction([...])), interactive transactions with manual control and business-logic validation inside the callback (with maxWait/timeout/isolationLevel options, up to Serializable), and optimistic concurrency control via a version-matched where clause on update.

A code review checklist spans schema quality (explicit @relation with fields/references, defined cascade behaviors, indexes on queried fields, @@map for table naming), query patterns (no N+1, select over full-model fetches, pagination, raw queries reserved for complex aggregations), performance (appropriate connection pooling, composite indexes, query logging in development), and migration safety (tested before production, backward-compatible, documented rollback). Five anti-patterns to avoid: implicit many-to-many relations instead of explicit join tables, over-including unneeded relations, ignoring connection limits, overusing raw queries, and running migrate dev in production.

Developers building on Prisma ORM who need concrete, progressive fixes for schema, migration, query performance, connection, and transaction problems - plus a review checklist to catch these issues before they ship.