Visual Schema Diff or a Code-Only Migration Library

A few hundred tables, four environments, and a change that has to reach all four in the same shape: that is where the choice between a code-only library and a visual diff stops being a matter of taste. DbSchema is the one to run for that schema. It holds the design in a .dbs XML file you commit, reads the live database over JDBC, and generates the migration script from the difference between the two, with the statements on screen before any of them runs.

What is a schema migration script?

A migration script alters database objects in place instead of rebuilding them, so the rows stay where they are while the structure moves. That is what separates a database from an application container: the container is replaced on deploy, and the database is edited while it is in use. The order of the statements matters for that reason. Reading every statement before it runs matters just as much.

The DDL is the small part. What surrounds it is version control, a fixed position in a sequence, and a record that it ran, so the same statements reach development, staging and production in the same order. All three approaches below give you that much, and they differ in who writes the DDL and in what happens when a database stops matching the files.

Where the script comes from decides how much of the reading you have to do yourself.

A mismatched data type or a foreign key left out of a hand-written script surfaces when the pipeline stops, which is late. Generating the script from a comparison removes the transcription step where those mistakes happen.

What is a visual schema diff tool?

DbSchema compares the live state of a database against a design model and shows the result as a list of objects rather than a log. It reads the catalog metadata directly, so the comparison covers tables, columns, data types, indexes, primary keys and foreign keys as they exist right now, not as a changelog says they should be.

The Synchronization Dialog is where the two states meet. Each difference is one row: a table missing from the database, a column whose type moved, an index that exists on staging and nowhere else. You set the direction per row, so one comparison can push three changes to the database and pull a fourth into the model.

Drift is what this catches. A hotfix applied by hand, a column added during an incident, an index created to rescue a slow query: none of them went through the pipeline, so no changelog knows about them, and a comparison against the live catalog is what reports them. Tools that pair a changelog runner with a visual schema diff are covered separately.

How code-only migration libraries work

Liquibase and Flyway keep the history in files and a tracking table. The Liquibase changelog sequentially lists all changes made to the database, in SQL, XML, YAML or JSON, and changesets run in the order they appear in that file[1]. Flyway orders by version instead: each versioned migration carries a version, a description and a checksum, and is applied in order and exactly once per target database[2].

Editing a file after it has been deployed is what both guard against. Liquibase stores an MD5SUM per row in DATABASECHANGELOG and uses it to detect a changeset changed after the fact[3], and Flyway compares checksums the same way.

Undoing a deployed change is the point where the two separate: Liquibase has rollback commands, while a Flyway undo migration is a U script you write yourself, carrying the version of the migration it reverses, and its documentation marks it as a Teams edition feature[4]. How much of the Liquibase rollback you write yourself depends on the changelog format. In the modeled changelogs, XML, YAML and JSON, Liquibase generates rollback SQL automatically for many change types, and a change type without automatic support needs rollback logic written by hand[10]. In formatted SQL changelogs, every changeset needs that logic written by hand[10].

A third shape exists on the command line. pg-schema-diff computes the differences between Postgres database schemas and generates the SQL required to get a database from point A to B, works as both a Go library and a CLI, and supports PostgreSQL 14 through 17[5]. The comparison is declarative, and it happens without an entity-relationship model to read it against.

Across a few hundred tables, a pull request full of sequential change files says what changed but not what the schema now looks like. A wider survey of the field is in schema migration tools compared.

Where the design model lives in Git

DbSchema saves the whole design into a single .dbs file: the schema, plus every diagram, layout, group, color, virtual foreign key and comment. The format is XML, human-readable, and openable in any text editor, and because it is plain text it works with Git the same way source code does[6].

The file holds structure and no rows, so it belongs in the application repository rather than in a separate store, and a reviewer sees the added column as added lines in a diff they already know how to read. Designing against it needs no connection at all: in offline mode DbSchema saves changes to the .dbs file and sends no statements to the database[6], so a refactoring can be worked through before anything is deployed.

Sharing it is a Git operation from inside DbSchema. Model → Git — Collaborative Design opens the Git dialog, where you stage the model file, commit it with a message, and push it to the remote your team pulls from.

Generating the migration script between two versions

The generation step needs two states and a decision per difference. One state is the approved model, the other is whatever the target database contains at that moment.

Where a database names its schemas differently from the model, Schema Mapping shows a model schema under the name that database uses, so one model serves staging and production without a second copy[7]. With the mapping in place, the comparison runs in four steps.

