Sequel is a simple, flexible, and powerful SQL toolkit and Object-Relational Mapping (ORM) library for Ruby. It provides both a high-level ORM and a low-level database toolkit, making it suitable for everything from simple scripts to complex applications.
# Install Sequel
gem install sequel
# With database adapters
gem install sequel sqlite3 pg mysql2
# Add to Gemfile
gem 'sequel', '~> 5.0'
gem 'sqlite3' # For SQLite
gem 'pg' # For PostgreSQL
gem 'mysql2' # For MySQL
bundle installrequire 'sequel'
# Connect to different databases
# SQLite
DB = Sequel.sqlite('myapp.db')
# PostgreSQL
# DB = Sequel.connect('postgres://user:password@localhost/myapp_development')
# MySQL
# DB = Sequel.connect('mysql2://user:password@localhost/myapp_development')
# Test connection
puts "Connected to #{DB.database_type} database"
# Enable logging
DB.loggers << Logger.new($stdout)
# Create a simple table
DB.create_table? :users do
primary_key :id
String :name, null: false
String :email, null: false, unique: true
Integer :age
Boolean :active, default: true
DateTime :created_at, default: Sequel::CURRENT_TIMESTAMP
DateTime :updated_at, default: Sequel::CURRENT_TIMESTAMP
endSequel's dataset API provides a powerful and intuitive way to query databases using method chaining.
require 'sequel'
DB = Sequel.sqlite('example.db')
# Get dataset for users table
users = DB[:users]
# Insert data
users.insert(name: 'Alice', email: 'alice@example.com', age: 30)
users.insert(name: 'Bob', email: 'bob@example.com', age: 25)
users.insert(name: 'Carol', email: 'carol@example.com', age: 35)
# Basic queries
puts "=== All Users ==="
users.all.each { |user| puts "#{user[:name]} (#{user[:age]})" }
puts "\n=== Active Users ==="
users.where(active: true).each { |user| puts user[:name] }
puts "\n=== Users over 25 ==="
users.where { age > 25 }.each { |user| puts "#{user[:name]} - #{user[:age]}" }
puts "\n=== Specific user ==="
alice = users.where(name: 'Alice').first
puts alice.inspect
# Count and aggregates
puts "\n=== Statistics ==="
puts "Total users: #{users.count}"
puts "Average age: #{users.avg(:age)}"
puts "Oldest user: #{users.max(:age)}"
puts "Youngest user: #{users.min(:age)}"# Complex queries with method chaining
users = DB[:users]
# Multiple conditions
young_active_users = users
.where(active: true)
.where { age < 30 }
.order(:name)
puts "Young active users:"
young_active_users.each { |user| puts user[:name] }
# Selecting specific columns
user_info = users
.select(:name, :email)
.where { age > 25 }
.order(:name)
puts "\nUser info (name, email):"
user_info.each { |user| puts "#{user[:name]} - #{user[:email]}" }
# Limit and offset
paginated_users = users
.order(:created_at)
.limit(2)
.offset(1)
puts "\nPaginated users (limit 2, offset 1):"
paginated_users.each { |user| puts user[:name] }
# Joins (create posts table first)
DB.create_table? :posts do
primary_key :id
foreign_key :user_id, :users
String :title, null: false
Text :content
DateTime :created_at, default: Sequel::CURRENT_TIMESTAMP
end
posts = DB[:posts]
posts.insert(user_id: 1, title: 'First Post', content: 'Hello World!')
posts.insert(user_id: 1, title: 'Second Post', content: 'Learning Sequel')
posts.insert(user_id: 2, title: 'Bob Post', content: 'MySQL is great')
# Join users and posts
user_posts = users
.join(:posts, user_id: :id)
.select(
Sequel[:users][:name].as(:user_name),
Sequel[:posts][:title].as(:post_title),
Sequel[:posts][:content]
)
puts "\nUser posts:"
user_posts.each do |row|
puts "#{row[:user_name]}: #{row[:post_title]}"
end
# Left join to include users without posts
all_users_posts = users
.left_join(:posts, user_id: :id)
.select(
Sequel[:users][:name].as(:user_name),
Sequel[:posts][:title].as(:post_title)
)
.order(Sequel[:users][:name])
puts "\nAll users with their posts (including users without posts):"
all_users_posts.each do |row|
post_title = row[:post_title] || 'No posts'
puts "#{row[:user_name]}: #{post_title}"
end# Update records
users = DB[:users]
# Update single record
users.where(name: 'Alice').update(age: 31)
# Update multiple records
users.where { age < 30 }.update(active: false)
# Update with expressions
users.update(updated_at: Sequel::CURRENT_TIMESTAMP)
# Delete records
users.where(active: false).delete
# Bulk operations
new_users = [
{ name: 'David', email: 'david@example.com', age: 28 },
{ name: 'Eve', email: 'eve@example.com', age: 32 },
{ name: 'Frank', email: 'frank@example.com', age: 27 }
]
# Multi-insert
users.multi_insert(new_users)
# Import (more efficient for large datasets)
users.import([:name, :email, :age], [
['Grace', 'grace@example.com', 29],
['Henry', 'henry@example.com', 34],
['Ivy', 'ivy@example.com', 26]
])
puts "Users after bulk operations:"
users.order(:name).each { |user| puts "#{user[:name]} (#{user[:age]})" }Sequel models provide an object-oriented interface to database records with associations, validations, and hooks.
require 'sequel'
DB = Sequel.sqlite('models_example.db')
# Enable model plugins
Sequel::Model.plugin :timestamps, update_on_create: true
class User < Sequel::Model
# Table is automatically inferred as :users
# You can specify explicitly: set_dataset :users
# Associations
one_to_many :posts
one_to_many :comments
# Validations
def validate
super
errors.add(:name, 'cannot be empty') if !name || name.empty?
errors.add(:email, 'cannot be empty') if !email || email.empty?
errors.add(:email, 'invalid format') if email && !email.match(/@/)
errors.add(:age, 'must be positive') if age && age <= 0
end
# Instance methods
def adult?
age && age >= 18
end
def display_name
"#{name} <#{email}>"
end
# Hooks
def before_create
super
self.created_at = Time.now
self.updated_at = Time.now
end
def before_update
super
self.updated_at = Time.now
end
# Class methods
def self.adults
where { age >= 18 }
end
def self.by_name(name)
where(name: name)
end
def self.active
where(active: true)
end
end
class Post < Sequel::Model
many_to_one :user
one_to_many :comments
def validate
super
errors.add(:title, 'cannot be empty') if !title || title.empty?
errors.add(:user_id, 'must be present') if !user_id
end
def summary(length = 100)
return '' unless content
content.length > length ? content[0..length] + '...' : content
end
end
class Comment < Sequel::Model
many_to_one :user
many_to_one :post
def validate
super
errors.add(:content, 'cannot be empty') if !content || content.empty?
errors.add(:user_id, 'must be present') if !user_id
errors.add(:post_id, 'must be present') if !post_id
end
end
# Create tables
DB.create_table? :users do
primary_key :id
String :name, null: false
String :email, null: false, unique: true
Integer :age
Boolean :active, default: true
DateTime :created_at
DateTime :updated_at
end
DB.create_table? :posts do
primary_key :id
foreign_key :user_id, :users, null: false
String :title, null: false
Text :content
DateTime :created_at
DateTime :updated_at
end
DB.create_table? :comments do
primary_key :id
foreign_key :user_id, :users, null: false
foreign_key :post_id, :posts, null: false
Text :content, null: false
DateTime :created_at
DateTime :updated_at
end# Create users
alice = User.create(name: 'Alice', email: 'alice@example.com', age: 30)
bob = User.create(name: 'Bob', email: 'bob@example.com', age: 25)
puts "Created user: #{alice.display_name}"
puts "Is Alice an adult? #{alice.adult?}"
# Create posts
post1 = Post.create(
user: alice,
title: 'Getting Started with Sequel',
content: 'Sequel is a powerful ORM for Ruby...'
)
post2 = Post.create(
user_id: alice.id,
title: 'Advanced Sequel Features',
content: 'In this post, we will explore advanced features like associations, validations, and hooks.'
)
post3 = Post.create(
user: bob,
title: 'Database Design Patterns',
content: 'Good database design is crucial for application performance...'
)
# Create comments
Comment.create(
user: bob,
post: post1,
content: 'Great introduction! Looking forward to more posts.'
)
Comment.create(
user: alice,
post: post3,
content: 'Thanks for sharing these patterns!'
)
# Query using models
puts "\n=== All Users ==="
User.all.each { |user| puts user.display_name }
puts "\n=== Adult Users ==="
User.adults.each { |user| puts "#{user.name} (#{user.age})" }
puts "\n=== Posts by Alice ==="
alice_posts = alice.posts
alice_posts.each { |post| puts "- #{post.title}" }
# Or query posts directly
alice_posts2 = Post.where(user: alice)
puts "Alice has #{alice_posts2.count} posts"
puts "\n=== Comments on Alice's first post ==="
post1.comments.each do |comment|
puts "#{comment.user.name}: #{comment.content}"
end
# Complex queries
puts "\n=== Posts with Comments ==="
posts_with_comments = Post
.join(:comments, post_id: :id)
.select(Sequel[:posts][:title])
.distinct
posts_with_comments.each { |post| puts "- #{post.title}" }
# Update models
alice.update(age: 31)
puts "\nAlice's updated age: #{alice.age}"
# Validation example
invalid_user = User.new(name: '', email: 'invalid-email')
if invalid_user.valid?
invalid_user.save
else
puts "\nValidation errors:"
invalid_user.errors.full_messages.each { |msg| puts "- #{msg}" }
endSequel provides a robust migration system for managing database schema changes over time.
require 'sequel'
# Migration class
class CreateUsers < Sequel::Migration
def up
create_table :users do
primary_key :id
String :name, null: false
String :email, null: false, unique: true
Integer :age
Boolean :active, default: true
DateTime :created_at, default: Sequel::CURRENT_TIMESTAMP
DateTime :updated_at, default: Sequel::CURRENT_TIMESTAMP
index :email, unique: true
index :active
end
end
def down
drop_table :users
end
end
class CreatePosts < Sequel::Migration
def up
create_table :posts do
primary_key :id
foreign_key :user_id, :users, null: false, on_delete: :cascade
String :title, null: false
Text :content
String :status, default: 'draft'
Integer :view_count, default: 0
DateTime :published_at
DateTime :created_at, default: Sequel::CURRENT_TIMESTAMP
DateTime :updated_at, default: Sequel::CURRENT_TIMESTAMP
index :user_id
index :status
index :published_at
end
end
def down
drop_table :posts
end
end
class AddCategoryToPosts < Sequel::Migration
def up
alter_table :posts do
add_column :category, String, default: 'general'
add_index :category
end
end
def down
alter_table :posts do
drop_index :category
drop_column :category
end
end
end
# Migration runner
class MigrationRunner
def initialize(database)
@db = database
setup_migration_table
end
def run_migrations(migrations_dir = 'migrations')
migration_files = Dir.glob(File.join(migrations_dir, '*.rb')).sort
migration_files.each do |file|
version = File.basename(file, '.rb')
next if migration_applied?(version)
puts "Running migration: #{version}"
# Load migration file
load file
# Get migration class (assumes file name matches class name)
migration_class = Object.const_get(version.split('_').map(&:capitalize).join)
# Run migration
@db.transaction do
migration = migration_class.new
migration.apply(@db, :up)
record_migration(version)
end
puts "Migration #{version} completed"
end
end
def rollback_migration(version)
return unless migration_applied?(version)
puts "Rolling back migration: #{version}"
# Load migration file
require_relative "migrations/#{version}.rb"
migration_class = Object.const_get(version.split('_').map(&:capitalize).join)
@db.transaction do
migration = migration_class.new
migration.apply(@db, :down)
remove_migration_record(version)
end
puts "Migration #{version} rolled back"
end
def pending_migrations(migrations_dir = 'migrations')
all_migrations = Dir.glob(File.join(migrations_dir, '*.rb'))
.map { |f| File.basename(f, '.rb') }
.sort
applied_migrations = @db[:schema_migrations].select_map(:version)
all_migrations - applied_migrations
end
private
def setup_migration_table
@db.create_table? :schema_migrations do
String :version, primary_key: true
DateTime :applied_at, default: Sequel::CURRENT_TIMESTAMP
end
end
def migration_applied?(version)
@db[:schema_migrations].where(version: version).count > 0
end
def record_migration(version)
@db[:schema_migrations].insert(version: version)
end
def remove_migration_record(version)
@db[:schema_migrations].where(version: version).delete
end
end
# Usage example
DB = Sequel.sqlite('migration_example.db')
# Create migrations directory and files
require 'fileutils'
FileUtils.mkdir_p('migrations')
# Save migration files (you would normally create these as separate files)
File.write('migrations/001_create_users.rb', <<~RUBY)
class CreateUsers < Sequel::Migration
def up
create_table :users do
primary_key :id
String :name, null: false
String :email, null: false, unique: true
Integer :age
Boolean :active, default: true
DateTime :created_at, default: Sequel::CURRENT_TIMESTAMP
end
end
def down
drop_table :users
end
end
RUBY
File.write('migrations/002_create_posts.rb', <<~RUBY)
class CreatePosts < Sequel::Migration
def up
create_table :posts do
primary_key :id
foreign_key :user_id, :users, null: false
String :title, null: false
Text :content
DateTime :created_at, default: Sequel::CURRENT_TIMESTAMP
end
end
def down
drop_table :posts
end
end
RUBY
# Run migrations
runner = MigrationRunner.new(DB)
puts "Pending migrations: #{runner.pending_migrations}"
runner.run_migrations
# Check applied migrations
applied = DB[:schema_migrations].all
puts "Applied migrations:"
applied.each { |m| puts "- #{m[:version]} (#{m[:applied_at]})" }Explore Sequel's advanced capabilities including plugins, transactions, and performance optimization.
require 'sequel'
DB = Sequel.sqlite(':memory:')
# Enable useful plugins globally
Sequel::Model.plugin :validation_helpers
Sequel::Model.plugin :timestamps
Sequel::Model.plugin :dirty
Sequel::Model.plugin :serialization
class User < Sequel::Model
plugin :secure_password # bcrypt integration
# Serialization
serialize_attributes :json, :preferences
serialize_attributes :yaml, :settings
# Validation helpers
def validate
super
validates_presence [:name, :email]
validates_unique :email
validates_format /\A[\w+\-.]+@[a-z\d\-]+(\.[a-z]+)*\z/i, :email
validates_integer :age, minimum: 0, maximum: 150
end
# Dirty tracking (tracks changes)
def log_changes
if changed_columns.any?
puts "Changed columns: #{changed_columns}"
changed_columns.each do |col|
puts " #{col}: #{initial_value(col)} -> #{self[col]}"
end
end
end
# Custom setters/getters
def age=(value)
super(value.to_i) if value
end
def preferences
super || {}
end
def set_preference(key, value)
prefs = preferences
prefs[key.to_s] = value
self.preferences = prefs
end
def get_preference(key)
preferences[key.to_s]
end
end
class Post < Sequel::Model
plugin :tree # Hierarchical data (parent/child relationships)
plugin :list # Ordered lists
plugin :touch # Touch associated records
many_to_one :user, touch: true
many_to_one :parent, class: self
one_to_many :children, key: :parent_id, class: self
def validate
super
validates_presence [:title, :content, :user_id]
validates_min_length 3, :title
validates_min_length 10, :content
end
# Tree structure methods (from tree plugin)
def ancestors_titles
ancestors.map(&:title)
end
def descendants_count
descendants.count
end
end
# Create tables
DB.create_table :users do
primary_key :id
String :name, null: false
String :email, null: false, unique: true
String :password_digest
Integer :age
Text :preferences # JSON serialization
Text :settings # YAML serialization
DateTime :created_at
DateTime :updated_at
end
DB.create_table :posts do
primary_key :id
foreign_key :user_id, :users, null: false
foreign_key :parent_id, :posts # For tree structure
String :title, null: false
Text :content, null: false
Integer :position # For list ordering
DateTime :created_at
DateTime :updated_at
end
# Usage examples
user = User.create(
name: 'Alice',
email: 'alice@example.com',
password: 'secret123',
age: 30
)
# Serialization
user.set_preference('theme', 'dark')
user.set_preference('notifications', true)
user.settings = { language: 'en', timezone: 'UTC' }
user.save
puts "User theme: #{user.get_preference('theme')}"
puts "User settings: #{user.settings}"
# Dirty tracking
user.name = 'Alice Johnson'
user.age = 31
user.log_changes
user.save
# Tree structure
parent_post = Post.create(
user: user,
title: 'Parent Post',
content: 'This is a parent post with children'
)
child_post1 = Post.create(
user: user,
parent: parent_post,
title: 'Child Post 1',
content: 'This is the first child post'
)
child_post2 = Post.create(
user: user,
parent: parent_post,
title: 'Child Post 2',
content: 'This is the second child post'
)
puts "Parent post children: #{parent_post.children.map(&:title)}"
puts "Child post ancestors: #{child_post1.ancestors_titles}"require 'sequel'
require 'benchmark'
DB = Sequel.sqlite(':memory:')
# Create test table
DB.create_table :items do
primary_key :id
String :name
Integer :value
DateTime :created_at, default: Sequel::CURRENT_TIMESTAMP
end
items = DB[:items]
# Performance comparison: with and without transactions
puts "=== Performance Comparison ==="
# Without transaction (slow)
time_without = Benchmark.measure do
1000.times do |i|
items.insert(name: "Item #{i}", value: rand(100))
end
end
# Clear table
items.delete
# With transaction (fast)
time_with = Benchmark.measure do
DB.transaction do
1000.times do |i|
items.insert(name: "Item #{i}", value: rand(100))
end
end
end
puts "Without transaction: #{time_without.real.round(3)}s"
puts "With transaction: #{time_with.real.round(3)}s"
puts "Speed improvement: #{(time_without.real / time_with.real).round(1)}x faster"
# Advanced transaction features
puts "\n=== Transaction Features ==="
# Savepoints
DB.transaction(savepoint: true) do
items.insert(name: 'Test 1', value: 10)
DB.transaction(savepoint: true) do
items.insert(name: 'Test 2', value: 20)
# This savepoint will be rolled back
DB.transaction(savepoint: true) do
items.insert(name: 'Test 3', value: 30)
raise Sequel::Rollback # Rollback just this savepoint
end
puts "After inner rollback: #{items.where(name: /Test/).count} test items"
end
end
# Transaction isolation levels
DB.transaction(isolation: :serializable) do
# Perform operations that require serializable isolation
critical_items = items.where { value > 50 }
max_value = critical_items.max(:value)
if max_value < 90
items.insert(name: 'High Value Item', value: 95)
end
end
# Optimistic locking example (requires timestamp column)
class OptimisticItem < Sequel::Model(:items)
plugin :optimistic_locking
# This would require a lock_version column in real usage
end
# Bulk operations for performance
puts "\n=== Bulk Operations ==="
# Multi-insert (efficient)
bulk_data = 100.times.map do |i|
{ name: "Bulk Item #{i}", value: rand(50) }
end
time_multi = Benchmark.measure do
items.multi_insert(bulk_data)
end
puts "Multi-insert for 100 records: #{time_multi.real.round(3)}s"
# Import (even more efficient for large datasets)
import_data = 1000.times.map do |i|
["Import Item #{i}", rand(50)]
end
time_import = Benchmark.measure do
items.import([:name, :value], import_data)
end
puts "Import for 1000 records: #{time_import.real.round(3)}s"
# Batch processing
puts "\n=== Batch Processing ==="
# Process records in batches to avoid memory issues
total_processed = 0
items.where { value > 25 }.paged_each(rows_per_fetch: 100) do |item|
# Process each item
total_processed += 1
# Update item (example processing)
items.where(id: item[:id]).update(value: item[:value] * 2)
end
puts "Processed #{total_processed} items in batches"
# Connection pooling for multi-threaded apps
puts "\n=== Connection Pooling ==="
# Configure connection pool
DB.pool.max_size = 10
DB.pool.timeout = 5
# Simulate concurrent operations
threads = []
5.times do |i|
threads << Thread.new do
DB.synchronize do |conn|
result = conn[:items].where { value < 50 }.count
puts "Thread #{i}: Found #{result} items with value < 50"
sleep(0.1) # Simulate work
end
end
end
threads.each(&:join)
# Pool statistics
puts "Connection pool size: #{DB.pool.size}"
puts "Connection pool max size: #{DB.pool.max_size}"Watch and learn sequel orm basics