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.
About the Author
BiographyMore Content by Brandon Liu