Skip to main content

How to Display SQL Queries in Rails Console

Coming from Python or Node, you probably relied on ORM logs or print statements to see what SQL was actually being executed. In Rails, you have tools built right into ActiveRecord.

This post covers the methods I reach for when I want to understand what a query is doing.

See the SQL Before Running It​

to_sql returns the SQL string that ActiveRecord would execute, without hitting the database. It's the first thing I check when building a complex query.

User.where(role: "admin").to_sql
# => "SELECT "users".* FROM "users" WHERE "users"."role" = 'admin'"

Use puts to make it easier to read, especially for chained queries:

puts User.joins(:orders)
         .where(orders: { status: "pending" })
         .where("created_at > ?", 30.days.ago)
         .to_sql

This is also useful for debugging association queries. If you already have a record loaded, you can inspect its scope:

user = User.find(1)
puts user.orders.where(status: "pending").to_sql

Understand the Execution Plan​

Once you know the SQL, explain shows you the database's execution plan: whether it's using indexes, doing full table scans, and so on.

User.where(role: "admin").explain

The output format depends on your database. For PostgreSQL you'll see something like:

EXPLAIN for: SELECT "users".* FROM "users" WHERE "users"."role" = 'admin'
Seq Scan on users  (cost=0.00..1.05 rows=1 width=...)

Check Column Types​

When you're not sure what type a column is or why a comparison is behaving unexpectedly, check the schema directly from the console:

User.columns_hash["role"].type       # => :string
Order.columns_hash["status"].type    # => :integer
Order.columns_hash["placed_at"].type # => :datetime

This is faster than opening schema.rb and is especially useful when working in a codebase where enums are stored as integers.

See the Raw Database Value​

Rails applies type casting when reading attribute values. If a value looks wrong and you want to rule out a casting issue, check the raw value before Rails transforms it:

order = Order.find(1)
order.placed_at                  # => Thu, 15 May 2025 10:00:00 UTC
order.placed_at_before_type_cast # => "2025-05-15 10:00:00"

This also helps when debugging integer-backed enums:

order.status                  # => "pending"
order.status_before_type_cast # => 0

Enable Live SQL Logging​

If you want to watch every SQL statement as it runs, you can attach a logger to ActiveRecord for the duration of your console session:

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

After this, every query you run will print its SQL directly to the console output. This is useful when you're tracing an unfamiliar method and want to see all the queries it triggers.

To turn it off:

ActiveRecord::Base.logger = nil

Note: this only affects the current console session.

Debugging Query Workflow​

When a query returns unexpected results or feels slow, this is the order I usually follow:

  1. Use to_sql to confirm the query is what you think it is
  2. Check column types with columns_hash if the WHERE clause looks odd
  3. Run explain to see if an index is being used
  4. Enable the logger if you're unsure which queries a method is triggering behind the scenes

References​