Sequel:

The Database Toolkit for Ruby

Jeremy Evans

History

History (2)

sequel_core vs sequel_model

Database Support

Adding adapters

Adding adapters (2)

Sequel::Database

uri = 'postgres://user:pass@host/database'
DB = Sequel.connect(uri)
DB = Sequel.postgres(database, :host =>host)
DB = Sequel.sqlite # Memory database

Sequel::Database (2)

# Set defaults for future datasets
DB.quote_identifiers = true
# Create Datasets
dataset = DB[:attendees]
# Setup SQL Loggers
DB.loggers << Logger.new($stdout)
# Handle transactions: block required, no way
# to leave a transaction open indefinitely
DB.transaction {
  # Execute SQL directly
  rows = DB['SELECT * FROM ...'].all
  DB["INSERT ..."].insert
  DB["DELETE ..."].delete
  DB << "SET ..." }

Connection Pooling

Sequel::Dataset

DB[:table].limit(5, 2).order(:column4).
  select(:column1, :column2).
  filter(:column3 =>0..100).all

Fetching Rows

Dataset inserting, updating, and deleting

DB[:attendees].insert(:name => 'Jeremy Evans')
DB[:attendees].filter(:confirmed => nil).
  update(:confirmed => true)
DB[:attendees].filter(:paid => false).delete

Dataset Filtering

ds = DB[:attendees]
# Strings
ds.filter('n = 1')      # n = 1
ds.filter('n > ?', 'M') # n > 'M'
# Hashes
ds.filter(:n =>1)        # n = 1
ds.filter(:n =>nil)      # n IS NULL
ds.filter(:fn =>'ln')    # fn = 'ln'
ds.filter(:fn =>:ln)     # fn = ln
ds.filter(:n =>[1, 2])   # n IN (1, 2)
ds.filter(:n =>1..2)     # n >= 1 AND n <= 2

More Advanced Filtering

ds.filter(:p * :q + 1 < :r)     # p * q + 1 < r
ds.filter(:p / (:q + 1) >= :r)  # p / (q + 1) >= r
ds.filter({:p => 3} | :r)       # p = 3 OR r
ds.filter(~{:p => 'A'} & :r)    # p != 'A' AND r
ds.filter(~:p)                  # NOT p
ds.filter(~(~{:p =>:q} & :r))   # p = q OR NOT r
ds.filter(:p =>ds.select(:q).filter(:r))
  # p IN (SELECT q FROM attendees WHERE r)

Sql String Manipulation

Concatenation

ds.select(:p + :q)                  # p + q
ds.select(:p.sql_string + :q)       # p || q
ds.select([:p, :q].sql_string_join) # p || q

Searching

ds.filter(:p.like(:q))       # p LIKE q
ds.filter(:p.like('q', /r/)) # p LIKE 'q' OR p ~ 'r'
ds.filter([:p, :q].sql_string_join.like('Test')) 
  # (p || q) LIKE 'Test'

Identifier Symbols

Dataset Joining

ds = DB[:attendees].
  join_table(:inner, :events, :id =>:event_id)
# FROM attendees INNER JOIN events
#   ON (events.id = attendees.event_id)

Dataset Joining (2)

ds = ds.join(:locations, :id =>:location_id)
# ... INNER JOIN locations ON
#   (locations.id = events.location_id)

ds = ds.left_outer_join(:caterers, \
  :id =>:events__caterer_id)
# ... LEFT OUTER JOIN caterers ON
#   (caterers.id = events.caterer.id)

Join Clobbering

# attendees: id, name, event_id
# events: id, name
ds = DB[:attendees].join(:events, :id =>:event_id)
ds.all
# => [{:id=>event.id, :name=>event.name}, ...]

Join Clobbering (2)

ds.select(:attendees__name___attendee,
  :events__name___event)
ds.all
# => [{:attendee=>attendees.name, \
#      :event=>events.name}, ...]

Dataset Graphing

# attendees: id, name, event_id
# events: id, name
ds = DB[:attendees].graph(:events, :id =>:event_id)
ds.all
# => [{:attendees=>{...}, :events=>{...}}, ...]

Sequel::Model

class Attendee < Sequel::Model; end
class Event < Sequel::Model(:events); end
class Foo < Sequel::Model
  set_dataset db[:bar].join(:baz, :id =>:baz_id)
end

Model Associations

Player.many_to_one :team
Team.one_to_many :players
Player.first.team
# SELECT * FROM team WHERE id = #{player.team_id}
Team.first.players
# SELECT * FROM players WHERE team_id = #{team.id}
Attendee.many_to_many :events
Attendee.first.events
# SELECT events.* FROM events INNER JOIN
# attendee_events ON attendee_events.event_id=events.id
# AND attendee_events.attendee_id = #{attendee.id}

