Diving into Rails SQL Query Builders | Arel and Sequel

SQL query builders sound pretty outstanding when you first hear about them, they do most of the legwork for building complex SQL queries. But are they as good as they sound? What about SQL query builders for Ruby on Rails, especially Arel and Sequel?

In this blog post we’re taking a deep dive into Rails SQL Query Builders; Arel and Sequel, along with a nifty comparison to help you decide which one to go for. 

Here’s what we’ll cover:

• ActiveRecord’s Arel
• Sequel: The Database Toolkit for Ruby
• Comparing Arel & Sequel
• Using Arel
• Sequel: When ActiveRecord Isn’t Enough
• Using Sequel
• The Grand Finale: Who Wins?

ActiveRecord’s Arel

ActiveRecord uses Arel under the hood to build SQL queries in an object-oriented way, that can be leveraged to build our own advanced queries. A powerful SQL AST (Abstract Syntax Tree) manager, Arel lets you combine selection statements from simple to very complicated queries. One of the most compelling reasons for even using Arel is that you can work with very complex queries without having to write, or learn SQL.

Arel works by generating abstract syntax trees or ASTs for your queries, using the visitor pattern to put them out to SQL strings and hand them to ActiveRecord for execution. 

Eric Hayes talked about cleaning up littered blocks of unformatted SQL with Arel to build composable query objects in his RailsConf 2020 session. In the talk, he elaborates that as the number of different queries grows, it becomes harder to keep the codebase functional as they are arranged and used in various ways.

It’s worth mentioning that Arel, although a public gem, is actually a private API in Rails. It was never a part of the public API of the framework, and therefore, never had an adequate API review, as voiced by Rafael França, Principal Engineer at Shopify. This makes the users of Arel responsible for checking that the Arel code works as expected with newer versions, which might be a risk one might not want to take. This risk can be mitigated by running test coverage.

In an effort to resolve this predicament, the Rails core team merged Arel inside ActiveRecord. Arel is now bundled in the ActiveRecord gem, and maintained in the rails/rails repository.

Sequel: The Database Toolkit for Ruby

Sequel is a flexible and powerful SQL database access toolkit for Ruby, designed to clear away issues from connecting to databases and manipulating them. It does most of the legwork so that you can focus on your application, including:

• Maintaining Connections
• Correctly Formatting SQL
• Fetching Records

Sequel is an ORM (Object-Relational Mapping) similar to ActiveRecord, and being routinely maintained by its lead developer, Jeremy Evans since 2008. The similarity between the two is quite uncanny, that some have even conjectured ActiveRecord of reinventing Sequel. 

This brings us to compare Arel and Sequel, both using the building-queries-via-method-chaining style.

Comparing Arel & Sequel

A natural trail of thought would be that ActiveRecord and thus Arel, is more convenient and a better fit, simply because it’s the default. There’s been a lot of online speculation about this, and we’ve witnessed some developers siding with Sequel being better suited on this one.

To start off, unlike Sequel, Arel is not an ORM (ActiveRecord is) but can be used to build an ORM (object-relational mapper). Arel makes it easy for framework developers to reduce coupling between the framework and the SQL database by providing a standardized API to generate SQL queries.

An issue faced by many developers who are curious about Arel is finding adequate documentation for using it. You can find bits and pieces of information about Arel online, but you’ll have to do a ton of research to get exactly what you’re looking for. 

That is not the case with Sequel. Sequel is regularly maintained and you can find official documentation to support your research and development process.

Using Arel

Head of Software Engineering, Jeffery Durand effortlessly convinces us in his blog that Arel can be used for any condition that you would normally try to write a string for. The results will include stable code that lasts a long time and getting SQL injection attack prevention for free.

When an entire program is a string that isn’t executed until runtime, finding errors and debugging becomes increasingly difficult when things go wrong. Codifying your SQL allows you to catch syntax errors before they make it into any of your environments.

A common pitfall is passing strings to Arel methods that expect another Arel node, meaning that you need to wrap it in Arel.SQL or Arel::Nodes.build.quoted.

Staff Software Engineer at Github, Cameron Dutro presented how to avoid using literal strings in your queries, by “arelizing” them. With Arel:

