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