Database Migration Strategies That Won't Break Production

Safe Migration Patterns

Pattern 1: Add Column (Safe)

// schema.prisma
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String
  avatar    String?  // New nullable column — SAFE
  // ...
}

Adding a nullable column is always safe — existing rows get null, no data loss.

Pattern 2: Rename Column (Dangerous)

-- DON'T do this in one migration:
ALTER TABLE "User" RENAME COLUMN "name" TO "fullName";
-- This breaks all queries using "name" instantly

-- DO this in phases:
-- Migration 1: Add new column
ALTER TABLE "User" ADD COLUMN "fullName" TEXT;
UPDATE "User" SET "fullName" = "name";

-- Deploy code that reads from both columns

-- Migration 2: Make new column required
ALTER TABLE "User" ALTER COLUMN "fullName" SET NOT NULL;

-- Deploy code that only uses new column

-- Migration 3: Drop old column
ALTER TABLE "User" DROP COLUMN "name";

Pattern 3: Add Index (Safe but Slow)

-- Standard index creation LOCKS the table
CREATE INDEX "User_email_idx" ON "User"("email");

-- Concurrent index creation (no lock, PostgreSQL)
CREATE INDEX CONCURRENTLY "User_email_idx" ON "User"("email");

For large tables (millions of rows), always use CONCURRENTLY.

Data Migrations

Schema migrations change structure. Data migrations change content:

// prisma/migrations/20260321_migrate_user_roles/migration.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  // Migrate old string roles to enum
  await prisma.$executeRaw`
    UPDATE "User" 
    SET "role" = 'CUSTOMER' 
    WHERE "role" = 'user'
  `;

  await prisma.$executeRaw`
    UPDATE "User" 
    SET "role" = 'SUPER_ADMIN' 
    WHERE "role" = 'admin'
  `;

  console.log("Role migration complete");
}

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

Zero-Downtime Migration Checklist

Before migration:
  □ Backup the database
  □ Test migration on staging with production data copy
  □ Review generated SQL (prisma migrate diff)
  □ Estimate migration duration on production data size
  □ Schedule maintenance window if needed
  □ Prepare rollback migration

During migration:
  □ Enable maintenance mode (if needed)
  □ Run migration with monitoring
  □ Verify data integrity
  □ Run application health checks
  □ Monitor error rates for 15 minutes

After migration:
  □ Verify all features work
  □ Check query performance
  □ Monitor for 24 hours
  □ Remove old code/columns after grace period
  □ Archive rollback migration

Rollback Strategies

Strategy 1: Reverse Migration

# Generate "down" migration manually
# Prisma doesn't auto-generate rollbacks

-- rollback_add_avatar.sql
ALTER TABLE "User" DROP COLUMN IF EXISTS "avatar";

Strategy 2: Point-in-Time Recovery

# PostgreSQL PITR (if configured)
pg_restore --target-time="2026-03-21 10:00:00" \
  --dbname=portfolio_db backup.dump

Strategy 3: Feature Flags

// Use feature flags to toggle new features
const config = await loadSiteSettings();
const useNewUserProfile = config.feature_new_profile === "true";

if (useNewUserProfile) {
  // Use new column
  return user.avatar;
} else {
  // Use old behavior
  return user.profileImage;
}

Seeding Data

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  // Upsert to make seeds idempotent
  await prisma.siteConfig.upsert({
    where: { key: "site_name" },
    update: {},
    create: {
      key: "site_name",
      value: "Developer Portfolio",
      group: "general",
      isSecret: false,
    },
  });

  console.log("Seed complete");
}

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

Our Migration Setup

The Developer Portfolio Platform uses:

npm run migrate        # prisma migrate dev (development)
npm run migrate:fresh  # Reset and re-migrate (development ONLY)
npm run seed           # Run seeders (tsx prisma/seeders/index.ts)

With 60+ models and 900+ lines of schema, we've developed robust migration practices:

• cuid() for all IDs — never UUID, always @default(cuid())
• Soft deletes — deletedAt DateTime? instead of actual deletion
• Timestamps — createdAt and updatedAt on every model
• Indexes — explicit indexes on frequently queried columns

Related reads:

• Prisma ORM Advanced Patterns — model design best practices
• PostgreSQL Performance — query optimization
• Deploying Next.js to Production — full deployment guide

Follow on LinkedIn for database engineering tips.

60+ models, zero-downtime migrations. Our Developer Portfolio Platform handles database evolution professionally — $299.