Database migration best practices and strategies. Use when managing schema changes.
This skill inherits all available tools. When active, it can use any tool Claude has access to.
This skill covers database migration patterns and best practices.
Use this skill when:
FORWARD-ONLY, REVERSIBLE - Migrations should be forward-only in production but designed to be logically reversible.
-- Safe: Add nullable column
ALTER TABLE users ADD COLUMN phone TEXT;
-- Safe: Add column with default
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';
-- Unsafe: Add NOT NULL without default
-- ALTER TABLE users ADD COLUMN required_field TEXT NOT NULL; -- DON'T DO THIS
// Step 1: Add nullable column
await prisma.$executeRaw`ALTER TABLE users ADD COLUMN bio TEXT`;
// Step 2: Backfill data
await prisma.$executeRaw`UPDATE users SET bio = '' WHERE bio IS NULL`;
// Step 3: Add NOT NULL constraint
await prisma.$executeRaw`ALTER TABLE users ALTER COLUMN bio SET NOT NULL`;
// Step 1: Add new column
await db.schema.alterTable('users').addColumn('full_name', 'text');
// Step 2: Backfill data
await db.update(users).set({ fullName: sql`${users.name}` });
// Step 3: Update application to use new column
// Deploy code that writes to both columns
// Step 4: Stop writing to old column
// Deploy code that only uses new column
// Step 5: Remove old column (separate migration)
await db.schema.alterTable('users').dropColumn('name');
-- PostgreSQL: Create index concurrently
CREATE INDEX CONCURRENTLY users_email_idx ON users(email);
-- Don't do this in production:
-- CREATE INDEX users_email_idx ON users(email); -- Blocks table
// Step 1: Stop reading the column in code
// Deploy code that doesn't read the column
// Step 2: Stop writing the column in code
// Deploy code that doesn't write the column
// Step 3: Remove the column
await db.schema.alterTable('users').dropColumn('old_column');
# Create and apply migration
npx prisma migrate dev --name add_phone_to_users
# Reset database (destructive)
npx prisma migrate reset
# Apply without generating
npx prisma db push
# Apply pending migrations
npx prisma migrate deploy
# Check migration status
npx prisma migrate status
-- prisma/migrations/20240101_add_phone/migration.sql
-- CreateTable
ALTER TABLE "users" ADD COLUMN "phone" TEXT;
-- CreateIndex
CREATE INDEX "users_phone_idx" ON "users"("phone");
npx drizzle-kit generate:pg
// src/db/migrate.ts
import { migrate } from 'drizzle-orm/postgres-js/migrator';
await migrate(db, { migrationsFolder: './drizzle' });
// migrations/backfill-user-slugs.ts
import { db } from '../src/db';
import { users } from '../src/db/schema';
import { isNull } from 'drizzle-orm';
import slugify from 'slugify';
const BATCH_SIZE = 1000;
async function backfillSlugs(): Promise<void> {
let processed = 0;
while (true) {
const batch = await db
.select({ id: users.id, name: users.name })
.from(users)
.where(isNull(users.slug))
.limit(BATCH_SIZE);
if (batch.length === 0) break;
await db.transaction(async (tx) => {
for (const user of batch) {
const slug = slugify(user.name, { lower: true });
await tx.update(users)
.set({ slug })
.where(eq(users.id, user.id));
}
});
processed += batch.length;
console.log(`Processed ${processed} users`);
}
console.log(`Backfill complete. Total: ${processed}`);
}
backfillSlugs().catch(console.error);
# Prisma doesn't have built-in rollback
# Instead, create a new migration that reverses changes
# Mark migration as rolled back (doesn't undo changes)
npx prisma migrate resolve --rolled-back 20240101_add_phone
// migrations/rollback-20240101.ts
import { db } from '../src/db';
async function rollback(): Promise<void> {
await db.transaction(async (tx) => {
// Reverse the changes
await tx.schema.alterTable('users').dropColumn('phone');
});
// Update migration table
await db.delete(migrations)
.where(eq(migrations.name, '20240101_add_phone'));
}
rollback().catch(console.error);
// migrations/__tests__/add-phone.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { migrate } from '../add-phone';
import { rollback } from '../rollback-add-phone';
import { setupTestDb, teardownTestDb } from '../../tests/utils';
describe('add-phone migration', () => {
beforeAll(async () => {
await setupTestDb();
});
afterAll(async () => {
await teardownTestDb();
});
it('adds phone column', async () => {
await migrate();
const columns = await db.query(`
SELECT column_name FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'phone'
`);
expect(columns.length).toBe(1);
});
it('rollback removes phone column', async () => {
await rollback();
const columns = await db.query(`
SELECT column_name FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'phone'
`);
expect(columns.length).toBe(0);
});
});
# .github/workflows/migrate.yml
name: Database Migration
on:
push:
branches: [main]
paths:
- 'prisma/migrations/**'
jobs:
migrate:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: actions/setup-node@v4
with:
node-version: '22'
- run: npm ci
- name: Run migrations
run: npx prisma migrate deploy
env:
DATABASE_URL: ${{ secrets.DATABASE_URL }}