Rails Migrations provide an easy way to make versioned revisions to your database structure over a period of time. When used properly they can make it easy to make modifications to your existing database programmatically as you need to make changes. This is extremely helpful for large up-and-running projects, but what about projects that are still in development? I don't know about you but for me, after working on large project for awhile there can be a ridiculous number migrations sitting around. It's also quite possible that a good portion of your earlier migrations have been nullified by other migrations later on. When you start throwing data into your migrations this can quickly become a tangled patchwork web of evil.

While migrations are extremely helpful for making modifications on production systems, we have found that having a pile of migration revisions really doesn't serve much purpose during the development cycle. This is especially true if you are not needing to work with live data and you have access to a good code repository like SVN. So during the development cycle for most of our Rails related projects we maintain one large initialization migration, an installation shell script, and a refresh shell script. The large migration handles the standard migration goodness, any type of data import, and other dynamic data installation initialization procedures. The installation script is run once to deploy the application on a new system; it typically sets up the database, runs the initialization migration, and handles whatever other settings you need for deployment. The refresh script simply refreshes the database and reruns the initialization migration; this is used when the migration is updated.

Below is a very simple example of how we put this idea to use:

Database Initialization Migration - 001_initialize_database.rb

require 'active_record/fixtures'
 
class InitializaDatabase < ActiveRecord::Migration
  def self.up
 
    # Initialize fixture array
    # This array is used to load a list of fixtures that will be loaded into the database at the end of the installation process
    fixture_array = []
 
    # It's important to use the :force option if you are not going to drop tables in your down method
    # this forces tables to be overwritten if they are already present
    create_table :customer, :force => true do |t|
      t.column :name, :string
    end
 
    # Add to fixture array
    fixture_array << :client
 
    # CREATE REMAINING TABLES
 
    # Load fixture data
    Fixtures.create_fixtures('db/fixtures', fixture_array)
  end
 
  def self.down
  end
end

Installation SQL Script - install.sql

CREATE DATABASE database_name_dev;
CREATE DATABASE database_name_pro;
CREATE DATABASE database_name_test;
GRANT ALL privileges ON database_name_dev.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL privileges ON database_name_pro.* TO 'user'@'localhost';
GRANT ALL privileges ON database_name_test.* TO 'user'@'localhost';

Installation Shell Script - install.sh

#!/bin/bash
 
mysql -u root --password=password -e "source install.sql"
rake db:migrate

Refresh Shell Script - refresh.sh

#!/bin/bash
 
rake db:migrate VERSION=0
rake db:migrate

Then to install on a system we simply run install.sh from the command line.

> ./install.sh

If a change is made to the schema we run refresh.sh from the command line.

> ./refresh.sh

Since refreshing the database wipes the database clean and reloads each time, its really only useful until you have reached a final stage of deployment. Hopefully by that time though you will have a relatively stable db structure and won't need to be adding a butt-load of migrations.

It's really a relatively simple concept, but I can't tell you how much time we have saved in deployment using this methodology. We'd love to hear your ideas and deployment strategies.