Show SQL generated by pending migrations in rails without updating the database

database, rake, ruby-on-rails

Solution

This can be done by monkey-patching the database adapter. This example works for MySQL.

Create a rake task for "fake db:migrate":

desc "Prints all SQL to be executed during pending migrations"
task :fake_db_migrate => :environment do

  module ActiveRecord
    module ConnectionAdapters
      class AbstractMysqlAdapter < AbstractAdapter

        alias_method :real_execute, :execute

        def execute(sql, name = nil)
          if sql =~ /^SHOW/ || sql =~ /^SELECT.*FROM.*schema_migrations/ || sql =~ /^SELECT.*information_schema/m
            real_execute(sql, name)
          else
            puts sql
          end
        end

      end
    end
  end

  Rake::Task["db:migrate"].invoke
end

The rake task monkey-patches the `execute` method in the connection adapter so that SQL is printed instead of being executed, before actually running the migrations. However, we still have to execute some of the internal SQLs that are used by the `db:migrate` task to get the database schema and to find out which migrations are pending. That's what the `real_execute` call does.

Test

Suppose now that and we have a pending migration in `db/migrate/20160211212415_create_some_table.rb`:

class CreateSomeTable < ActiveRecord::Migration
  def change
    create_table :some_table do |t|
      t.string :string_column, null: false, default: 'ok'
      t.timestamps
    end
  end
end

$ rake db:migrate:status
...
down    20160211212415  Create some table

Now, let's run our fake migrations task:

$ rake fake_db_migrate
== 20160211212415 CreateSomeTable: migrating ==================================
-- create_table(:some_table)
CREATE TABLE `some_table` (`id` int(11) auto_increment PRIMARY KEY, `string_column` varchar(255) DEFAULT 'ok' NOT NULL, `created_at` datetime, `updated_at` datetime) ENGINE=InnoDB
   -> 0.0009s
== 20160211212415 CreateSomeTable: migrated (0.0010s) =========================

BEGIN
INSERT INTO `schema_migrations` (`version`) VALUES ('20160211212415')
COMMIT

The migrations status has not been changed, i.e. the migration is still pending:

$ rake db:migrate:status
...
down    20160211212415  Create some table

Tested on rails 4.2.3 with the `mysql2` gem.

Problem

I'd like to have a way to produce the actual sql (ie: if I pasted into a mysql console, it would work) that will be generated by a rake db:migrate without actually updating the target database. rake db:migrate:status does a good job of showing which migrations are pending for a given database, but I've yet to find a way to get the actual SQL produced. Any ideas?

Original source

Related problems