How can I see the SQL that will be generated by a given ActiveRecord query in Ruby on Rails

Question

I would like to see the SQL statement that a given ActiveRecord Query will generate. I recognize I can get this information from the log after the query has been issued, but I'm wondering if there is a method that can be called on and ActiveRecord Query.

For example:

SampleModel.find(:all, :select => "DISTINCT(*)", :conditions => ["`date` > #{self.date}"], :limit => 1, :order => '`date`', :group => "`date`")

I would like to open the irb console and tack a method on the end that would show the SQL that this query will generate, but not necessarily execute the query.

Answer 1

Create a .irbrc file in your home directory and paste this in:

if ENV.include?('RAILS_ENV') && !Object.const_defined?('RAILS_DEFAULT_LOGGER')
  require 'logger'
  RAILS_DEFAULT_LOGGER = Logger.new(STDOUT)
end

That will output SQL statements into your irb session as you go.

EDIT: Sorry that will execute the query still, but it's closest I know of.

Answer 2

When last I tried to to this there was no official way to do this. I resorted to using the function that find and its friends use to generate their queries directly. It is private API so there is a huge risk that Rails 3 will totally break it, but for debugging, it is an ok solution.

The method is construct_finder_sql(options) (lib/active_record/base.rb:1681) you will have to use __send__ because it is private.

Answer 3

You could change the connection's log method to raise an exception, preventing the query from being run.

It's a total hack, but it seems to work for me (Rails 2.2.2, MySQL):

module ActiveRecord
  module ConnectionAdapters
    class AbstractAdapter
      def log_with_raise(sql, name, &block)
        puts sql
        raise 'aborting select' if caller.any? { |l| l =~ /`select'/ }
        log_without_raise(sql, name, &block)
      end
      alias_method_chain :log, :raise
    end
  end
end

Answer 4

This is what I usually do to get SQL generated in console

-> script/console
Loading development environment (Rails 2.1.2)
>> ActiveRecord::Base.logger = Logger.new STDOUT
>> Event.first

You have to do this when you first start the console, if you do this after you have typed some code, it doesn't seem to work

Can't really take credit for this, found it long time ago from someone's blog and can't remember whose it is.

Answer 5

Similar to penger's, but works anytime in the console even after classes have been loaded and the logger has been cached:

ActiveRecord::Base.connection.instance_variable_set :@logger, Logger.new(STDOUT)

Answer 6

Try the show_sql plugin. The plugin enables you to print the SQL without running it

SampleModel.sql(:select => "DISTINCT(*)", :conditions => ["`date` > #{self.date}"], :limit => 1, :order => '`date`', :group => "`date`")