How to Review a Database Migration Like a Senior Engineer

The difference between a junior and senior engineer reviewing a database migration isn't experience with SQL. It's systematic paranoia.

Senior engineers don't just read the migration file. They interrogate it. They ask what happens if the deploy fails mid-migration. They check whether the app code is compatible with the schema change before and after the migration runs. They know that "just adding a column" has killed production databases.

This guide is the review framework I wish I had ten years ago. It's not about being slow — it's about being fast and right. You can run through this in under 10 minutes once it becomes habit.

The Three-Lens Review Framework

Every migration review should look at three things:

1. Schema correctness — Does the SQL do what the author thinks it does?
2. Application compatibility — Will the app break before, during, or after this migration?
3. Operational safety — Can this run in production without locking tables or consuming all disk I/O?

Miss any one of these and you're rolling dice. Let's break each down.

1. Schema Correctness

This is the "does the diff match the intent" check. You're not approving the migration until you can trace every line of SQL back to a specific requirement.

The Schema Correctness Checklist

The most common mistake here is drift. A developer writes a migration, tests it locally, then tweaks the schema directly in staging without updating the migration file. The migration that lands in production doesn't match the actual desired end state.

Pro tip: Always diff the staging schema against production after the migration runs in staging. If the diff isn't empty, something was changed outside version control.

Tool: SchemaLens

This is exactly what SchemaLens was built for. Paste your old schema and new schema, and it shows you a semantic diff — not a line-by-line text diff, but a structural comparison of tables, columns, indexes, constraints, and triggers. It flags breaking changes and generates the migration SQL for you.

During Launch Week (May 14–21), all Pro features are free — including full migration generation, rollback scripts, and breaking change detection.

2. Application Compatibility

Schema changes and app deploys rarely happen atomically. There's always a window where the old app code sees the new schema, or the new app code sees the old schema. Senior engineers plan for both.

The Application Compatibility Checklist

The classic mistake is adding a NOT NULL column without a default. The migration locks the table while it rewrites every row. The app starts throwing errors because it hasn't been updated to provide a value for the new column. And if the deploy rolls back, the column already exists but the old app code doesn't know about it.

The expand/contract pattern solves this:

-- Step 1 (deploy 1): Add column as nullable
ALTER TABLE users ADD COLUMN preferences JSONB;

-- Step 2 (deploy 2): Backfill data, then add NOT NULL
UPDATE users SET preferences = '{}' WHERE preferences IS NULL;
ALTER TABLE users ALTER COLUMN preferences SET NOT NULL;

Two deploys, zero downtime. This is how mature teams operate.

3. Operational Safety

Even a "correct" migration can destroy production if it locks a table for 30 seconds or fills the disk with WAL logs.

The Operational Safety Checklist

PostgreSQL's CREATE INDEX locks writes on the table. On a 500GB table, that can be minutes. The fix is CREATE INDEX CONCURRENTLY — but that can't run inside a transaction, and it can fail silently if there are duplicate values in a unique index.

MySQL's ALTER TABLE rewrites the entire table for many operations. On MySQL 5.7, adding a column to a large InnoDB table is an hours-long operation. On MySQL 8.0, it's instant — but only if you're using the right algorithm.

Know your database. Know your version. Know your table sizes.

The 5-Minute Review Routine

Here's the actual workflow I use:

1. Read the PR description. What problem is this solving? If the description is vague, stop and ask.
2. Diff the schemas. Use a tool (or pg_dump --schema-only before and after) to see exactly what changed structurally.
3. Trace every change to a requirement. No orphan migrations. Every line of SQL should have a reason.
4. Check the app code in the same PR. Is there corresponding code that uses the new schema? Does it handle the transition window?
5. Ask "what if it fails?" For every migration, imagine it failing at the worst possible moment. Is the database still consistent? Can you roll back?

If you can't answer all five confidently, the migration isn't ready.

Common Migration Anti-Patterns to Reject

Some patterns should be automatic rejections:

• Adding a column with NOT NULL and no default on a table with >1M rows. Instant lock. Instant outage.
• Creating an index without CONCURRENTLY on a production table with active writes.
• Dropping a column that might be referenced by a view, trigger, or legacy app code.
• Renaming a table or column in a single deploy without an expand/contract plan.
• Modifying a column type (e.g., INT → BIGINT) without checking if the app casts the value anywhere.
• Adding a foreign key without validating existing data first. The constraint creation will fail if there are orphans.

Building the Habit

The goal isn't to become a DBA. The goal is to build a spidey sense for migration risk. After reviewing 50 migrations using this framework, you'll start spotting problems in seconds — the same way a senior engineer can glance at a function and know it's O(n²).

Start with the checklist. Eventually, you won't need it.

"The best time to catch a bad migration is in code review. The second best time is in staging. The worst time is at 2 AM when production is down."

Related Reading

• How to Review a SQL Schema Change in 5 Minutes
• The Complete SQL Migration Checklist for Production
• 10 Database Schema Changes That Break Production
• Database Migration Horror Stories (and How to Avoid Them)