What the SQL Schema Diff Checker does
This checker compares two sets of CREATE TABLE and CREATE INDEX statements - the schema you run today and the one you want - and lists every added or dropped table, column, index and constraint, every type and nullability change, and a risk level for each. It then writes a draft migration plan in PostgreSQL, MySQL, SQL Server or SQLite syntax, ordered so additive steps come first and destructive ones last.
It reads the SQL as text in your browser. Nothing is executed, nothing connects to a database and nothing is uploaded, so you can paste a production dump. The plan is a starting point for review, not a script to run blind.
How to use it
- Choose the dialect you will migrate with. It decides the ALTER syntax in the plan and whether names are compared case-sensitively.
- Paste the current schema into Before and the target schema into After. A
pg_dump --schema-only,mysqldump --no-data, SQL Server "Script table as CREATE" or an ORM's generated DDL all work; drop a .sql file onto either box if it is easier. - Press Compare schemas. Changes that can lose or refuse data are listed first, then a table of every change with its reason.
- Read the draft plan, edit it, and test it against a restored copy of production data before it goes anywhere near the real database.
Reading the results
High risk means data can be lost or the statement can fail on existing rows: DROP TABLE, DROP COLUMN, a narrowing type change (bigint to integer, varchar(255) to varchar(120), numeric(10,2) to numeric(10,4)), a cross-family conversion such as integer to uuid, a new NOT NULL column with no default, or removing a primary key.
Check first means the change is valid but can fail or lock on real data: making an existing column NOT NULL (fails on any NULL), adding a unique index or constraint (fails on duplicates), changing a primary key, adding a foreign key, or switching timestamp to timestamptz, which reinterprets stored values in the session time zone.
Low risk covers additive and widening changes: new tables, nullable columns or columns with a default, longer strings, wider integers, new non-unique indexes and dropped non-unique indexes. Low risk is about data safety; a type change on a large PostgreSQL table still rewrites it under an exclusive lock, and the finding says so.
When a column disappears and another of exactly the same type appears in the same table, the finding suggests it may be a rename. A diff of two snapshots cannot know intent, and generating DROP plus ADD for a rename silently throws the data away.
Worked example: a release that renames, narrows and tightens a customers table
Before: customers(id integer, email varchar(255), full_name varchar(200), phone varchar(40) NULL, created_at timestamp) with a plain index on email, plus an audit_log table. After: id becomes bigint, email shrinks to varchar(120), full_name is replaced by display_name varchar(200), phone becomes NOT NULL, country char(2) NOT NULL and marketing_opt_in boolean NOT NULL DEFAULT false are added, created_at becomes timestamptz, the email index becomes a unique index on lower(email), audit_log is dropped and a new orders table appears.
The checker reports 12 changes: 4 high risk (audit_log dropped, full_name dropped, email narrowed from 255 to 120 characters, and country added as NOT NULL with no default, which fails on a table with rows), 3 to check first (phone NOT NULL needs a backfill, the unique index fails on duplicate addresses, and the timestamptz switch changes meaning) and 5 low risk (orders, display_name, marketing_opt_in with its default, id widened to bigint, the old index dropped).
Because display_name has the same type as the dropped full_name, the drop finding suggests ALTER TABLE customers RENAME COLUMN full_name TO display_name instead - the difference between keeping every customer's name and losing it.
Why the plan is ordered the way it is
The draft plan creates new tables and adds columns first, then changes types, defaults and nullability, then keys and indexes, and only then drops indexes, columns and tables. Deploying in that order lets old and new application code run side by side during a rollout, and it keeps anything irreversible until last, after you have checked the earlier steps worked.
For PostgreSQL the plan uses CREATE INDEX CONCURRENTLY, which avoids blocking writes but cannot run inside a transaction block. SQL Server stores defaults as named constraints, so the plan tells you to drop the old DF_ constraint by name. SQLite cannot alter a column in place, so the plan gives the documented rebuild recipe - create a new table, copy the rows, drop the old one, rename - once per table rather than once per change.
What the parser understands
The tokenizer handles single-quoted strings with doubled quotes, PostgreSQL dollar quoting, MySQL backslash escapes, double-quoted, backtick and square-bracket identifiers, line and nested block comments, and GO batch separators. Unquoted names are folded to lower case; quoted names keep their exact spelling. Types are normalised so that character varying(255) and varchar(255), int4 and integer, decimal and numeric, or bigserial and bigint compare as equal.
Limitations: what the result does not prove
- It compares structure only. Views, functions, triggers, grants, sequences, partitions, comments and CHECK constraint bodies are not compared, and it cannot see the data - it can say a NOT NULL change might fail, not whether your table holds NULLs.
- Risk levels are rules of thumb about data safety. Lock duration and rewrite time depend on the engine version, table size and settings; check your engine's documentation for the exact behaviour of each ALTER.
- Renames are only suggested, never assumed. If a column was renamed and changed type in the same release, it appears as a drop and an add.
- The plan is a draft. Foreign keys that reference a changed primary key, dependent views and application code are not rewritten for you.
Privacy: where your data goes
Everything you paste, type or drop is processed in this browser tab. It is not uploaded, logged, stored or sent to analytics. Session recording and tag-manager scripts are switched off on this page.
Standards and sources
- PostgreSQL - ALTER TABLE
- PostgreSQL - CREATE INDEX (building indexes concurrently)
- MySQL 8.4 - ALTER TABLE statement
- SQL Server - ALTER TABLE (Transact-SQL)
- SQLite - ALTER TABLE and the table rebuild procedure
Frequently asked questions
Does this tool connect to my database or run the migration?
No. It reads the two pieces of SQL as text in your browser and never executes anything, including the draft plan it writes. There is no database connection and nothing is sent to a server, so a production schema stays on your machine.
How do I get the CREATE TABLE statements for my current schema?
PostgreSQL: pg_dump --schema-only dbname. MySQL: mysqldump --no-data dbname, or SHOW CREATE TABLE t for one table. SQL Server: right-click a table in SSMS, Script Table as, CREATE To. SQLite: .schema in the sqlite3 shell. Most ORMs can also print the DDL they would generate.
Why is shrinking a varchar marked as high risk?
Any existing value longer than the new limit either makes the ALTER fail or, in MySQL without strict mode, is silently truncated. Before narrowing, find the longest value with a query such as SELECT max(length(email)) FROM customers and decide what to do with the rows that do not fit.
How should I add a NOT NULL column to a table that already has rows?
Either give it a default in the same statement, so existing rows receive that value, or add it as nullable, backfill it in batches, and then set NOT NULL. PostgreSQL 11 and later add a column with a constant default without rewriting the table; older versions and some other engines do rewrite it.
Why does changing timestamp to timestamptz need checking if no data is lost?
PostgreSQL converts the stored values by interpreting them in the session's time zone. If the server wrote UTC but the migration session runs in another zone, every value shifts. Set the session time zone deliberately, or use an explicit USING clause, before converting.
Can it detect a renamed column or table?
Not with certainty, because two snapshots do not record intent. When a dropped column and an added column in the same table have exactly the same type, the drop finding says it may be a rename and suggests RENAME COLUMN, which keeps the data.
Why are there no CHECK constraints or views in the comparison?
Comparing them properly needs an expression-level parser for each dialect, and a naive text comparison would report harmless formatting changes as differences. The tool counts CHECK constraints but does not compare their bodies; review those and any views by hand.
Last reviewed by the A2Z.Tools team against the sources listed above.
