Episode #179: Sequel

 
Upgrade to download episode video.

Episode Script

When we talk about working with SQL databases in Ruby, these days the library that everyone immediately thinks about is ActiveRecord. But ActiveRecord isn't a one-size-fits-all solution. We might want to write a quick script to gather some information from a database, without taking the time to model table rows as classes. We might need to interact with a legacy database whose schema clashes with ActiveRecord's conventions. Or we might want to build our own object-relational modeling layer, while avoiding the necessity of writing adapters to a myriad different database management systems.

All of these are examples of problems that ActiveRecord is poorly suited to. What we'd like is a database library that abstracts the differences between individual databases, but which otherwise doesn't impose any particular conventions or object-modeling assumptions on our data or our code.

Enter the Sequel gem. This gem makes it easy to talk to a myriad different databases from Ruby, including PostgreSQL, MySQL, Oracle, and many others. And it provides a simple yet very powerful API for doing so.

Let's start by establishing a connection to an in-memory SQLite database. Before we do anything else, we'll need a schema. We can do this by calling methods directly on the database connection. Let's create a couple of tables for managing shopping lists.

First, a table, so that we can keep track of whose shopping list is whose. We'll give it a primary key called . Behind the scenes, will arrange for this field to be automatically incremented whenever a new record is added.

We'll also give the table a column. Notice that to specify that this is a column, we just use the name of the Ruby class. This is the pattern for most columns in the Sequel schema DSL: use the name of a Ruby type, and Sequel figures out a database-appropriate type to use for the column.

Next we declare an table. We start out by declaring a foreign key relationship to the table. This highlights one of the differences in usage patterns between ActiveRecord and Sequel. ActiveRecord prefers to keep all constraints and validations on the application side. Whereas Sequel strives to make it easy to make use of the full power of our database's built-in constraints. In this case, Sequel will declare a column, as well as set up a foreign key constraint ensuring that the field will always point to a valid row in the table.

Next up comes a column for item . We add that in the absence of a specified value, it should default to 1.

Finally, we set up another constraint – there should only be one row for a given person and item name.

Now lets add some data to our tables. We'll start with people. We operate on an individual table by selecting it with the subscript operator. Then to create a new row, we send the message with a hash of keys and values. The return value of an is the primary key of the row.

Let's verify that we've added some rows. We can count the rows in a given table. We can also show all of the rows. They are returned as an array of simple hashes.

Next up, let's add some items to our shopping lists. This time, we'll use a shortcut: we can specify values in the same order they were declared in the schema, and skip the column names.

We add another item, using the hash version of , but this time we leave off the field. Remember, we specified in the schema that this column should default to 1. When we list the contents of the table, we can see that indeed, the database has defaulted the value to 1.

Speaking of things the database takes care of for us, lets try to add another row with the same name and person ID.

We get a constraint exception in response. This is the result of the constraint we specified in the schema. But again, this isn't the Sequel library validating the data – it's just giving us access to the database's constraint capabilities.

Let's quickly add a few more items.

We've already seen that we can dump the contents of a table. If we simply reference a table without sending it any other messages, we get a object. Datasets are the central concept in the Sequel library. Just about any database operation we can imagine, we can accomplish with a dataset.

First off, a Dataset is . So we can iterate over it, and use any of the other Enumerable methods.

We can also use various aggregation methods. For instance, we can sum the total quantities of all items:

If we want to select one record by column value, we can easily do it using the subscript operator with a hash argument:

We can filter datasets using the method. We can filter by the value of a column:

Notice that by itself, this has just prepared a SQL query, without actually executing it. Datasets are “lazy” in this sense. They don't execute a query until they are forced to by a method such as or .

There are many, many variations on filtering using the method, which I don't have time to go over in detail. One of the most interesting, though, is the ability to use a Ruby block to specify the query. Here, we write a ruby block which tests for a greater than 1. As we can see in the result, Sequel translates this Ruby code into SQL, which we can then execute.

Now, this is relational data we're dealing with, and of course Sequel has tools for exploring relations. We can use the join method to join our and tables.

You might notice that in the resulting data, only the item name shows up, not the person name. If we replace with , Sequel automatically aliases the column in order to resolve the naming collision.

We can also use a Sequel extension called to neatly joined separate results from different tables into their own sub-hashes.

Sequel is a library with tremendous depth, and we could probably go on for hours exploring its many capabilities. For instance, I introduced it as a way to skip object/relational mapping layers, but Sequel actually has its own optional ORM layer, similar to ActiveRecord! If this little taste of the library has piqued your interest, I encourage you to check out the excellent online documentation and spend some time playing with it. Happy hacking!