Working with SQL in ActiveRecord

Why

I was working on a more complicated query and was completely stuck on how to construct the query. The worst part was this feeling was déjà vu, but I couldn’t remember what to search until a full day of struggling.

So this is a quick memo to myself about working with ActiveRecord and SQL statements.

First: Hint of Working in pure SQL

ActiveRecord is great, it definitely provides a nice abstraction to the database from Ruby. The first hint for me to work in pure SQL is: if I want to take advantage of SQL functionality, like having the SQL database do computations on the dataset as part of the result, build the query in SQL instead of ActiveRecord (more on why below.)

Usually anything with a join and a clause on two different joined columns required should be a good hint.

Second: Jumpstart SQL statement

I’m not a DBA and thanks to the ActiveRecord abstractions: also not that familiar with working directly with the app database. To help build the initial SQL query, use ActiveRecord’s to_sql method to generate a base SQL statement. Something like this:

Model_a.joins(item: :model_a_item_id).to_sql

Use this as a base statement, and layer on more SQL. :-D

Third: Use the SQL prompt!

Work from the SQL database’s prompt to develop the query. The feedback system in the SQL prompt is far superior and faster than ActiveRecord’s interface. Any errors from the database usually require a reconnection.

Fourth: Bring the SQL back

When the query is getting everything you want, bring the query back into ActiveRecord using ActiveRecord::Base.connection.exec_query("<SQL statement>"), which returns: ActiveRecord::Result. Methods that work on the ActiveRecrod::Result:

Fifth: collect $200

Because you just got the database to do more work for you!

No need to deal with nil results or empty columns. The database does a lot of work around this and will handle empty operations properly (dealing with computations on a nil in Ruby is never pretty.)

Future reference:

Some important details to have as reference. (Mostly PostgreSQL hstore stuff)

To be updated as more stuff is discovered. :-)

ActiveRecord and Me

ActiveRecord is a great abstraction for the database, as long as database expectations are simple, such as simple database queries (Model.find and Model.where).

Once there are multiple joins and the database is expected to do more work (so Ruby doesn’t have to), ActiveRecord just doesn’t cut it for me.

Anytime there is an error with the query, ActiveRecord returns a generic error saying the database connection needs to be rest, which really means the app needs to be restarted.

Ruby vs. SQL Mindset

When working in Ruby, I definitely solve a problem very differently than using SQL. In Ruby, the mindset is to use graphs; in SQL the mindset is sets.

The end result of using Ruby or SQL will be the same, but the mental model of getting there is so different. Fundamental design of Ruby and the design of SQL are just so different.

I find it’s much better to stop working in Ruby and just be in pure SQL mode to develop a good SQL solution. (On the contrary, when have I stopped to think: how to do this in SQL? That’s something to ponder…?)

Also, there are more SQL questions than for ActiveRecord questions. 368,618 vs. 22,321, respectively, as of today (Nov 25, 2016)

Middle Ground? Squeel

One possible solution I have discovered is the Squeel gem, which looks super awesome. As much as I like writing out SQL, the context switch from Ruby to SQL and back again when reading code is quite a burden as well.

Overall

The switching context between Ruby and SQL takes a long time for me. I’ve included some pointers for me to get to working in pure SQL quicker, but after ranting a bit, I realize that learning more SQL will go a long way.

Go Forth & select * from knowledge :-D