Agnostic Database Migrations

Problem

Version control of source code and build products are crucial tools in the software developer’s toolbox. When you need to add some new features to a project, there’s usually a workflow: checkout a specific branch, make some changes, test those changes, and then commit back to the code repository.

Although the details vary from project to project, tools like Git and Mercurial generalize pretty well across all sorts of development projects, including web development, mobile, desktop, kernel, etc. On the deployment side, we have tools like Fabric, Puppet, Docker, etc. that create order and predictability.

What do you do when you need to upgrade a relational database schema? For example, you can git checkout 2.1.0 to get the latest source code, but there’s no equivalent command to upgrade the database schema to a specific version.

In the web development world, databases are often version controlled with a system of migrations. (More often, they aren’t version controlled at all!) A migration is a script that modifies the schema in some specific way, such as adding a column or creating a new index. I use the word schema here to mean the shape of your data, i.e. the tables, columns, and data types that define your database.

Although simple in concept, migrations are frighteningly complex in practice because the tooling to work with migrations is highly fragmented. I have worked at 4 companies as a software developer, and at each company we had completely different tools for handling migrations. (At one company, the migrations were a completely manual process: the DBAs wrote extremely long migration scripts for each new version of the application.) When we ran up against unacceptable limitations in these tools, we ended up designing and writing our own migration tools from scratch.

The major problem with existing migration systems is that they are almost always closely tied to some framework. For example Django Migrations, Doctrine Migrations, Alembic for SQL Alchemy, and Active Record Migrations for Ruby On Rails are all tied to specific web development frameworks or object relational mappers (ORMs). So if you work on a RoR project this month and then work on a Django project next month, you’ll need to learn a completely new migrations system! Can you imagine if Git only worked with one web development framework, and when you picked a different framework for your next project, you also had to pick a new version control system too? Madness!

Solution

This is why I built Agnostic Database Migrations. I started with two design goals:

1. Generalize well across databases, programming languages, frameworks, and development workflows.
2. Prefer convention over configuration so you can focus on your work – not on the tool.

In support of these goals, Agnostic has a carefully defined feature set:

• All migrations are written in pure SQL.
• No configuration files required.
• Built-in migration testing.
• “Up” migrations only; no “down” migrations.
• Handles your existing branch and merge workflows with aplomb.

All this in less than a thousand lines of code makes for a small, understandable tool that you can depend on. Agnostic has a permissive MIT license that makes it easy to use in corporate environments. It also has high-code coverage, automated regression tests that run on Travis CI against each database backend.

Example

Here’s a quick example of how you might add Agnostic to an existing project. After you’ve installed Agnostic, you need to create a directory to hold your migration scripts and you need to bootstrap the system, which means creating a table to keep track of migration metadata.

~/myapp $ mkdir migrations
~/myapp $ agnostic -t postgres -u myuser -d mydb bootstrap
Migration table created.

Notice how you don’t need to write any configuration files or install any middlewares or any crazy things like that. (By the way, I have omitted the password in these examples because setting up the database password is a subtle issue that I don’t want to delve into here.)

Next, we can add some migrations to our migrations directory.

~/myapp $ cat > migrations/add_cell_phone.sql
ALTER TABLE customer ADD cell_phone VARCHAR(255);
^D
~/myapp $ cat > migrations/add_nickname.sql
ALTER TABLE customer ADD nickname VARCHAR(255);
^D

Notice how the migrations are just pure SQL scripts. Most migration tools “helpfully” allow you to write migrations using a migrations API, but that just creates lock-in: if you use a different ORM on your next project, you’ll need to learn a whole new API.

Finally, executing your migration scripts:

~/myapp $ agnostic -t postgres -u myuser -d mydb migrate
Backing up "mydb" to "/tmp/tmpm8glpgaa".
About to run 2 migrations in "mydb":
 * Running migration add_cell_phone (1/2)
 * Running migration add_nickname (2/2)
Migrations completed successfully.
Removing backup "/tmp/tmpm8glpgaa".

Agnostic determines which migration scripts are present in the file system but haven’t been executed against the database, then it runs those scripts in a predictable order. A backup is made before running migrations so that if any migration scripts fail, the database can be rolled back to its pre-migration state.

Conclusion

Agnostic is currently in a beta release and supports MySQL and PostgreSQL. I plan to add support for SQLite and then release version 1.0. If you want to learn more:

• Read The Docs
• Source Code

Thanks for reading!