What a Database Migration Tool Does
For the backend developer or DBA deciding how schema changes will be tracked, generated and deployed across environments.
On this page
A release carries three schema changes. Staging already has two of them, because somebody applied them by hand last Tuesday, and nobody is certain which two. A migration tool exists to remove that sentence from your life: it records what has been applied to which database, and it produces the DDL for the gap between where a database is and where the release needs it to be. Everything else it does follows from those two jobs.
How do I migrate one database to another database?
By moving the structure first, as a versioned artifact, and the rows afterwards. The dump-and-restore habit does not survive contact with a running system: mysqldump and pg_dump produce a whole-database snapshot, and a snapshot is not how you apply the third of the twelve changes a release contains to a database that is already serving traffic.
What replaces it is a schema that lives in source control, with each change recorded separately, so the deployment reads that record instead of asking a person. Whether the record is a directory of numbered SQL files or a single declarative model file is the choice the rest of this article is about, and it is compared in more depth in schema migration tools compared.
The sequence is the same either way:
- Extract the baseline structure from the source database by reverse-engineering its catalog.
- Commit that baseline, tables, indexes and constraints included, to the repository.
- Produce a migration for each subsequent change.
- Run each migration against a test database first, and check that the application's queries still work against the result.
- Apply the tested migrations to production in the deployment window.
Schema migration or data transfer?
Schema migration and data transfer are two different jobs, and confusing them is why a data load fails at three in the morning. A schema migration changes the structure: tables, columns, data types, keys, indexes. A data transfer moves rows between databases whose structures already match. The structure has to arrive first, because a row inserted into a table whose foreign key target does not exist yet is rejected, and a value copied into a column of the wrong type either fails to convert or converts into something you did not intend.
The order also decides where a failure is cheap. A structural mistake caught before the load costs one ALTER TABLE; the same mistake caught after it costs a truncate and a second load of every row.
| Phase | Language | What it changes | What goes wrong |
|---|---|---|---|
| Schema migration | DDL | Tables, columns, indexes, constraints | Locks, breaking changes |
| Data transfer | DML | Rows | Constraint violations, type conversion |
Run the structural synchronization to completion, confirm the target's tables match, and only then start the bulk insert.
ORM, CLI or visual sync?
Three kinds of tool do this work, and they differ in where the change is authored.
Visual synchronization tools keep the schema as a design file you edit in a diagram, and generate the deployment SQL by comparing that file against a live database. DbSchema works this way: the project lives in a plain .dbs XML file you can commit, diff and review, and the migration script is derived from an actual difference rather than written in advance. The reviewer sees the design change and the generated statements as two separate artifacts, which is what makes a schema review possible for someone who is not the author.
Command line migration engines, Flyway and Liquibase among them, keep the schema as a directory of change files and apply them in order, recording what has run in a table inside the target database. They are framework-agnostic, so the same engine serves services written in different languages.
Object-relational mappers, such as Active Record, Django's ORM and Prisma, generate a migration from a changed entity class. The schema follows the application's model definitions, which suits a codebase where one language owns the whole database.
Changelogs or versioned SQL files?
The two open-source engines differ in what a change looks like on disk. Flyway has you write the SQL yourself, one file per change, in the dialect of the target engine, with every engine-specific clause available to you. A Flyway file carries its version in the file name, followed by a description, as in V001.002__NewTwitterColumn.sql. The versions decide the order the files run in, and each versioned migration is applied to a target database exactly once. The flyway_schema_history table records which ones have run and stores a checksum of each, which is what detects a file edited after it was applied[1].
Liquibase has you describe the change as a changeset instead, in XML, YAML, JSON or plain SQL, and translates that description into the DDL the connected engine expects. The same changelog can therefore deploy to more than one engine, at the cost of learning the changeset vocabulary and of writing anything unusual as raw SQL anyway.
DbSchema sits at a different point: the authored artifact is the design, and the SQL is produced at deployment time from the difference between that design and the database in front of it. The generated script can be saved into a Flyway or Liquibase project directory, so the two approaches combine rather than compete.
| What differs | DbSchema | Flyway | Liquibase |
|---|---|---|---|
| You author | The design model | A versioned SQL file | A changeset |
| Format | XML .dbs file | Plain SQL | XML, YAML, JSON or SQL |
| Deployment script | Generated from the difference | The file itself | Generated per engine |
| Record of what ran | The live catalog, read on demand | flyway_schema_history | DATABASECHANGELOG |
How Liquibase tracks what it has applied
Liquibase keeps its record in a table it creates in the target database, DATABASECHANGELOG, with one row per changeset it has run. The row identifies the changeset by its id, its author and the path of the changelog file. It also stores an MD5 checksum of the changeset as it stood when it executed[2].
On each later run Liquibase computes the checksum again and compares it against the stored one, which is how a changeset edited since its deployment is recognized[3]. Whether that edit stops the deployment or triggers another one is decided by the changeset's runOnChange attribute. Left unset or set to false, an edited changeset raises a checksum error instead of running, because the database is now in a state the changelog no longer describes. Set to true, the changeset runs again every time it is modified, which is what a view or a stored procedure written as CREATE OR REPLACE needs when it lives in one file across its revisions[4].
Correcting a typo in a changeset that has already reached an environment is therefore a deployment decision rather than an edit. Fixing the file in place makes the checksum disagree with the row, and the corrected SQL still has to reach the database as a new changeset.
How do migration tools generate dynamic SQL?
By comparing two schema states object by object and emitting the statements that turn one into the other. DbSchema does this between the design model and a live catalog: it reads the catalog over JDBC, matches tables, columns, data types, indexes and constraints against the model, and generates CREATE TABLE, ALTER TABLE ADD COLUMN or DROP INDEX for each difference, in the dialect of the connected engine.
Ordering is the part that is easy to underestimate. A table has to exist before the index on it, a referenced key before the foreign key that points at it, and a column before the constraint that checks it. A generated script gets that ordering from the dependency graph it just walked; a hand-written one gets it from whoever remembered.
What happens when a generated script fails halfway through is a question about the engine rather than about whatever produced the script, because only some engines undo a committed DDL statement when the transaction rolls back. Rolling back a database schema change goes through that engine by engine, and it is worth reading before you rely on a script being all-or-nothing.
Running migrations in a CI/CD pipeline
A pipeline runner, GitHub Actions or GitLab CI or Jenkins, checks out the schema files with the application code, injects the connection credentials from its secret store, and runs the migration against a disposable database before it goes near staging. That first run is the one that earns its place: it catches a statement that will not parse and a constraint the existing rows violate, at a point where the cost is a failed build.
DbSchema covers the half of that workflow the runner cannot: designing the change and seeing what it will do. You edit the schema in an offline design model with no database attached, commit the .dbs file, and connect on demand to compare the model against development, staging or production, reading the generated migration SQL beside the diagram it came from. Editing the model and comparing change nothing in the database; the database changes only when you execute the generated script.
DbSchema comes in three editions. The free Community Edition connects to a database, reverse-engineers it, draws the interactive diagram, and runs SQL in the editor. The Pro Edition adds saving the design to a .dbs file, schema synchronization, HTML5 documentation, the visual query builder and relational data browse. The Architect Edition adds logical and conceptual design, for modelling before an engine is chosen.
The fastest way to judge any of this is to watch one change turn into statements. Take DbSchema from https://dbschema.com/download.html, point it at a development database, change a single table in the diagram, and compare the model against that database: the script that comes back is exactly what a migration for that change would contain. Schema synchronization and saving the model to a file are Pro Edition features, and the installation kit carries a 15-day Pro trial.
Frequently asked questions
What does a database migration tool do?
A migration tool records which structural changes have been applied to which database, and produces the DDL for the gap between the two. The statements are yours to read before anything runs: in DbSchema they arrive in the Sync Dialog, where you can edit them directly before executing.
Is Liquibase a database migration tool?
Liquibase is a command line migration engine: you describe each structural change as a changeset, and Liquibase applies those changesets to a target database and records what it has run there. The section on changelogs and versioned SQL files sets that against a Flyway SQL file and against generating the script in DbSchema from a design model.
How do I migrate one database to another database safely?
Move the structure first and the rows second, and generate the structural script from a comparison rather than writing it by hand. DbSchema compares your design model against the target database and produces the statements for exactly that gap, which is also the list you review before anything runs.
Which is better for migrations, Flyway or Liquibase?
Flyway and Liquibase differ in what you write, not in quality: Flyway takes plain SQL in the dialect of one engine, while Liquibase takes changesets it translates per engine. Write SQL if you target one engine and want its specific clauses, and changesets if the same change has to deploy to several. In either case the SQL can be generated in DbSchema from the difference between your design model and the target database, then committed into the project rather than typed.
How do you automate database migrations in a CI/CD pipeline?
A runner such as GitHub Actions, GitLab CI or Jenkins checks out the schema files with the application code, injects the credentials from its secret store, and applies the migration to a disposable database before staging. The generated script from DbSchema can be committed into that pipeline like any other migration file.
