Prisma

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

Quick Troubleshooting

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

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