Prisma Core Knowledge

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: prisma for comprehensive documentation.

When NOT to Use This Skill

• Raw SQL Operations: Use the database-query MCP server for direct SQL queries and database inspection

• Other ORMs: Use appropriate skills (drizzle , typeorm , sqlalchemy ) for other ORM frameworks

• Database Design: Consult architect-expert or sql-expert for database architecture decisions

• Performance Profiling: Use performance-profiler MCP for query performance analysis

• Migration Rollbacks: Engage devops-expert for production migration strategies

Schema Definition

// schema.prisma generator client { provider = "prisma-client-js" }

datasource db { provider = "postgresql" url = env("DATABASE_URL") }

model User { id Int @id @default(autoincrement()) email String @unique name String? posts Post[] createdAt DateTime @default(now()) updatedAt DateTime @updatedAt }

model Post { id Int @id @default(autoincrement()) title String content String? published Boolean @default(false) author User @relation(fields: [authorId], references: [id]) authorId Int }

CRUD Operations

// Create const user = await prisma.user.create({ data: { email: 'user@example.com', name: 'John' } });

// Read const users = await prisma.user.findMany({ where: { email: { contains: '@example.com' } }, include: { posts: true }, orderBy: { createdAt: 'desc' }, take: 10 });

const user = await prisma.user.findUnique({ where: { id: 1 } });

// Update await prisma.user.update({ where: { id: 1 }, data: { name: 'Jane' } });

// Delete await prisma.user.delete({ where: { id: 1 } });

Relations

// Create with relation await prisma.user.create({ data: { email: 'user@example.com', posts: { create: [ { title: 'First Post' }, { title: 'Second Post' } ] } } });

// Query with relation const userWithPosts = await prisma.user.findUnique({ where: { id: 1 }, include: { posts: { where: { published: true } } } });

Commands

npx prisma init npx prisma migrate dev --name init npx prisma generate npx prisma studio

Anti-Patterns

Anti-Pattern Why It's Bad Better Approach

synchronize: true in production Can cause data loss, no migration history Use prisma migrate deploy

No select or include on queries Fetches all fields, performance waste Select only needed fields

N+1 queries without include

Multiple round trips to database Use include or select with nested relations

Missing indexes on foreign keys Slow joins and lookups Add @@index([foreignKeyField])

Hardcoded connection strings Security risk, no environment flexibility Use env("DATABASE_URL")

No transaction for multi-step operations Data inconsistency risk Wrap in $transaction

Using findMany() without pagination Memory issues with large datasets Add take and skip or cursor-based pagination

Ignoring Prisma error codes Poor user experience, security risks Handle PrismaClientKnownRequestError codes

No connection pool limits Connection exhaustion Configure connection_limit in URL

Running migrations manually in prod Human error, no audit trail Automate via CI/CD with migrate deploy

Quick Troubleshooting

Issue Likely Cause Solution

"Can't reach database server" Wrong DATABASE_URL or DB not running Verify connection string, check DB status

"Unique constraint failed" (P2002) Duplicate value in unique field Check for existing record, handle error gracefully

"Foreign key constraint failed" (P2003) Referenced record doesn't exist Verify related record exists before insertion

"Record not found" (P2025) Query returned no results Use findUnique with null check or handle error

Slow queries Missing indexes, N+1 problem Add indexes, use include for relations

"Type 'X' is not assignable" Generated client out of sync Run npx prisma generate

Migration conflicts Multiple developers creating migrations Coordinate migrations, use git properly

Connection pool exhausted Too many concurrent connections Increase connection_limit or use Prisma Accelerate

"Client is not connected" PrismaClient not initialized Ensure new PrismaClient() before use

Schema drift Manual DB changes not in schema Use prisma db pull to sync or create migration

Production Readiness

Security Configuration

// schema.prisma - Secure connection datasource db { provider = "postgresql" url = env("DATABASE_URL") // Use env vars, never hardcode }

// DATABASE_URL format with SSL // postgresql://user:pass@host:5432/db?sslmode=require&sslcert=/path/to/client-cert.pem