• Familiarity with SQL syntax is not required (only need to know Ruby syntax)
• Ruby syntax checking comes with it
• Compose queries in a step-by-step pattern (chainable)
• Readable (Just Ruby, not SQL-embedded in Ruby)

While talking about what Arel can do for you, he also shares important factors of what Arel doesn’t know.

• Arel doesn’t know about your models
• Arel knows little about your database
• It does know about the queries you’re trying to construct
• It doesn’t store or retrieve data

The factors that Arel doesn’t know about, or doesn’t care about come under ActiveRecord’s to-do list. To sum up, Arel constructs queries and ActiveRecord does everything else. 

If you’re starting out with Arel and would like to get a comprehensive view of it in less than 40 minutes, I’d highly recommend watching Cameron’s whole RailsConf session.

Sequel: When ActiveRecord Isn’t Enough

Aside from comparing Arel with Sequel, for the sake of better understanding, it also makes sense to compare ActiveRecord with Sequel.

There can be times when ActiveRecord may not seem enough when creating more complex queries. This was the case for Ruby-off-Rails evangelist, Janko Marohnić who delivered some interesting points by comparing ActiveRecord and Sequel in his RubyDay 2020 session. We cover his views in this section:

Comparison by Design

ActiveRecord establishes the connection when the connection object is retrieved as part of a query, rather than establishing when the establish_connection method was called. 

Another issue put forward by Janko was that ActiveRecord doesn’t immediately define the column methods on the models. They’re defined when the user initializes a first model instance, making it problematic to define methods on runtime.

Comparing the same case, but now using Sequel, when you call Sequel.connect method, the database connection is established instantly. 

With Sequel, column methods are immediately defined when Sequel model is created.

Comparison by Explicitness

Sequel’s interface and API are far more explicit. ActiveRecord relation objects act as relations and arrays simultaneously. When you load records the second time, it returns cached results, whereas in Sequel it’s the opposite. 

Sequel datasets, which are the equivalent of ActiveRecord relations, are frozen and use a functional style where modification methods return modified copies of the dataset. Executing any method will do the same thing regardless of the number of times it’s called. This compels you to choose where exactly you want to execute the query.

Using Sequel

Some would say that using literal strings is fine in certain cases, which is true, the cases being;

• You’re targetting a single database
• No plans to support another database
• You’re proficient in SQL syntax for all supported databases

A strong point raised by Jeremy Evans was having to use strings placeholders, either with question marks or with named placeholders; for which you will need to remember the order in which to play the arguments. Remembering the order is easy when there are only a few arguments. But this becomes a huge problem for complex cases with many arguments.

That’s where Sequel comes in. By using Sequel’s DSL syntax, the arguments are used directly in the query, resulting in more concise and less redundant code. Here’s a handy Sequel cheatsheet to get you started.

Sequel currently has adapters for ADO, Amalgalite, IBM_DB, JDBC, MySQL, Mysql2, ODBC, Oracle, PostgreSQL, SQLAnywhere, SQLite3, and TinyTDS.

The Grand Finale: Who Wins?

We could go on and on about the similarities and differences between Arel and Sequel, but the question many would ask still remains; “Which one is better?”. 

There are particular instances where Sequel (dare we say) is better than Arel. As mentioned previously, Sequel has far better documentation inclusive of bug tracking and a dedicated discussion forum. 

Arel’s lack of documentation makes it difficult for users to get on-board, not to mention going back and forth between bugs. Moreover, the scattered nature of the information makes it vexing to find exactly what you’re looking for.

Sequel also scores in terms of community support. From its impressive bug tracking on GitHub (0 open issues at the time of writing this blog post. Wow!) to healthy online discussion, Sequel offers massive support to its users. That’s not the case for Arel unfortunately. Of course, there’s a discussion forum for ActiveRecord as well, but digging deeper, you’re not gonna find much within the forum regarding Arel.

An interesting point raised by Monterail’s Principal Engineer, Wojciech Maciejak was the fact that Sequel uses PostGres extended query protocol, making Sequel much faster than ActiveRecord and reduces server-side resource usage. 

On this note, we end the battle of SQL query builders. It’s upto you to decide what works based on project requirements and expectations.