Guides

Using Claude Code for safe database migrations

A bad migration is one of the few mistakes an AI agent can make that you can't just git revert. The data is already changed, or the table is already locked. The fixes below are less about the SQL and more about never letting a migration run somewhere you can't undo it.

1. Give it the real schema, not its guess

Claude will infer column names and types from surrounding code if you let it, and that inference is often wrong on older tables. Point it at the source of truth instead:

Read the current schema (schema.rb / prisma/schema.prisma / the latest
migrations in db/migrate) and the last 5 migration files, so new ones
match our naming and structure. Don't assume a column exists — check.

If you've connected a database MCP server, Claude can query the live schema directly instead of reconstructing it from migration history. See connecting MCP servers.

2. Ask for expand-contract, not drop-and-recreate

Renaming or retyping a column in one migration breaks any code still deployed against the old shape. Ask for the safer sequence explicitly:

Don't rename or change the type of an existing column in place.
Use expand-contract: 1) add the new column, 2) backfill it, 3) write to
both columns until the app is fully deployed, 4) a later migration drops
the old one. Flag anywhere this migration would lock a large table.

This matters most for tables large enough that an ALTER TABLE takes real time, or that are read from on every request.

3. Every migration needs a working rollback

A migration that isn't reversible turns a bad deploy into an incident. Ask Claude to write and check the down side too:

Write the down migration alongside the up migration. Then simulate it:
apply up, then down, then up again against the test database, and show
me the output. If a step can't be reversed (a dropped column with data),
say so explicitly instead of writing a down migration that silently loses data.

4. Review the migration diff like any other diff

Migrations deserve the same scrutiny as application code, arguably more. Use the same review habits from reviewing code with Claude Code, with migration-specific asks:

Review this migration. Check specifically for:
- a missing index on a new foreign key or frequently-filtered column
- a NOT NULL column added without a default, which fails on existing rows
- an index built without CONCURRENTLY on Postgres, which locks writes
- a default value on a large table that rewrites every row
Report file:line and the concrete failure, not general advice.

5. Keep it off the production database

Claude should never have a path to a production connection string. Deny it at the permission layer so this doesn't depend on remembering to say no in the moment:

{
  "permissions": {
    "deny": [
      "Read(./.env.production)",
      "Read(./**/*production*)"
    ],
    "ask": [
      "Bash(psql:*)",
      "Bash(*migrate*:*)"
    ]
  }
}

A permission rule only stops Claude's own tool calls, not a command that embeds a URL. A PreToolUse hook matching Bash can additionally scan commands for a production host or connection string and block with exit code 2 before it runs. See hook examples, or build one without writing the script yourself with the free hook builder.

6. Test it against a real, throwaway database

A migration that type-checks isn't the same as one that runs. Point Claude at a disposable database and have it prove the migration works before you look at it:

Apply this migration against the local/test database, not production.
Run the app's test suite after. If the migration seeds or backfills data,
show me row counts before and after on the affected table.

For a schema change that touches a lot of existing rows, ask for the backfill as a separate, batched step rather than inline in the migration, so it can be paused or retried without holding a long transaction.

Migration review checklist

Check	Why it matters
Rollback (down migration) exists and was tested	Undoing a bad deploy shouldn't require a second migration written under pressure
New required columns have a default or are added nullable first	A NOT NULL column with no default fails immediately against existing rows
Indexes added concurrently on tables taking live traffic	A plain CREATE INDEX locks the table for the duration of the build
No rename or type change on a live column in one step	Breaks any code still running the previous deploy
Backfills are batched, not one giant update	Avoids long-held locks and huge transactions

Make it a habit

• Add the schema-first and expand-contract rules to CLAUDE.md so every session picks them up without repeating the prompt. See the CLAUDE.md template.
• Commit the migration on its own branch, separate from the application code that depends on it, following the same git workflow you'd use for anything else.