Close
Glad You're Ready. Let's Get Started!

Let us know how we can contact you.

Thank you!

We'll respond shortly.

LABS
Access your database's best features with Sequel

Sequel is a wonderful library for interacting with relational databases. Some of my favorite aspects:
1. Out-of-the box support for foreign key constraints.
2. Straightforward migration DSL.
3. Support for all major free RDBMSes and even some proprietary ones. Also JRuby support through JDBC. (although JDBC doesn’t necessarily
give you all the features a native driver would. Projects like jruby-pg are making progress though.)
4. “One way to do it” for common operations such as inserts and updates. Parsimonious API (compared to ActiveRecord).
5. bin/sequel executable gives you an interactive ruby session directly from a database URL.
6. Extremely powerful Dataset abstraction lets you write general query code.
7. The source code itself is a pleasure to work with and the maintainer (jeremyevans) is extremely diligent about pull requests.

One feature i’d like to expand upon is the ability to call raw SQL functions from Sequel’s DSL.

Here’s a migration where I create a speeches table with some full-text search columns:


Sequel.migration do
  up do
    create_table :speeches do
      primary_key :id
      String :text
      String :speaker
      Integer :year
    end

    run "ALTER TABLE speeches ADD COLUMN ts_text tsvector;"
    run "CREATE INDEX ts_text_idx ON speeches USING gin(ts_text);"
    run "CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
         ON speeches FOR EACH ROW EXECUTE PROCEDURE
         tsvector_update_trigger(ts_text, 'pg_catalog.english', text, speaker);"
  end
end

 

And here’s some sample code to query it:


require 'sequel'

def search(dataset, term)
  results = dataset.select do
    [ts_headline('english',
                 :text,
                 to_tsquery('english', term),
                 'MaxFragments=2').as(headline),
     id,
     speaker]
  end
  results.filter("ts_text @@ to_tsquery('english', ?::text)", term)
end

DB = Sequel.connect('postgres://localhost/blogpost')

DB[:speeches].insert(:year => 1865, :speaker => 'President Lincoln', :text => 'Fourscore and seven years ago')
DB[:speeches].insert(:year => 1963, :speaker => 'President Kennedy', :text => 'I am a Jelly Donut')

p search(DB[:speeches], 'President').all # returns both speeches
p search(DB[:speeches].where("year > ?", 1900), 'President').all # returns only the Kennedy speech

The search function takes in a dataset and a query term, and returns a new dataset filtered with a body or speaker name matching the query term,
as well as a “headline”, or the context around the search term to display in search results. This takes full advantage of a full-text search index.

Sequel is also great as glue code for calling PostGIS functions. I’ve found it especially useful for DRY-ing up long sets of raw SQL queries into maintainable scripts.

 

Comments
  1. Robbie Clutton says:

    Neat. I’d be interested in seeing how you’d recommend testing using Sequel.

    Also, if anyone is interested in searching in Postgres, check out pg_search: https://github.com/Casecommons/pg_search

  2. David Tengdin says:

    Glad to see others trying out this gem. We recently moved License Finder to using a SQLite database for its datastore and picked sequel over active record since it has fewer dependencies and does seem more lightweight.

  3. Brandon Liu says:

    David: Indeed, I’ve found that Sequel is a great fit for plain-old-Ruby projects as well as with Sinatra.

    Robbie:
    Good question, I think any pattern that applies to testing with ActiveRecord applies the same way,
    if anything i’ve found Sequel’s API surface to be smaller and this more easily mockable.

    Additionally if you want to run your tests without a real database there’s a
    built-in mock database object that lets you assert on the SQL statements generated by your code.

Post a Comment

Your Information (Name required. Email address will not be displayed with comment.)

* Copy This Password *

* Type Or Paste Password Here *