Database Migration Software: Versioned Schemas, Not Ad-Hoc SQL

A migration tool records which schema changes have been applied to which database, so deploying code and deploying the schema it needs stop being two unrelated activities.

Every team without one has the same setup: a folder of SQL files, a wiki page describing the order to run them in, and one person who knows which ones production has actually had. It works until someone is on holiday.

Migration tools replace that with a version table in the database itself, so the database knows what has been applied to it. That single idea is most of the value.

Table of contents

• Versioned or declarative
• What any of them gives you
• The rules for changing a live schema
• Rollback is mostly a myth
• Where migrations run in a deploy
• The database underneath
• How this fits the rest of the stack
• FAQ

Versioned or declarative

The category splits in two, and it is the first choice to make.

Versioned tools apply an ordered sequence of change scripts. Each has a version, each runs once, and the tool records which have run in a table. Flyway is the archetype; Liquibase, Alembic for Python, and the migration systems in Rails, Django and Laravel all work this way.

-- V2__add_user_email_index.sql
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

The current schema is whatever you get by replaying every migration from empty, which means there is no single file describing what the schema is. That is the main criticism, and it is a fair one on a project with three hundred migrations.

Declarative tools take a description of the desired schema and compute the changes needed to reach it, like Terraform for databases. Atlas is the well-known example, and some ORMs generate migrations by diffing models against the database.

The desired state is one readable file, which is genuinely nicer. The cost is that the tool decides how to get there, and for destructive or data-moving changes you want to make that decision yourself.

In practice, versioned tools remain the safer default for production databases, because every change is explicit, reviewable and reversible on your terms. Declarative tools are excellent for development and for keeping environments in sync.

What any of them gives you

Whichever you pick, the mechanics are similar and the benefits are the same:

• A version table in the database recording what has been applied, so the database is self-describing.
• Checksums on applied migrations, so editing one after it has run is detected rather than silently ignored.
• Ordering, so migrations run in a defined sequence rather than alphabetically by whoever named the file.
• Transactional application where the database supports DDL in transactions — Postgres does, MySQL largely does not.
• A repeatable path from empty, which is how a new developer or a test suite gets a correct schema.

That last one is underrated. Being able to build the current schema from nothing in one command is what makes ephemeral test databases practical, and it is where most of the day-to-day time saving comes from.

The rules for changing a live schema

This is the part that matters more than tool choice. During a deploy, old and new application code run simultaneously — briefly during a rolling deploy, longer if you deploy gradually. The schema must work for both.

The expand-and-contract pattern, across three deploys:

1. Expand. Add the new structure, nullable or with a default. Old code ignores it; new code can use it.
2. Migrate and dual-write. New code writes both old and new; backfill existing rows in batches.
3. Contract. Once nothing reads the old structure, drop it.

Renaming a column illustrates it. ALTER TABLE users RENAME COLUMN email TO email_address breaks every running instance of the old code instantly. Instead: add email_address, write to both, backfill, switch reads, stop writing email, drop email. Three deploys instead of one, and no downtime.

Rules that follow from this:

• Never drop or rename in the same deploy that stops using it.
• Add columns nullable or with a default. A NOT NULL column with no default fails every insert from old code.
• Build indexes concurrently. CREATE INDEX locks the table for writes; CREATE INDEX CONCURRENTLY does not, and on a large table that is the difference between a deploy and an outage.
• Backfill in batches, outside the migration. A single UPDATE over ten million rows holds locks and bloats the transaction log.

Rollback is mostly a myth

Most tools support down migrations, and they are far less useful than they look.

The reason is that schema changes destroy information. A down migration for DROP COLUMN can recreate the column; it cannot recreate the data. Reversing a type change loses precision. Reversing a merge cannot un-merge.

So the realistic strategy is forward-only:

• Write migrations that are safe to apply and do not need reversing — which expand-and-contract gives you naturally.
• Fix problems with a new migration rather than by reversing.
• Keep backups and know your restore time, because that is your actual recovery path for a genuinely destructive mistake.
• Test migrations against a copy of production data, where the surprises are — an index build that takes four hours on real volume takes one second on an empty dev database.

That last point is the one that catches teams. Migrations are almost always tested on small data and run on large data.

Where migrations run in a deploy

Three options, with a clear ranking:

A separate step before the deploy is the correct default. Migrations run once, complete before new code starts, and a failure stops the deploy rather than leaving a half-updated application.

On application startup is convenient and dangerous. Multiple instances starting together race to apply the same migration, and most tools handle this with a lock — but you have now made every instance’s startup depend on acquiring that lock, and a slow migration blocks the whole rollout.

Manually is honest for genuinely risky changes and should not be your routine. If every deploy needs someone at a terminal, migrations will get skipped.

Whichever you choose, the migration user should be a separate database account with DDL rights, while the application runs as a user that has only DML. An application that cannot drop a table cannot drop a table by accident.

The database underneath

All of this assumes a database you can back up, restore and connect to reliably — because the real recovery plan for a bad migration is a restore, not a down script.

Two things worth confirming before you need them: that backups exist and that you have actually restored from one. An untested backup is a hypothesis.

On RunxBuild, managed Postgres and MySQL come with backups, user management, connection limits and private networking, so the migration runs against a database whose recovery path is a platform feature rather than a script somebody wrote once. The service running the migration deploys from the same repository with the build log alongside it.

How this fits the rest of the stack

A migration tool’s real contribution is a version table that makes the database self-describing — pick a versioned tool for production, and spend your attention on expand-and-contract rather than on the tool comparison. Treat rollback as a restore rather than a down script, build indexes concurrently, and test against production-sized data. The RunxBuild hosting calculator shows a managed database with backups next to the service that migrates it.

Useful related references:

• SQL Managed Instance: The PaaS Database, the Cost, the Migration Story, and When to Use One
• Joomla to WordPress Migration: What Transfers, What Does Not, and the Redirects
• Cloud Migration Solutions: The Six Strategies and When to Use Each
• Databases on RunxBuild

FAQ

What is database migration software?

A tool that applies versioned schema changes and records which have been applied in a table in the database itself. That makes the database self-describing, so any environment can be brought to the current schema reliably and repeatably.

What is the difference between versioned and declarative migration tools?

Versioned tools apply an ordered sequence of change scripts, each running once. Declarative tools take a description of the desired schema and compute the changes needed. Versioned is safer for production because every change is explicit and reviewable.

How do I change a schema without downtime?

Use expand-and-contract across three deploys: add the new structure without removing the old, migrate data and write to both, then drop the old once nothing reads it. Never drop or rename in the same deploy that stops using something.

Should I write down migrations?

They are less useful than they appear, because schema changes destroy information a down migration cannot recreate. Prefer forward-only migrations, fix problems with a new migration, and treat a tested backup restore as your real recovery path.

When should migrations run during a deploy?

As a separate step before the new code starts, so a failure stops the deploy cleanly. Running them on application startup makes every instance’s boot depend on a migration lock, and a slow migration then blocks the entire rollout.