Database Migrations | Bootspring Docs

Safe migration strategies for production databases.

Development Migrations#

Create Migration#

# Create a new migration
npx prisma migrate dev --name add_user_role

# Create migration without applying
npx prisma migrate dev --create-only

Reset Database#

# Reset and reseed (development only)
npx prisma migrate reset

Production Migrations#

Deploy Migrations#

# Apply pending migrations
npx prisma migrate deploy

CI/CD Integration#

# .github/workflows/deploy.yml
name: Deploy

on:
  push:
    branches: [main]

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '20'

      - name: Install dependencies
        run: npm ci

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

Safe Migration Patterns#

Adding a Column#

// Step 1: Add nullable column
model User {
  id    String  @id
  email String
  phone String? // New nullable column
}

npx prisma migrate dev --name add_phone_column

Adding Required Column with Default#

// Add with default value
model User {
  id        String   @id
  email     String
  createdAt DateTime @default(now()) // New with default
}

Renaming a Column#

-- migrations/xxx_rename_column/migration.sql
-- Prisma doesn't auto-detect renames, create manually
ALTER TABLE "User" RENAME COLUMN "name" TO "fullName";

// Update schema to match
model User {
  id       String  @id
  fullName String? // Renamed from name
}

Adding an Index#

model Post {
  id        String   @id
  slug      String
  authorId  String
  createdAt DateTime @default(now())

  // Add indexes
  @@index([slug])
  @@index([authorId, createdAt])
}

Changing Column Type#

-- migrations/xxx_change_type/migration.sql
-- Two-step approach for safety

-- Step 1: Add new column
ALTER TABLE "Post" ADD COLUMN "views_new" BIGINT DEFAULT 0;

-- Step 2: Migrate data
UPDATE "Post" SET "views_new" = "views"::BIGINT;

-- Step 3: Drop old, rename new
ALTER TABLE "Post" DROP COLUMN "views";
ALTER TABLE "Post" RENAME COLUMN "views_new" TO "views";

Zero-Downtime Migrations#

Expand-Contract Pattern#

// Phase 1: Expand (add new column)
// migrations/001_add_new_column/migration.sql
ALTER TABLE "User" ADD COLUMN "email_new" TEXT;

// Phase 2: Migrate data (application code)
// scripts/migrate-emails.ts
const users = await prisma.user.findMany({
  where: { email_new: null },
  take: 1000,
});

for (const user of users) {
  await prisma.user.update({
    where: { id: user.id },
    data: { email_new: normalizeEmail(user.email) },
  });
}

// Phase 3: Contract (remove old column)
// migrations/002_remove_old_column/migration.sql
ALTER TABLE "User" DROP COLUMN "email";
ALTER TABLE "User" RENAME COLUMN "email_new" TO "email";

Dual-Write Pattern#

// lib/services/user-service.ts
export async function updateUserEmail(userId: string, email: string) {
  // Write to both old and new columns during transition
  await prisma.user.update({
    where: { id: userId },
    data: {
      email,        // Old column
      email_v2: email, // New column
    },
  });
}

Handling Breaking Changes#

Deprecate Before Remove#

// Step 1: Mark as deprecated in code
interface User {
  id: string;
  /** @deprecated Use fullName instead */
  name?: string;
  fullName: string;
}

// Step 2: Populate new field
await prisma.user.updateMany({
  where: { fullName: null },
  data: { fullName: prisma.user.fields.name },
});

// Step 3: Remove old field after full migration

Feature Flags for Migrations#

// lib/features.ts
export const FEATURES = {
  USE_NEW_EMAIL_FIELD: process.env.USE_NEW_EMAIL_FIELD === 'true',
};

// In your code
function getUserEmail(user: User): string {
  if (FEATURES.USE_NEW_EMAIL_FIELD) {
    return user.emailNormalized;
  }
  return user.email;
}

Migration Scripts#

Batch Data Migration#

// scripts/migrate-data.ts
import { prisma } from '@/lib/prisma';

const BATCH_SIZE = 1000;

async function migrateUsers() {
  let processed = 0;
  let hasMore = true;

  while (hasMore) {
    const users = await prisma.user.findMany({
      where: {
        migratedAt: null,
      },
      take: BATCH_SIZE,
    });

    if (users.length === 0) {
      hasMore = false;
      break;
    }

    await prisma.$transaction(
      users.map((user) =>
        prisma.user.update({
          where: { id: user.id },
          data: {
            fullName: `${user.firstName} ${user.lastName}`,
            migratedAt: new Date(),
          },
        })
      )
    );

    processed += users.length;
    console.log(`Migrated ${processed} users`);

    // Small delay to reduce database load
    await new Promise((resolve) => setTimeout(resolve, 100));
  }

  console.log(`Migration complete: ${processed} users migrated`);
}

migrateUsers().catch(console.error);

Verify Migration#

// scripts/verify-migration.ts
import { prisma } from '@/lib/prisma';

async function verifyMigration() {
  const unmigrated = await prisma.user.count({
    where: { migratedAt: null },
  });

  const nullValues = await prisma.user.count({
    where: { fullName: null },
  });

  console.log(`Unmigrated users: ${unmigrated}`);
  console.log(`Users with null fullName: ${nullValues}`);

  if (unmigrated > 0 || nullValues > 0) {
    console.error('Migration incomplete!');
    process.exit(1);
  }

  console.log('Migration verified successfully');
}

verifyMigration().catch(console.error);

Rollback Strategies#

Automatic Rollback Script#

-- migrations/xxx_add_feature/down.sql
-- Create a down migration for manual rollback

DROP INDEX IF EXISTS "Post_slug_idx";
ALTER TABLE "Post" DROP COLUMN IF EXISTS "views";

Point-in-Time Recovery#

# For Neon
neonctl branches create --name rollback-point

# Restore to branch if needed
neonctl branches restore --source rollback-point

Best Practices#

Always backup before production migrations
Test migrations on staging first
Use transactions for multi-step migrations
Create reversible migrations when possible
Monitor database performance during migrations
Run migrations during low-traffic periods
Have a rollback plan ready