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)
   File.open(path, 'w') { |file| file << string }
 end
 
end

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)
       end
     end
   
     define_execute(connection) { |*args| old_method.bind(self).call(*args) }
   end
   
   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
   end
   
   def define_execute(connection, &block)
     connection.class.send :define_method, :execute, &block
   end
 end
end

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
     MigrationSql.execute
   end
 end
end