Add Custom SQL to Rails Active Record Migrations

Ruby on Rails’ Migrations offer a nice alternative to traditional SQL DDL statements, that fit in with the Rails agile development philosophy. Migrations let you use Ruby code to create, alter and drop databases and tables, allowing you, amongst other things to switch databases mid-development, without having to refine your SQL.

Such convenience can sometimes mean a lack of flexibility. If you are trying to add a column to a MySQL database with type bigint unsigned, you’ll run into problems as ActiveRecord does not support unsinged bigints. You can solve the problem with some creative use of Ruby’s command line execution features - any code in a ruby file that is included between backticks(’ ` ‘) will be executed as a command line argument - so now you can include custom SQL statements without losing the benefits of Migrations:

class MyClass < ActiveRecord::Migration
  def self.up
    remove_column :mytable, :old_column
    puts `mysql database -u user -ppassword
    -e 'alter table mytable add column new_column bigint unsigned'`
  end
..
..
end

April 10th, 2007 - Posted by in ruby, ruby on rails, tips | |

Leave a reply

You must be logged in to post a comment.