Sequel Interview with Jeremy Evans

With the recent release of Sequel 2.9.0, I've finally taken the time to interview Jeremy Evans, the project's maintainer. Back in Jan 2008, I interview Sharon Rosner (the former maintainer). Sequel has come a long way since then, so it's about time I revisited the project.

---

You took over Sequel from Sharon Rosner almost a year ago. How and why did you end up holding the reins on this project?

Jeremy My first experience with Sequel was with Sequel 1.2, in February of 2008. I was looking for an additional ORM to support in my Scaffolding Extensions project, and had already added support for ActiveRecord and DataMapper. At the time, Sequel didn't really have support for model associations, and the recommendation was to just implement your own methods to get associated objects.

I worked on a simple associations implementation for Sequel that was only around 60 lines and handled the three main association types (many_to_one, one_to_many, many_to_many) with full reflection support. I posted on the Sequel mailing list with it and Sharon liked it. It became the basis for the association support Sequel introduced in 1.3, and I was given subversion commit rights.

In the middle of March 2008, Sharon emailed four of the developers with subversion commit rights and asked if we could assume leadership of Sequel, since he was planning to move on from programming and do something completely different. I was one of the two developers that responded, and initially I was only going to be responsible for sequel_model. I ended up taking over maintenance of sequel_core a couple months afterward, since the other developer didn't have enough time to maintain it.

With Merb being merged into Rails, do you see a future for Sequel in Rails 3.0?

Jeremy Yes. I'm guessing the merb_sequel plugin will be ported over to Rails 3.0.

It's fairly easy to use Sequel in Rails already, just by requiring it and setting up a database connection with Sequel.connect. I have 5 Rails projects that use Sequel in this manner.

It's good to have people using the projects they're working on. What kinds of changes have you made to Sequel based on the way you use it?

Jeremy The association code was designed to be easy to use by my Scaffolding Extensions project. The eager loading code was designed to be fairly similar in usage to ActiveRecord's, making it easy for me to convert projects from ActiveRecord to Sequel.

On my largest Rails project, I often had cases where associated objects needed to refer back to their parent objects:

 artist.albums.each{|albums| p album.artist}

That causes issues because ActiveRecord will do a query for each album to get the artist. I originally handled this in ActiveRecord by doing something like:

 Artist.has_many :albums, :include=>:artist

I never liked this way of doing things, so I changed Sequel's association implementation so that the parent association is cached (the ActiveRecord parental_control plugin does something similar, I hear).

I only add things to Sequel itself if they are generic and not tied to any specific implementation. The most recent example of this is support I added to Sequel for Giftsmas. Giftsmas runs on PostgreSQL and uses triggers to handle some constraints. I added {create,drop}_{language,function,trigger} methods to the PostgreSQL adapter, which are generic enough they can be used by any PostgreSQL user. I then created the sequel_postgresql_triggers project, which has specific implementations of some common column types (counter/sum cache columns, immutable columns, timestamp columns). Giftsmas uses sequel_postgresql_triggers in it's migration to set up the database.

I didn't add the code in sequel_postgresql_triggers to Sequel itself because they are specific implementations, and there are other (perhaps better) ways of doing the same thing.

What kind of tools are you using to help with code quality — test coverage, complexity, refactoring, etc.?

Jeremy I test coverage before every release, anything less than 100% gets fixed. 100% code coverage means nothing, but less than 100% code coverage means something.

I don't use any IDE refactoring support (I code in vim and SciTE depending on the situation). I don't use any ABC metrics to measure code complexity. There are parts of Sequel that could definitely do with refactoring, but I generally wait to refactor until I'm going do make changes to the code that refactoring will help. I don't refactor for it's own sake.

I use TDD when the problem space is known and I know what outputs I want for each input. I'd say I do TDD for new features about 50% of the time, and about 90% of the time for bug fixes.

In an interview with InfoQ, you talked about eight things you thought had gotten better with Sequel. What's improved since then?

Jeremy That was shortly after the 2.7.0 release, so the major improvements since then are:

• Database stored procedure support in the MySQL and JDBC adapters.
• Much better support for database schemas.
• Much improved compound SQL statement support (i.e. UNION, EXCEPT, and INTERSECT).

For full details, please see the release notes:

• 2.8.0
• 2.9.0

