When your staging and production databases drift apart, the fastest way to bring them back in line is to compare the two schemas and generate the exact SQL that makes one match the other. This guide walks through how to compare two database schemas and produce a safe, reviewable migration script.
Why compare database schemas?
Manual tracking of schema changes does not scale. Columns get added on one environment and forgotten on another, an index exists in dev but not prod, a foreign key was dropped during a hotfix. A schema diff answers three questions at once:
- What is different? Every added, removed, and modified table, column, index, constraint, view, and trigger.
- Which direction? Which objects exist in the source but not the target, and vice versa.
- How do I fix it? The DDL that transforms the target schema into the source schema.
Step 1 — Connect the source and target
Point the tool at the database you are comparing from (the source) and the one you are comparing to (the target). FoxSchema never writes to the source — it only ever modifies the target, and only when you explicitly apply a migration. Supported dialects include MySQL, PostgreSQL, MariaDB, SQL Server, Azure SQL, Oracle, IBM Db2, SQLite, ClickHouse, and Amazon Redshift.
Step 2 — Run the comparison
Choose a source and target connection and run the comparison. The engine introspects both schemas and produces a grouped, searchable diff. Results are colour-coded:
- Added — exists in source, missing in target.
- Modified — exists in both but differs.
- Removed — exists in target, not in source.
Drill into any object to see the exact column, index, foreign-key, and trigger differences.
Step 3 — Generate the migration SQL
From the diff, generate the migration script. The output is runnable DDL for the target database's dialect — nothing has been applied yet. Review it, then either apply it through the tool (a pre-migration snapshot is taken first) or copy the SQL and run it yourself.
For large migrations, an optional skip-on-error mode continues past any single object that fails instead of rolling back the whole run, so you can apply what works and fix the rest afterward.
Step 4 — Verify
Re-run the comparison after applying. A clean diff (everything unchanged) confirms the two schemas now match. Every applied migration is recorded in history with its script and snapshot.
Try it
FoxSchema is a free, open-source schema diff and migration tool. Download the desktop app for macOS, Windows, or Linux, or self-host the web app with Docker. New to it? Start with the documentation.