1. Open the .dbs model that holds the approved design.
2. Connect over JDBC to the target database, whichever environment it is.
3. Read the list of differences, object by object.
4. Choose per object which way the difference travels, then generate the SQL.

Both directions are supported, and they touch different things. Pushing generates a SQL migration script for the target database and changes nothing until you execute it. Pulling merges the live changes back into the local design model, which updates the .dbs file and leaves the database alone. Either way the statements appear before they run, and DbSchema writes them in the dialect of the connected engine.

A worked before-and-after schema diff

Take a change with two parts, a column and the constraint that gives it meaning.

ALTER TABLE orders ADD COLUMN payment_method_id INT NOT NULL;
ALTER TABLE orders ADD CONSTRAINT fk_orders_payment
  FOREIGN KEY (payment_method_id) REFERENCES payments(id) ON DELETE RESTRICT;

You open the model offline and add payment_method_id to the orders table on the diagram, then drag from the column's handle to the referenced column in payments, which is one of the two documented ways to create a foreign key in DbSchema. Saving at this point writes to the .dbs file, and staging still knows nothing about either change.

Then you connect to staging and run the comparison. DbSchema reports the column and the constraint as present in the model and absent from the database, and generates the statements that add them. You read them, edit them if you want to, and click Execute, which is the moment staging matches the model[8]. The statements can also be exported as a file and handed to a migration script pipeline instead of being run from the dialog.

Which tool is best for data migration?

For an architect who owns a schema across several environments, DbSchema is the one to run, because it gives you the whole schema and the change at once: the diagram to reason about, the XML file to review in a pull request, the live comparison to catch what drifted, and the generated script to read before it deploys. That chain moves a change from model to production without hand-written DDL, across the SQL and NoSQL engines DbSchema supports.

Code-only libraries own one job: a headless container start-up, where a service applies its own migrations with no interface involved. Liquibase and Flyway run from build tools and CI runners in exactly that position, and a team whose policy requires every change to be a developer-authored SQL file writes those files by hand. The trade-off argued side by side is in Liquibase, Flyway or a visual schema compare.

Nothing stops the two from meeting. The script DbSchema generates is ordinary SQL, so it can be committed as the next numbered migration and executed by the same runner as everything else, with the diff doing the writing.

Pick the environment that has drifted furthest, open your model against it, and read the difference in the Synchronization Dialog before you write a line of DDL. Download DbSchema at https://dbschema.com/download.html. Reverse-engineering, the interactive diagrams and the SQL editor are in the free Community Edition; saving the design to a .dbs file and schema synchronization are Pro, and the download runs as a 15-day Pro trial that can be extended by another 15 days.

Frequently asked questions

What is a schema diff tool?

DbSchema compares an offline design model against a live database, or one database against another, and lists the objects that differ. The section above shows what that list looks like and how a difference becomes a migration script.

Is Liquibase a database migration tool?

Liquibase applies schema changes from versioned changelogs, and states that it works with 60+ databases, including relational, NoSQL and graph databases[9]. The changelog is a text file, so reviewing a change means reading DDL rather than looking at a diagram.

How do you generate a migration script from a schema diff?

In DbSchema the script comes out of the comparison between the .dbs model and whatever the target database holds at that moment. The four steps that produce it, and the Schema Mapping that lets one model serve staging and production, are in the section on generating the script above.

What is the difference between state-based and migration-based deployments?

A state-based deployment compares the target state against the current database and derives the change from that comparison. A migration-based deployment replays a sequence of incremental scripts, so the target state is whatever the sequence produces.

Can I version a database schema in Git?

The .dbs file is plain XML, so Git versions the design the way it versions source code. After you pull a teammate's commit, Compare with Current in DbSchema opens the Synchronization Dialog on exactly what changed.

Are there libraries for schema diffing?

pg-schema-diff annotates each statement of its plan with the hazards it carries, such as a concurrent index build. Approving those hazards on the command line is what lets apply run the plan[5]. The plan arrives as terminal output, not as the object list a DbSchema comparison puts on screen.

Sources

1. docs.liquibase.com
2. documentation.red-gate.com
3. docs.liquibase.com
4. documentation.red-gate.com
5. github.com
6. dbschema.com
7. dbschema.com
8. dbschema.com
9. docs.liquibase.com
10. docs.liquibase.com