I must confess I was initially weary of migrations. But I tend to refactor mercilessy when I code and more often than not this refactoring involves changing the database.

When I started coding 17books.com I had only one copy of the database — on my laptop. But the moment I uploaded a copy of the site (then still in development) for my colleagues to test I knew I was heading for trouble.

After giving migrations a go I became a convert overnight.

I thought I’d share the non-obvious migrations here in the hope that they will be useful to other merciless refactorers. I’ll assume that you’re familiar with the basics of migrations, in particular that you can generate migrations and can do basic operations such as adding tables and columns.

Down to business. Say you’re creating a movie database (taking on IMDB are we?) and for a start you would like to record films and their directors. Here’s how you’ve defined your tables:

  create_table "directors", :force => true do |t|
    t.column "name", :string
  end

  create_table "films", :force => true do |t|
    t.column "name", :string
    t.column "director_id", :integer
  end

Eagerly you start adding your favourite directors:

id	name
1	Steven Spielberg
2	George Lucas

And then their early films from the late 70s onwards:

id	name	director_id
1	Jaws	1
2	Star Wars Episode IV: A New Hope	2

But then you get to 1981 and want to add Raiders of the Lost Ark … and run into a problem. It turns out Steven Spielberg and George Lucas teamed up to direct this film together, but our films table allows only one director per film!

What we need to do is turn the one-to-many association between directors and films into a many-to-many association. In plain text, we need to allow more than one director per film.

How can we do this with migrations without losing our data?

First we need to create the table that will link films to directors:

  create_table "directors_films", :id => false do |t|
    t.column :film_id, :integer
    t.column :director_id, :integer
  end

Now comes the key part: we transfer the existing film directors to the newly created table:

  execute "INSERT INTO directors_films " +
     "SELECT id AS film_id, director_id AS director_id FROM films"

That’s the hard part done with, now we can safely drop the redundant director foreign key from the films table:

 remove_column :films, :director_id

Eyes closed, fingers crossed — let’s migrate:

 $ rake migrate

No data should be lost, but let’s check anyway. First the films table:

id	name
1	Jaws
2	Star Wars Episode IV: A New Hope

Looks good. The director_id column is gone, as expected. How about the directors_films table?

film_id	director_id
1	1
2	2

Excellent! No data has been lost, migrations (and a bit of SQL) saved the day.

For the sake of completeness let’s add Raiders of the Lost Ark. I’m not keen on SQL so let’s do this in Ruby:

  $ ruby script/console
  >> indy = Film.create("name" => "Raiders of the Lost Ark")
  >> steven = Director.find(1)
  >> george = Director.find(2)
  >> indy.directors << steven
  >> indy.directors << george
  >> indy.save

Note: the above assumes that you have defined the following associations in your models:

  class Director < ActiveRecord::Base
     has_and_belongs_to_many :films
  end

  class Film < ActiveRecord::Base
    has_and_belongs_to_many :directors
  end

I’ve tested the above in PostgreSQL, but it should work equally in MySQL. Let me know if you experience any problems.