No Proxies for Associations

Player.many_to_one :team
player.team # Team or nil
player.team = team

Team.one_to_many :players
team.players # Array of Players
team.players_dataset # Sequel::Dataset for this
                     # team's players
team.add_player(player)
team.remove_player(player)
team.remove_all_players

No Proxies for Associations (2)

No Proxies for Associations (3)

Association Options

# Only return players with 20 or more goals
Team.one_to_many(:high_scorers, :class =>Player){|ds|
  ds.filter{|o| o.goals >= 20}}

Association Options (2)

module SameName
  def self_titled
    first(:name=>model_object.name)
end end
Artist.one_to_many :albums, :extend=>SameName
Artist.first.albums_dataset.self_titled

Overriding Association Methods

Eager Loading

Advanced Associations

Sequel allows you full control over associations via the :dataset option

# AR has_many :through=>has_many
# Firm one_to_many Clients one_to_many Invoices
Firm.one_to_many :invoices, :dataset =>proc{
  Invoice.eager_graph(:client).
  filter(:client__firm_id =>pk)}

# Joining on any of multiple keys to a single key
# through a third table
Artist.one_to_many :songs, :dataset=>proc{
  Song.select(:songs.*).join(Lyric, :id =>:lyric_id,
  id=>[:composer_id, :arranger_id, :vocalist_id,
  :lyricist_id])}

Eager Loading of Advanced Associations

Sequel allows you full control via the :eager_loader option

Firm.one_to_many :invoices, :eager_loader =>(
 proc{|key_hash, firms, associations|
  # key_hash: {:id=>{1=>[firm1], 2=>[firm2]}, 
  # :type_id=>{1=>[firm1, firm2], 2=>[firm3, firm4]}}
  id_map = key_hash[Firm.primary_key]
  firms.each{|f| f.associations[:invoices] = []}
  Invoice.eager_graph(:client).
   filter(:client__firm_id =>id_map.keys).all{|i|
    id_map[inv.client.firm_id].each {|firm|
     firm.associations[:invoices] << inv
 }}})

Polymorphic Associations - DRY Too Far

Advanced Associations: What Else Can you Do?

Validations

validates do
  format_of :a, :with=>/\A.*@.*\..*\z/
  uniqueness_of :a_id, :b_id # both unique
  uniqueness_of [:a_id, :b_id] # combination
end

Custom Validations

