Generate a SQL migration from two schemas
Paste the schema you have and the schema you want. Get the migration between them for PostgreSQL, MySQL, or SQL Server, in an order that runs, with a warning for every statement that can lose data.
The order is the hard part
Working out what changed between two schemas is tedious. Writing the statements in an order a database will accept is where hand-written migrations fail, because each statement runs against whatever the ones before it left behind. The script is written in this order:
- Drop foreign keys that are going away. A table they point at may be about to go too.
- Drop indexes and primary keys that are going or changing. So the columns under them are free to change.
- Create enum types and add enum values. New columns are about to use them.
- Create new tables, without their foreign keys. Two new tables may reference each other.
- Add columns, then alter columns. Additions never block an alteration.
- Drop columns, then drop tables. Only after nothing still depends on them.
- Add primary keys, indexes, and finally foreign keys. Every table and column they name now exists.
- Drop enum types nothing uses any more. Last, once no column refers to them.
What it warns you about
- Data loss. Dropping a column or a table, narrowing a type, and removing an enum value are each called out, and the summary says when a migration can lose data.
- Statements that fail on a table with rows. A new
NOT NULLcolumn with no default is the common one. - Things the database cannot do in place. PostgreSQL cannot remove a value from an enum, and SQL Server cannot change an identity column. These are written into the script as comments, never silently skipped.
- Possible renames, of a column or of a whole table, as a comment beside the drop and the add that a rename would replace.
Both sides are the whole schema
Paste every table on each side, not only the ones that changed. A table missing from the right-hand side reads as a table you want dropped, and a foreign key into a table that is not there cannot be resolved.
DB Planner does this from history: it keeps versions of each schema document, compares any saved version with the current one, and writes the same script without you finding the old text. Check the target schema first with the schema checker, or generate the full DDL for a database that does not exist yet.
Migration generator questions
How do I generate a migration script between two schemas?
Paste the schema you have now on the left and the schema you want on the right, as DBML or as CREATE TABLE statements, and choose the database. The result is the ALTER TABLE script that takes the first to the second, with a list of what changes.
Why did it drop and add a column I only renamed?
Because two schemas cannot say whether a column was renamed or replaced, and guessing wrong either loses data or keeps data that should have gone. When exactly one column of a type leaves a table and one arrives, the script marks it as a possible rename in a comment and leaves the decision to you.
Which databases does it write migrations for?
PostgreSQL, MySQL, and SQL Server. The script is wrapped in a transaction for PostgreSQL and SQL Server. MySQL commits every schema statement on its own, so there the script cannot be made atomic and is not pretended to be.
Is it safe to run the script on production?
Read it first, and run it against a copy. The generator flags the statements that lose data and the ones that fail on a table that already has rows, such as adding a NOT NULL column with no default, but it cannot see your data or your traffic.
Put your schema on the board
Start on the free plan, no card needed. Pro is $5 a month when you outgrow it.
Start designing free