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')
Without a filename argument, the sqlite adapter will setup a new sqlite database in memory.
DB = Sequel.sqlite
require 'logger' DB = Sequel.sqlite '', :loggers => [Logger.new($stdout)] # or DB.loggers << Logger.new(...)
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 row[:name] end
dataset = DB[:items] dataset = DB.from(:items)
dataset = DB[:managers].where(:salary => 5000..10000).order(:name, :department)
dataset.insert(:name => 'Sharon', :grade => 50)
dataset.each{|r| p r} dataset.all # => [{...}, {...}, ...] dataset.first # => {...}
dataset.exclude(:active).delete dataset.where('price < ?', 100).update(:active => true)
dataset.map{|r| r[:name]} dataset.map(:name) # same as above dataset.inject(0){|sum, r| sum + r[:value]} dataset.sum(:value) # better
dataset.where(:name => 'abc') dataset.where('name = ?', 'abc')
dataset.where{value > 100} dataset.exclude{value <= 100}
dataset.where(:value => 50..100) dataset.where{(value >= 50) & (value <= 100)} dataset.where('value IN ?', [50,75,100]) dataset.where(:value=>[50,75,100]) dataset.where(:id=>other_dataset.select(:other_id))
dataset.where('price > (SELECT avg(price) + 100 FROM table)') dataset.where{price > dataset.select(avg(price) + 100)}
DB[:items].where(Sequel.like(:name, 'AL%')) DB[:items].where(:name => /^AL/)
DB[:items].where{(x > 5) & (y > 10)}.sql # SELECT * FROM items WHERE ((x > 5) AND (y > 10)) DB[:items].where(Sequel.or(:x => 1, :y => 2)) & Sequel.~(:z => 3)).sql # SELECT * FROM items WHERE (((x = 1) OR (y = 2)) AND (z != 3))
DB[:items].where{x + y > z}.sql # SELECT * FROM items WHERE ((x + y) > z) DB[:items].where{price - 100 < avg(price)}.sql # SELECT * FROM items WHERE ((price - 100) < avg(price))
dataset.order(:kind) dataset.reverse_order(:kind) dataset.order(Sequel.desc(:kind), :name)
dataset.limit(30) # LIMIT 30 dataset.limit(30, 10) # LIMIT 30 OFFSET 10
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
dataset.count #=> record count dataset.max(:price) dataset.min(:price) dataset.avg(:price) dataset.sum(:stock) dataset.group_and_count(:category) dataset.select_group(:category).select_append{avg(:price)}
dataset.update(:updated_at => Sequel.function(:NOW)) dataset.update(:updated_at => Sequel.lit('NOW()')) dataset.update(:updated_at => Sequel.lit("DateValue('1/1/2001')") dataset.update(:updated_at => Sequel.function(:DateValue, '1/1/2001'))
DB.create_table :items do primary_key :id String :name, :unique => true, :null => false TrueClass :active, :default => true foreign_key :category_id, :categories DateTime :created_at index :created_at end DB.drop_table :items DB.create_table :test do String :zipcode enum :system, :elements => ['mac', 'linux', 'windows'] end
DB[:items].select(Sequel.as(:name, :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
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
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, ...}], ...]
Generated with the Darkfish Rdoc Generator 2.