Database Migrations with Claude Code: Safe Schema Changes Without Breaking Production

#claudecode #database #postgres #prisma

Database migrations are risky. A bad migration can take down production for hours. Claude Code can help — but you need to give it the right constraints.

The Risk Without Constraints

If you just say "add a column to the users table," Claude Code might generate:

ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL;

This fails on a table with existing rows because NOT NULL requires a default value.

Or it might suggest dropping a column that's still in use.

CLAUDE.md for Safe Migrations

## Database Migration Rules

### Schema Changes (Prisma)
- Use `prisma migrate dev` for development migrations
- Use `prisma migrate deploy` for production (never migrate:dev in prod)
- Migration files are in `prisma/migrations/` — never edit manually
- Always run `npx prisma generate` after schema changes

### Safe Change Patterns
- Adding nullable columns: safe (no data loss)
- Adding columns with DEFAULT: safe
- Adding indexes: safe (but locks table briefly — use CONCURRENTLY for large tables)
- Removing columns: UNSAFE — deprecate first, remove after 2 deploy cycles
- Renaming columns: UNSAFE — use add+copy+remove pattern
- Changing column type: UNSAFE — usually requires a migration plan

### Deployment Order
- Always deploy code BEFORE removing deprecated columns
- Add new column → deploy code that reads both → remove old column
- Never modify column types that have data

### Rollback
- Every migration must have a rollback strategy
- Destructive changes (DROP) must have data backup first

Asking for Migration Safety Review

Review this database migration for safety before I apply it.
The table has ~1M rows and cannot have downtime.

Migration:
[paste the migration SQL or Prisma migration]

Questions:
1. Will this lock the table? If so, for how long?
2. Does it need a default value for existing rows?
3. What's the rollback if something goes wrong?
4. Should this be split into multiple smaller migrations?

Column Deprecation Pattern

When removing a column:

I need to remove the `phone_number` column from the users table.
The column was deprecated 2 months ago but the code still references it.
Generate the multi-step migration plan:

1. Identify all code that uses phone_number
2. Remove code references
3. Mark column as deprecated in schema
4. Generate the final removal migration

Current schema: [paste schema]

Large Table Migrations

For tables with millions of rows:

I need to add a `last_login_at` timestamp column to the users table.
The table has 5M rows. Generate a migration that:
- Uses CONCURRENTLY for index creation (PostgreSQL)
- Adds the column as nullable first
- Backfills data in batches of 10k rows
- Doesn't lock the table

Database: PostgreSQL 16

Generated approach:

-- Step 1: Add nullable column (instant)
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;

-- Step 2: Backfill in batches (run via script)
UPDATE users SET last_login_at = created_at
WHERE id BETWEEN $1 AND $2;

-- Step 3: Add index concurrently (non-locking)
CREATE INDEX CONCURRENTLY idx_users_last_login ON users(last_login_at);

-- Step 4: Make NOT NULL after backfill completes
ALTER TABLE users ALTER COLUMN last_login_at SET NOT NULL;

Prisma Schema Review

Review this Prisma schema change before I create the migration.
Focus on:
1. Is this backwards compatible?
2. Will existing data break?
3. Are there missing indexes (foreign keys, frequently queried fields)?
4. Is the column name consistent with the project naming convention?

Current schema: [paste prisma/schema.prisma]
Proposed change: [describe what you want to change]

CI Check for Dangerous Migrations

Add to your GitHub Actions:

- name: Check migration safety
  run: python .github/scripts/check_migration.py prisma/migrations/

# .github/scripts/check_migration.py
import sys, pathlib, re

dangerous = [
    (r'DROP\s+(TABLE|COLUMN|INDEX)\s+(?!IF EXISTS)', "Destructive DROP without IF EXISTS"),
    (r'ALTER.*NOT NULL(?!.*DEFAULT)', "Adding NOT NULL without DEFAULT"),
    (r'TRUNCATE', "TRUNCATE in migration"),
]

for f in pathlib.Path(sys.argv[1]).rglob("*.sql"):
    content = f.read_text()
    for pattern, msg in dangerous:
        if re.search(pattern, content, re.IGNORECASE):
            print(f"DANGER [{f.name}]: {msg}")
            sys.exit(1)