Why you should stop using ActiveRecord and start using Sequel

Note: Please be aware that we are talking about Sequel and not Squeel, which is a different library on top of ActiveRecord.

At first glance of the documentation, Sequel might strike you as a fairly innocent and basic ORM. Don’t let that fool you! Sequel is very powerful and supports virtually everything ActiveRecord does, and much more.

Lets get a few things out of the way first. Sequel is based on the active record pattern, so you will find it very similar to ActiveRecord (the ORM) in many ways. It was not designed specifically with Rails in mind, and unfortunately does not integrate with Rails by itself – which I consider an unfortunate downside. However fear not as there is a sequel-rails gem which will take care of that, and is actively maintained.

Why?

I’ll explain a few differences that I find particularly nice and useful below, but here is a quick list of a few things I like about Sequel:

  • Plays nice with threads, unlike ActiveRecord which has problems I discuss at the end of the post.
  • Allows you to avoid SQL code fragments 99% of the time, if you want and without screwing with core Ruby classes.
  • Modular design with plugins and extensions.
  • Foreign key constraint support in migrations out of the box.
  • Left join support. Heh.
  • More powerful association preloading.

A lot of it just has to do with the general feel of the ORM though. It’s hard to put the finger on it but Sequel just seems to have a more solid foundation to me.

Documentation

