Tuesday, March 06, 2007

Rails migration pitfalls

When we create a migration, we are essentially creating a "delta" SQL script, whose intent is to change the state of the database at a given time, including its schema and its data, from point A to point B. Rails migration scripts even allow you to rollback from B to A. A lot of developers got the delta of the schema part right, because it is the most intriguing part, but they fail to recognize the data part in their migrations.

Take a look at the following migration:

class MoveColumnFooFromTableAToTableB < ActiveRecord::Migration
  def self.up
    remove_column :table_a, :foo
    add_column :table_b, :foo, :string
  end

  def self.down
    add_column :table_a, :foo, :string
    remove_column :table_b, :foo
  end
end


This migration drops column "foo" from Table A, and adds column "foo" to Table B. From the schema point of view, the migration achieves what it's supposed to do - moving a column. But this delta script is flawed from the data point of view.

Imaging your application is at 1.0, users start inserting 20MM rows into your Table A. This migration script is part of your release 1.5 upgrade. You run this script against your table A. Poof! The column is moved, but you just lost *all* of your data in the column "foo" forever. Table B now has a "foo" column with no data in it. Things went wrong, so you rollback, right? Try it. Still, your Table A now contains a "foo" column with all NULL values in it. The data are gone.

Worse yet, your boss is standing behind you, giving you 15 minutes to fix the whole mess. "Database migration sucks...", you mumbled.

This migration fails to migrate the data from point A to point B. So, what should developers have done differently? Well, here's one way to do it:

  def self.up
    add_column :table_b, :foo, :string
    execute("UPDATE table_b INNER JOIN table_a ON table_b.id = table_a.id SET table_b.foo = table_a.foo")
    remove_column :table_a, :foo
    
  end

  def self.down
    add_column :table_a, :foo, :string
    execute("UPDATE table_a INNER JOIN table_b ON table_a.id = table_b.id SET table_a.foo = table_b.foo")
    remove_column :table_b, :foo
  end


Now with this delta script, which accounts for data migration as well, will do what it is intended to migrate: schema and data.

Here is another gotcha situation:

  def self.up
    add_column :table_a, :foo, :null => true
  end


When you have a table that contains rows in it, like our 20MM row table, after this migration, the 20MM rows will contain NULL in column "foo". Your database will complain null column constraint violated after this migration.

So be careful when you are performing these migrations. My advice is, do a sanity check on all your migrations by running them against a database with tables populated with data. It actually may not be a bad idea to run a CI build on any migration check-ins, to tick off all migrations, against a database full of data, if your migrations are meant to migrate data.

3 comments:

Patrick Bacon said...

All excellent points. You might be interested in a plugin recently released by Atomic Object for testing migrations: migration_test_helper

rams said...

Thanks for the tip. The example you used where a column is moved to another table can be handled graciously like you suggest, but how do you handle cases where a table with data is dropped and then when you roll back the table needs to magically appear with the data? Do we "save" the data another place or do we never ever drop tables? A forward-only migrations setup will work but I still am not clear if a truly forward and backward migrations setup can be had without "saving" data someplace. Ideas?

Stephen Chu said...

Generally speaking, dropping a column upon migration means you want to rid of the data in that column. It is less common to drop column and want to bring back the data when rollback, but it does happen, especially when a release fails.

In such cases, you must backup the data before migrating your schema and data changes. There are various ways to do online/offline backup of your data. It makes sense to backup your production database/tables prior to applying database migration.