Direct SQL queries in Rails apps

I’ve been working on some stats tracking.  This means a lot of complex queries, and sometimes ActiveRecord just doesn’t cut it.  Doing it the ruby way (or at least, the ruby way I know how to do) would often mean queries > 30 seconds.  A simple SQL query could get me the same data in <100ms.  For some reason, I was having a really hard time figuring out how to do direct SQL queries though. After banging my head, here’s what I figured out.

There are two ways to directly query the DB with AR: find_by_sql and ActiveRecord::Base.connection.execute. They each have their place, and I use both.

find_by_sql
find_by_sql is super easy, as it actually returns an ActiveRecord object. This means you don’t need to do anything unusual. It works on an object, and works great when you’re selecting fields from that object specifically. Here’s an example of how I use find_by_sql and some fun SQL to get a funnel of our users:

@funnel = User.find_by_sql("SELECT DATE_TRUNC('month', created_at) AS month,
             COUNT(id) AS registered,
             SUM(CASE WHEN verified_at IS NOT NULL THEN 1 ELSE 0 END) AS verified,
             SUM(CASE WHEN confirmed_billing_at IS NOT NULL THEN 1 ELSE 0 END) as confirmed
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month")

connection.execute
The other way to execute SQL is with connection.execute. This returns a database specific object, for example a PGrecord for my postgres database. You can then iterate over that object like any standard ruby array of hashes. The hash key will be the SQL header. Here’s an example where I calculate the # of new user signups per week:

sql = "SELECT COUNT(*), DATE_TRUNC('week', created_at) FROM USERS GROUP BY DATE_TRUNC('week', created_at)"
results = ActiveRecord::Base.connection.execute(sql)
results.each do |foo|
  puts "#{foo['date_trunc']} #{foo['count']}"
end