First I have to mention that while the documentation of Sequel is good, the organisation can be a bit problematic when you are trying to figure out how to do something. If you can’t find a way to do what you want, you can try to find help on IRC (#sequel on FreeNode). If you already know ActiveRecord, here is a good overview of how to do things.

The cheat sheet is also a useful resource.

Querying

Let’s just get this out of the way first – querying in Sequel is immensely more powerful compared to ActiveRecord. You can do almost anything without writing any SQL.

The basic querying syntax is very similar to ActiveRecord. I should actually probably put that the other way around since I believe Sequel had this syntax even before Rails 3 came around. Keep in mind that querying is just one aspect of an ORM, and is not by any means the only reason I like Sequel.

The first thing to note is that where in Sequel has two aliases, filter and more importantly, find. This will probably be annoying for you while you are getting used to Sequel. The analogy to find in Sequel is to use square brackets.

That’s a bit weird but when you get used to it, it doesn’t really matter. Anyway, the where syntax is in its core the same as in ActiveRecord.

However, you do get more power with Sequel. Note that the non-SQL-fragment syntax is completely optional and you don’t need to use it if you don’t like it. You can continue using the same “prepared statement” style or hashes as with ActiveRecord, if you wish.

You might say that it takes more code in certain cases, but at least you don’t need to write SQL fragments, which is just ugly. You can always do that though, if you want. I think the :users__name part is particularly nice and useful.

Sequel used to, by default, monkey-patch certain core Ruby classes like Symbol to add functionality for querying – similar to Squeel for ActiveRecord. However this was turned off in later versions and deprecated. I just wanted to mention this as it shows an example of a good design decision.

As if all that wasn’t enough, there is also a block version of where that works similarly to that of Squeel. You should be using the latter version with a block parameter, or you might encounter hard-to-debug problems if you have local variables with the same name as a field you are referencing in the block.

Subqueries

Same as in ActiveRecord, it seems. Although in ActiveRecord it’s not a widely known feature.

Pluck

You might be sad when you find out there is no pluck in Sequel. Well, fear not, it just has a more sensible name – select_map. Which is really what pluck does, it selects and then maps. However in Sequel select_map was never limited to a single column like it was in ActiveRecord until Rails 4. Additionally, Sequel has select_hash, which will obviously give you a hash of the selected columns. You can also use naked.

select_all

Sequel has a useful method called select_all, which will simply make the select part of the query select all columns of a certain table:

Artist.select_all(:artists) #=> SELECT artists.* FROM artists

Can be useful when doing joins.

Order

In Sequel, you have order, but you also have reverse_order. Just continuing the trend of avoiding SQL code fragments. But note that order in Sequel will override all previous orders. If you don’t want that you’ll have to use order_append or order_prepend. This is again a demonstration of good design compared to ActiveRecord, which has random rules if orders are appended or prepended (they just changed it spontaneously in Rails 4). In Sequel there is no confusion about that.

If you are appending orders you might want to know that you can also order descending like this: Article.order(Sequel.desc(:name)). It’s not possible to use a SQL code fragment, so if you are porting code from ActiveRecord, you’ll have to fix your orders.

Eager loading

For eager loading, again, Sequel gives control to you. While ActiveRecord will decide for you if it will eager load by adding columns to the SELECT of your SQL, or doing an additional query, Sequel lets you choose yourself, which also makes it clear which of the two ways is being used. If you want to do eager loading in the same query, use eager_graph, if you want to eager load with an additional query, use eager. Using eager is generally recommended unless you need to filter by the other table. It’s usually faster too (even though that might be counter-intuitive to you).

You can also provide additional options to eager_graph to control which kind of join is made and provide extra join conditions.

LEFT JOIN

Don’t you just hate it when you need to do a left join in ActiveRecord? That has to be one of the most annoying things ever. Not in Sequel, though.

I do have to admit that it sucks a little that you will have to explicitly tell Sequel the keys to use for the join though (for the normal inner join as well). But then again, it does give you more power as you can join any way you want.

Plugins and extensions

Sequel is more modular than ActiveRecord. Beyond the basic functionality, you can enable plugins and extensions for extra features. There are a lot of these that come with Sequel, and all that I mention later on are already there for you.

If you are using sequel-rails, I recommend you use the new config.sequel.after_connect configuration option in your application.rb like so:

Note that you can also enable plugins on a per-model basis if you want.

created_at and updated_at

Sequel doesn’t update created_at/updated_at by default. But you can easily enable it by using the timestamps plugin.

Sequel::Model.plugin :timestamps, update_on_create: true

Associations

You might find querying on associations is a little too basic in Sequel (associations return simple Arrays by default). That’s just because you need to enable the association_proxies plugin.

Sequel::Model.plugin :association_proxies

Postgres hstore and arrays

While hstore and arrays are the new cool of Rails 4, it’s nothing new in the Sequel world. If you need it, just use the pg_array and pg_hstore extensions. Again, just add the following to your config.sequel.after_connect:

Sequel::Model.db.extension :pg_array
Sequel::Model.db.extension :pg_hstore

Pagination

Pagination support is built in to Sequel. Simply use the pagination extension. Simple and effective!

Sequel shorts

You can also check out some cool tricks with Sequel right here on my blog.

Threading

I first started looking into Sequel when I had problems with ActiveRecord and threading. While this might not be of importance to you, it is nice to know that you are set up to use threads if you need to, without having to fear about your connection pool depleting randomly.

The advantage of Sequel in the threading area is that it takes a slightly different approach than ActiveRecord to connection pooling, but it makes all the difference. In ActiveRecord, you can use ActiveRecord::Base.connection_pool.with_connection in combination with a block to checkout a connection, do some queries and then check the connection back into the pool. This is fine – the problem is when you let ActiveRecord manage the pool for you. In that case, when you do a query, ActiveRecord will automatically checkout a connection, but when the connection is checked back in is not up to you anymore, and here is the potential problem, depending on what your threads do and how long they live. It also means that a thread is locking up a connection even when it’s not using it, which is kind of lame too. You can argue that you can just use ActiveRecord::Base.connection_pool.with_connection in such cases, but it’s not hard to miss it somewhere (perhaps where it’s not obvious that a DB query is being made), and then you’re screwed. It’s close to impossible to find out where you forgot to manage the connection pool by yourself.

In Sequel, however, a connection is only checked out at the moment it is needed (i.e. just before performing an SQL query), and then checked back in, so it is immediately available to other threads again, even if the current thread will need it in the future. This is all done in a thread-safe manner. Obviously this makes it so you can usually have a smaller connection pool when doing threading. It is worth to mention that this approach is slightly slower / has more overhead than the ActiveRecord approach, but I think the benefits outweigh this by a large margin. Also, it is possible to turn this feature off if you are not using threads very easily by setting Sequel.single_threaded to true. The biggest benefit here is that you need not worry about threads locking up connections when they don’t need them.

Conclusion

As you can see, Sequel is really powerful. It gives us many tools that are simply not available in ActiveRecord. We can also usually avoid writing any SQL code, even though it may cost us a little bit more Ruby code, but nothing to write home about. It’s a good library for the advanced user, as you can customise it to your needs much more than ActiveRecord.

Add to all these features a sane support for threading, it has definitely become my ORM of choice. What I covered here is just a small preview of what Sequel has to offer you – so start using it!

11 thoughts on “Why you should stop using ActiveRecord and start using Sequel

    • Thanks. Making the switch is a little scary since you leave the comfort of the default Rails stack, but for me it has really been worth it.

  1. To me “users__name” and wrapping expressions is anything but nice. To each his own I guess.

    It’s always interesting to learn about what others prefer. I have to say I rather like SQL and the mix of AR and SQL never bothers me… but I’ve always loved SQL so I’m biased :)

    • There are cases where it doesn’t really matter, but there are also cases when it is really useful to avoid SQL. One example is when you have a value in a variable that can be NULL or a value. In such cases it is better to avoid SQL so the ORM can generate the correct expression (e.g. IS NULL vs. = value). When you have code split into several methods that interact with scopes, it is also useful in certain cases to have more power. Also combinations where you have NOT and OR are usually easier to handle in Sequel without having to worry about NULL cases and so on. So yeah, it’s easier to just write SQL, but it’s harder to do significant refactors and it’s harder to let the ORM handle things for you. Do not forget that Sequel does allow you to simply use SQL fragments.

  2. I agree, but it’s hard to do in an application that already uses ActiveRecord. Some time ago I spent 1-2 hours looking into whether sequel and ActiveRecord could share the same connection, so that I could gradually migrate parts of the app but unfortunately I didn’t find a way to make it work.

    • I don’t think you can share connections, but you can have 2 connection pools (one for each library). Although best idea is just to choose one and stick with it.

      • Yeah I wanted to hack something together that makes sequel and ActiveRecord share the same connection. Because that way I could keep my app running on ActiveRecord and begin migrating complex queries to Sequel one by one.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s