I've been using Sequel in production since 2012, April and I still think this is the best decision I've made so far for the whole project lifetime.

I had played with it sometimes in the past years, when Arel hasn't been added to ActiveRecord yet and I found it amazing on how it supported lazy queries. Then I spent a few years working with Java, Groovy and Grails when I changed my job in 2009, but kept reading about Ruby (and Rails) news until I found out that AR has added support for lazy queries through Arel, when Rails 3 was released. Then I assumed AR would be a better fit than Sequel since it's already integrated with Rails and lots of great plug-ins would support it better.

I was plain wrong! In 2011 I changed my job again to work on another Grails application. After finding a bug with no fix or workaround available I decided to create a Rails application to forward the affected requests to. So, in April of 2012 I started to create my Rails app and its models using ActiveRecord. A week later I moved all models from ActiveRecord to Sequel and have been happy since then.

Writing some queries with ActiveRecord was still a pain while Sequel made it was a joy to work with. The following sections will go to each topic I find Sequel is an improvement over AR.

Database pooling implementation

These days I decided to recreate a few models with ActiveRecord so that we could use an admin interface with the activeadmin gem, since it doesn't support Sequel. After a few requests to the admin interface it stopped responding with timeout errors.

Then I decided to write some code to test my suspicions and run it in the console:

This yielded an timeout error in the last run. This didn't happen with my Sequel models:

Notice that I don't even need the join call inside the block for it to work since the count call is so much faster than the timeout settings.

The curious thing is that I didn't get any timeout errors when using activeadmin with a regular Rails application, so I investigated what was so special on it that I could access the admin interface as many time I wanted and it wouldn't ever timeout.

I knew the main difference between my application and a regular Rails application is that I only required active_record, while Rails will require active_record/railtie. So I decided to take a look at its content and found this:

So I found that AR was tricking here delegating the pool management to the web layer by always clearing active connections from the pool after the request was processed in that middle-ware:

Despite the name clear_active_connections! it seems to actually only close and checkin back to the pool the single current connection, whose id is stored in a thread local variable, from my understanding after taking a glance over AR pool management source code. That means that if the request main thread spawns a new thread any connection checked out in the new thread won't be automatically collected by Rails and your application would start to throw timeout exceptions when waiting for a connection to be available in the pool, for no obvious reason, unless you understand how the connection pool works in AR and how it's integrated in Rails. Here's an example:

Try running this controller using a single server process 6 times (assuming the pool size is the default of 5 connections). This should fail:

That means the user is responsible for closing the connection, checking it in back to the pool before the thread is terminated. This wouldn't be a concern if Post was a Sequel model.

Then I recalled this article from Aaron Patterson.

Update note: it seems this specific case will be fixed in ActiveRecord 4.2 due to the automatic connection check-in upon dead threads strategy implemented in pull request #14360.

Ability to join the same table multiple times with different aliases

The main reason I left AR for Sequel was the need for joining the same table multiple times with different aliases for each joined table. Take a look at this snippet from this sample project:

I still don't know how to write such query using AR. If you do, please comment on how to do so without resorting to plain SQL or Arel, which is considered an internal implementation detail of AR for which the API could change anytime even for a patch release.

as and named are not part of Sequel::Model, but implemented as a plug-in. See next section.

Built-in plugin support for models

Although it's not a strong reason to move to Sequel, since it's easily implemented with regular Ruby modules in AR, it's nice to have such a built-in API for extending models:

Support for composite primary keys

Sequel does support composite primary keys, which are specially useful for join tables, while ActiveRecord requires a unique column as the primary key.

No need to monkey patch it

It seems lots of people don't find AR's API good enough because they keep monkey patching it all the time. I really try very hard to avoid any dependency on a library that relies on monkey patching something, specially AR, since it's always changing its internal implementation.

So, with all major and minor Rails release we often find gems that stopped working due to such internal changes. For example, activeadmin stopped working with Rails 4.1.0.beta1 release even if the public AR public API remained the same.

It takes so much time to work on code that relies on monkey patching AR, that Ernie Miller, after several years trying to provide improvements over AR gave up.

Not surprisingly, one of the gems he used to maintain, polyamorous, was the reason why activeadmin stopped working with latest Rails release.

I never felt the need for monkey patching Sequel's classes.

Documentation

Sequel's documentation is awesome! That was the first thing I noticed when I moved from AR to Sequel. Arel is considered internal implementation detail and AR users are not supposed to rely on Arel's API, which makes AR's API much more limited besides being badly documented.

Support

Sequel's mailing list has awesome support from Jeremy Evans, the gem maintainer. As for AR, there's no dedicated list for it and one has to subscribe to a Rails related list to discuss AR stuff.

Separation of concerns

I like to keep the concerns separately and I can't think about why an ORM solution should be attached to a web framework implementation. If Rails has great features in a new release with regards to action handling, I shouldn't be forced to upgrade the ORM library at the same time I upgrade Rails.

Also, if a security fix affects AR only, why should a new Rails version be released?

Often AR will introduce incompatibilities in new versions, while I haven't seen this happening with Sequel yet for the features I use. Also, I'm free to upgrade either Rails or Sequel any time.

Of course, this doesn't apply to ORM solutions only, but it's also valid for mailing handling but this is another topic, so I'll focus on Sequel vs AR comparison only.

Sequel can also be useful without models

Sometimes it doesn't make sense to create a model for each table. Sequel's database object allows you to easily access any table directly while still supporting all dataset methods like you'd do with Sequel models:

Philosophy

