Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

219 lines (147 sloc) 6.164 kb

Cheat Sheet

Open a database

require 'rubygems'
require 'sequel'

DB = Sequel.sqlite('my_blog.db')
DB = Sequel.connect('postgres://user:password@localhost/my_db')
DB = Sequel.postgres('my_db', :user => 'user', :password => 'password', :host => 'localhost')
DB = Sequel.ado('mydb')

Open an SQLite memory database

Without a filename argument, the sqlite adapter will setup a new sqlite database in memory.

DB = Sequel.sqlite

Logging SQL statements

require 'logger'
DB = Sequel.sqlite '', :loggers => [Logger.new($stdout)]
# or
DB.loggers << Logger.new(...)

Using raw SQL

DB.run "CREATE TABLE users (name VARCHAR(255) NOT NULL, age INT(3) NOT NULL)"
dataset = DB["SELECT age FROM users WHERE name = ?", name]
dataset.map(:age)
DB.fetch("SELECT name FROM users") do |row|
  p r[:name]
end

Create a dataset

dataset = DB[:items]
dataset = DB.from(:items)

Most dataset methods are chainable

dataset = DB[:managers].where(:salary => 5000..10000).order(:name, :department)

Insert rows

dataset.insert(:name => 'Sharon', :grade => 50)

Retrieve rows

dataset.each{|r| p r}
dataset.all #=> [{...}, {...}, ...]
dataset.first

Update/Delete rows

dataset.filter(~:active).delete
dataset.filter('price < ?', 100).update(:active => true)

Datasets are Enumerable

dataset.map{|r| r[:name]}
dataset.map(:name) # same as above

dataset.inject(0){|sum, r| sum + r[:value]}
dataset.sum(:value) # same as above

Filtering (see also doc/dataset_filtering.rdoc)

dataset.filter(:name => 'abc')
dataset.filter('name = ?', 'abc')

dataset.filter{|o| o.value > 100}
dataset.exclude{|o| o.value <= 100}

dataset.filter(:value => 50..100)
dataset.where{|o| (o.value >= 50) & (o.value <= 100)}

dataset.where('value IN ?', [50,75,100])
dataset.where(:value=>[50,75,100])

dataset.filter(:name => 'abc').first
dataset[:name => 'abc']

dataset.where('price > (SELECT avg(price) + 100 FROM table)')
dataset.filter{|o| o.price > dataset.select(o.avg(price) + 100)}

Advanced filtering using ruby expressions

DB[:items].filter{|o| o.price < 100}.sql 
#=> "SELECT * FROM items WHERE (price < 100)" 

DB[:items].filter(:name.like('AL%')).sql 
#=> "SELECT * FROM items WHERE (name LIKE 'AL%')"

There's support for nested expressions with AND, OR and NOT:

DB[:items].filter{|o| (o.x > 5) & (o.y > 10)}.sql 
#=> "SELECT * FROM items WHERE ((x > 5) AND (y > 10))" 

DB[:items].filter({:x => 1, :y => 2}.sql_or & ~{:z => 3}).sql 
#=> "SELECT * FROM items WHERE (((x = 1) OR (y = 2)) AND (z != 3))"

You can use arithmetic operators and specify SQL functions:

DB[:items].filter((:x + :y) > :z).sql 
#=> "SELECT * FROM items WHERE ((x + y) > z)" 

DB[:items].filter{|o| :price - 100 < o.avg(:price)}.sql 
#=> "SELECT * FROM items WHERE ((price - 100) < avg(price))"

Ordering

dataset.order(:kind)
dataset.reverse_order(:kind)
dataset.order(:kind.desc, :name)

Row ranges

dataset.limit(30) # LIMIT 30
dataset.limit(30, 10) # LIMIT 30 OFFSET 10

Joins

DB[:items].left_outer_join(:categories, :id => :category_id).sql 
#=> "SELECT * FROM items LEFT OUTER JOIN categories ON categories.id = items.category_id"

DB[:items].join(:categories, :id => :category_id).join(:groups, :id => :items__group_id) 
#=> "SELECT * FROM items INNER JOIN categories ON categories.id = items.category_id INNER JOIN groups ON groups.id = items.group_id"

Summarizing

dataset.count #=> record count
dataset.max(:price)
dataset.min(:price)
dataset.avg(:price)
dataset.sum(:stock)

dataset.group(:category).select(:category, :AVG.sql_function(:price))

SQL Functions / Literals

dataset.update(:updated_at => :NOW.sql_function)
dataset.update(:updated_at => 'NOW()'.lit)

dataset.update(:updated_at => "DateValue('1/1/2001')".lit)
dataset.update(:updated_at => :DateValue.sql_function('1/1/2001'))

Schema Manipulation

DB.create_table :items do
  primary_key :id
  String :name, :unique => true, :null => false
  boolean :active, :default => true
  foreign_key :category_id, :categories
  Time :created_at

  index :grade
end

DB.drop_table :items

DB.create_table :test do
  String :zipcode
  enum :system, :elements => ['mac', 'linux', 'windows']
end

Aliasing

DB[:items].select(:name.as(:item_name))
DB[:items].select(:name___item_name)
DB[:items___items_table].select(:items_table__name___item_name)
# => "SELECT items_table.name AS item_name FROM items AS items_table"

Transactions

DB.transaction do
  dataset.insert(:first_name => 'Inigo', :last_name => 'Montoya')
  dataset.insert(:first_name => 'Farm', :last_name => 'Boy')
end # Either both are inserted or neither are inserted

Database#transaction is re-entrant:

DB.transaction do # BEGIN issued only here
  DB.transaction
    dataset << {:first_name => 'Inigo', :last_name => 'Montoya'}
  end
end # COMMIT issued only here

Transactions are aborted if an error is raised:

DB.transaction do
  raise "some error occurred"
end # ROLLBACK issued and the error is re-raised

Transactions can also be aborted by raising Sequel::Rollback:

DB.transaction do
  raise(Sequel::Rollback) if something_bad_happened
end # ROLLBACK issued and no error raised

Savepoints can be used if the database supports it:

DB.transaction do
  dataset << {:first_name => 'Farm', :last_name => 'Boy'} # Inserted
  DB.transaction(:savepoint=>true) # This savepoint is rolled back
    dataset << {:first_name => 'Inigo', :last_name => 'Montoya'} # Not inserted
    raise(Sequel::Rollback) if something_bad_happened
  end
  dataset << {:first_name => 'Prince', :last_name => 'Humperdink'} # Inserted
end

Miscellaneous:

dataset.sql #=> "SELECT * FROM items"
dataset.delete_sql #=> "DELETE FROM items"
dataset.where(:name => 'sequel').exists #=> "EXISTS ( SELECT * FROM items WHERE name = 'sequel' )"
dataset.columns #=> array of columns in the result set, does a SELECT
DB.schema(:items) => [[:id, {:type=>:integer, ...}], [:name, {:type=>:string, ...}], ...]
Jump to Line
Something went wrong with that request. Please try again.