Database Migrations

Evolve your database schema safely and confidently

---

When to Use This Skill

Use this skill when:

• Creating or modifying database schemas
• Deploying database changes to production
• Planning rollback strategies
• Performing data migrations

---

Critical Patterns

Pattern 1: Development Workflow

When: Creating new migrations in development

Good:

# 1. Modify schema.prisma
model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String
}

# 2. Create migration
npx prisma migrate dev --name add_user_model

# Creates migration file and applies it

Why: migrate dev handles everything in development.

---

Pattern 2: Production Deployment

When: Deploying to production

Good:

# Deploy migrations (non-interactive)
npx prisma migrate deploy

# CI/CD pipeline

Bad:

# ❌ Never use migrate dev in production
npx prisma migrate dev

Why: migrate deploy is non-interactive and safe for automation.

---

Pattern 3: Backward Compatible Changes

When: Adding new fields or models

Good:

// ✅ Adding optional field
model User {
  id       Int     @id
  email    String  @unique
  bio      String? // Existing rows get NULL
}

// ✅ Adding field with default
model User {
  id        Int      @id
  email     String   @unique
  role      String   @default("USER")
  createdAt DateTime @default(now())
}

Why: These changes don't break existing data or code.

---

Pattern 4: Safe Field Rename

When: Renaming without data loss

Good (3-step process):

// Step 1: Add new field mapping to same column
model User {
  id       Int    @id
  name     String
  fullName String @map("name")
}

// Step 2: Update app code to use fullName
// Step 3: Remove old field
model User {
  id       Int    @id
  fullName String @map("name")
}

Bad:

// ❌ Direct rename (data loss)
model User {
  id       Int    @id
  fullName String // Was "name", data lost!
}

Why: Direct rename is seen as remove + add.

---

Pattern 5: Making Field Required

When: Converting optional to required field

Good (3-step process):

// Step 1: Add default
model User {
  id    Int     @id
  name  String? @default("")
}

npx prisma migrate dev --name add_default_name

// Step 2: Backfill NULL values
await prisma.$executeRaw`UPDATE "User" SET name = '' WHERE name IS NULL`

// Step 3: Make required
model User {
  id    Int    @id
  name  String @default("")
}

Why: Multi-step prevents errors from existing NULL values.

---

Code Examples

Example 1: Adding a New Model

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  createdAt DateTime @default(now())
  posts     Post[]
}

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

# Create migration
npx prisma migrate dev --name add_post_model

# Applies migration and generates Prisma Client

Example 2: Safe Data Migration

// Backfill default values before making field required
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function backfillUserNames() {
  // Update all NULL names to empty string
  await prisma.$executeRaw`
    UPDATE "User"
    SET name = COALESCE(name, '')
    WHERE name IS NULL
  `;

  console.log('Backfill complete');
}

backfillUserNames()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

For comprehensive examples and detailed implementations, see the references/ folder.

---

Production Checklist

Before deployment:

• Test migration in staging (with production data copy)
• Backup production database
• Review migration SQL
• Have rollback plan ready

Deployment:

# 1. Backup
pg_dump production_db > backup_$(date +%Y%m%d).sql

# 2. Deploy code (if backward compatible)
git push production main

# 3. Run migrations
npx prisma migrate deploy

# 4. Verify
npx prisma migrate status

---

Best Practices

Development:

• ✅ Use prisma migrate dev for iterative changes
• ✅ Keep migrations small and focused
• ✅ Review generated SQL before committing

Production:

• ✅ Always backup before migration
• ✅ Test in staging first
• ✅ Use prisma migrate deploy only
• ✅ Have rollback plan

Safety:

• ❌ Never delete deployed migrations
• ❌ Never modify committed migrations
• ❌ Never use migrate reset in production

---

Progressive Disclosure

For detailed implementations:

• Prisma Workflows - Dev workflow, production deployment, reset commands
• Migration Strategies - Safe renames, data migrations, breaking changes

---

References

• Prisma Workflows
• Migration Strategies
• Prisma Migrate Docs

---

Maintained by dsmj-ai-toolkit