How to compare database schemas between staging and production

What schema drift is

Staging and production are supposed to match. Over time they don't: a hotfix applied directly to production, a migration that failed halfway, an index added during an incident and never written into a migration. The result is code that works in staging and fails in production, or a migration that breaks on deploy.

Comparing schemas before a release catches this while it is cheap to fix.

1. What to compare

• Tables and columns: names, data types, lengths, nullability and defaults.
• Indexes: missing, extra or different column order.
• Constraints: primary keys, foreign keys, unique and check constraints.
• Code objects: views, functions, stored procedures and triggers.

Leave out things that are supposed to differ: owners, grants, statistics and storage settings.

2. Diff schema-only dumps

The quickest complete check: export the schema from each environment without data, then diff the files.

pg_dump --schema-only --no-owner --no-privileges -h staging-db -d app > staging.sql
pg_dump --schema-only --no-owner --no-privileges -h prod-db    -d app > prod.sql
diff -u staging.sql prod.sql

mysqldump --no-data --skip-comments --routines --triggers -h staging-db app > staging.sql
mysqldump --no-data --skip-comments --routines --triggers -h prod-db    app > prod.sql
diff -u staging.sql prod.sql

For SQL Server, script both databases with Generate Scripts in SSMS, or extract them with sqlpackage, and compare the output. Visual Studio's Schema Compare also does this directly between two databases.

3. Compare with catalog queries

When you only care about columns, a catalog query gives a tidy list you can diff or load into a spreadsheet. Run the same query in both environments.

SELECT table_name,
       column_name,
       data_type,
       character_maximum_length,
       is_nullable,
       column_default
FROM information_schema.columns
WHERE table_schema = 'public'   -- 'dbo' on SQL Server, the database name on MySQL
ORDER BY table_name, column_name;

Export each result to a file and diff them. Every line that differs is a column that is missing, extra or defined differently.

4. Indexes and constraints

Indexes are where drift hides most often, because they are added during incidents.

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

SELECT OBJECT_NAME(i.object_id) AS table_name,
       i.name AS index_name,
       i.type_desc,
       i.is_unique,
       STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
GROUP BY i.object_id, i.name, i.type_desc, i.is_unique
ORDER BY table_name, index_name;

5. Reconcile safely

1. For each difference, decide which side is correct. Production is not always right: a hotfix may need to move back into staging and into a migration.
2. Write the fix as a migration in your normal tool, not as a one-off script, so every environment gets it.
3. Check what the change will lock. Adding a column with a default, changing a type, or building an index can lock a large table. Use online or concurrent options where your database has them.
4. Keep a reverse script and label it untested until you have run it somewhere.

6. Keep it from drifting again

• Make migrations the only way schema changes reach production.
• Run the comparison as a step before each release and fail the release on unexpected differences.
• When an incident needs a hotfix index, write the migration the same day.

The same steps in Quorum

Quorum compares two connections side by side, keeps schema snapshots over time, and generates the script to reconcile them, labelled per object.

-- reconcile prod ← staging · 3 statements · reverse script generated (untested)
ALTER TABLE dbo.Orders ADD FulfilledAt datetime2 NULL;

• Schema compare across environments: added, removed and changed tables, columns, indexes, constraints and routines.
• Schema history and snapshots: compare any two points in time to see what changed and when.
• Migrations with a recovery plan: the exact script, likely locking and data-loss risk; reverse scripts labelled untested until validated.
• Drift detection before promotion, with releases tracked from QA to production.