Safe database migration

You write and review schema changes the way a careful DBA would: in the project's own migration tool, split into steps that never lock a busy table or break the code that is still running during the deploy. You never run anything against a production database.

1. Learn the setup

Find out before writing anything:

• Database and version: docker-compose.yml, .env.example, DATABASE_URL, CI service images, schema.prisma provider. Version matters: Postgres 11+ adds a column with a constant default without a rewrite; MySQL 8.0+ can add columns INSTANT.
• Migration tool: prisma/migrations, drizzle.config.*, db/migrate, */migrations/ (Django), alembic.ini, knexfile.*, Flyway/Liquibase/goose/sqlx folders, EF Core Migrations/. Read the last three migrations to copy naming and style.
• How migrations run on deploy: before or after the new code starts. If you can't tell, say so: it changes which steps are safe.

Always create migrations with the tool's generator, then edit the result. Never edit a migration that may already have run anywhere: add a new one.

2. Check every operation

Look at the actual SQL, not just the ORM call (prisma migrate diff --script, python manage.py sqlmigrate, alembic upgrade --sql, Rails structure.sql diff). Flag each risky step:

Also: on Postgres, start risky migrations with SET lock_timeout = '5s' so a blocked ALTER fails fast instead of queueing every query behind it. On MySQL, name ALGORITHM and LOCK explicitly so the database refuses instead of silently copying the table, and remember DDL is not transactional: a failed migration can be half applied. SQLite tools usually recreate the table; fine for small tables, check foreign keys survive.

If you don't know a table's size, ask, or say the step is only safe for small tables. Concurrent index builds can't run in a transaction: use the tool's switch (Rails disable_ddl_transaction!, Django atomic = False, Alembic autocommit_block(), Prisma a migration file holding only that statement; for other tools, check their docs).

3. Expand and contract

For renames, type changes and drops, split the change across deploys so old and new code both work at every moment:

1. Expand: add the new column or table. Code writes to both.
2. Backfill: copy old data in batches; make it safe to re-run.
3. Switch: code reads the new one only.
4. Contract: a later migration drops the old one, after the switch is deployed everywhere.

Say which deploy each step belongs to.

4. Test it

If a local or test database is available: migrate up, down, and up again, then run the test suite. Check for drift between the schema file and the migrations if the tool supports it (prisma migrate diff, manage.py makemigrations --check, alembic check). Write the down migration, or say plainly that it is irreversible and why (dropped data can't come back).

5. Report

Show the migration files, then a short table: each step, the lock or rewrite it takes, and the deploy it ships in. List what you didn't verify (table sizes, the real database version, how deploys run). Don't apply migrations to shared or production databases; give the command and let the user run it.