I am working on a Ruby project that accesses a DB using the library Sequel instead of the commonly used Active Record.

Sequel and Active Record are similar, but they also have crucial differences, that you should not overlook to code with confidence and save time.

Most Ruby on Rails (RoR) projects use Active Record (AR), not because of specifics advantages over Sequel, but because RoR uses it by default.

Sequel is often used in non-RoR frameworks since it is generally faster and allocates less memory than AR (see Janko Marohnić's talk for more, check out the resources underneath).

Both libraries provide Object-Relational mapping to map DB tables to Models (Ruby objects) and declare relationships between the tables.

Both libraries have pretty similar capabilities, but their approach is different.

Let's look at how to query the data from a table. They both have a where method, which accepts hashes as params. For example:


Book.where(title: 'The clean coder')

Book.where(year: 2020..2021)

Those method calls are identical for both AR and Sequel, and they return an object that represents an SQL query.

AR returns an ActiveRecord::Relation object, and Sequel returns a Dataset object.

Perfect, so far, no surprises.

The differences are apparent when you want to write more complex queries, and hashes are not expressive enough to describe them.

AR uses one of these approaches:

  • by using SQL fragments:

    Book.where("published_at >= ?", 1990)

    Book.where("title ilike ?", '%clean')
  • by using AREL:

    Book.where(Book.arel_table[:published_at].greq(1990))

    Book.where(Book.arel_table[:title].lower.matches('clean'))

Arel is the layer underneath AR. It transforms an SQL Abstract Syntax Tree into a SQL string.

Arel is not simple, and often the Arel expressions are verbose.

Sequel tackles the generation of complex queries in a different way:

  • by providing expression API:

    Book.where(Sequel[:published_at] >= 1900)

In the example above, Sequel[:published_at] is a column qualifier, and it uses the operator >= instead of the methods greq (used in Arel).

The last command seems to break the Ruby syntax, but in fact >= is a method, and it could be re-written this way:


    Sequel[:published_at].>=(1900)

This strategy makes the expression more readable, with less nesting and parenthesis than Arel.

  • by using virtual row blocks syntax:

    Book.where{stars >= 3}

Sequel also has a virtual row blocks syntax, in which a block is passed to the query method, and behind the scenes, the method_missing is used to instantiate identifiers and function objects.

It is undoubtedly the most readable way to generate SQL in Sequel.

This post wanted only to highlight some differences in how Sequel and AR tackle complex queries.

Here are a few links to deepen the topic.

Resources:

If you want to delve into the differences between Arel and Sequel read the articles by Jeremy Evans, lead developer of the Sequel:

Talks:

The talk that Janko Marohnić gave at Rubyday 2020 is one of the best resources on Sequel vs ActiveRecord: When ActiveRecord is not enough.

An old but comprehensive talk of Jeremy Evans explaining Sequel. It is very dense, with Jeremy speacking at 2x speed: Sequel by Jeremy Evans/

A short and friendly Sequel tutorial by Avdi Grimm