Viewing migration SQL without running the migration

10 Jan 2018

I've seen the question "how can I get the SQL for a migration without running it?" come up a few times. The low-tech answer is to ignore the "without running" part of the question and just run the migration, grep the log file for the SQL output, and then do a db:rollback. But that seems like cheating and is kind of a pain, too. So, how can we make it better?

My initial approach was to intercept a method call pretty far down in the Active Record stack and, if it was the particular migration I was looking for, print the SQL rather than executing it. I'm using PostgreSQL, and I'd like to intercept the ActiveRecord::ConnectionAdapters::PostgreSQLAdapter#execute method, so here's a proxy:

module MyTweak
  def execute(sql, name=nil)
    if caller.detect {|x| x =~ /20171010151334/ } && sql !~ /SHOW TIME ZONE/
      puts sql

Actually using this was clumsy though. I had to get into a console, apply the code modification, and then explicitly invoke the migration:

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
  prepend MyTweak
require "#{Rails.root}/db/migrate/20171010151334_add_wing_count_to_jets"

There's some sample output here, but you get the idea. There's not much to it and using it is kind of painful.

This StackOverflow thread has some nicer approaches. The top answer uses alias_method to redefine ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter#execute so it does more or less what I did in the MyTweak module above; it short-circuits certain methods and prints out the SQL. The real improvement though is that it defines a fake_db_migrate Rake task that does the monkeypatch and then runs db:migrate, so it works for any migration without having to hardcode anything.

Another answer in that thread is more work but also seems less fragile. It uses a transaction and a rollback to get the SQL. You still have to get into a console and require the specific migration, but then instead of patching the code you run the migration in a transaction with an explicit raise of a rollback exception:

ActiveRecord::Base.connection.transaction do :up
  raise ActiveRecord::Rollback

That approach also ignores the "without running the SQL" part of the question. But that's probably fine; the intent is to figure out the SQL and leave the database in the original state, and that's achieved by running the SQL and rolling it back.

Over on the Rails core project there's a recently opened PR for this feature. The PR uses an approach that piggybacks on the "dry run" flag and uses a rollback strategy. It'll be interesting to see how that implementation evolves and if it makes it into Active Record core.

You might be wondering why we don't just grab the Arel AST for the migration and call `to_sql` on it. But that's not how migrations work. They don't go through an intermediate tree generation and traversal process like Active Record queries do. Instead, they're built from SQL as needed. For example, here's an excerpt from the PostgreSQL adapter's version of the very handy migration method `change_column_default`; you can see how it's concatenating strings:

  alter_column_query = "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} %s"
  if default.nil?
    # <tt>DEFAULT NULL</tt> results in the same behavior as <tt>DROP DEFAULT</tt>. However, PostgreSQL will
    # cast the default to the columns type, which leaves us with a default like "default NULL::character varying".
    execute alter_column_query % "DROP DEFAULT"
    execute alter_column_query % "SET DEFAULT #{quote_default_expression(default, column)}"

Arel doesn't support DDL ASTs, but DDL grammars exist and you could imagine putting together an intermediate layer for migrations. It's hard for me to imagine that being worth the hassle, though, since the current approach has done the job for the past decade.

To wrap up, all in all I'd say the rollback strategy seems cleanest since it doesn't involve monkeypatching the database adapter. But one of those monkeypatches might be a good escape hatch if you just want a one-time quick hack. And keep an eye on the Rails changelog to see if this makes it into core.