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.