Arel, the SQL syntax tree constructor library backing ActiveRecord, allows you to express your SQL queries without the use of fragments. ActiveRecord uses it internally to represent a variety of queries. It does this transparently using the traditional where(key: value) syntax that we’ve seen since the early days of Rails.

In cases where you only need to write simple query predicates, the traditional syntax is what you should reach for, full stop. However, when you need to write a more complex query like grouped pagination or null-based ordering, I recommend reaching for Arel.

However, because Arel considers itself a “framework framework,” it’s not a truly enjoyable to use out-of-the-box. This article shares three ways to make Arel a joy to use.

Table accessor

Arel’s status as a private API makes it so there are not easy-to-use affordances for it by default. So, it stands to reason, the first step we should take is to make it easier to use Arel queries. That first step is a simple one-line method:

class ApplicationRecord < ActiveRecord::Base
  def self.[](field)
    arel_table[field]
  end
end

With this change, you can use Model[:field] to access the Arel attribute for any field or alias in your models. That allows you to make writing even simple queries less error-prone.

To show what I mean, take the following example from RubyGems.org.

def self.find_by_owner_handle!(handle)
  joins(:user).find_by(users: { handle: handle }) ||
    joins(:user).find_by!(users: { id: handle })
end

One thing that, no matter how long I write Rails applications, I make a mistake on the first try is switching between the singular and plural forms of the model versus the table. To paraphrase one of my daughter’s favorite books …

I understand that tables are plural. I know that models are singular. I know all that. I am aware.

But still I cannot remember in a query.

– Apologies to Mr. Collins

Inevitably, I will read the joins clause (joins(:user)) and intuitively write the predicate as user: { handle: handle } even though I know that it’s SQL that we’re writing in the Hash-based query.

It’s not a big deal.

It’s really isn’t.

But it’s just enough of a paper cut that it can cause me to lose my focus when my test unexpectedly blinks red.

As such, I’ve taken to writing these types of query like so:

def self.find_by_owner_handle!(handle)
  joins(:user).find_by(User[:handle].eq(handle)) ||
   joins(:user).find_by!(User[:id].eq(handle))
end

This is unambiguous. I’m writing more Ruby and less Hash-as-SQL. I don’t have to think about it and the way the Arel attribute works, it feels like I’m accessing a Hash so I have the muscle memory for it. No more paper cuts!

As an aside, because this is Ruby, there’s more than one way to do the same thing and I might instead use the merge method. But that’s because this is a simple example; in general, the principle still holds.

Operator aliasing

Another paper cut that I experience that makes using Arel not quite joyful is remembering the names of the operators. Is it gteq or gteql? Likewise, is it eq or eql … or was it equal? What about neq vs not_eq?

Abbreviations (or lack thereof) for these operators change across languages and, in some cases, include all of them for different uses! If you find yourself hopping between languages with any sort of regularity, it can be difficult to write this correctly on the first try:

Post[:created_at].gteq(1.day.ago)

To remove some ambiguity, you can use the superpowers of Ruby to define operators that you won’t mistake:

module Arel::Predications
  alias_method :>, :gt
  alias_method :>=, :gteq
  alias_method :<, :lt
  alias_method :<=, :lteq
end

That change allows you to write this with a maths-like syntax:

Post[:created_at] > 1.day.ago

Or you might choose to do something heretical and alias gt as after so you can write the following, thus saving some mental overhead when performing date maths:

Post[:created_at].after 1.day.ago

Just remember, with great power and all that.

Extending Arel and ActiveRecord

Sometimes the SQL standard moves more slowly than the industry does, leading to databases innovating ahead of the standard. That innovation is great because it helps raise the tide for everyone, but it can lead to opaque, hard-to-remember syntax that only works for a single database type.

For example, I can never remember any PostgreSQL array functions. Which set of symbols means “contains”? Gotta look it up.

To help with this, you can define custom Arel nodes like the following:

class Arel::Nodes::Contains < Arel::Nodes::InfixOperation
  def initialize(left, right)
    super(:'@>', left, right)
  end
end

This encodes the knowledge of symbol translate to “contains” in the Contains class, which is much easier to remember. Since this is a custom Arel node, you must let your database-specific visitor know how to handle it, which you can do this like this for PostgreSQL:

class Arel::Visitors::PostgreSQL < Arel::Visitors::ToSql
  alias_method(
    :visit_Arel_Nodes_Contains,
    :visit_Arel_Nodes_InfixOperation
  )
end

This change tells the visitor that the Contains node is just an Infix Operation (which is any operation of the form arg1 op arg2, like 1 + 2).

Once you have this change, you can use your custom Arel node in a query like the following:

Post.where(
  Arel::Nodes::Contains.new(
    Post[:tags],
    Arel::Nodes.build_quoted('ruby')
  )
)

This query translates to SELECT * FROM "posts" WHERE "posts"."tags" @> 'ruby'; to look for all posts that have a “ruby” tag. The bit here that you need to know is the build_quoted method, which is responsible for wrapping — or “quoting” — arguments for use as parameters in SQL queries.

You can, of course, abstract this one level higher to ActiveRecord, which I recommend doing. But that’s another post for another day.

Conclusion

Arel is a powerful way to compose your SQL queries. However, the Rails core team considers it a private API so it’s not a smooth experience to use it out-of-the-box.

But this is Ruby! With a little bit of love, Arel can be a joy to use. It can save you from silly mistakes, make your queries more expressive, and be easily extended once you know how it works. I encourage you to give it a try to see if it helps you in your day-to-day work.

Do you use Arel in your project? If so, have you run into any issues with it? What’s your favorite thing to do with it?