What do you see happening in the next major Sequel release?

Jeremy I expect the 2.10.0 release of Sequel to include:

• A Firebird adapter.
• A DataObjects (the underlying database connection libary used by DataMapper) adapter.
• Better handling of MySQL CREATE TABLE options, such as the ability to specify an engine.

I don't have any other major plans for 2.10.0, but most of Sequel's new features originate in the community (in ideas if not code), so I'm sure there will be other improvements.

Sequel 2.10.0 will probably be released in the first half of February 2009.

That's pretty aggressive. How do you keep your release cycle short?

Jeremy I put all but the simplest commits through the same test suites I put the releases. Releases are generally time based, not feature based. If a feature isn't ready and is going to hold up the release more than a week, it just goes in the next release. For example, the firebird adapter is already mostly complete and was going to be in 2.9.0, but the developer working on it asked for a little more time to polish it, so it didn't make 2.9.0 and will have to wait for 2.10.0.

How closely do you watch other ORMs (Ruby or non-Ruby)? Which ones seem most interesting to you? What do you learn from them?

Jeremy I browse the DataMapper mailing list and periodically chat with the DataMapper developers on IRC. I generally look at the "What's New in Rails" posts to see what is going on in ActiveRecord. However, I don't follow either very closely. I also spent a bit of time looking at Lone, a PostgreSQL-specific ruby ORM.

DataMapper is interesting in that their focus is very different from Sequel's, in that they are aiming to be a persistance framework for generic classes. I don't have any experience with their recent code, though, the last time I used DataMapper was in the 0.2.5/0.3 timeframe.

I'm sure ActiveRecord taught me a few things, as I used it for years, but that's probably at a more subconcious level. Lone's prepared statement support gave me some ideas for the prepared statement interface in Sequel. I don't think I spent enough time using DataMapper to learn anything specific.

Why do you think sequel is a compelling option for a Ruby ORM?

Jeremy

• Sequel code is generally clear and consise, and it has a very rubyish syntax.
• Sequel::Model's associations are the most powerful of any ruby ORM, as Sequel allows the user much more control over how the associations work.
• Sequel is especially compelling whenever you are dealing with sets of objects instead of single objects.
• Sequel is very easy to contribute to. You don't need 3 +1s. Submit a pull request, post on the mailing, or ping me on IRC and I generally review patches quickly and decide if they are a good fit or not.
• Bugs in Sequel are generally fixed quickly. The Sequel bug tracker has no open bugs currently, and that is how it is most of the time.
• Sequel's internals are clean and easy to extend and modify if you need to.
• Sequel's connection pool doesn't require the user or framework clean up connections manually.

Any closing thoughts or advice for people looking at Sequel?