AR's philosophy is to delegate constraints to the application model's layer, while Sequel prefers to implement all constraints in the database level, when possible/viable. I've always agreed that we should enforce all constraints in the database level. But this isn't common among most AR users. AR migrations doesn't make it easier to create a foreign key properly using its DSL, for example and treat them as second-class citizen, as opposed to Sequel's philosophy.

The only RDBMS database solution I currently use is PostgreSQL and I really want to use several features that are only supported by PostgreSQL. Sequel's PG adapter allows me to use those features if I want to, even knowing that it won't work for other database vendors.

This includes recursive transactions through save-points, options to drop temp table on commit and so on.

Another example: AR 4.1.0.beta1 introduced support for enums, in a database independent way.

I'd much prefer to use PostgreSQL's enum type for things like that, which comes with database-side built-in validations/constraints.

Also, although you can manage association cascades in the application-side using this plugin with Sequel, usually you'd be advised to perform such cascade operations in the database level when creating the foreign keys, for instance. Also, when a database trigger better takes care of an after/before hook than an application's code, you should not be afraid of getting advantage of those.

Faster testing when using factories

With PostgreSQL feature of using save-points in transactions, I can set-up RSpec to allow transactional before/after(:all) blocks in addition to the before/after(:each) ones.

This allows me to save quite some time when I can create several database records in a context which will then be shared among several examples, instead of recreating them every-time.

RSpec's support for this is not good (like having a let global variant over the context) but it's not hard to get this set-up working in a good enough way, speeding up my test suite a lot.

And it's pretty easy to use Sequel's core support for nested transactions so that I can be sure that the database state will be always consistent before each example is run.

Migrations

I strongly believe a database's schema change should be handled by a separate project, instead of inside an application using the database. More applications may use the same database at some point and it makes sense that managing your database should be handled by a separate application.

I still don't have a favorite migrations solutions as each of them have their pros and drawbacks. I'm still using AR's migration for historical reasons, as I used the standalone_migrations gem in a separate project even when my application was written only in Grails and the Rails app didn't exist yet. Since standalone_migrations only supports AR 3.x branch, and I was interested in some features from AR 4, I created another gem, called active_record_migrations to be able to use AR 4 migrations support in stand-alone mode.

DSL

I much prefer the Sequel's DSL for writing the migrations as it supports more things in an easier way than AR'S migrations. Also, I'm allowed to use any dataset methods from an migration, instead of having to write everything not supported by the DSL as plain SQL queries.

On the other side, AR, since version 4, allows us to have an reversible block inside a change method which can be quite useful.

Tooling

AR provides a good migration generator, which lacks on Sequel and can be very helpful when creating new migrations.

Performance

I didn't create any specific performance tests to compare both ORM solutions but I do remember that my specs run much faster when I migrated from AR to Sequel and I've also heard from other people that Sequel is faster for most use cases, in MRI at least.

Query DSL

I really like to have control over the generated SQL and a good ORM solution for me is one that will allow me to have better control over it. That's why I don't like the Hibernate's HQL language.

The database should be your friend and if it supports some functions or syntax that would help you why not use them?

Sequel allows me to use nearly all features available through its DSL from my database vendor of choice: PostgreSQL. It also provides me easy access and documentation to use all kind of stuff I can do with plain SQL like "ilike" expressions, sub-queries, nested transactions, import data from file, recursive queries, Common Table Expressions (WITH queries) and so on.

Why not using straight SQL instead of some ORM when cross-database vendors is not an issue?

First, I'd like to say that most of Sequel DSL actually supports multiple database vendors.

But I only find that useful if you're writing some kind of plug-in or library that should not depend on a single database vendor. But that's not the case for general use applications.

Once you opt for some database vendor in your application, you shouldn't have to worry about supporting other database vendors.

So, someone might ask why using any ORM solution if you're fine with writing plain SQL?

There are many reasons for that. First, most plug-ins expect some Ruby interface to deal with, instead of SQL. This is the case with FactoryGirl, Devise and so on. But this is not the main reason.

An ORM provides lots of goodies, like an easy-to-use API to create and update records, automatic typecasting, creating transactions and much more. But even this is not the main reason for me to prefer an ORM over plain SQL.

The main reason for me is the ability to easily compose a query in some way that is easy to read and maintain, specially when parts of the query depend on the user requesting it or some controller's param. It's great that you can change some query on the fly, like this:

Sequel's drawbacks

When a generic query is performed, Sequel will convert any returned rows as hashes with the column names as keys converted to symbols. This may be a problem if you generate the queries dynamically and alias them based on some table's id that depend on the user input. If you have enough ids being queried, Sequel may create lots of symbols that will never be garbage collected.

The lack of migration generators built-in for Sequel migrations makes the creation of new migrations a less than ideal task. You may create some custom rake task to aid with migration creations and it shouldn't be complicated but having that support built into the Sequel core would certainly help.

The main drawback of Sequel is certainly lack of native support of other great gems like Devise, ActiveAdmin and Rails itself. Quite some useful Rails plug-ins will only integrate with ActiveRecord.

Overall feeling

Most of my server-side tasks involve querying data from an RDMBS database and serving JSON representations to the client-side API. So, an ORM solution is a key library for me.

And I couldn't be happier with all goodness I get from Sequel, which gets out of my way when querying the database in contrast with ActiveRecord, when I used to spend a lot of time trying to figure out whether some kind of query was possible at all.

Thanks, Jeremy Evans, for maintaining such a great library and being so responsive in the mailing list! I really appreciate your efforts, documentation and Sequel itself.

Also, thank you for kindly reviewing this article, providing insightful improvements over it.

Finally, if you're interested on getting started with Sequel in a Rails application, I've published another article on the subject on April, 2012.