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

Question

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?

Answer 1

Very interesting question! I found this way:

1. Assume your migration placed in file db/migrate/20160102210050_create_items.rb and called CreateItems

2. Go to Rails console and load migration file:

   rails c
   require './db/migrate/20160102210050_create_items'
   

3. Open transaction, run migration and rollback transaction before commit :)

   ActiveRecord::Base.connection.transaction do
     CreateItems.new.migrate :up
     raise ActiveRecord::Rollback
   end
   

If you want to check SQL on rollback, just call CreateItems.new.migrate :down on step 3. SQL will be executed and tested on database, but not committed - so you can verify your migration without affects.

Answer 2

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.