labs postgresql

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.

 

Related Articles