Jeremy The Sequel community is very friendly, so if you have any issues getting things setup or have questions about how things work after reading the documentation, stop by the Google Group) or IRC (#sequel on Freenode) and hopefully we can help you out.

New Improved MySQL library

MySQLPlus is a new, non-blocking MySQL driver for Ruby 1.8 and 1.9 (anyone know if it will run on Rubinius?) from eSpace, the folks who created NeverBlock. (They also talk about NeverBlockPG, a postgreSQL driver, but it seems to have been deleted.) To quote eSpace's announcement:

[MySQLPlus does] IO operations concurrently and in a transparent manner, thanks to NeverBlock. An interesting side effect emerged during the development of this driver. We were required to update the current MySQL driver to be able to do async operations. Once those were done, we discovered that the basic foundation for threaded support was there. Hence we went forward and implemented it (with help from Ruby gurus like Aman Gupta and Roger Pack). What we have now is a new general purpose MySQL driver that supports threaded access and async operations. This means that you can send queries to a MySQL server in a concurrent manner from Ruby applications. This is big news for those waiting for Rails thread safety. Finally there is a MySQL driver that can help them achieve that concurrency.

I'd love to see what alternative ORMs like Sequel and DataMapper will do with this kind of library underneath them.

Sequel Interview with Sharon Rosner

With the recent release of Sequel, I took some time to interview Sharon Rosner about it. Sequel is a really cool project, and uses some Ruby tools in novel ways. Read on and find out more.

---

There are already several ORMs out there. Why write another one?

Sharon I wrote Sequel mainly because I tried ActiveRecord and it really didn’t fit what I wanted to do. The first thing that frustrated me was the lack of support for multi-threading. I was messing with Mongrel and writing my own web controller framework, and couldn’t get ActiveRecord to work properly with multiple threads. It leaked memory like a sieve, and it just felt wrong. There was also no support for connection pooling for example.

The other, even more important, issue was that ActiveRecord is great for dealing with individual records, but if you need to work with multiple records or large datasets, it kinda feels awkward. If you need to filter then you have to write raw SQL expressions, and it’s a bit hard to do GROUP BY and than sort of stuff. Also, ActiveRecord loads the entire result set into memory before you can iterate over it. Not very nice if you work with millions of records.

So I started from there and tried to design a Ruby interface for databases that would feel like Ruby code, where I didn’t have to switch between SQL and Ruby. So the basic idea was that you can express an SQL query using Ruby constructs, and then iterate over the results just like you iterate over an array or any Enumerable, fetching each record as a Ruby hash with symbols for keys:

DB[:posts].filter(:category => 'ruby').each {|row| puts row[:title]}

I actually wrote Sequel to be used in this project I was working on (and still am), and added more features as I needed them. The development of Sequel is still very much feature-request driven. People ask for stuff and if it’s a good idea we add it the library.

Apart from that, I like the fact that there are several different ORM’s for Ruby. Each has a different mindset, each has its pros and cons. It’s very much in the Ruby spirit – one of the things I like most about Ruby is that you can write the same stuff a million different ways. Some people dislike it, but I think it’s brilliant!

What are some of the cooler things that you’ve added to Sequel based on other people’s requests?

Sharon Some people were asking for a way to change table schemas easily, so I came up with a DSL for doing that, so you can do stuff like:

alter_table :items
  add_column :name, :text, :unique => true
  drop_column :category
end

And Sequel will generate the correct SQL for you. Another thing that was requested is support for accessing values inside arrays, so we came up with a way to specify array subscripts:

DB[:items].filter(:col|1 => 0).sql #=> "SELECT * FROM items WHERE (col[1] = 0)"

What spaces do you think Sequel plays well in? Which spaces should be left to a different approach?

Sharon One of the things that separates Sequel from other ORM’s is that you don’t really have to define model classes. In fact one of the recent changes (in version 0.5) was to divide the code into two separate gems—sequel_core which takes care of connecting to databases and fetching records, and sequel_model which implements an ORM layer on top.

With sequel_core you can fetch records as naked Ruby hashes, which you can also use to insert and update records. So that gives you a lot of freedom in querying virtually any database, without making assumptions on how the schema looks. So Sequel can be used with legacy databases and also as a general purpose tool for writing short scripts that process records. In fact, Sequel also lets you fetch records with arbitrary SQL and iterate over the results using the same interface.

Sequel can be used as a general low level database access library. It is built so you can stick any ORM model implementation on top. I know for example that there was an effort to graft Og on top of Sequel, so this kind of stuff can be done. Sequel already has adapters for ADO, ODBC, PostgreSQL, MySQL, SQLite, and DBI. There are also experimental adapters for Oracle, Informix, DB2, OpenBase and JDBC (on JRuby). In that respect, I believe Sequel can a good replacement for DBI.

The flip side, however, is that Sequel is not really made for Ruby beginners. The API is very terse and very powerful if you know how to use it, but newcomers might be bewildered by how short everything looks. :-) They better stick with ActiveRecord, Og or DataMapper.

What other DB tools have you looked at, learned from as you’ve worked on Sequel?

Sharon Actually a lot of my inspiration came from two Python frameworks: web.py, which is sort of a micro web-framework, and sqlalchemy, which is in my opinion a brilliant piece of work. I haven’t used, but I read a lot of the documentation and grabbed some ideas from there, like for example using a URL to specify a database connection, or the separation between a layer that deals with fetching records and a modelling layer.

What have you learned about Ruby while you’ve been working on Sequel?

Sharon I learned a lot about meta-programming. I got a lot from reading the stuff that _why wrote. He’s probably the brightest most genius Ruby programmer in existence today! A lot of parts of Sequel are written using meta-programming techniques. Once you know how to use those, you can do nuclear stuff!

