Further adventures along our Trail

More about Migrations.

Here are a few more things I've learned about Rails 1.1 DB Migrations, or I forgot to note in my last post.

• Make small migrations. It is ok to have very many migrations. Goes with test a little, code a little, repeat, refactor, etc.
  
• Don't put in the id column for the table. The create_table method does that by default.
• When naming your migrations, only use "pure" CamelCase. E.g. This "AddSSNToClient" doesn't work because ./script/generate migration will creare 003_add_ssn_to_client but the name of the class will be AddSSNToClient, and 'rake migrate' will look for AddSsnToClient.
• Use db/schema.rb to populate your production databases and other development servers. This will still preserve your version # and should allow you to back out to previous versions even on these DB instances.
  
• Commit any code changes prior to your first migration. Tag this so you know where you came from.
  
• Use rake db:schema:dump one time when you want to start using migations from a pre-existing SQL DDL generated schema. At this point you will be at version 0. Generate your next model or generate a new migration which will be version 1. If you back out via rake migrate VERSION=0 you will be at the last point before you started using migrations.
• You can port your development environment to a new computer and a new instance of your DB server. Check out your head SVN trunk. I have a Rake task that generates my DBs and sets up config/database.yml*. run that or create at least your development and test dbs. Then run 'rake migrate'. You can backup and restore your old data into your new MySQL instance.
  

In my last post, I said you should start Rails Migrations from Day 1. That is true, but what if you have an existing schema? That's OK, too. It will just start at revision 0. From that point forward, you want to do migrations only.

If you are using MySQL, then I recommend doing a

mysqldump -t db_name >db/mydbstruct.sql

and commiting that before continuing. You should even make a full backup of your database, which is always a good idea when playing around with DBs.

The sequence (for legacy DB conversion to Rails Migrations) is:

• mysqldump -t mydb >db/mydb_struct.sql
• svn add db/mydb_struct.sql
• svn commit -m "Commit DB Version 000 prior to first migration"
  
• "svn copy" to tags/DBVersion_000
• ./script/generate migration AddColumnToMytabs
• Edit db/migrate/001_add_column_to_mytabs.rb and add your column(s)
  
• rake migrate
• rake # to run tests
• svn commit -m "Switched to Rails Migrations. DB Version 001. Added column to Mytabs."
  
• svn copy to tags/DBVersion_001

Or, if you are creating a new model,

• ./script/generate model NewModel
• Edit db/migrate/001_add_columns_to_mytabs.rb
• ... etc.
  

Some have suggested that you should do rake db:schema:dump then copy and paste the output inside db/schema.rb into a new migation. I am not exactly sure when this is useful. Perhaps if you want to start using migrations from scratch so that your first migration (001) creates all your legacy tables.

* This is my rake task. I copy it into lib/tasks (actually I have a generator to do that for me.) The fix_yaml task is needed in my case because I am running MySQL 4.1.x on Ubuntu. The socket is in a different place than Rails thinks and I don't want to be root for my personal DBs I create.

lib/tasks/create_db.rake

require 'yaml'
fn_yaml='config/database.yml'
myuserid='me'
yml=YAML::load(File.open(fn_yaml))

desc 'Creates databases in mysql'
task :create_db => [:fix_yaml] do
      databases do |db|
              system "echo create database #{yml[db]['database']} | mysql"
      end
end

desc 'Drops databases in mysql'
task :drop_db do
      databases do |db|
              system "echo drop database #{yml[db]['database']} | mysql"
      end

end

desc 'Fix database.yml'
task :fix_yaml do
      databases do |db|
              yml[db]['username'] = me
              yml[db]['socket'] = '/var/run/mysqld/mysqld.sock'
      end
      File.new(fn_yaml, 'w').write yml.to_yaml
end


def databases
      %w{development test production}.each do |db|
              yield db
      end
end

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