Schema Diff — Compare Database Schemas

Free Database Schema Diff & Migration Generator

Database schemas evolve constantly — new features need new tables, growing datasets require column type changes, and refactoring demands relationship modifications. Our Schema Diff tool compares two versions of a database schema and generates the exact ALTER TABLE statements needed to migrate from one to the other. All processing runs in your browser, so your database structures never leave your device.

What Are Database Migrations and Why Do They Matter?

A database migration is a set of SQL statements that transforms a database schema from one version to another. When you add a feature that requires a new table, change a column's type to accommodate different data, or add an index to improve query performance, you are performing a schema migration. In production systems, migrations are the controlled, repeatable mechanism for evolving database structure alongside application code.

Migrations matter because database schema changes are among the riskiest operations in software deployment. Unlike application code, which can be rolled back by deploying a previous version, schema changes alter the persistent state of your system. A dropped column deletes data permanently. A type change can fail halfway through on a table with millions of rows, leaving the database in an inconsistent state. A missing index can cause a query that ran in milliseconds to take minutes under production load.

Good migration practices reduce this risk by making schema changes explicit, versioned, reviewable, and reversible. Each migration is a file in version control with a clear UP script (apply the change) and an optional DOWN script (reverse it). The team reviews migrations in pull requests just like application code. Migration frameworks (Flyway, Liquibase, Prisma Migrate, Alembic, Rails ActiveRecord Migrations) track which migrations have been applied to each environment, preventing double-application or missed migrations.

The Risks of Manual Schema Changes in Production

When developers make schema changes by running ad-hoc ALTER TABLE statements directly against a production database, several things go wrong. First, there is no record of what changed or why. Three months later, when someone asks why the orders table has a legacy_status column, nobody remembers. Second, the same change must be manually replicated in every environment — development, staging, QA, production — and any inconsistency between environments leads to bugs that only appear in production.

Third, manual changes cannot be reviewed before execution. A typo in an ALTER TABLE statement can drop the wrong column or change the wrong type. Without a review step, these mistakes reach production. Fourth, manual changes are not easily reversible. If you realize after deploying that the migration broke something, you need to figure out the reverse operation under pressure, which is when mistakes compound.

Schema diffing tools address these problems by generating migration scripts automatically from a declarative target state. Instead of writing ALTER TABLE statements by hand, you define what the schema should look like after the change, and the tool calculates the exact steps to get there. The generated script can be reviewed, tested, and version-controlled like any other code artifact.

How Schema Diffing Works: Comparing Two Database States

Schema diffing compares two snapshots of a database schema — typically the current production schema (Schema A) and the desired target schema (Schema B). The diff engine parses the CREATE TABLE statements in both schemas into tables, columns, types, constraints, default values, and foreign keys, then computes the set of changes needed to transform A into B. It doesn't compare indexes, CHECK constraints, or ON DELETE actions yet.

Our diff engine detects six categories of changes:

Tables added. A table exists in Schema B but not in Schema A. The migration adds a CREATE TABLE statement for it.
Tables removed. A table exists in Schema A but not in Schema B. The migration drops the table. Foreign keys in other tables that reference it aren't dropped first, so remove those references in the same migration.
Columns added. A column exists in a table in Schema B that is not present in the same table in Schema A. The migration adds the column with its type, constraints, and default value.
Columns removed. A column in Schema A is absent from Schema B. The migration drops the column, after dropping the column's own foreign key if it has one.
Columns modified. A column exists in both schemas but with different properties — a different data type, a changed default value, a constraint added or removed (NOT NULL, UNIQUE). The migration alters the type, nullability, and default; UNIQUE and primary key changes appear as comments for you to write by hand.
Foreign key changes. A relationship has been added, removed, or pointed at a different table or column. Changes to ON DELETE and ON UPDATE actions aren't detected yet.

Each change is displayed with clear before/after comparisons and color coding — green for additions, red for removals, amber for modifications. The migration runs in fixed phases: foreign keys that are removed or changed are dropped first, then tables and columns change, then new foreign keys are added. Tables aren't sorted by dependency, so check the order of CREATE TABLE and DROP TABLE statements when the tables reference each other.

Migration SQL Generation

The migration generator writes a SQL script to transform Schema A into Schema B in the dialect you pick. Review it before running it:

PostgreSQL uses ALTER COLUMN ... TYPE with a USING clause for type conversions, SET NOT NULL / DROP NOT NULL for constraint changes, and ALTER COLUMN ... SET DEFAULT for default values.
MySQL uses MODIFY COLUMN for type and constraint changes, which requires restating the full column definition including the new type, nullability, and default.
SQL Server output uses ALTER COLUMN with the new type, but type names aren't translated to SQL Server's yet, and new columns are written as ADD COLUMN where SQL Server expects ADD. Edit these before running it.
SQLite has limited ALTER TABLE support: it can add, rename, and (since version 3.35) drop columns, but it can't change a column's type or constraints. For those changes the output adds a note to recreate the table (create a new table, copy the data, drop the old table, rename the new one).

