Use `to_sql` to see what query ActiveRecord will generate

If you’re trying to write a tricky ActiveRecord query that includes joins, complex where clauses, or selecting specific values across tables, it can be hard to remember every part of the ActiveRecord DSL.

Is it joins(:orders) or joins(:order)? Should you use where(role: { name: 'Manager' }) or where(roles: { name: 'Manager' }).

It’s a good idea to test these queries in the Rails console so you can quickly iterate, but sometimes you’ll be left scratching your head because when you run the code you get a weird result like:

#<MyModel::ActiveRecord_Relation:0x23118>

And if you try to access the results, the query blows up with a cryptic error like:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "permission"
LINE 1: ...."id" = "permissions_users"."permission_id" WHERE "permissio...

Usage

Sometimes you just want to inspect the generated SQL to debug what’s going wrong.

It’s actually really easy to do this with ActiveRecord: simply call to_sql on your query and, instead of running the SQL, it will print out the full query – even if the SQL is invalid.

User.where("email LIKE '%?'", "gmail.com").to_sql
=> "SELECT \"users\".* FROM \"users\" WHERE (email LIKE '%'gmail.com'')"

Aha! We messed up the %? syntax.

User.where("email LIKE ?", "%gmail.com").to_sql
=> "SELECT \"users\".* FROM \"users\" WHERE (email LIKE '%gmail.com')"

It’s especially helpful if you have multiple database tables involved.

User.joins(:permissions).where(permission: { key: :edit_posts }).to_sql
=> "SELECT \"users\".* FROM \"users\" INNER JOIN \"permissions_users\" ON \"permissions_users\".\"user_id\" = \"users\".\"id\" INNER JOIN \"permissions\" ON \"permissions\".\"id\" = \"permissions_users\".\"permission_id\" WHERE \"permission\".\"key\" = 'edit_posts'"

Whoops! We need to use the plural permissions in our where.

User.joins(:permissions).where(permissions: { key: :edit_posts })

This tip has saved me countless hours of debugging complex queries. I also reach for it to validate tricky queries where I want to be sure Rails is generating the intended SQL query.

Additional Resources

Rails API: ActiveRecord::Relation#to_sql