validates_each(:amount, :if=>:confirmed?){|o,a,v|
  o.errors[a] << "is less than 100" if v < 100
end

Hooks

Dataset Pagination

Model Caching

Schema Definition

DB.create_table(:attendees) do
  primary_key :id # integer/serial/identity
  String :name # varchar(255)/text
  column :registered_at, DateTime # timestamp
  money :price # money
  foreign_key :event_id, :events
  index :name, :unique =>true
  index [:name, :event_id]
  constraint :b, ~{:price =>0} # price != 0
  check{|o| o.registered_at > '2008-12-31'}
  primary_key [:name, :price] # composite pk
  foreign_key [:name, :price], :blah, \
    :key => [:att_name, :att_price] # composite fk
end

Schema Modification

DB.alter_table(:attendees) do
  add_column :confirmed, :boolean, :null =>false
  drop_constraint :b
  add_constraint :c do |o| # price != 0 if confirmed
    {:confirmed =>~{:price=>0}}.case(true)
  end
  add_foreign_key :foo_id, :foos
  add_primary_key :id
  rename_column :id, :attendee_id
  drop_column :id
  set_column_default :name, 'Jeremy'
  set_column_type :price, Numeric
  set_column_allow_null :confirmed, true
end

Schema Modification (2)

DB.add_column :attendees, :confirmed, :boolean
DB.add_index :attendees, :confirmed
DB.drop_index :attendees, :confirmed
DB.rename_column :attendees, :id, :attendee_id
DB.drop_column :attendees, :attendee_id
DB.set_column_default :attendees, :name, 'Jeremy'
DB.set_column_type :attendees, :price, Numeric
DB.rename_table :attendees, :people
DB.drop_table :people
DB.create_view :ac, DB[:attendees].where(:confirmed)
DB.drop_view :ac

Migrations

class CreateAttendees < Sequel::Migration
  def up
    create_table(:attendees) {
      primary_key :id
      String :name }
  end
  def down
    drop_table(:attendees)
  end
end # CreateAttendees.apply(DB, :up)

Migrator

Sequel::Migrator.apply(DB, '.') # To current version
Sequel::Migrator.apply(DB, '.', 5, 1) # To 5 from 1
# $ sequel -m /path/to/migrations -M 5 postgres://...

Migration Philosophy

Model Schemas

Bound Variables

ds = DB[:items].filter(:name =>:$n)
ds.call(:select, :n =>'Jim')
ds.call(:update, {:n =>'Jim', :new_n =>'Bob'}, \
  :name =>:$new_n)

Prepared Statements

ds = DB[:items].filter(:name =>:$n)
ps = ds.prepare(:select, :select_by_name)
ps.call(:n =>'Jim')
DB.call(:select_by_name, :n =>'Jim')
ps2 = ds.prepare(:update, :update_name, \
  :name =>:$new_n)
ps2.call(:n =>'Jim', :new_n =>'Bob')

Stored Procedures

DB[:table].call_sproc(:select, :mysp, \
  'param1', 'param2')
sp = DB[:table].prepare_sproc(:select, :mysp)
sp.call('param1', 'param2')
sp.call('param3', 'param4')

Master/Slave Databases

DB=Sequel.connect('postgres://m/db', :servers => \
  {:read_only =>proc{|db| :host =>db.slave}}, \
  :max_connections =>16) # 4 connections per slave
# Master host: m; slave hosts: s1, s2, s3, s4
def DB.slave; "s#{((@current_host||=-1)+=1)%4}" end

Sharding/Partitioning

s = {}
%w'a b c d'.each{|x| s[x.to_sym] = {:host=>"s#{x}"}}
DB=Sequel.connect('postgres://m/db', :servers=>s)
DB[:table].server(:a).filter(:num=>10).all
DB[:table].server(:b).filter(:num=>100).delete

Core Class Methods

h = {:a =>1, :b =>2} # or [[:a,1],[:b,2]]
h.sql_expr    # a = 1 AND b = 2
h.sql_negate  # a != 1 AND b != 2
h.sql_or      # a = 1 OR b = 2
~h            # a != 1 OR b != 2
h.case(0, :c) # CASE c WHEN a THEN 1
              # WHEN b THEN 2 ELSE 0 END

Core Class Methods (2)

# (a,b) IN (1=2 AND 3=4)
filter([:a,:b]=>[[1,2],[3,4]])
# (a,b) IN ((1,2),(3,4))
filter([:a,:b]=>[[1,2],[3,4]].sql_array)
{:a=>1} & :b # a = 1 AND b
{:a=>1} | :b # a = 1 OR b
:a.as(:b)  # a AS b
'a'.as(:b) # 'a' AS b
:a.cast(:integer) # CAST(a AS integer)
:a.cast_numeric << 1 # CAST(a AS integer) << 1
'1.0'.cast_numeric(:real) # CAST('1.0' AS real) 
:a.cast_string + :b # CAST(a AS varchar(255)) || b

Core Class Methods (3)

'a'      # 'a'
'a'.lit  # a
'a'.to_sequel_blob # Needed for dealing with blob
                   # columns on most databases
:a + :b - :c * :d / :e  # (a + b) - ((c * d) / e)
:a & :b | ~:c           # (a AND b) OR NOT c
:a.sql_function         # a()
:a.sql_function(:b, :c) # a(b, c)
:a.extract(:year)       # EXTRACT(year FROM a)
# Except on Ruby 1.9:
:a < 'a'; :b >= 1 # a < 'a'; b >= 1
:a[]              # a()
:a[:b, :c]        # a(b, c)

Core Class Methods (4)

:a__b              # a.b
:a__b.identifier   # a__b
:a.qualify(:b)     # b.a
:a.qualify(:b__a)  # b.a.a
:b__c.qualify(:a)  # a.b.c
:a.like(:b)        # PostgreSQL: a LIKE b
:a.like('b')       # MySQL:      a LIKE BINARY 'b'
:a.like(/b/)       # PostgreSQL: a ~ 'b'
:a.like(/b/)       # MySQL:      a REGEXP BINARY 'b'
:a.ilike(:b)       # MySQL:      a LIKE b
:a.ilike('b')      # PostgreSQL: a ILIKE 'b'
:a.ilike(/b/)      # PostgreSQL: a ~* 'b'
:a.ilike(/b/)      # MySQL:      a REGEXP 'b'
:a.sql_number << 1 # a << 1

sequel command line tool

Using Sequel in Web Applications

Lightweight?

Current Status

Contributing

The Future: Sequel 3.0

Questions?