BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News Sequel, The Database Toolkit For Ruby

Sequel, The Database Toolkit For Ruby

This item in japanese

Bookmarks

ActiveRecord is the de facto Rails ORM. Apart from being an alternative to ActiveRecord, Sequel (v2.7.1) offers a comprehensive Ruby toolkit to handle database operations.

- Sequel provides thread safety, connection pooling and a concise DSL for constructing database queries and table schemas.
- Sequel also includes a lightweight but comprehensive ORM layer for mapping records to Ruby objects and handling associated records.
- Sequel supports advanced database features such as prepared statements, bound variables, master/slave configurations, and database sharding.
- Sequel makes it easy to deal with multiple records without having to break your teeth on SQL.
- Sequel currently has adapters for ADO, DB2, DBI, Informix, JDBC, MySQL, ODBC, OpenBase, Oracle, PostgreSQL and SQLite3.

An example of the Sequel DSL:
DB[:countries].filter(:region => 'Middle East').reverse_order(:area).limit(5).avg(:GDP)
Which will be translated to SQL:
SELECT avg(GDP) FROM countries WHERE region = 'Middle East' ORDER BY area DESC LIMIT 5

A quick look at the changelog shows the activity of the project, with new releases announced on a regular basis.

InfoQ had the chance to catch up with Jeremy Evans to talk about the progress of Sequel and how it compares with ActiveRecord.

InfoQ - In a previous interview, Sharon Rosner said he started Sequel because of Rails lack for multithreading and connection pool. With Rails 2.2 supporting those, can this still a valid reason for opting for Sequel?

Jeremy - I haven't used ActiveRecord's new connection pooling features, so I'm not sure how well they compare to Sequel's. Sequel's connection pooling feature has been around since well before I took over maintenance, and I haven't heard any complaints about it, so I'm confident that Sequel's implementation is robust. Time will tell if ActiveRecord's implementation is as well.

What about the other reasons? Is Sequel still more agile than ActiveRecord at handling large datasets and multiple records? Is it still more rubyish than today's ActiveRecord by making it possible to avoid raw SQL as much as possible? Are there other domains where Sequel performs better than ActiveRecord? What about the other way round?

Sequel takes SQL and exposes it at a ruby level. With ActiveRecord, you are using SQL string fragments to compose queries, while you are using plain ruby objects with Sequel. Sequel certainly is a more rubyish database library, and it is possible to write non trivial apps with Sequel without using any SQL strings.
There are some areas in both frameworks that have no equivalent in the other. For example, Sequel has built in support for master/slave databases, database sharding, bound variables, and prepared statements. ActiveRecord doesn't support those (at least not without plugins), but ActiveRecord's schema parser currently has more features than Sequel's, and ActiveRecord has built in support for some association types (e.g. polymorphic associations), that you can duplicate with Sequel, but are not as easy to use with Sequel.
I'm not sure if performance would be a reason to use Sequel over ActiveRecord (or vice versa). One area where ActiveRecord performs better is if you are selecting a lot of columns that you aren't using. Sequel typecasts data when it is retrieved from the database, while ActiveRecord delays typecasting until use. This isn't a major issue unless you are using dates, as ruby's date class is very slow to instantiate. If you only select columns you are using, Sequel should be faster. If this could be simplified into a single statement, Sequel performs worse if you don't optimize, and better if you do.

While Sequel supports numerous databases, a lot of performance improvements are done targeting PostgreSQL or MySQL; Are you confident about performance with other databases?

I only use PostgreSQL in my production applications, so most of the database-specific optimizations I make tend to be related to PostgreSQL, since that is what benefits my applications the most.  I use SQLite for testing in some of my applications, but the only time I use MySQL is to test Sequel with it.  All other databases that Sequel works with are untested by me.
To answer your question directly, no, I'm not confident, as I have no experience using Sequel with other databases. I can't recall any performance related complaints, however.

This interview took place 10 months ago while Sequel was in its 0.5 version. Since then you took lead of the project. Which new features introduced up to v2.7.1 are the more significant to you? Are there any new killing features set in the roadmap yet? Do you have many feedback from succesful Rails/Merb implementations using Sequel?

I didn't start using Sequel till 1.2, so I don't have knowledge of changes before then. Since 1.2, the most significant changes and new features are:

1) Magic Removal: Before 2.0, Sequel's implementation relied on a lot of magic. There were many significant internal changes to make the code cleaner and easier to work with between 1.5 and 2.0.

2) Documentation: When I took over maintenance, much of Sequel was undocumented. Now, all of Sequel has at least basic documentation, except for some of the adapters.

3) Associations: Sequel has the most powerful associations of any ruby ORM. While ActiveRecord makes implementing certain association types less work (e.g. polymorphic associations), Sequel makes it possible to implement associations that ActiveRecord does not allow, by exposing more of the inner workings to user and allowing for finer grained control. 

4) Dataset Graphing: This allows you graph two arbitrary datasets (tables) together and have the result split into into the component tables.  The use case for this is when you want to join two tables that have identically named columns, but you don't want to alias the columns and split the result set into component tables manually. Graphing handles all of that for you. 

5) Expression Filter DSL: Before, for advanced filtering, Sequel used ParseTree, which had suboptimal performance and didn't work on JRuby or Ruby 1.9.  The expression filter DSL is very simple use, performs better, and works on all popular Ruby implementations. This DSL is what makes code like: "dataset.filter(:number > 2)" work. 

6) JRuby and Ruby 1.9 support: The three most popular ruby implementations now all support Sequel. 

7) Prepared Statements/Bound Variables: Sequel allows the use of real database prepared statements and bound variables on some databases, with emulated support available on all other databases. Prepared statements can be significantly faster than regular queries depending on the database being used. 

8) Master/Slave Databases and Sharding: Sequel has built in support for both master/slave (read-only) databases, as well as database sharding. 

As for the future roadmap, there isn't much on it. I'm open to ideas, but it currently fills all of my needs.  I'm currently working on fixing some corner cases, but I don't have any planned features. 
I know there are quite a few Rails, Merb, Ramaze and Sinatra implementations using Sequel. Personally, I use Rails or Ramaze with Sequel in my applications.

Rate this Article

Adoption
Style

BT