= 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 << "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'] # same as above # Filter using a subquery 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" == 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 Miscellaneous: dataset.sql #=> "SELECT * FROM items" dataset.delete_sql #=> "DELETE FROM items" dataset.where(:name => 'sequel').exists #=> "EXISTS ( SELECT 1 FROM items WHERE name = 'sequel' )" dataset.columns #=> array of columns in the result set, does a SELECT # Works on PostgreSQL, MySQL, SQLite, and JDBC DB.schema(:items) => [[:id, {:type=>:integer, ...}], [:name, {:type=>:string, ...}], ...]