Active Record or Sequel: Which Best Fits The Needs of Your Ruby App?
Tags: ruby
Published: 2024-03-20 14:00:00

When it comes to choosing an object-relational mapping (ORM) library for your Ruby application, Active Record is usually the favorite choice. It's an easy-to-use ORM library that allows for lots of data wrangling without resorting to SQL. All the same, you might wonder: "Is Active Record the only Ruby ORM library I can use?"

In this article, we'll compare some Active Record features to its lesser-known but powerful cousin, Sequel. There are too many points of comparison to cover everything (such as how each library handles _CRUD_ operations, table joins, associations, database replication and sharding, etc). Instead, we'll scratch the surface of a few database operations — namely, filtering, database locking, and transactions — and show how each library handles them.

By the end, you'll have a better idea about how to use each library's strengths to the fullest.

But first, let's learn what object-relational mapping is all about.

## What Is Object-Relational Mapping (ORM)?

Object-relational mapping is a way to pass data between an application and its data store, usually a relational database like SQLite, PostgreSQL, or MySQL. Without ORM techniques, you would be forced to write raw SQL queries for all operations to get data in or out of your Ruby app's database. But with an ORM, you get access to class objects and methods, making it much easier to read/write data to your database without using raw SQL.

Let's introduce the two ORM libraries we're looking at, starting with Active Record, and then moving on to Sequel.

## Introducing Active Record and Sequel for Ruby

Let's quickly examine what Active Record and Sequel can do.

### Active Record

