Schema migrations are the most dangerous code you ship. They run once, cannot be rolled back trivially, and affect every query in your application. A bad migration doesn't just break deploys — it corrupts data, locks tables for hours, and wakes up on-call engineers at 3 AM.
The good news: most schema disasters are preventable with a disciplined review process. This checklist is the one we wish every team had before approving a migration PR.
Run these before you even open the migration PR. They take 30 seconds and catch 50% of issues.
Staging schemas are often cleaner than production. Test against a recent dump or a read replica. If you can't, at least verify the migration works on a dataset with realistic row counts.
Every migration should have a rollback plan. If you're adding a column, the rollback drops it. If you're dropping a column, the rollback adds it back — but you may have lost data. Document what data loss is acceptable.
Some ALTER TABLE operations acquire exclusive locks. In PostgreSQL, adding a column with a default locks the table until the rewrite completes. In MySQL, ALTER TABLE on large tables can take hours. Check your dialect's lock behavior.
Adding a NOT NULL column without a default to a table with existing rows will fail. If you need it NOT NULL, add it as nullable first, backfill data, then add the constraint in a follow-up migration.
VARCHAR(255) is not a universal default. Email addresses need VARCHAR(254) per RFC. JSON should use JSONB in PostgreSQL, not TEXT. Timestamps should include timezone awareness. Wrong types are expensive to fix later.
DEFAULT 0 on a foreign key is a landmine. DEFAULT CURRENT_TIMESTAMP is usually fine. DEFAULT gen_random_uuid() is great for primary keys. Make sure the default matches your application's expectations.
Prefer created_at over created or date_created. Use singular nouns. Match existing naming conventions in the schema. Inconsistent naming makes ORM mapping and reporting queries painful.
Foreign key constraints do not auto-create indexes in most databases. Without an index, deletes on the parent table scan the entire child table. Always index foreign key columns.
Unique constraints are powerful but come with performance costs on writes. They also prevent legitimate duplicates in edge cases (e.g., soft deletes). Make sure the business rule truly requires uniqueness at the database level.
CHECK (price > 0) seems safe until you need to support discounts or refunds that result in zero or negative amounts. CHECK constraints are hard to relax later. Prefer application-level validation for business rules that change.
Adding a feature usually means adding queries. Check your ORM logs or query planner for sequential scans on large tables. Add indexes before the deploy, not after the incident.
In a composite index (a, b, c), the database can use the index for queries on a, a,b, and a,b,c — but not b or c alone. Put the most selective column first.
If you have an index on (a, b), you don't need a separate index on (a) (in most databases). Redundant indexes slow down writes and consume disk space.
Dropped columns, tables, or enums might still be referenced by application code, background jobs, analytics pipelines, or third-party integrations. Search your codebase for the name before dropping.
Type changes like VARCHAR(100) → VARCHAR(50) can truncate data. TEXT → INTEGER can fail on non-numeric values. Always validate the transformation on real data first.
Database enums are rigid. Adding a value requires an ALTER TYPE in PostgreSQL or a table rebuild in MySQL. If the enum values change frequently, consider a lookup table instead.
Run a quick \d table_name (PostgreSQL) or SHOW CREATE TABLE (MySQL) to confirm the migration applied exactly as intended. Don't assume — verify.
Check error rates, query latency, and lock wait times for 30 minutes after deploy. A migration that ran fine in staging can behave differently under production load.
If your team maintains an internal schema doc, ERD, or API spec, update it now while the changes are fresh. Stale documentation confuses new hires and slows debugging.
This checklist is comprehensive, but running through it manually for every migration is tedious. Here's the faster way:
Semantic diff catches the structural changes. The checklist catches the judgment calls. Together, they make schema reviews fast, thorough, and repeatable.
SchemaLens compares two CREATE TABLE dumps and shows you a visual diff with a generated migration script. Free for up to 10 tables.
Try SchemaLens Free →Pro tip: Print this checklist and tape it next to your monitor. Or better, add it to your team's PR template so every migration gets reviewed the same way.