CODEWITHSUNDEEP
ruby-on-railsdatabaserake
Mason Bryant
Mason Bryant

Reputation: 1392

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

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?

Upvotes: 18

Views: 7212

Answers (4)

shock_one
shock_one

Reputation: 5925

A slightly more low-level function, which can be used for your purpose:

# Get SQL query of a migration expression without executing it.
#
# @example
#   schema_statement_to_sql { create_table(:tomatoes) }
#   # => "CREATE TABLE \"tomatoes\" (\"id\" serial primary key) "
def schema_statement_to_sql(&block)
  raise ArgumentError, 'No block given' unless block_given?

  connection = ActiveRecord::Base.connection
  original_execute = connection.method(:execute)
  sql_to_return = ''
  capturing_execute = proc { |sql| sql_to_return = sql }
  connection.define_singleton_method(:execute, &capturing_execute)

  begin
    connection.instance_eval(&block)
  ensure
    connection.define_singleton_method(:execute, &original_execute)
  end

  sql_to_return
end

Upvotes: 2

Matouš Borák
Matouš Borák

Reputation: 15944

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.

Upvotes: 3

Semjon
Semjon

Reputation: 1023

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.

Upvotes: 23

Edward
Edward

Reputation: 3499

You can do 

rake db:migrate --dry-run --trace

and rake will test the task. Then use one of the methods listed to get the SQL that would be run.

Upvotes: -2

Related Questions