Database Migrations | Bootspring Docs

Patterns for managing database migrations with Prisma.

Overview#

Migrations enable safe schema evolution:

Version-controlled schema changes
Reversible modifications
Data transformations
Zero-downtime deployments

Prerequisites:

Prisma ORM setup
Version control (Git)

Implementation#

Development Migration#

# Create and apply migration
npx prisma migrate dev --name add_user_role

# Reset database (drops all data!)
npx prisma migrate reset

# Generate Prisma client only
npx prisma generate

Production Migration#

# Apply pending migrations
npx prisma migrate deploy

# Check migration status
npx prisma migrate status

Data Migration Script#

// scripts/migrate-add-slugs.ts
import { prisma } from '@/lib/db'
import { slugify } from '@/lib/utils'

async function migrate() {
  const posts = await prisma.post.findMany({
    where: { slug: null }
  })

  console.log(`Migrating ${posts.length} posts...`)

  for (const post of posts) {
    const slug = slugify(post.title)

    await prisma.post.update({
      where: { id: post.id },
      data: { slug }
    })
  }

  console.log('Migration complete')
}

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

Batch Data Migration#

// scripts/migrate-batch.ts
import { prisma } from '@/lib/db'

const BATCH_SIZE = 100

async function migrate() {
  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
      continue
    }

    await prisma.$transaction(
      users.map((user) =>
        prisma.user.update({
          where: { id: user.id },
          data: {
            // Migration logic
            displayName: user.name || user.email.split('@')[0],
            migratedAt: new Date()
          }
        })
      )
    )

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

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

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

Reversible Migration#

// scripts/migrate-reversible.ts
import { prisma } from '@/lib/db'

async function up() {
  // Add new column with default
  await prisma.$executeRaw`
    ALTER TABLE "User" ADD COLUMN "status" TEXT DEFAULT 'ACTIVE'
  `

  // Populate based on existing data
  await prisma.$executeRaw`
    UPDATE "User" SET "status" = 'INACTIVE'
    WHERE "lastLoginAt" < NOW() - INTERVAL '90 days'
  `
}

async function down() {
  await prisma.$executeRaw`
    ALTER TABLE "User" DROP COLUMN "status"
  `
}

// Run with: npx ts-node scripts/migrate-reversible.ts up
const direction = process.argv[2]

if (direction === 'up') {
  up().then(() => console.log('Migration up complete'))
} else if (direction === 'down') {
  down().then(() => console.log('Migration down complete'))
} else {
  console.log('Usage: npx ts-node script.ts [up|down]')
}

Safe Schema Changes (Zero-Downtime)#

// Approach for zero-downtime migrations

// Step 1: Add new column (nullable)
// schema.prisma: newField String?

// Step 2: Deploy code that writes to both columns
await prisma.user.update({
  where: { id },
  data: {
    oldField: value,
    newField: value // Write to both
  }
})

// Step 3: Backfill existing data
// scripts/backfill.ts

// Step 4: Deploy code that reads from new column

// Step 5: Remove old column in schema
// schema.prisma: remove oldField

// Step 6: Clean up code

Column Rename Pattern#

// scripts/rename-column.ts
// Safe column rename without downtime

async function renameColumn() {
  // Phase 1: Add new column
  await prisma.$executeRaw`
    ALTER TABLE "Post" ADD COLUMN "slug" TEXT
  `

  // Phase 2: Copy data
  await prisma.$executeRaw`
    UPDATE "Post" SET "slug" = "permalink"
  `

  // Phase 3: Add constraints (after code deploy)
  await prisma.$executeRaw`
    ALTER TABLE "Post" ALTER COLUMN "slug" SET NOT NULL
  `

  // Phase 4: Drop old column (after verification)
  await prisma.$executeRaw`
    ALTER TABLE "Post" DROP COLUMN "permalink"
  `
}

Migration Verification#

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

async function verify() {
  // Check all records migrated
  const unmigrated = await prisma.user.count({
    where: { displayName: null }
  })

  if (unmigrated > 0) {
    console.error(`${unmigrated} users not migrated!`)
    process.exit(1)
  }

  // Check data integrity
  const invalid = await prisma.user.count({
    where: {
      displayName: ''
    }
  })

  if (invalid > 0) {
    console.error(`${invalid} users have empty displayName!`)
    process.exit(1)
  }

  console.log('Migration verification passed!')
}

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

CI/CD Integration#

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

on:
  push:
    branches: [main]

jobs:
  deploy:
    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: Generate Prisma Client
        run: npx prisma generate

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

      - name: Deploy application
        run: npm run deploy

Best Practices#

Always use migrations in production - Never use db push in production
Test migrations locally first - Run against a copy of production data
Make migrations reversible - Plan rollback strategies
Batch large data migrations - Avoid locking tables for long periods
Verify after migration - Check data integrity post-migration

Prisma - Prisma setup and basics
Seeding - Database seeding
Transactions - Transaction handling