Evolving a database schema with DB migrations


Managing database schemas can be a pain:

Many projects still rely on manually applied sql scripts. And sometimes not even that (a quick sql statement here or there to fix a problem). And soon, many questions arise:

  • What state is the database in on this machine?
  • Has this script already been applied or not?
  • Has the quick fix in production been applied in test afterwards?
  • How do you set up a new database instance?

More often than not, the answer to these questions is: We don't know.

Flyway (Why database migrations?)

You can resolve such questions with a database migration tool. The tool expects a list of schema updates, called migrations. When you run the tool, it applies the migrations1 to the specified target database.

Typically, you store migrations in a version control system and run them in the deployment pipeline. Such setup aligns with best practices for continuous delivery and dev-ops2.

There are tens of migration tools to choose from3. In API-based tools, you write migrations against a language-specific API. In SQL-based tools you write migrations in plain SQL. Some tools offer advanced features, such as repeatable transactions and placeholders. When choosing, consider database-specific edge-cases and tool's maturity.

Personally, I've seen Flyway in battles: Scala, Python, and NodeJS projects with over 200 migrations. I've found it to be battle-tested4.

Other alternatives, such as dbMate, are explored in this HackerNews thread.


  1. Applying migrations is an idempotent operation, that is, you can run them repeatedly regardless of the current version of the schema.
  2. The dev/prod parity factor, the build-release-run factor , and the one-off admin process factor.
  3. For example, for Python there's alembic; for NodeJS there's node-sqlite, knexjs, pg-migrate, db-migrate, and umzug; for Ruby there's active record, for Java there's flyway. The SQL-based family includes flyway, liquibase, and sqitch.
  4. Flyway has a rich feature set to cover edge cases. For complex applications, follow recommended practices for designing migrations.

-

If you need help with building the tech products get in touch.