cheat_sheet.rdoc

doc/cheat_sheet.rdoc
Last Update: 2022-10-21 08:19:04 -0700

Cheat Sheet

Open a database

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($stdout)

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 row[: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 # => {...}
dataset.last # => {...}

Update/Delete rows

dataset.exclude(:active).delete
dataset.where{price < 100}.update(active: true)
dataset.where(:active).update(price: Sequel[:price] * 0.90)

Merge rows

dataset.
  merge_using(:table, col1: :col2).
  merge_insert(col3: :col4).
  merge_delete{col5 > 30}.
  merge_update(col3: Sequel[:col3] + :col4)

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) # better

Filtering (see also Dataset Filtering)

Equality

dataset.where(name: 'abc')

Inequality

dataset.where{value > 100}
dataset.exclude{value <= 100}

Inclusion

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

dataset.where(value: [50,75,100])
dataset.where(id: other_dataset.select(:other_id))

Subselects as scalar values

dataset.where{price > dataset.select(avg(price) + 100)}

LIKE/Regexp

DB[:items].where(Sequel.like(:name, 'AL%'))
DB[:items].where(name: /^AL/)

AND/OR/NOT

DB[:items].where{(x > 5) & (y > 10)} 
# SELECT * FROM items WHERE ((x > 5) AND (y > 10))

DB[:items].where(Sequel.or(x: 1, y: 2) & Sequel.~(z: 3)) 
# SELECT * FROM items WHERE (((x = 1) OR (y = 2)) AND (z != 3))

Mathematical operators

DB[:items].where{x + y > z} 
# SELECT * FROM items WHERE ((x + y) > z)

DB[:items].where{price - 100 < avg(price)} 
# SELECT * FROM items WHERE ((price - 100) < avg(price))

Raw SQL Fragments

dataset.where(Sequel.lit('id= 1'))
dataset.where(Sequel.lit('name = ?', 'abc'))
dataset.where(Sequel.lit('value IN ?', [50,75,100]))
dataset.where(Sequel.lit('price > (SELECT avg(price) + 100 FROM table)'))

Ordering

dataset.order(:kind) # kind
dataset.reverse(:kind) # kind DESC
dataset.order(Sequel.desc(:kind), :name) # kind DESC, name

Limit/Offset

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

Joins

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

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

Aggregate functions methods

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

dataset.group_and_count(:category).all
dataset.select_group(:category).select_append{avg(:price)}

SQL Functions / Literals

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'))

Schema Manipulation

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, default: Sequel::CURRENT_TIMESTAMP, index: true

  index [:category_id, :active]
end

DB.drop_table :items

Aliasing

DB[:items].select(Sequel[:name].as(:item_name))
DB[:items].select(Sequel.as(:name, :item_name))
DB[:items].select{name.as(:item_name)}
# SELECT name AS item_name FROM items

DB[Sequel[:items].as(:items_table)].select{items_table[:name].as(:item_name)}
# SELECT items_table.name AS item_name FROM items AS items_table

Transactions

DB.transaction do
  # BEGIN
  dataset.insert(first_name: 'Inigo', last_name: 'Montoya')
  dataset.insert(first_name: 'Farm', last_name: 'Boy')
end
# COMMIT

Transactions are reentrant:

DB.transaction do
  # BEGIN
  DB.transaction do
    dataset.insert(first_name: 'Inigo', last_name: 'Montoya')
  end
end
# COMMIT

Transactions are aborted if an error is raised:

DB.transaction do
  # BEGIN
  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
  # BEGIN
  raise(Sequel::Rollback)
end
# ROLLBACK issued and no error raised

Savepoints can be used if the database supports it:

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

Retrieving SQL

dataset.sql # "SELECT * FROM items"
dataset.insert_sql(a: 1) # "INSERT INTO items (a) VALUES (1)"
dataset.update_sql(a: 1) # "UPDATE items SET a = 1"
dataset.delete_sql # "DELETE FROM items"

Basic introspection

dataset.columns # => [:id, :name, ...]
DB.tables # => [:items, ...]
DB.views # => [:new_items, ...]
DB.schema(:items) # => [[:id, {:type=>:integer, ...}], [:name, {:type=>:string, ...}], ...]
DB.indexes(:items) # => {:index_name => {:columns=>[:a], :unique=>false}, ...}
DB.foreign_key_list(:items) # => [{:name=>:items_a_fk, :columns=>[:a], :key=>[:id], :table=>:other_table}, ...]