Prisma

--- name: Prisma description: Write efficient Prisma queries, design schemas, and avoid common ORM pitfalls. metadata: category: database skills: ["prisma", "orm", "database", "typescript", "nodejs"] ---

Schema Design Traps

• @default(cuid()) over @default(uuid()) for IDs—shorter, URL-safe, still unique
• @updatedAt doesn't update on nested writes—must touch parent record explicitly
• Implicit many-to-many creates join table you can't customize—use explicit for extra fields
• @unique on nullable field allows multiple NULLs—intended behavior but often surprising
• Enum changes require migration—can't add values without downtime unless using String

Query Patterns I Forget

• findUniqueOrThrow / findFirstOrThrow—cleaner than null check after findUnique
• createMany skips hooks and returns count only—use create in loop if you need records back
• upsert requires unique field in where—can't upsert on non-unique compound conditions
• connectOrCreate in nested writes—avoids separate existence check
• select and include are mutually exclusive—can't mix; use nested select inside include

N+1 Query Prevention

• Default queries don't include relations—every access triggers new query
• include everything you'll access—check logs for unexpected queries
• Middleware can't see includes—adding includes in middleware doesn't help
• findMany + include better than loop of findUnique—single query vs N queries
• Dataloader pattern for GraphQL resolvers—Prisma doesn't batch automatically

Transaction Gotchas

• $transaction([]) array syntax rolls back all on any failure—use for atomic operations
• Interactive transactions $transaction(async (tx) => {}) hold connection—keep short
• Default 5s timeout on interactive transactions—increase for long operations
• Nested writes are already transactional—don't wrap single create with relations in transaction
• $transaction doesn't retry on conflict—implement retry logic for optimistic locking

Type Safety Gaps

• include result type doesn't narrow—TypeScript thinks relations might be undefined
• Raw queries return unknown[]—need manual type assertion or Prisma.$queryRaw
• JSON fields are JsonValue—cast needed; consider using typed JSON libraries
• Prisma.validator for reusable query fragments with correct types
• Return types of $executeRaw is count—not the affected rows

Migration Issues

• prisma db push for prototyping—prisma migrate dev for version control
• db push can drop data silently—never use in production
• Shadow database required for migrate dev—needs create permission or separate DB
• Renaming field = drop + create by default—use @map to keep data
• Large table migrations lock table—consider running raw SQL with concurrent indexes

Performance Traps

• findMany without take can return millions—always paginate
• count() scans table—expensive on large tables; consider approximate or cached counts
• include with large relations loads everything—use cursor pagination for big lists
• Relation counts: _count: { select: { posts: true } }—single query, not N+1
• orderBy on non-indexed field = slow—ensure indexes match sort patterns

Raw Query Patterns

• $queryRaw for reads, $executeRaw for writes—different return types
• Use Prisma.sql template for safe interpolation—never string concatenation
• Raw queries bypass Prisma hooks and middleware—intentional but easy to forget
• $queryRawUnsafe exists but name is a warning—use only for dynamic column names
• Raw results use database column names—not Prisma field names if @map used

Connection Management

• Default pool size 5—too low for production; set connection_limit in URL
• PlanetScale/serverless needs ?pool_timeout=0—prevents connection exhaustion
• $disconnect() in scripts and tests—lambda/serverless should manage differently
• Prisma Accelerate or Data Proxy for edge/serverless—direct DB connections don't scale

Middleware Patterns

• Soft delete via middleware: intercept delete, convert to update—but deleteMany needs handling
• Audit logging: $use captures all queries—but adds latency to every operation
• Middleware runs in order added—earlier middleware sees raw params
• Can't modify include in middleware—transform happens before middleware

Common Mistakes

• Forgetting await—Prisma returns promises; queries don't execute without await
• update without where = error—unlike some ORMs, Prisma requires explicit where
• Decimal fields return strings—Prisma Decimal type, not JavaScript number
• @relation names must match—cryptic error if they don't
• Schema drift: production differs from migrations—run prisma migrate deploy in CI