// Secure PrismaClient initialization import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({ log: process.env.NODE_ENV === 'production' ? ['error'] : ['query', 'info', 'warn', 'error'], errorFormat: process.env.NODE_ENV === 'production' ? 'minimal' : 'pretty', });

Connection Pooling

// schema.prisma - Connection pool settings datasource db { provider = "postgresql" url = env("DATABASE_URL") // For serverless (e.g., Vercel, AWS Lambda) // Use Prisma Accelerate or PgBouncer }

// Connection management const prisma = new PrismaClient({ datasources: { db: { url: process.env.DATABASE_URL, }, }, });

// Connection pool via URL params // ?connection_limit=5&pool_timeout=10

// For serverless: use Prisma Accelerate // DATABASE_URL="prisma://accelerate.prisma-data.net/?api_key=xxx"

Query Optimization

// Select only needed fields const users = await prisma.user.findMany({ select: { id: true, email: true, // Don't select unnecessary fields }, });

// Use pagination const users = await prisma.user.findMany({ take: 20, skip: (page - 1) * 20, cursor: lastId ? { id: lastId } : undefined, });

// Batch operations for bulk inserts await prisma.user.createMany({ data: users, skipDuplicates: true, });

// Use transactions for related operations await prisma.$transaction([ prisma.order.create({ data: orderData }), prisma.inventory.update({ where: { productId }, data: { quantity: { decrement: 1 } } }), ]);

// Interactive transactions with timeout await prisma.$transaction(async (tx) => { const user = await tx.user.create({ data: userData }); await tx.profile.create({ data: { userId: user.id, ...profileData } }); return user; }, { maxWait: 5000, // Wait for connection timeout: 10000, // Transaction timeout });

Error Handling

import { Prisma } from '@prisma/client';

async function createUser(data: UserInput) { try { return await prisma.user.create({ data }); } catch (error) { if (error instanceof Prisma.PrismaClientKnownRequestError) { // Unique constraint violation if (error.code === 'P2002') { throw new ConflictError(User with this ${error.meta?.target} already exists); } // Foreign key constraint violation if (error.code === 'P2003') { throw new BadRequestError('Related record not found'); } // Record not found if (error.code === 'P2025') { throw new NotFoundError('Record not found'); } } throw error; } }

Migrations in Production

Generate migration (development)

npx prisma migrate dev --name add_user_status

Apply migrations (production)

npx prisma migrate deploy

Reset database (DANGER - only for development)

npx prisma migrate reset

Check migration status

npx prisma migrate status

CI/CD migration workflow

jobs: migrate: steps: - name: Apply migrations run: npx prisma migrate deploy env: DATABASE_URL: ${{ secrets.DATABASE_URL }}

Monitoring Metrics

Metric Alert Threshold

Query duration p99

100ms

Connection pool wait time

5s

Failed queries

1%

Connection pool exhaustion Any occurrence

Slow queries count

10/min

Logging & Tracing

import { PrismaClient } from '@prisma/client';

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

// Log slow queries prisma.$on('query', (e) => { if (e.duration > 100) { console.log(Slow query (${e.duration}ms): ${e.query}); } });

// OpenTelemetry integration import { PrismaInstrumentation } from '@prisma/instrumentation';

registerInstrumentations({ instrumentations: [new PrismaInstrumentation()], });

Graceful Shutdown

// Proper cleanup on shutdown async function gracefulShutdown() { await prisma.$disconnect(); process.exit(0); }

process.on('SIGTERM', gracefulShutdown); process.on('SIGINT', gracefulShutdown);

Checklist

• Database URL via environment variables

• SSL/TLS connection enabled

• Connection pooling configured

• Error handling for Prisma errors

• Migrations via CI/CD pipeline

• Select only needed fields

• Pagination on list queries

• Transactions for related operations

• Slow query logging

• Graceful shutdown handling

• OpenTelemetry tracing (optional)

• Prisma Accelerate for serverless (optional)

Reference Documentation

• Schema Reference

• Query Patterns

• Deep: Relations