Turning a Schema Diff Into a Safe Migration Script
For the person holding a generated schema diff who has to decide which of its statements can run on production; the PostgreSQL locking behavior is cited where it decides the answer.
On this page
DbSchema writes the migration script for you and lets you check it before anything runs. It compares your design model with the target database, or one model file with another. Then it generates the DDL that closes the difference. Treat that script as a proposal. Your job is to read it and split it in two. The additive statements can run during traffic. The others destroy values or take a lock that stops every other query. In a difference tree, the two kinds look the same.
A migration script moves a database from one structure to the next
A migration script is a file of DDL, and sometimes DML. It moves a database from its current structure to the one you want, in a fixed order, and keeps the existing rows. You review it like code, run it once per environment, and keep it for the next database.
Two approaches produce that file, and they differ in where the truth lives. A migration-based workflow records every change as a numbered script. Each script runs once, in order, and is never edited afterwards.
A state-based workflow keeps a definition of the finished schema. It compares that definition with the live catalog and computes the DDL at deployment time. To choose between the two, read Liquibase, Flyway or a visual schema compare. The schema migration tools comparison sets the main tools side by side.
A generated diff is a proposal, not a migration
The comparison reports what differs. It does not report what closing the difference costs. Say a column exists in the database but not in the model. The diff returns a DROP COLUMN, because that makes the two match. It cannot know whether that column holds the only copy of some data. A type narrowed in the model returns an ALTER that truncates or rejects stored values. Both statements sit in the tree next to a harmless ADD COLUMN, styled the same way.
Order is the second question, though a good generator settles it: a foreign key needs its referenced table first. Timing is the third. An ALTER that runs in milliseconds on an empty development database can lock a production table for minutes. That turns a correct script into an outage. Reading the script answers all three questions before anything runs.
Name the kind of migration before you plan it
The kind of migration decides the testing, the downtime window and the rollback plan. Most releases contain more than one kind.
| Kind | What changes | Typical work | Main risk |
|---|---|---|---|
| Schema | Structure | Add columns, alter types, create indexes, add keys | Locks, invalidated plans |
| Data | Rows | Backfills, splitting columns, re-encoding | Truncation, constraint violations |
| Application | The query layer | ORM mappings, SQL, data access code | Runtime errors on unmapped columns |
| Infrastructure | Where it runs | New host, major version upgrade, another engine | Driver and parameter differences |
What to see: a generated script covers only the first row. The other three are yours to plan.
A schema migration rarely comes alone. Splitting a full_name column in two adds columns, fills them, and changes the code that writes them. The three steps must be sequenced so the running code works after each one.
Follow six phases, and never skip the rehearsal
Follow the phases in order, because each one feeds the next.
- Read the source database. Reverse-engineer the catalog and note the data volumes. List the unusual types, foreign keys and views that depend on the tables you will change.
- Define the target. Build the structure you want as a model, settle the naming, and write down the constraints that must hold afterwards.
- Generate the script. Compare the target with the source and produce the DDL. Write the reverse script now, while both shapes are in front of you.
- Rehearse at production volume. Run the script on a staging copy with a similar row count. Time each statement, not just the whole file.
- Deploy. Run the script on the live database with a lock timeout set, inside the window you sized in phase four.
- Check the result. Compare the database with the target model again, confirm the indexes built, and test the application's reads and writes.
Phase four is the one teams skip, and the one that catches real failures. A small dataset proves the script is valid SQL, not that you can afford to run it.
Keep the design as a file you can review in Git
DbSchema stores the design in a single .dbs file. It is plain XML with the tables, columns, data types, indexes, foreign keys, virtual foreign keys, comments and diagram layout. The XML is readable, so it belongs in the repository, and a schema change arrives as a diff in a pull request.
Saving the model to a file is a Pro feature. Git — Collaborative Design on the Model menu opens a Git client inside DbSchema. It has Stage, Commit, Push, Pull, Stash and Create Branch, so you commit the file from the window that edits it.
This is what a reviewer sees when a release adds an active column and an index to the customers table:
<table name="customers" spec="" >
<column name="id" type="int4" jt="4" mandatory="y" />
<column name="email" type="varchar" length="255" jt="12" mandatory="y" />
<column name="created_at" type="timestamptz" jt="93" mandatory="y" />
+ <column name="active" type="bool" jt="16" mandatory="y" defo="true" />
<index name="pk_customers" unique="PRIMARY_KEY" >
<column name="id" />
</index>
+ <index name="idx_customers_active" unique="NORMAL" >
+ <column name="active" />
+ </index>
</table>
The type, the nullability and the default are readable before any SQL exists. mandatory="y" with defo="true" means a NOT NULL column with a default. A reviewer who knows the table has forty million rows can say so while the change is still a text edit. DbSchema can also open two .dbs files and synchronize between them. The same comparison then runs between two branches or two tagged releases, with no database connection.
Generate the SQL by comparing the model with the target database
DbSchema turns the model into DDL with one more comparison, against the database the script will run on. The statements depend on what that database already has.
- Connect to the target. DbSchema reaches development, staging or production over JDBC. On its Settings tab, mark a connection you only read from as Read Only Connection. That blocks every change made through DbSchema.
- Open Schema → Compare Model with Database. DbSchema reads the live catalog and lists every table, column, type, key and index that differs from the model.
- Decide each row in the difference tree: push it to the database, pull it into the model, or leave it.
- Open the Sync Dialog from Schema → Synchronize Model with Database. It generates the SQL that brings the database in line with the model. Review it there, and save it to your migration pipeline if it will run later.
Steps one to three change nothing in the database. Pulling a difference into the model rewrites only the .dbs file on your disk. The database changes at one moment: when you execute the generated statements, and DbSchema lets you step through them one at a time. The DDL uses the syntax of the model's engine, such as PostgreSQL, MySQL, SQL Server or Oracle.
Check one setting before you compare. DbSchema identifies a schema by its catalog name as well as its schema name. Compare appdb.public with appdb_stg.public, and every object reads as missing on one side. The script then rebuilds the schema instead of altering it. So compare across servers that use the same catalog and schema names. Or reverse-engineer each side into its own .dbs file and compare the two models, as shown in comparing two database schemas.
Split the script and run the additive half first
Split the generated script in two before you run any of it. The additive statements go first: new tables, new nullable columns, new indexes, and new constraints that are not enforced yet. The application usually keeps working, because nothing it reads has moved.
Every DROP and every narrowing ALTER goes into a second script. That one runs only after no running instance uses those objects any more. This order is the expand and contract pattern. Deploying PostgreSQL schema changes safely shows the DDL it needs. Deploying SQL Server schema changes safely does the same for SQL Server.
The lock decides whether the additive half can run during traffic. In PostgreSQL 18, an ACCESS EXCLUSIVE lock conflicts with every other lock mode[1]. It makes the holder the only transaction that can touch the table. Many forms of ALTER TABLE and ALTER INDEX take a lock at that level. It is also the only lock that blocks a plain SELECT[1]. A lock is normally held until the transaction ends. By default, a transaction waits for a conflicting lock indefinitely[1]. So limit that wait in the migration session before the DDL:
SET lock_timeout = '100ms';
ALTER TABLE orders ADD COLUMN payment_status varchar(32);
lock_timeout stops any statement that waits longer than the set time for a lock[2]. The limit applies to each lock attempt separately, and a value with no unit means milliseconds. The default, zero, turns it off[2]. That is why the migration session has to set it.
Index creation is the statement most worth taking out of the script and running alone. A standard index build blocks writes on the table until it finishes. CREATE INDEX CONCURRENTLY builds the index without blocking inserts, updates or deletes[3]. It comes with two conditions the generated script will not handle for you. First, it cannot run inside a transaction block, unlike a regular CREATE INDEX. Second, if the build fails, for example on a deadlock or a duplicate value in a unique index, it leaves an invalid index behind. Queries ignore that index, but every write still pays to update it. The fix is to drop the index and build it again[3].
Download DbSchema at https://dbschema.com/download.html and open the model against your own database. Generate the DDL from the differences it finds, read it, and split it before you run it. Schema synchronization and saving the design to a .dbs file are Pro features. The free Community Edition connects, reverse-engineers the schema and draws the diagram.
Frequently asked questions
What is a database schema migration script?
A file of DDL, sometimes with DML, that moves a database from one structure to another in a fixed order and keeps its rows. See what a migration script is.
What are the 4 types of database migration?
Schema, data, application and infrastructure migrations. The table of the four kinds gives each one's typical work and main risk.
What are the 6 phases of database migration?
Read the source, define the target, generate the script with its reverse, rehearse at production volume, deploy with a lock timeout, and compare again. See the six phases.
How do you generate a migration file?
Keep the design as a .dbs file in the repository, commit it from the Git client inside DbSchema, and review its diff. One repository can hold one .dbs file per database.
How do you generate a SQL script from a diff?
Compare the model with the connected database, then open Schema → Synchronize Model with Database. You can edit the generated statements in the Sync Dialog before they run.
How do you run a migration script safely?
Run the additive half first, and hold every DROP and narrowing ALTER until the old code is gone. In PostgreSQL 18, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW without CONCURRENTLY each take an ACCESS EXCLUSIVE lock[1], so they need a window of their own.
Sources
Generate the migration script from your schema diff
DbSchema compares your design model against the live database, reports the differences object by object, and generates the DDL for your engine. Schema synchronization is a Pro feature; the Community Edition connects to your database and reverse-engineers the schema.