Comments in the script mark each changed table, column, and foreign key, which makes it easier to review.

Best Practices for Database Migration Workflows

Always back up before migrating. Even well-tested migrations can fail on production data that contains edge cases not present in staging — NULL values in columns you are changing to NOT NULL, strings too long for a new VARCHAR limit, or foreign key references to rows that no longer exist. A backup lets you recover from migration failures without data loss.

Test on a copy of production data. Schema changes that execute instantly on empty tables can take hours on tables with millions of rows. Adding a NOT NULL column to a table with 50 million rows requires writing a default value to every existing row. Changing a column type from VARCHAR to INTEGER requires validating and converting every existing value. Test your migration against a realistic data volume to discover performance issues before they cause a production outage.

Make migrations reversible. For each UP migration, write a corresponding DOWN migration that reverses the change. This lets you roll back if the migration causes unexpected application errors. Not all migrations are perfectly reversible (you cannot un-drop a column and recover its data), but having a DOWN script for the structural changes makes rollbacks much faster.

Deploy schema changes separately from application code. When a migration and the code that depends on it are deployed simultaneously, a failure in either one leaves the system in an inconsistent state. Instead, deploy migrations in two phases: first, make additive changes (new tables, new columns) that the current application code can ignore. Then deploy the application code that uses the new schema. Finally, remove anything that is no longer needed (old columns, old tables) in a subsequent migration.

Version your migrations. Keep migration files in version control alongside your application code. Use a migration framework (Flyway, Liquibase, Prisma Migrate, Alembic) to track which migrations have been applied to each environment. Never edit a migration that has already been applied — create a new migration instead.

Common Migration Pitfalls

Column renames vs. drop/add. If you rename a column, some migration tools generate a DROP COLUMN followed by an ADD COLUMN rather than an ALTER COLUMN RENAME. This destroys all existing data in the column. Our diff tool doesn't detect renames yet: a renamed column shows up as one column removed and one added. Replace that DROP/ADD pair with a RENAME COLUMN statement before you run the migration.

Data loss from type changes. Changing a column from TEXT to VARCHAR(50) will fail if any existing row contains a string longer than 50 characters. Changing from VARCHAR to INTEGER will fail if any existing value cannot be parsed as a number. Always check your existing data against the new constraints before running the migration. A simple query like SELECT COUNT(*) FROM users WHERE LENGTH(name) > 50 can prevent a migration failure.

Foreign key ordering. You cannot drop a table that is referenced by foreign keys in other tables. You cannot add a foreign key to a table that references a table that does not exist yet. Migration scripts must order operations correctly: drop foreign key constraints first, then drop or modify tables, then create new tables, then add new foreign key constraints. Our diff tool drops changed foreign keys first and adds new ones last, but it doesn't sort tables by dependency, so check the order when tables that reference each other are created or dropped.

Table locking on large tables. ALTER TABLE operations on tables with millions of rows can lock the table for the duration of the change, blocking all reads and writes. In MySQL, adding a column or changing a type on a large table can lock it for minutes or hours. Use online DDL tools (pt-online-schema-change for MySQL, pg_repack for PostgreSQL) or the database's built-in online DDL support to perform zero-downtime migrations on large tables.

Related Tools

Design your target schema visually using our Schema Designer, or paste existing SQL into our SQL to Diagram tool to visualize your current schema before comparing. Browse our Schema Templates library for well-designed starting points. For API-level schema work, see our JSON Schema Validator. To learn more, read our Database Design Fundamentals guide.

Frequently Asked Questions

Is my database schema sent to a server?

No. The diff engine runs entirely in your browser. Both schemas and the generated migration script stay on your device. We record anonymous usage metadata about each comparison: the dialect and counts of tables and changes, never the schemas or their names.

What SQL dialects are supported?

The tool writes migration SQL in PostgreSQL, MySQL, SQLite, or SQL Server syntax, with each dialect's quoting. PostgreSQL and MySQL output is the most complete. SQL Server output still needs its type names and ADD clauses edited, and some SQLite changes need a table rebuild, which the output notes.

Can I paste existing SQL to compare two schemas?

Yes. Paste CREATE TABLE statements from your current database in the Schema A pane and the target schema in the Schema B pane. The parser reads CREATE TABLE statements with their columns, primary keys, unique constraints, and foreign keys. It skips other statements, such as CREATE INDEX and ALTER TABLE, and doesn't support schema-qualified names (public.users) or named CONSTRAINT clauses yet. You can also use our lightweight DSL syntax if you prefer.

Does the tool detect column renames?

Not yet. A renamed column shows up as one column removed and one added, and the migration drops the old column, which destroys its data. When you see a removed and an added column of the same type in one table, replace the DROP/ADD pair with RENAME COLUMN before running the migration.

How do I handle migrations for tables with millions of rows?

For large tables, ALTER TABLE operations can lock the table and take a long time. Use online DDL tools like pt-online-schema-change (MySQL) or pg_repack (PostgreSQL) to perform changes without blocking reads and writes. Test migration performance against a copy of your production data to estimate execution time before running in production.