DEV Community

loading...

Use `to_sql` to see what query ActiveRecord will generate

matt swanson
Full-stack product engineer with 10 years experience building web applications. Keep it boring.
Originally published at boringrails.com on ・2 min read

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>
Enter fullscreen mode Exit fullscreen mode

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...
Enter fullscreen mode Exit fullscreen mode

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'')"
Enter fullscreen mode Exit fullscreen mode

Aha! We messed up the %? syntax.

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

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'"
Enter fullscreen mode Exit fullscreen mode

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

User.joins(:permissions).where(permissions: { key: :edit_posts })
Enter fullscreen mode Exit fullscreen mode

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


Discussion (0)