Database Migration Checklist: What to Review Before Running ALTER TABLE

A migration that works on an empty development database can still fail or do damage in production, because production has data, traffic, and application code that depends on the old schema. You don't need a heavyweight process to catch most of the problems — you need to look at each statement and ask which of a few well-known categories it falls into.

A migration worth reviewing

Here are four statements that look routine:

ALTER TABLE orders DROP COLUMN legacy_ref;
ALTER TABLE orders RENAME COLUMN cust_id TO customer_id;
ALTER TABLE orders ADD COLUMN region TEXT NOT NULL;
CREATE INDEX idx_orders_customer ON orders (customer_id);

Each one belongs to a different risk category. The checklist below walks through them.

1. Destructive and irreversible

DROP COLUMN permanently deletes the data in that column, and DROP TABLE deletes the whole table. There is no undo inside the migration itself; recovery means restoring from a backup. Before running one, confirm the data is truly unused and that a recent backup exists. Dropping a primary key is in the same category, because the table no longer has a unique row identifier, and ORMs, replication and cascading deletes often rely on one. For the application-side risks, see DROP COLUMN in production: what actually breaks.

2. Statements that fail on existing data

These are the ones that pass on an empty table and fail in production:

• ADD COLUMN ... NOT NULL with no default fails immediately if the table already has rows, since existing rows would have no value for it. In the example above, region TEXT NOT NULL is exactly this case. Adding it with a DEFAULT, or adding it nullable and backfilling first, avoids the failure.
• SET NOT NULL on an existing column fails if any row currently holds NULL. Backfill first. The pattern for doing it without a long lock is in adding a NOT NULL column in PostgreSQL.
• Adding a foreign key fails if orphaned rows exist — child rows pointing at a parent that isn't there.
• Changing a column's type can fail, or silently lose precision, if existing values don't fit the new type.

3. Statements that silently break application code

These succeed in the database and fail later, in code the migration doesn't touch:

• Renaming a column or table. In the example, cust_id becoming customer_id breaks every query, ORM model, view and report that still uses the old name — including the previous version of your application that is still running during a rolling deploy.
• Dropping an index. Nothing errors; queries that depended on it just get slower, sometimes by orders of magnitude on a large table.

A safer rename is to add the new column, write to both for a while, migrate readers, and only then drop the old one.

4. Statements that are safe but take a lock

Adding a nullable column or an index is generally safe in the sense of not failing or losing data, but a plain CREATE INDEX on a large, busy table can block writes while it builds. In PostgreSQL, CREATE INDEX CONCURRENTLY builds the index without blocking writes, at the cost of taking longer and not being usable inside a transaction block. Check how big the table is before choosing.

A pre-flight routine

1. List every statement and tag it destructive, data-dependent, code-breaking, or lock-taking.
2. For destructive ones, confirm the backup and that the data is unused.
3. For data-dependent ones, run a quick check query first — for example, SELECT COUNT(*) FROM orders WHERE region IS NULL.
4. For code-breaking ones, search the codebase for the old name and plan the deploy order.
5. Rehearse on a copy of production-sized data if the table is large.
6. Write down the rollback step before you run the migration, not after it fails.

Automating the first pass

The Migration Checker applies this same categorisation to the SQL you paste in, flagging dropped columns, primary key removals, NOT NULL without a default, renames and dropped indexes by severity. On the example above it rates the DROP COLUMN and the NOT NULL column as danger and the rename as a warning; the standalone CREATE INDEX is not flagged, so the lock question in section 4 is one to check yourself. It reads the statements only — it can't see your data or your application code, so the check queries and the code search above are still yours to do.

Run your migration through it

Paste your migration into the Migration Checker to see each statement rated before it runs. It works entirely in your browser, so the schema you paste is not uploaded.