Sunday, November 12, 2006

Rails: Generate SQL from Migrations

Unfortunately, my new client will not allow migrations to be run in production. This isn't a surprise since they have a DB group that maintains their databases. And, the DB group requires application developers to submit SQL scripts when database modifications are necessary.

At first this may sound like a pain, but it's actually quite easy to capture the SQL that migrations generate. There may be easier ways; however, it seemed simple enough to temporarily change the behavior of the execute method.
require 'find'

class SqlWriter

def self.write(path, string), 'w') { |file| file << string }


class MigrationSql
class << self
def execute
connection = ActiveRecord::Base.connection
old_method = connection.class.instance_method(:execute)

define_execute(connection) { |*args| SqlWriter.write(@sql_write_path, args.first) }

root = RAILS_ROOT + "/db/migrate"
output_dir = root + "/../migration_sql"
Dir.mkdir output_dir unless File.exists? output_dir
Find.find(root) do |path|
unless path == root
require path
file = File.basename(path, ".rb")
write_sql(connection, output_dir, file, :up)
write_sql(connection, output_dir, file, :down)

define_execute(connection) { |*args| old_method.bind(self).call(*args) }

def write_sql(connection, output_dir, file, direction)
connection.instance_variable_set :@sql_write_path, output_dir + "/" + file + "_#{direction}.sql"
file.gsub(/^\d\d\d_/,'').camelize.constantize.send direction

def define_execute(connection, &block)
connection.class.send :define_method, :execute, &block
Now that we have the behavior necessary we can wrap the call to the execute method of the MigrationSql class in a rake task.
namespace "db" do
namespace "generate" do
desc "generate sql for migrations"
task "migration_sql" => :environment do


  1. Anonymous12:50 PM

    I made a plugin last year which you may find useful.
    Basically it alters the execute method to send the sql to whatever object it was given. Let me know if you like it!

  2. Anonymous12:36 AM

    Great! I was just thinking about writing a similar plugin when I was deep in using ActiveRecord in a legacy DB environment so that I can spool the SQL statements that would have been executed in a file. Perhaps you and Mark can consolidate your approaches.

    The idea I had was to introduce a class variable like ActiveRecord::Base.spool_file "somefile.sql" and ActiveRecord::Base.execute false/true to make ActiveRecord spool out to file and/or actually really execute on the db.

  3. Anonymous12:04 PM

    Jay, this is a great idea and good sample code!
    However, I came up on a limitation where your approach doesn't work when the migrations need to add data in addition to just generating the create statements.
    Rails tries to do a "show fields from table" statement in that case, but since the connect.execute statement has been overridden it doesn't return any data and the migration fails.

    Aside from adding the file-write code to the initial execute method, can you think of any better approach?

  4. Anonymous9:37 AM


    You could do a regex match for /show fields from/ and allow the query to go to the original execute method and behave normally.

    Sorry, I haven't had to deal with this so I don't have any working code for that situation.

    Cheers, Jay


Note: Only a member of this blog may post a comment.