Complex migrations often take a few tries to get right. Here is an easy way to make them repeatable during development.

One of my favorite trick in Rails development is snapshotting my development database.

Say I need to make major changes to the database structure. This would mean adding and removing columns and maybe even tables, but more importantly some data conversion is often needed. This can be pretty tricky to get right and even more tricky to make repeatable.

To test a complex migration locally, I first copy the production database into development.

script/pull_prod_db # I'll discuss this in a separate post.

Then I snapshot the development DB.

script/snapshot_dev_db

Now I’m ready to try out my fancy migration. I usually don’t get it right the first time though, so chances are that this failed, leaving the DB in some half-baked state. To retry, I restore my dev DB from the snapshot I just took.

script/restore_dev_db

My database is now back in pre-migration state and I can fix the migration and try again.

These scripts are very simple and only take a second or two to run. Here they are:

script/snapshot_dev_db

pg_dump -U gomore -c gomore_development > tmp/snapshot.db

script/restore_dev_db

cat tmp/snapshot.db | psql -U gomore gomore_development

One caveat is that existing tables are not dropped when restoring the DB. This means that migrations which create new tables may not be repeatable. To solve this, I usually drop the table in the migration before creating it.

class CreateUsers < ActiveRecord::Migration
  def up
    drop_table :users if table_exists?(:users)
    create_table :users do |t|
      ...
    end
  end
end

Finally, I’m sure some people would argue that complex DB conversions do not belong in migrations at all. Even though this use case requires a lot more code than simple DB migrations, I think it makes perfect sense to put it in ordinary Rails migrations. Because, complexity differences aside, this is exactly what migrations are for: Taking the DB from one state to another using a chunk of code that then becomes irrelevant. Also, they’re easy to run automatically on deploy.