Simple Database Updates with Ruby

gcode

As our PHP development team grows we’ve begun running into the familiar problem of database migrations. And not only between local dev environments, but deployments too. It’s nice that Susie told Joe Developer about her latest db changes so he can continue work on his feature, but if I need to do a release early tomorrow morning, I’m staring at a nasty database error on the live site.

Existing Solutions

Of course I went out on the “open market” and had a look at the available offerings. Phing seems very mature and feature complete, but involves XML editing which, after years of java development, horrifies me. Codeigniter Migrations looks great but I don’t particularly want to bootstrap the entire framework for some db migrations. Both Ruckusing Migrations and MySQL PHP Migrations look quite promising but I don’t need full blown up/down migrations right now.

Requirements

I need to be able to tie a subversion revision id with some database update. The database migration should be executed automatically on the staging environment when the developer commits their changes. And Ops needs an email notification which alerts us to a successful (or failed) db migration on staging, including the revision id and the SQL we’ll need to execute on production for the next release.

The players

The updates should be in one file which doubles as a “bootstrap” script for empty databases. Here’s a snippet of our update.sql file:

We have a _sql_version table in the database which contains the last successfully executed migration block id from the update.sql file. Taking the last id from this table, the update_db.rb ruby script scans the update.sql file looking for any/all migration blocks to execute by looking for ids greater than the last id from the table. Thus, the revision ids listed in the file are just a “best guess” from the developer (as they are writing this in before committing). The system is very tolerant as it executes any & all revisions after the last known id.

Here’s the update script – please forgive a PHP developer for playing around in Ruby 😉 :

require 'rubygems'
require 'mysql'
require 'net/smtp'
require '_common/globalvars'

# take a cmdline argument of the current SVN Revision ID
# NOTE : this is NOT the user revision ID written in the update.sql (which is just a guess), 
#        but the REAL svn revision id from the server
svn_revision_id = ARGV[0].to_i

db_host = MyRegistry::get()['database']['cr']['host'];
db_user = MyRegistry::get()['database']['cr']['username'];
db_pass = MyRegistry::get()['database']['cr']['password'];
db_name = MyRegistry::get()['database']['cr']['name'];

def send_email(svn_revision_id, message)
	msg = <<END_OF_MESSAGE
From: 
To: 
Subject: [#{svn_revision_id}] db updates & schema changes
	
#{message}
END_OF_MESSAGE
	
	Net::SMTP.start('localhost') do |smtp|
		smtp.send_message msg, 'db_admin'
	end
end

begin
  dbh = Mysql.real_connect(db_host, db_user, db_pass, db_name)
  
  # determine the last svn sql update revision id from database
  last_sql_user_revision = 0
  result = dbh.query('SELECT MAX(user_revision) FROM _sql_version');
  last_sql_user_revision = result.to_i
  result.free
  
  #PARSE update.sql
  updates = []
  queryString = ""
  user_revision = nil
  catch :OUT do
    File.open('update.sql') do |file|
      file.readlines.each do |line|
        line = line.strip()
        unless line.empty?
          throw :OUT, user_revision = line.to_i if (line.to_i > 0 && line.to_i  0) 
            user_revision = line.to_i
          else
            if (line.index(';') != nil)
              updates.push(Hash[user_revision, query_string + ' ' + line])
              query_string = ""
            else
              query_string += ' ' + line
            end
          end
        end
      end
    end
  end
  
  #EXECUTE SQL statements parsed out above
  last_user_revision_id = -1
  message = ""
  db_updates_made = 0
  catch :OUT do
    updates.each do |update|
      update.each_pair do |user_revision, query_string|
        throw :OUT if (user_revision <= last_sql_user_revision)
      
        dbh.query(query_string)
        db_updates_made += 1
        
        message += query_string + "rn"
        last_user_revision_id = user_revision if (last_user_revision_id  0)
    update_sql_version = "insert into _sql_version (`user_revision`) value (#{last_user_revision_id})"
    dbh.query(update_sql_version)
    
    #EMAIL developers with ALL SQL updates for SVN Revision ID
    message = "Apply following " + db_updates_made.to_s + " updates to db:rnrn" + message + 
              "rnrn#{update_sql_version};rnrnNOTE: These updates have already been applied to staging."
    puts message
    send_email(svn_revision_id, message)
  else 
    puts "0 updates made to db.rnrnupdate.sql = #{user_revision} & _sql_version.user_revision = #{last_sql_user_revision}"
  end
rescue Mysql::Error => e
  puts "Error message: #{e.error} (#{e.errno}) | Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate")
  if (db_updates_made > 0)
    puts "rnDied in section #{last_user_revision_id}, but successfully executed the following queries: #{message}"
  end
  raise RuntimeError
ensure
  dbh.close if dbh
end

The last update is to the _sql_version table:

Now we’re ready for the next db migration.

Automated Migrations with Hudson

Hudson runs that ruby script on every SVN update:

A notification email gets sent out with the status, svn revision id, and any SQL that should be executed. As this email goes out to the whole team, developers can use this to bring their own environment up to speed (either manually or just executing the update_db.rb script locally).

If you are debating how to rollout db migrations for your team, definitely take a look at those open options I first mentioned. But if you have any reservations, feel free to borrow my ideas & code! For the rest of you guys, how are you realizing migrations in your coding projects?