Well, our first ever spotlighted project has been selected. For June, it’s Sequel, the Concise ORM for Ruby.

Development on this project has been like wildfire for the last couple months, with releases every few weeks. Support is available for PostgresSQL, MySQL, SQLite3, and ODBC. There is also a wrapper for RubyDBI, which means that you can hook into pretty much any mainstream database if needed.

Because code often speaks louder than words, we’ll take a look at Sharon’s proposal which won the June spotlight.

The Proposal

require 'rubygems'
require 'sequel/sqlite' # gem install sequel (and sqlite3 as well)

DB = Sequel.open 'sqlite:/' # memory DB

DB.create_table :items do
  text :name
  decimal :price
end

items = DB[:items]

1000.times {|i| items << {:name => "item#{i}", :price => rand * 100}}

puts "#{items.count} total items"

puts "Average price: #{items.avg(:price)}"

puts "3 most expensive items:"
items.order(:price.DESC).limit(3).print(:name, :price)

puts "#{items.filter {price < items.avg(:price)}.count} below the average"

puts "Changing price for expensive items"
items.filter {price > items.avg(:price)}.update(:price => 'price + 10'.expr)

puts "Highest price: #{items.max(:price)}"

puts "Updated average price: #{items.avg(:price)}"

Output:

1000 total items
Average price: 51.9679610670265
3 most expensive items:
+-------+----------------+
|name   |price           |
+-------+----------------+
|item725|99.9570494326071|
|item179|99.9012407557774|
|item626|99.6490915522042|
+-------+----------------+
478 below the average
Changing price for expensive items
Highest price: 109.957049432607
Updated average price: 57.1879610670265

As you can see, it is indeed pretty concise.

Cool features

- A little IRB wrapper that lets you load a database directly, a la sequel sqlite:///test.db

- Sequel Datasets are Enumerable, but hit the database when the methods are called

- Arbitrary SQL execution:

  DB << "create table t (a text, b text)"
  DB << "insert into t values ('a', 'b')"

- Lots of ways to filter data:

  my_posts = posts.filter(:category => 'ruby', :author => 'david') 

  my_posts = posts.filter(:stamp => (2.weeks.ago)..(1.week.ago)) 

  my_posts = posts.filter(:category => ['ruby', 'postgres', 'linux']) 

  my_posts = posts.filter {category == ['ruby', 'postgres', 'linux']} 

  my_posts = posts.filter {stamp > 1.month.ago} 

  my_posts = posts.filter(:category => /ruby/i) 

  my_posts = posts.exclude(:category => /ruby/i) 

  my_posts.each {|row| p row} 

  posts.filter('(stamp < ?) AND (author <> ?)', 3.days.ago, author_name)

That’s basically just what jumped right out at me, it looks like things go a whole lot deeper than that.

Selfish Investigations

I first gained interest in Sequel slightly before this contest started, as I thought it might jive nicely with Ruport. Turns out, that’s definitely the case. Here’s my naive hack that actually works for the simple cases:

class Sequel::Dataset
  def report_table
     Table(:column_names => columns, :data => all)
  end
end

This lets you use Sequel for your data acquisition and Ruport for your formatting needs.

irb(main):017:0> puts DB[:payr_employees].report_table
+---------------------------------------------------------------------------------------------+
| lunch_hours | lunch_time | start_time | avg_hours | code |      name      | is_manager | id |
+---------------------------------------------------------------------------------------------+
|           0 | 12:00      | 08:00      |        40 | gsgi | Gregory Gibson | true       |  1 |
|           1 | 13:00      | 11:00      |        30 | jon  | Jon Juraschka  | false      |  2 |
+---------------------------------------------------------------------------------------------+
=> nil
irb(main):018:0> puts DB[:payr_employees].filter(:code => "jon").report_table
+--------------------------------------------------------------------------------------------+
| lunch_hours | lunch_time | start_time | avg_hours | code |     name      | is_manager | id |
+--------------------------------------------------------------------------------------------+
|           1 | 13:00      | 11:00      |        30 | jon  | Jon Juraschka | false      |  2 |
+--------------------------------------------------------------------------------------------+
=> nil
irb(main):019:0> puts DB[:payr_employees].report_table.to_csv
lunch_hours,lunch_time,start_time,avg_hours,code,name,is_manager,id
0,12:00,08:00 ,40,gsgi,Gregory Gibson,true,1
1,13:00,11:00,30,jon,Jon Juraschka,false,2

Since Sequel::Dataset#all just returns an array of hashes, it was trivial to use with Ruport and I suspect that this structure is common in a lot of other Ruby libraries too, which means that Sequel offers a whole lot of shoehorn capabilities.

Final Impressions

I really like what I see here. It’s really a different feel than full blown ORM like ActiveRecord, and seems like it might be the ideal choice for quick and dirty SQL hackery. I definitely think that you could pick up the basics for this lib in 10-15 minutes and already have it be useful for you. The fact that the project is active and relatively new means there is room for growth and improvement. The API doesn’t feel incredibly polished, but for the most part, is fairly intuitive.

I definitely think it’s a project to keep an eye on, and didn’t run into many problems with it while playing around with it. Hopefully, I’ll get a chance to work with it in some real projects soon.

Sequel Community Resources

The docs are halfway decent and the group has some posts worth checking out in the archive, for sure.

June Spotlight Honorable Mention

Though I felt like Sequel had the strongest proposal, the other ones I received were excellent. I’d like to offer an honorable mention for MenTal guY’s Omnibus Concurrency Library. The coolest thing in his proposal was Array#parallel_map which works like map, but concurrently. There was some other sick stuff in their too, so it’s worth checking out for sure.

That’s all for this month. I’ll be accepting submissions for July until the end of the month. Until then, go give Sequel some love!