The biggest discovery I made though was ParseTree. It’s a library that takes your code and gives you back a parse tree made from arrays with symbols in them. It’s the ultimate meta-programming tool. I believe it’s really the next step for Ruby programming and should become part of the Ruby core. Unfortunately, it doesn’t work with Ruby 1.9, at least for now.

Sequel uses ParseTree to translate Ruby code into SQL. So for example, the following code:

DB[:items].filter {:score > 100 and :category.in('ruby', 'perl')}

Is translated into the SQL statement “SELECT * FROM items WHERE (score > 100) AND (category IN (‘ruby’, ‘perl’))”

The Ruby to SQL translator (I call it “The Sequelizer”) is also smart enough to evaluate Ruby expressions, so you can also use constants, variables and ivars, and make calculations.

Another important thing I learned was the value of properly written specs and good code coverage. The thing about rspec is that unlike unit testing, when you write specs you tend to repeat a lot of expectations. I found that with specs the code gets a much more thorough work-out than with unit tests. A lot of stuff gets tested multiple times under different scenarios.

RSpec has also proved to be an indispensable tool for debugging, but this really requires a change in how you work. When you work on a bug, instead of just hammering out a solution, you first write a spec that will fail, setting expectations that expose the bug. Only then do you fix your code so the spec will pass. This seems trivial but it’s important when you develop a library used by hundreds of people. When you work this way you can also ensure that the bug doesn’t return when you make changes to your code.

I use RCov in conjunction with RSpec to make sure every line of code is covered by specs and as of version 1.0 we have 100% code coverage!

Are you using other coverage tools (like dcov or heckle)? Why or why not?

Sharon Both dcov and heckle are things I haven’t looked into in depth. Sequel is not very strong on documentation, and we should put a lot more effort into it. As regards heckle, I tried playing with it a few times, but eventually it would always make the specs hang. Some of the Sequel specs really wreak havoc with Ruby threads and loading/unloading of classes and methods, so I don’t know if heckle can really be beneficial for us. As many other people have already observed, 100% code coverage doesn’t mean there are no bugs or that the code is perfect, but it’s still, together with specs, a good indication of code quality.

Often, people ask if you need to know Ruby to use Rails. How much SQL do you need to get started with Sequel and how much SQL do you need to know to really use Sequel well?

Sharon That depends. The abstraction provided by ORM’s is never perfect, and if you’re going to deal with a relational database, you pretty much need to understand SQL. But with Sequel, you don’t need to know how columns, strings and other literal values should be quoted, or worry about SQL injection, or what’s the correct order of SQL clauses.

There’s a #filter method for specifying record filters, an #order method for specifying the order, a #limit method for limiting the size of the result set and so on. Sequel has idioms for column references (using symbols), SQL functions ( e.g. :max[:price]), array subscripts (as shown above), qualified column names, column aliasing, etc, so the mapping of Ruby to SQL is pretty much complete. There’s also substantial support for sub-queries, which is a believe a unique feature of Sequel. And you can always see what the SQL looks like by calling #sql. Sequel also provides a DSL for defining and changing table schemas. In that respect, you can forget how SQL statements look and manipulate your database using Ruby code.

So if you’re just writing a blog app and need to put some stuff in a database, you don’t really need to know SQL, but if you’re dealing with large data sets or complex relationships then you better have a good understanding of how relational databases work and how records are fetched, and that involves at least some knowledge of SQL.

What books, websites, etc. would you recommend for people wanting to learn more about SQL?

Sharon I’m really not the type that reads books on programming, I just dive right in. There are though tons of articles on the web about the subject. That’s especially useful if you want to look at how to do more complex stuff like multiple joins, sub-queries, grouped queries etc.

Where can people turn for more information about Sequel?

Sharon There are four good places to look:

• the project page which contains some Wiki pages about Sequel.
• the API Documentation
• the google group dedicated to sequel (very friendly people there)
• there are always some people lurking on IRC at #sequel or #merb.

I also encourage people to look at the code, which is here

Is there a Sequel book lurking in the wings?

Sharon Not that I know of, and I’m not really the person to take on that kind of project. But if somebody wants to go ahead and do it, that would be like super cool!

What’s the coolest thing that someone’s done with Sequel?

Sharon I’d have to say Hackety Hack, which is a project by _why to make programming accessible to kids. He wraps Sequel a bit to make it more friendly but you can see a bit of Sequel code right there on the front page!