[Active Record](https://www.rubydoc.info/gems/activerecord) is the most well-known Ruby object-relational mapping library. It defines class objects that are directly mapped to tables in a database and provides convenient methods for manipulating data using these classes.

The library was first described by the pioneering computer scientist Martin Fowler in his book _Patterns of Enterprise Architecture_. It comes bundled with Rails, which could explain why it's more popular than Sequel. That said, Active Record is suitable for:

- Object-oriented database operations
- Performing validations on models before persisting them in the database
- Representing models, their data, and relationships

### Sequel

On the other hand, [Sequel](https://github.com/jeremyevans/sequel) is the lesser-known of the two. It includes a powerful DSL for manipulating data within a variety of databases.

Sequel can:

- Represent database records as Ruby objects
- Handle simple and even complex associations in a concise way
- Do database sharding, database replications, and more

One standout feature that makes Sequel so powerful is its [_dataset_](https://sequel.jeremyevans.net/rdoc/files/doc/dataset_basics_rdoc.html). A dataset is Sequel's way of directly representing an SQL query and making it readily available for a developer to use.

Of course, a lot more could be said of each of these ORM libraries, but we'll leave it at that. Let's dive straight into our first example to see how Active Record and Sequel deal with record filtering.

## Filtering Records

Filtering data is the process of refining data results by applying specific filters. By using filters, you can fetch exactly what you want from a dataset. Let's see how each ORM library achieves this, starting with Active Record.

### Using Active Record

Let's say you want to filter data to return all orders of products priced under $20. How would you achieve this using Active Record?

You can apply _conditions_ to your Active Record query. Active Record has many methods that act as conditions for filtering data, including `where`, `limit`, and `where.not`. While it's not possible to get into all of the conditions in this article, we'll sample a few and see how they stack up against Sequel's filter methods.

For Active Record, you'll first need to set up the proper model associations like so:

```ruby
# models/order.rb
class Order < ActiveRecord::Base
  belongs_to :product
end
```

Then the Product model:

```ruby
# models/product.rb

class Product < ActiveRecord::Base
  has_many :orders
end
```

Now for the Active Record query:

```shell
product_orders_under_20 = Order.joins(:product).where('products.price < ?', 20)
```

In this example, we've used a _joins_ to include results from the products table and then applied a _where_ condition to filter for the products that fit our desired criteria.

<Banner lang="ruby" />

### Using Sequel

Sequel's use of SQL representations in the form of datasets makes filtering a breeze. Let's consider how Sequel would handle the example we used for Active Record.

Just like we did with Active Record, you need to define some models first:

```ruby
# models/product.rb

class Product < Sequel::Model
  one_to_many :orders
end
```

The `Order` model:

```ruby
# models/order.rb

class Order < Sequel::Model
  many_to_one :product
end
```

And this is how you'd run the query with Sequel:

```shell
product_orders_under_20 = DB[:orders].join(:products, id: :product_id).where { price < 20 }
```

Next up, let's look at database locks.

## Database Locking

Imagine you have a content management system where a user with the editor role can edit other users' posts. Let's say there are several editors in the organization and it just so happens that two editors end up working on the same post at the same time. Such a scenario raises several questions, such as which editor's work is saved, assuming both commit changes at the same time.

This challenge is solved through the use of database locks.

### Using Active Record

Active Record allows for two types of locks: optimistic locks and pessimistic locks. In optimistic locking, you need to include a version column in the respective database table. This is so that a record is checked against a matching version to ensure that another user or process doesn't modify it. If it is, an error is raised.

The example below shows how optimistic locking happens:

```shell
invoice1 = Invoice.find(1)
invoice2 = Invoice.find(1)

invoice1.total_amount = 100
invoice1.save # 100

invoice2.total_amount = 500
invoice2.save # raises ActiveRecord::StaleObjectError
```

When it comes to pessimistic locks, Active Record can implement them at the row level if your database allows for it. There are several ways of achieving pessimistic locking with Active Record, but we won't go into the details now. Instead, the example below should give you an idea of how this is done:

```shell
Invoice.lock.find(1) # applies a pessimistic lock on the record so that an UPDATE is done
```

### Using Sequel

On the other hand, Sequel implements optimistic locking through a [plugin](https://sequel.jeremyevans.net/plugins.html).

You first add the plugin to the model like so:

```ruby
# invoice.rb

class Invoice < Sequel::Model
  plugin :optimistic_locking
end
```

Then test this with two concurrent queries:

```shell
invoice1 = Invoice.find(1)
invoice2 = Invoice.find(1)

invoice1.update(status: 'Sent') # record will be updated

invoice2.update(status: 'Void') # raises Sequel::NoExistingObject error
```

For pessimistic locking, Sequel implements the lock when the records (or record) are fetched using the following methods: `lock_style`, `lock`, and `for_update`. Again, we won't provide details on this. You can read [the documentation](https://sequel.jeremyevans.net/documentation.html) or [Jeremy Evans' Pessimistic Locking blog post](https://sequel.jeremyevans.net/2010/03/09/pessimistic-locking.html) to learn more.

Here's an example using the `for_update` method to implement a pessimistic lock:

```shell
dataset = DB[:invoices]
invoice1 = dataset.where(id: 1).for_update # will apply a pessimistic lock on invoice1
```

Next, let's take a look at database transactions.

## Database Transactions

To understand the concept of database transactions, consider a peer-to-peer lending app where Person A sends some money to Person B who can then withdraw the money and use it. Here, the app needs to first withdraw money from Person A's account and then send that amount over to Person B.

In such a scenario, you wouldn't want the app to send money to Person B if there's an error with the withdrawal from Person A's account. The ideal solution would be to wrap the two processes in a "database transaction", so if one of the processes fails (usually the first one), then the database can be rolled back to its original state before any of the transactions happened.

### Using Active Record

With Active Record, implementing these queries without the use of database transactions might look something like this:

```shell
person_a = Person.find_by(name: 'A')
person_b = Person.find_by(name: 'B')

withdrawal = person_a.accounts.first.withdraw(250)
person_b.accounts.first.account_total # 100

person_b.find(B).accounts.first.deposit(withdrawal)
person_b.accounts.first.account_total # 450
```

Although this could work, you also need to account for something going wrong with the withdrawal and take steps to take care of it. Let's now use a database transaction to improve this example:

```shell
a_account = Person.find_by(name: 'A').accounts.first
b_account = Person.find_by(name: 'B').accounts.first

Account.transaction do
  a_account.withdraw(250)
  b_account.deposit(250)
end
```

Here, we use `ActiveRecord::Transactions` to wrap the two queries in a transaction so that the database can be rolled back if something goes wrong.

What about Sequel?

### Using Sequel

Just like in Active Record, if you want to implement transactions in Sequel, you'll need to be explicit (although there are situations where this is enabled by default).

With the same example we used for Active Record, this is how to wrap our queries using a transaction in Sequel:

```shell
DB.transaction do
  Person.first(name: "A").accounts.first.withdraw(250)
  Person.first(name: "B").accounts.first.deposit(250)
end
```

From this example, we see that Sequel's dataset feature allows for complex transactional queries to be built using a few commands.

## When To Choose Active Record Vs. Sequel for Ruby

When should you use Active Record, and when might Sequel be a better choice? Here's a quick summary of each:

- Active Record is very beginner-friendly, in that, it conveniently hides complex SQL queries under the hood. On the other hand, if you are an advanced user who's comfortable using SQL, then this may be a bit limiting. Additionally, if you're looking for more powerful features like proxying queries, you'll likely need to add complementary gems on top of Active Record.
- Sequel is very powerful, especially for those who master its dataset feature. That said, you can expect a steeper learning curve compared to Active Record. If you get through that, though, you will be rewarded with a flexible ORM that is perfect for almost anything you could throw at it.

## Wrapping Up

In this article, we've compared two Ruby object-relational mapping libraries, Active Record and Sequel. We explored how each handles filtering, database locking, and transactions. Finally, we touched on when you might want to use one ORM over the other.

Until next time, happy coding!

**P.S. If you'd like to read Ruby Magic posts as soon as they get off the press, [subscribe to our Ruby Magic newsletter and never miss a single post](https://blog.appsignal.com/ruby-magic)!**

**P.P.S. Did you know that AppSignal offers an Active Record integration? [Find out more](https://www.appsignal.com/ruby/active-record-monitoring).**