Active Record or Sequel: Which Best Fits The Needs of Your Ruby App?

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 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 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. 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:

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

Then the Product model:

# models/product.rb

class Product < ActiveRecord::Base
  has_many :orders
end

Now for the Active Record query:

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.

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:

# models/product.rb

class Product < Sequel::Model
  one_to_many :orders
end

The Order model:

# models/order.rb

class Order < Sequel::Model
  many_to_one :product
end

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

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:

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:

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.

You first add the plugin to the model like so:

# invoice.rb

class Invoice < Sequel::Model
  plugin :optimistic_locking
end

Then test this with two concurrent queries:

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 or Jeremy Evans' Pessimistic Locking blog post to learn more.

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

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:

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:

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:

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!

P.P.S. Did you know that AppSignal offers an Active Record integration? Find out more.