Database Migration Checklist

I think a great migration database migration is: correct, atomic, reversible, consistent with the style of the application, and (mostly) uncoupled from your application and RDBMS.

These ideas are framework-agnostic, but as an example, we’ll use this migration from the Ruby on Rails documentation:

class CreateProducts < ActiveRecord::Migration
  def change
    create_table :products do |t|
      t.string :name
      t.text :description

      t.timestamps
    end
  end
end

Correct

After running each migration, it’s important to inspect the database schema to ensure that the correct changes are present. I’ve seen many pull requests, and opened a few myself, where the migration and the schema tell competing stories.

Consistently committing by patch helps catch such discrepancies.

Atomic

Atomic changes change just one thing and can’t be logically reduced.

Our example migration is atomic because it does just one thing: create a table.

We strive for atomicity in commits, pull requests, and migrations. Atomic changes are easier to understand. They are easier to roll back, and rolling back doesn’t abandon other desirable changes.

Reversible

Reversibility lets us roll back.

Our migration uses the change method, which is reversible because Active Record only allows code inside the method that it knows how to reverse, such as adding and removing columns and indices.

We strive for reversibility because mistakes happen. In more complex migrations, we might use separate up and down methods or raw SQL, and it’s easy to sneak in a typo or forget to restore an index dropped while dropping a table. Migrations are generally untested and often run first in environments with trivial data compared to production– stuff breaks.

At Hashrocket we proved the reversibility of our Active Record migrations with this command-line function, run on each commit:

function twiki () {
  rake db:migrate && rake db:migrate:redo && rake db:test:prepare
}

This runs the migration, rolls it back, and runs it again, stopping if any step fails.

Stylistically Consistent

Be consistent with the code style of your data directory.

If the migrations in the codebase use the ORM DSL, use that. If they are written in SQL with HEREDOCs, use that. I try to honor the conventions of the codebase unless there’s a strong reason not to. For instance, my codebase uses ORM migrations but I need to use an advanced RDBMS feature not supported by my ORM.

Uncoupled

Use a model name in a migration, and you’re coupling your migration to that model. Use a feature from your RDMBS, and you’re coupling to that. Coupling is defensible when it’s intentional and you’re tolerant of the tradeoff. Generally, it’s bad.

I try to delegate schema changes to the migrations and data changes to scripts.

Conclusion

Like many best practices, it’s more work to follow these rules! But they keep the data directory easier to work with for everyone.

Update: Adam Young made a great point in response to this post. Reversibility isn’t always possible or likely to succeed, and it costs development time to enforce it. I like reversibility as a guideline. But for certain migrations or at certain scale, it might not be possible or prudent.

• Databases