Sequel ORM Basics in Ruby

Introduction to Sequel

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.

Installing Sequel

# 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 install

Basic Sequel Setup

require '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
end

Dataset Operations

Sequel's dataset API provides a powerful and intuitive way to query databases using method chaining.

Basic Dataset Queries

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)}"

Advanced Dataset Operations

# 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

Dataset Modification

# 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

Sequel models provide an object-oriented interface to database records with associations, validations, and hooks.

Basic Model Definition

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

Working with Models

# 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}" }
end

Migrations in Sequel

Sequel provides a robust migration system for managing database schema changes over time.

Creating Migrations

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]})" }

Advanced Sequel Features

Explore Sequel's advanced capabilities including plugins, transactions, and performance optimization.

Model Plugins and Extensions

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}"

Transactions and Performance

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}"

Sequel Best Practices

Use transactions for operations that need to be atomic
Leverage dataset method chaining for readable and efficient queries
Use prepared statements for frequently executed queries
Implement proper validations in model classes
Use associations to express relationships between models
Enable query logging during development for debugging
Use migrations to manage database schema changes
Take advantage of Sequel plugins for common functionality
Use bulk operations (multi_insert, import) for large datasets
Configure connection pooling appropriately for your application load

Ruby Language

Rails Framework

Production Ready