Module 3, Topic 6
In Progress


Module Progress
0% Complete

Have you ever needed to write a quick script to gather some info from a database, and felt like setting of active record models was overkill?

Have you ever had to work with an idiosyncratic legacy database schema, or wanted to build your own object relational modeling layer?

In this episode, you’ll learn how the Sequel gem provides a simple, powerful API for talking to many different databases from Ruby.

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 :people table, so that we can keep track of whose shopping list is whose. We'll give it a primary key called :id. Behind the scenes, Sequel will arrange for this field to be automatically incremented whenever a new record is added.

We'll also give the table a :name column. Notice that to specify that this is a String column, we just use the name of the Ruby String 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 :items table. We start out by declaring a foreign key relationship to the :people 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 :person_id column, as well as set up a foreign key constraint ensuring that the field will always point to a valid row in the people table.

Next up comes a column for item quantity. 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.

require "sequel"

DB = Sequel.sqlite

DB.create_table :people do
  primary_key :id
  String :name

DB.create_table :items do
  foreign_key :person_id, :people
  String  :name
  Integer :quantity, default: 1
  unique  [:person_id, :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 #insert message with a hash of keys and values. The return value of an insert is the primary key of the row.

stacey_id = DB[:people].insert(name: "Stacey")
avdi_id   = DB[:people].insert(name: "Avdi")

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.

DB[:people].count               # => 2
# => [{:id=>1, :name=>"Stacey"}, {:id=>2, :name=>"Avdi"}]

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.

DB[:items].insert(avdi_id, "Pie", 2)

We add another item, using the hash version of #insert, but this time we leave off the quantity 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 quantity 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.

DB[:items].insert(person_id: avdi_id, name: "Granola")
# ~> /home/avdi/.rvm/gems/ruby-2.1.0/gems/sqlite3-1.3.8/lib/sqlite3/statement.rb:108:in `step': SQLite3::ConstraintException: columns person_id, name are not unique (Sequel::DatabaseError)
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sqlite3-1.3.8/lib/sqlite3/statement.rb:108:in `block in each'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sqlite3-1.3.8/lib/sqlite3/statement.rb:107:in `loop'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sqlite3-1.3.8/lib/sqlite3/statement.rb:107:in `each'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:158:in `to_a'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:158:in `block in execute'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:95:in `prepare'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:134:in `execute'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sequel-4.6.0/lib/sequel/adapters/sqlite.rb:181:in `block (2 levels) in _execute'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sequel-4.6.0/lib/sequel/database/logging.rb:37:in `log_yield'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sequel-4.6.0/lib/sequel/adapters/sqlite.rb:181:in `block in _execute'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sequel-4.6.0/lib/sequel/database/connecting.rb:229:in `block in synchronize'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sequel-4.6.0/lib/sequel/connection_pool/threaded.rb:104:in `hold'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sequel-4.6.0/lib/sequel/database/connecting.rb:229:in `synchronize'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sequel-4.6.0/lib/sequel/adapters/sqlite.rb:172:in `_execute'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sequel-4.6.0/lib/sequel/adapters/sqlite.rb:143:in `execute_insert'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sequel-4.6.0/lib/sequel/dataset/actions.rb:811:in `execute_insert'
# ~>    from /home/avdi/.rvm/gems/ruby-2.1.0/gems/sequel-4.6.0/lib/sequel/dataset/actions.rb:339:in `insert'
# ~>    from -:25:in `<main>'

We get a constraint exception in response. This is the result of the unique 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.

DB[:items].insert(avdi_id, "Mineral Water", 1)
DB[:items].insert(stacey_id, "Heads of Kale", 3)
DB[:items].insert(stacey_id, "Mineral Water", 2)

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 Dataset object. Datasets are the central concept in the Sequel library. Just about any database operation we can imagine, we can accomplish with a dataset.

# => #<Sequel::SQLite::Dataset: "SELECT * FROM `items`">

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

DB[:items].each do |row|
  p row
# >> {:person_id=>2, :name=>"Pie", :quantity=>2}
# >> {:person_id=>2, :name=>"Granola", :quantity=>1}
# >> {:person_id=>2, :name=>"Mineral Water", :quantity=>1}
# >> {:person_id=>1, :name=>"Heads of Kale", :quantity=>3}
# >> {:person_id=>1, :name=>"Mineral Water", :quantity=>2}
# => {:person_id=>2, :name=>"Pie", :quantity=>2}

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

DB[:items].sum(:quantity)       # => 9

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

DB[:items][name: "Granola"]
# => {:person_id=>2, :name=>"Granola", :quantity=>1}

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

DB[:items].where(name: "Mineral Water")
# => #<Sequel::SQLite::Dataset: "SELECT * FROM `items` WHERE (`name` = 'Mineral Water')">

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 #each or #all.

DB[:items].where(name: "Mineral Water")
# => #<Sequel::SQLite::Dataset: "SELECT * FROM `items` WHERE (`name` = 'Mineral Water')">

DB[:items].where(name: "Mineral Water").all
# => [{:person_id=>2, :name=>"Mineral Water", :quantity=>1},
#     {:person_id=>1, :name=>"Mineral Water", :quantity=>2}]

There are many, many variations on filtering using the #where 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 quantity greater than 1. As we can see in the result, Sequel translates this Ruby code into SQL, which we can then execute.

DB[:items].where{quantity > 1}
# => #<Sequel::SQLite::Dataset: "SELECT * FROM `items` WHERE (`quantity` > 1)">

DB[:items].where{quantity > 1}.all
# => [{:person_id=>2, :name=>"Pie", :quantity=>2},
#     {:person_id=>1, :name=>"Heads of Kale", :quantity=>3},
#     {:person_id=>1, :name=>"Mineral Water", :quantity=>2}]

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 people and items tables.

DB[:people].join(:items, person_id: :id).where(person_id: avdi_id).all
# => [{:id=>2, :name=>"Granola", :person_id=>2, :quantity=>1},
#     {:id=>2, :name=>"Mineral Water", :person_id=>2, :quantity=>1},
#     {:id=>2, :name=>"Pie", :person_id=>2, :quantity=>2}]

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

DB[:people].graph(:items, person_id: :id).where(person_id: avdi_id).all
# => [{:id=>2,
#      :name=>"Avdi",
#      :person_id=>2,
#      :items_name=>"Granola",
#      :quantity=>1},
#     {:id=>2,
#      :name=>"Avdi",
#      :person_id=>2,
#      :items_name=>"Mineral Water",
#      :quantity=>1},
#     {:id=>2, :name=>"Avdi", :person_id=>2, :items_name=>"Pie", :quantity=>2}]

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

DB.extension :graph_each

DB[:people].graph(:items, person_id: :id).where(person_id: avdi_id).all
# => [{:people=>{:id=>2, :name=>"Avdi"},
#      :items=>{:person_id=>2, :name=>"Granola", :quantity=>1}},
#     {:people=>{:id=>2, :name=>"Avdi"},
#      :items=>{:person_id=>2, :name=>"Mineral Water", :quantity=>1}}, # !> instance variable @columns not initialized
#     {:people=>{:id=>2, :name=>"Avdi"},
#      :items=>{:person_id=>2, :name=>"Pie", :quantity=>2}}]

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!