prisma-expert

Use this skill when

• Designing or reviewing Prisma schema models and relations
• Debugging migration conflicts or failed migrations
• Fixing N+1 query problems and over-fetching
• Configuring connection pooling for serverless or long-running environments
• Designing transaction patterns for multi-step operations
• Integrating Prisma with NestJS, Next.js, or other frameworks

Do not use this skill when

• Raw SQL optimization is needed — use postgres-expert or mongodb-expert
• Database server configuration is the issue — use database-expert
• Connection pooling at infrastructure level (PgBouncer) — use devops-expert

Instructions

1. Identify the Prisma-specific issue category (schema, migration, query, connection, transaction)
2. Check for common anti-patterns in schema or queries
3. Apply progressive fixes: minimal → better → complete
4. Validate with Prisma CLI commands and type checking

Capabilities

Schema Design

• Model definitions with proper @id, @unique, @default, @map, @@map
• Explicit relation definitions with @relation, fields, references, onDelete, onUpdate
• Composite indexes with @@index for frequently queried multi-column patterns
• Enum types for stable, fixed value sets
• Cascade behavior selection: Cascade, Restrict, SetNull, SetDefault, NoAction
• Schema validation with npx prisma validate
• Schema drift detection with npx prisma migrate diff

Common Schema Issues

• Incorrect relation definitions causing runtime errors
• Missing indexes on frequently queried or filtered fields
• Enum synchronization issues between schema and database
• Field type mismatches between schema and existing database
• Missing @@map causing table name conflicts

Migrations

• Development workflow: npx prisma migrate dev --name descriptive_name
• Production deployment: npx prisma migrate deploy (never migrate dev in production)
• Status check: npx prisma migrate status
• Conflict resolution: npx prisma migrate resolve --applied or --rolled-back
• Squashing migrations for clean baseline: create baseline migration
• Shadow database issues: ensure DATABASE_URL has permissions to create/drop databases

Query Optimization

• N+1 problem: always include relations in a single query rather than querying in a loop
• Select only needed fields with select — never fetch entire models for partial use
• Use $queryRaw for complex aggregations that Prisma's type-safe API cannot express efficiently
• Query logging in development: log: [{ emit: 'event', level: 'query' }] with $on('query', ...)
• Pagination: skip + take for offset pagination; cursor-based pagination for feeds

Connection Management

• Singleton PrismaClient pattern: use global variable in development to prevent connection exhaustion across hot reloads
• Graceful shutdown: process.on('beforeExit', () => prisma.$disconnect())
• Serverless environments (Vercel, Lambda): singleton pattern is essential; connection limit via DATABASE_URL parameters
• Connection URL parameters: connection_limit, pool_timeout
• High-traffic production: use PgBouncer connection pooler in front of PostgreSQL

Transaction Patterns

• Sequential operations: pass array of Prisma promises to $transaction([...])
• Interactive transactions: $transaction(async (tx) => { ... }) for business logic with rollback
• Transaction options: maxWait (wait for slot), timeout (total execution time), isolationLevel
• Optimistic concurrency: use version field in WHERE clause to detect concurrent updates
• Deadlock prevention: always acquire locks in consistent order across transactions

Environment Detection

• Check Prisma version: npx prisma --version
• Check database provider: grep for provider in schema.prisma
• Check migration state: ls prisma/migrations/ and npx prisma migrate status
• Check client generation: verify node_modules/.prisma/client/ exists

Behavioral Traits

• Reads existing schema.prisma before suggesting changes
• Validates schema with npx prisma validate after modifications
• Runs npx prisma generate after schema changes before testing code
• Never recommends migrate dev for production deployments
• Prefers explicit relations with named @relation over implicit ones
• Uses select by default to prevent over-fetching; uses include when all relation fields are needed
• Checks migration status before proposing migration commands

Knowledge Base

Schema Quality Priorities

1. Explicit @relation with fields/references/onDelete for every relation
2. @@index on every FK column and frequently filtered column
3. @@map / @map for clean table/column naming conventions
4. Cascade behaviors that match domain semantics (delete user = delete posts vs protect)
5. Enums for stable sets, TEXT for evolving values

N+1 Resolution Priority

1. Add include for needed relations (eliminates extra queries)
2. Use select to fetch only needed fields (reduces data volume)
3. Use $queryRaw for complex joins/aggregations Prisma cannot optimize

Connection Strategy by Environment

• Long-running server (Express, Fastify): single PrismaClient at module level
• Serverless (Next.js, Vercel): singleton via globalThis to survive hot reloads
• High concurrency: PgBouncer + connection_limit parameter in DATABASE_URL
• Multiple databases: separate PrismaClient instances per DATABASE_URL

Migration Safety Rules

• Never use migrate dev in production (it resets the shadow database)
• Always run migrate status before migrate deploy in CI/CD
• Test migrations on staging database before production
• Keep migration files in version control alongside schema.prisma
• Document rollback steps for each migration that cannot be automatically reversed

Anti-Patterns

• Implicit many-to-many with auto-generated join table for complex relationships — use explicit join model
• Over-including: including all relations when only a subset is needed
• Ignoring connection limits: not configuring pool size for the deployment environment
• Raw query overuse: using $queryRaw when Prisma's type-safe API would work
• Migration dev in production: always use migrate deploy in production environments

Constraints

• Always run npx prisma generate after schema.prisma changes before building
• Never use migrate dev in production or CI/CD deployment pipelines
• Always use explicit @relation definitions with fields, references, and onDelete
• Never use include in high-frequency queries without confirming the relation data is actually needed
• Always use the singleton pattern for PrismaClient in serverless environments
• Transaction isolationLevel: 'Serializable' for critical financial or inventory operations

Code Review Checklist

Schema Quality

• All models have appropriate @id primary keys
• Relations use explicit @relation with fields and references
• Cascade behaviors defined (onDelete, onUpdate)
• Indexes added for frequently queried fields (@@index)
• Enums used for fixed value sets
• @@map used for consistent table naming

Query Patterns

• No N+1 queries (relations included when needed, not queried in loops)
• select used to fetch only required fields
• Pagination implemented for list queries
• Raw queries used for complex aggregations
• Proper error handling for database operations

Performance

• Connection pooling configured for deployment environment
• Indexes exist for WHERE clause fields
• Composite indexes for multi-column queries
• Query logging enabled in development
• Slow queries identified and optimized

Migration Safety

• Migrations tested before production deployment
• Backward-compatible schema changes (no data loss without explicit plan)
• Migration files reviewed for correctness
• Rollback strategy documented

API Reference

Detailed API documentation: references/REFERENCE.md.

When to read: when you need exact method signatures, configuration options, type definitions, or implementation details not covered above.

How to use: search or read the reference for specific APIs before writing code. Don't read the entire file — look up only what you need.