What is Arel: Between ActiveRecord and SQL Queries
I recently heard that ActiveRecord uses Arel under the hood. What is Arel? When would it be useful in practice? Whenever I hear the phrase under-the-hood I get excited. I want to know EVERYTHING.
So let's find out!
ActiveRecord is useful and its DSL allows us to write many queries with
.where. But it does have limitations in the types of SQL queries we can compose in the
It can combine statements using AND and comparison operators like = and !=. But it doesn’t provide a DSL for numeric comparisons like <= or >= for example. In that case, Rails developers usually write out a SQL string literal. Using Arel could be another (better?) way to this.
What is Arel
Arel is a library used for constructing SQL queries. Introduced in Rails 3. Arel stands for "A relational Algrebra" from Arel. It used to be a separate library (before 2017) but is now bundled with
ActiveRecord. Here is the source, it's a quick read if you're curious.
Arel gives us access to SQL AST (Abstract Syntax Tree) for our model. That's really cool because we can systematically build powerful queries that are readable.
Okay enough preamble, let's see some code.
How does Arel work?
Every ActiveRecord model has a
arel_table method to access the underlying Arel interface. ActiveRecord
where uses Arel under the hood.
my_table = User.arel_table, this
is the same as
They both return the same SQL string. Try it in your rails console!
> User.where(my_table[:id].in([1,2,3])).to_sql => "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" IN (1, 2, 3)" > User.where(id: [1,2,3]).to_sql => "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" IN (1, 2, 3)" >
in is one of the predicate methods that can be used. We can get list of all predications by calling
Arel::Predications.instance_methods. Here is a subset from my console:
> Arel::Predications.instance_methods => [:does_not_match_regexp, :does_not_match_any, :does_not_match_all, :gteq_any, :gteq_all, :gt_any, :gt_all, :lt_any, :in, :lt_all, :lteq_any, :lteq_all, ... :not_between, :gt, :not_in_any, :not_in_all, :matches_regexp, :matches_any, :matches_all, :does_not_match]
One nice thing about Arel is that it doesn't touch the database, until we pass it in as a parameter to the
where clause. This means we can construct custom queries even if the model doesn't exit by defining our own Arel table.
Here is an example of a query built that way with
veg = Arel::Table.new(:vegetables) query = veg[:created_at].gteq( 5.days.ago ).and( veg[:color].eq("green").or( veg[:gardener].eq("Emily") ) ) > query.to_sql => "\"vegetables\".\"created_at\" >= '2022-03-06 22:59:31.928634' AND (\"vegetables\".\"color\" = 'green' OR \"vegetables\".\"gardener\" = 'Emily')"
And here are some more SQL AST methods found in
outer_join and such.
> Arel::SelectManager.instance_methods - Object.methods => [:source, :limit=, :offset=, :offset, :distinct, ... :union, :locked, :group, :orders, :join_sources, :outer_join, :join, :project, :lock, :from, :order, :froms, :window, :take, :having, ...]
Those are a lot of predicates and lot of AST methods, we could likely do everything imaginable in SQL using those.
Also btw, since Arel is a private API, we should be cautioned that using it has a cost of potential breaking changes when we upgrade Rails.
So this Arel interface seems useful and kinda cool to see how ActiveRecord builds the underlying SQL queries. And I like how readable and composable that vegetables query is above.
This post synthesizes information from the below sources:
this post from 2016, this post from 2014, and the source code
If you want to dig into Arel some more, here more resources:
Railsconf 2014 Advanced aRel talk
Arel helper Gem
Tool to convert SQL queries to Arel
If you like short explanations of common concepts, come back here often. We plan to publish these regularly in the coming months. Or if you prefer to be notified, subscribe via RSS or drop your email below. I will email a round up of posts once or twice a month.