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

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.

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

For Rails 2:

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

For Rails 3.0.x:

ActiveRecord::Base.logger = Logger.new(STDOUT)

For Rails >= 3.1.0 this is already done by default in consoles. In case it's too noisy and you want to turn it off you can do:

ActiveRecord::Base.logger = nil

Stick a puts query_object.class somewhere to see what type of object your working with, then lookup the docs.

For example, in Rails 3.0, scopes use ActiveRecord::Relation which has a #to_sql method. For example:

class Contact < ActiveRecord::Base
  scope :frequently_contacted, where('messages_count > 10000')
end

Then, somewhere you can do:

puts Contact.frequently_contacted.to_sql

just use to_sql method and it'll output the sql query that will be run. it works on an active record relation.

irb(main):033:0> User.limit(10).where(:username => 'banana').to_sql
=> "SELECT  "users".* FROM "users"  WHERE "users"."username" = 'banana'
LIMIT 10"

when doing find, it won't work, so you'll need to add that id manually to the query or run it using where.

irb(main):037:0* User.where(id: 1).to_sql
=> "SELECT "users".* FROM "users"  WHERE "users"."id" = 1"

This may be an old question but I use:

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

The explain method will give quite a detailed SQL statement on what its going to do

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.

When last I tried to do this there was no official way to do it. 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.

Edit: construct_finder_sql was removed in Rails 5.1.0.beta1.

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.

EDIT: Now with arel, you can build up scopes/methods as long as the object returns ActiveRecord::Relation and call .to_sql on it and it will out put the sql that is going to be executed.

My typical way to see what sql it uses is to introduce a "bug" in the sql, then you'll get an error messages spit out to the normal logger (and web screen) that has the sql in question. No need to find where stdout is going...

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`")

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

In Rails 3 you can add this line to the config/environments/development.rb

config.active_record.logger = Logger.new(STDOUT)

It will however execute the query. But half got answered :