Happy Trails

Happy Migrations.

Rails 1.1 has a really cool mechanism for dealing with updates to a database during development: Migrations. Actually, I think migrations predates Rails 1.1, but that is where I first discovered it and it is supposed to be improved. Anyway, migrations are the way you should do all your database development from day 1. It makes the handling database changes much more agile. I tried to wrap my head around migrations from reading online docs, but I was quite unsuited to that task. Thankfully, I attended the 2nd Weekly Hacking Night [1] of StL.rb with Sean Carely and Craig Buchek and I learned what a dunce I had been.

Here are some notes about migrations that I have collected. Others have written eloquently on the mechanics of migrations [2], [3]. I am primarily concerned about the process of migrations. What to do and when to do it.

Lets start with some advice from my little green friend:
Yoda says: If once you start down the migration path, forever will it dominate your destiny, consume you it will.

Lets also assume some starting parameters:

• You are starting a new project.
• You are using Rails 1.1
• Your DB engine is supported by schema-type Ruby. IOW, you can use db/schema.rb instead of the old schema.sql style. (These are at least MySQL, PostgresSQL and SQLLite, mssql. And some rumored others.) I will be using MySQL here.
  
• You are using Subversion for version control.
  

and finally:

• You are committed to never use SQL to modify your DB structure again.

Whew, glad thats out of the way. Migrations and models and tests are well integrated in R1.1. You should use the model generator to create them. Lets begin at the point where we have created our fresh Rails app, we have created a repository with trunk, tags and branches folders in SVN for it, imported our created rails app into trunk and checked out a new working copy. We should have adjusted config/database.yml to reflect our connection and login parameters for MySQL. We may have done other stuff like create controllers and some views, but we haven't created our first model yet. We haven't even created our databases yet. This is where we start with our first migrations.

Our Rails app is called trails and it will feature campers, equipment, counselors, hikes, etc. Lets create our databases:

for d in development test production; do echo create database trails_${d}\;; done | mysql

Now lets add our first model: Camper.

./script/generate model Camper
exists  app/models/
exists  test/unit/
exists  test/fixtures/
create  app/models/camper.rb
create  test/unit/camper_test.rb
create  test/fixtures/campers.yml
create  db/migrate
create  db/migrate/001_create_campers.rb

Note that it created db/migrate and 001_create_campers.rb for us. This is how we are going to create our first table. Note that the version # is 001. As you create new migrations, these will increment, e.g. 002_xxx.rb, 003_yyy.rb, etc. When you run your migrations, rake will load them in that order and the highest versioned file in db/migrate will become the schema version #. More on that later.

Next you need to edit db/migrate/001_create_campers.rb and add columns to the table. Next run

rake migrate

This will create the table in the development db and create a new table called schema_info with a version column. That version will be set to 1. It will also create a file called db/schema.rb with all our current table settings. Schema.rb will contain the current version of the DB since our last rake migrate, so that is a quick way to discover the current version #. The test framework will use db/schema.rb to create the test database and set its version to 1 as well. Just running

rake

will do that for you, as well as running all your tests.

We next create a new model Equipment using the same procress of generate model, edit db/002_create_euipment.rb and rake migrate. Now our development schema_info.version is 2 and we have a new table called equipment.

Next we might decide that each camper is responsible for some pieces of equipment. We need to link our Campers to our Equipment on a foreign key which we forgot to add when we created the model. We do that by generating a new migration:

./script/generate migration AddCamperIdToEquipment

Add our column to db/migrate/003_add_camper_id_to_equipment,rb and then run

rake migrate

We are now at version 3. Adding some tests, then runing rake with no options will update our test DB to version 3 as well.

At this point, we have done a few migations and have a code base that is passing all our tests. Time to check in. Before we do, Sean pointed out that we can use Subversion metadata to keep commits in sync with our DB.

svn propset migrate-version 001 .

will do that for us. Later, we can svn propget migrate-version . to query it.

svn status

Add any missing files, then

svn commit -m "Database now at version 003"

Whenever I commit following one or more migrations, I like to tag it so it is recorded that the code base is working to a specific DB release.

svn copy -m "DB Version 003" svn+ssh://ip.of.host.box/path/to/repos/trunk svn+ssh://ip.of.host.box/path/to/repos/tags/DBVersion_003

After a few more models have been generated and migrations executed, we might want to revert to a previous database version. Assuming we are at version 7, we just migrate down with :

rake migate VERSION=6

We can run svn diff to discover what code changes we made since our last tag DBVersion_006 and back those out as well. I generally make it a policy to commit and tag in SVN when I've created a few models (or just one) or I've generated a special migration to add, rename or remove a column.

To summarize:

./script/generate model ModelName
- edit db/migrate/001_create_model_names.rb
rake migrate
rake        # runs tests, updates test DB to current schema.
./script/generate model NewModel
- edit db/migrate/002_create_new_models.rb
rake migrate
rake
# add a missing column
./script/generate migration AddColumnToNewModels
- edit db/migrate/003_add_column_to_new_models.rb
rake migrate
rake
# check in
svn propset migrate-version 003 .
svn status      # discover unversioned files
svn add db/migrate/001_ , 002 ...   etc.
svn commit -m "DB Version 003"
svn copy -m "DB Version 003" svn+ssh://ip.of.host.box/path/to/repos/trunk svn+ssh://ip.of.host.box/path/to/repos/tags/DBVersion_003
...
# revert to a previous version of the DB Current version is 7
rake migate VERSION=6
svn diff svn+ssh://ip.of.svn.box/path/to/repos/tags/DBVersion_006 svn+ssh://ip.of.svn.box/path/to/repos/trunk
# make changes or use svn revert
rake     # revert our test DB to version 6 and rerun our tests.

One last note. If you generate migrations, be sure to name them uniquely. That is because rake will load all the classes in db/migrate in order of version # and Ruby will overrite the first same named class with the last same named class. Use explicit names that reflect what the migration is going to do, e.g. AddClassNameToStudents. This is the ProgramingByIntention [4] principle of XP.

Overall, I have found migrations to fit nicely with the XP test-code-refactor cycle. It opens the database up to being able to be refactored. Subversion is a tool that helps me when I inevitably shoot myself in the foot.

[1] http://sean-carley.blogspot.com/2006/04/stlrb-hacking-nights.html#links
[2] http://wiki.rubyonrails.com/rails/pages/UsingMigrations
[3] http://rails.rubyonrails.org/classes/ActiveRecord/Migration.html
[4] http://c2.com/cgi/wiki?IntentionalProgramming