Overview

Sequel provides database access and ORM functionality for Ruby applications through a flexible, composable API. The toolkit supports multiple database adapters including PostgreSQL, MySQL, SQLite, and others, offering both raw SQL execution and high-level model abstractions.

The core architecture centers around the Sequel::Database class for connections, Sequel::Dataset for query building, and Sequel::Model for object-relational mapping. Sequel emphasizes explicit control over database operations while providing convenient abstractions for common patterns.

# Database connection
DB = Sequel.connect('postgres://user:pass@localhost/mydb')

# Raw query execution
DB['SELECT * FROM users WHERE active = ?', true].all
# => [{id: 1, name: "Alice", active: true}, ...]

# Dataset query building
DB[:users].where(active: true).select(:id, :name).all
# => [{id: 1, name: "Alice"}, {id: 2, name: "Bob"}]

Sequel datasets use method chaining for query construction, with each method returning a new dataset object rather than modifying the original. This functional approach enables query reuse and composition without side effects.

active_users = DB[:users].where(active: true)
admin_users = active_users.where(role: 'admin')
recent_admins = admin_users.where(created_at: Date.today - 30)

The model layer provides ActiveRecord-style functionality with associations, validations, and hooks while maintaining Sequel's explicit philosophy. Models inherit from Sequel::Model and automatically infer table structure and relationships.

Basic Usage

Database connections require specifying an adapter and connection parameters through a connection string or hash. Sequel supports connection pooling, prepared statements, and transaction management across all supported adapters.

# Connection string format
DB = Sequel.connect('adapter://user:password@host:port/database')

# Hash format with options
DB = Sequel.connect(
  adapter: 'postgres',
  host: 'localhost',
  database: 'myapp',
  user: 'postgres',
  password: 'secret',
  max_connections: 10
)

Dataset operations form the foundation of Sequel's query interface. Datasets represent SQL queries and support method chaining for building complex conditions, joins, and aggregations.

# Basic filtering and selection
users = DB[:users]
active_users = users.where(active: true)
user_names = active_users.select(:id, :name, :email)

# Ordering and limiting
recent_users = users.order(Sequel.desc(:created_at)).limit(10)

# Aggregation
user_count = users.where(active: true).count
# => 42

# Grouping
user_stats = DB[:orders]
  .group(:user_id)
  .select(:user_id, Sequel.function(:count, :*).as(:order_count))

Model definition establishes the relationship between Ruby classes and database tables. Sequel automatically detects primary keys, foreign keys, and column types based on database schema.

class User < Sequel::Model
  # Table automatically inferred as 'users'
  # Primary key automatically detected
  
  one_to_many :orders
  many_to_one :company
  
  def before_save
    self.updated_at = Time.now
    super
  end
end

# Model usage
user = User.new(name: 'Alice', email: 'alice@example.com')
user.save

# Finding records
User[1]  # Find by primary key
User.first(name: 'Alice')
User.where(active: true).all

CRUD operations work through both dataset and model interfaces. The dataset interface provides direct SQL-like operations, while models offer object-oriented persistence methods.

# Dataset CRUD
DB[:users].insert(name: 'Bob', email: 'bob@example.com')
DB[:users].where(id: 1).update(active: false)
DB[:users].where(active: false).delete

# Model CRUD
user = User.create(name: 'Charlie', email: 'charlie@example.com')
user.update(active: false)
user.delete

Advanced Usage

Query composition enables building reusable query components and dynamic filtering logic. Sequel datasets support complex joins, subqueries, and window functions for sophisticated data retrieval patterns.

# Complex joins with multiple tables
detailed_orders = DB[:orders]
  .join(:users, id: :user_id)
  .join(:products, id: Sequel[:orders][:product_id])
  .select(
    Sequel[:orders][:id].as(:order_id),
    Sequel[:users][:name].as(:user_name),
    Sequel[:products][:title].as(:product_name),
    Sequel[:orders][:total]
  )

# Subquery usage
expensive_orders = DB[:orders].where(total: 100..Float::INFINITY)
high_value_users = DB[:users].where(
  id: expensive_orders.select(:user_id).distinct
)

# Window functions for analytics
ranked_sales = DB[:sales]
  .select(
    :*,
    Sequel.function(:row_number)
      .over(partition: :region, order: Sequel.desc(:amount))
      .as(:rank)
  )

Custom dataset methods extend query functionality for domain-specific operations. These methods become available on all datasets and support method chaining like built-in operations.

module DatasetExtensions
  def active
    where(active: true)
  end
  
  def by_date_range(start_date, end_date)
    where(created_at: start_date..end_date)
  end
  
  def with_stats
    select_append(
      Sequel.function(:count, :*).over.as(:total_count),
      Sequel.function(:avg, :amount).over.as(:avg_amount)
    )
  end
end

Sequel::Dataset.register_extension(:custom, DatasetExtensions)
DB.extension(:custom)

# Usage of custom methods
recent_active_orders = DB[:orders]
  .active
  .by_date_range(Date.today - 7, Date.today)
  .with_stats

Model plugins extend functionality through mixins that add methods, validations, and callbacks. Sequel includes numerous plugins for common patterns like timestamps, soft deletes, and serialization.

class User < Sequel::Model
  plugin :timestamps, update_on_create: true
  plugin :validation_helpers
  plugin :json_serializer
  plugin :dirty  # Track attribute changes
  
  def validate
    super
    validates_presence [:name, :email]
    validates_unique :email
    validates_format /\A[^@\s]+@[^@\s]+\z/, :email
  end
  
  def before_update
    if column_changed?(:email)
      self.email_verified = false
    end
    super
  end
end

# Plugin functionality
user = User.new(name: 'Dave')
user.valid?  # => false (missing email)
user.email = 'dave@example.com'
user.save

user.update(email: 'newemail@example.com')
user.email_verified  # => false (automatically set by hook)

Association customization handles complex relationship patterns through options and custom methods. Associations support eager loading, conditions, and custom join logic.

class User < Sequel::Model
  one_to_many :orders, order: Sequel.desc(:created_at)
  one_to_many :recent_orders, class: :Order,
    conditions: { created_at: Date.today - 30..Date.today }
  
  many_to_many :roles, join_table: :user_roles,
    left_key: :user_id, right_key: :role_id
  
  one_to_one :profile, key: :user_id
  
  def admin?
    roles.any? { |role| role.name == 'admin' }
  end
end

class Order < Sequel::Model
  many_to_one :user
  one_to_many :line_items
  many_to_one :shipping_address, class: :Address, key: :ship_address_id
  
  dataset_module do
    def shipped
      where(status: 'shipped')
    end
    
    def high_value(threshold = 100)
      where { total > threshold }
    end
  end
end

# Association usage with eager loading
users_with_orders = User.eager(:orders, :profile).where(active: true)
admin_users = User.eager(:roles).select { |u| u.admin? }

Error Handling & Debugging

Database connection errors require retry logic and graceful degradation strategies. Sequel provides connection pooling that handles temporary failures and connection timeouts automatically.

# Connection error handling
begin
  DB = Sequel.connect(DATABASE_URL, 
    max_connections: 10,
    pool_timeout: 5,
    retry_on_disconnect: true
  )
rescue Sequel::DatabaseConnectionError => e
  logger.error "Database connection failed: #{e.message}"
  raise unless Rails.env.development?
  # Fallback to SQLite in development
  DB = Sequel.sqlite
end

# Query execution error handling
def safe_user_lookup(email)
  User.first(email: email)
rescue Sequel::DatabaseError => e
  logger.warn "Database query failed: #{e.message}"
  nil
rescue Sequel::NoMatchingRow => e
  logger.info "User not found: #{email}"
  nil
end

Validation errors in models provide structured feedback for data integrity issues. Sequel's validation helpers generate detailed error messages for different validation types.

class User < Sequel::Model
  plugin :validation_helpers
  
  def validate
    super
    validates_presence [:name, :email], message: "is required"
    validates_unique :email, message: "already exists"
    validates_format /\A[^@\s]+@[^@\s]+\z/, :email, 
      message: "must be valid email format"
    validates_length_range 8..100, :password, 
      message: "must be between 8 and 100 characters"
  end
end

# Handling validation errors
user = User.new(name: '', email: 'invalid-email')
unless user.valid?
  user.errors.full_messages.each do |error|
    puts error
  end
  # => "name is required"
  # => "email must be valid email format"
end

# Rescue validation errors during save
begin
  user.save
rescue Sequel::ValidationFailed => e
  errors = e.model.errors.full_messages
  render json: { errors: errors }, status: 422
end

SQL query debugging utilizes Sequel's logging capabilities to inspect generated queries, parameter binding, and execution timing. The logger captures all database operations including transactions.

# Enable query logging
DB.loggers << Logger.new($stdout)

# Log specific operations
DB.transaction do
  user = User.create(name: 'Test User', email: 'test@example.com')
  # Logs: INSERT INTO users (name, email) VALUES ('Test User', 'test@example.com')
  
  orders = user.orders_dataset.where(status: 'pending')
  # Logs: SELECT * FROM orders WHERE (user_id = 1) AND (status = 'pending')
end

# Custom query inspection
dataset = DB[:users].where(active: true).order(:name)
puts dataset.sql
# => SELECT * FROM users WHERE (active IS TRUE) ORDER BY name

# Parameter binding inspection
filtered_users = DB[:users].where(created_at: Date.today..Date.today + 1)
puts filtered_users.sql
# => SELECT * FROM users WHERE (created_at >= '2023-01-01' AND created_at < '2023-01-02')

Transaction debugging tracks nested transactions, rollback scenarios, and deadlock detection. Sequel provides transaction hooks for monitoring and error recovery.

# Transaction monitoring
class TransactionMonitor
  def self.around_transaction(db)
    start_time = Time.now
    result = yield
    duration = Time.now - start_time
    Rails.logger.info "Transaction completed in #{duration}s"
    result
  rescue => e
    Rails.logger.error "Transaction failed: #{e.message}"
    raise
  end
end

DB.transaction(rollback: :reraise) do |conn|
  TransactionMonitor.around_transaction(DB) do
    # Complex multi-table operations
    user = User.create(name: 'New User', email: 'new@example.com')
    profile = Profile.create(user_id: user.id, bio: 'User bio')
    
    # Simulate potential failure point
    raise "Simulated error" if rand < 0.5
    
    Order.create(user_id: user.id, total: 99.99)
  end
end

Performance & Memory

Query optimization focuses on efficient SQL generation, proper indexing usage, and minimizing database round trips. Sequel's dataset methods generate optimized queries that leverage database-specific features.

# Efficient bulk operations
users_data = [
  { name: 'User 1', email: 'user1@example.com' },
  { name: 'User 2', email: 'user2@example.com' },
  # ... 1000 more records
]

# Bulk insert - single query instead of 1000
DB[:users].multi_insert(users_data)

# Bulk update with conditions
DB[:users].where(active: false).update(status: 'inactive')

# Efficient counting with exists
has_orders = DB[:orders].where(user_id: user.id).exists
# Better than: orders_count = DB[:orders].where(user_id: user.id).count > 0

Memory management becomes critical when processing large result sets. Sequel provides streaming interfaces and lazy evaluation to handle datasets that exceed available memory.

# Memory-efficient iteration over large datasets
DB[:large_table].paged_each(rows_per_fetch: 1000) do |row|
  # Process one row at a time
  # Only 1000 rows held in memory at once
  process_row(row)
end

# Lazy dataset evaluation
large_dataset = DB[:orders].where(created_at: Date.today - 365..Date.today)
# No query executed yet

processed_count = 0
large_dataset.each do |order|
  # Query executed when iteration begins
  # Processes rows as they're fetched
  process_order(order)
  processed_count += 1
  
  # Periodic progress logging
  puts "Processed #{processed_count} orders" if processed_count % 1000 == 0
end

Connection pooling configuration affects both performance and resource utilization. Pool size, timeout settings, and connection validation prevent resource exhaustion and improve response times.

# Production connection pool settings
DB = Sequel.connect(DATABASE_URL,
  max_connections: 20,          # Pool size based on server capacity
  pool_timeout: 5,              # Wait time for available connection
  pool_sleep_time: 0.001,       # Sleep between connection attempts
  pool_connection_validation: true,  # Validate connections before use
  
  # Connection-specific optimizations
  prepared_statements: true,     # Cache prepared statements
  prepared_statements_cache_size: 100,
  
  # Timeout settings
  connect_timeout: 10,          # Initial connection timeout
  read_timeout: 30,             # Query execution timeout
  write_timeout: 30             # Write operation timeout
)

# Monitor pool usage
pool_stats = DB.pool.instance_eval do
  {
    size: @allocated.size,
    available: @available.size,
    created: @created_count
  }
end
puts "Pool stats: #{pool_stats}"

Query performance analysis identifies bottlenecks through execution plan examination and timing measurement. Sequel integrates with database-specific profiling tools for detailed performance insights.

# Query timing measurement
def benchmark_query(description, &block)
  start_time = Time.now
  result = yield
  duration = Time.now - start_time
  puts "#{description}: #{(duration * 1000).round(2)}ms"
  result
end

# Compare query strategies
users = benchmark_query("N+1 query pattern") do
  User.all.map { |user| [user.name, user.orders.count] }
end

users = benchmark_query("Optimized with eager loading") do
  User.eager(:orders).map { |user| [user.name, user.orders.length] }
end

# Explain query execution
complex_query = DB[:users]
  .join(:orders, user_id: :id)
  .where(active: true)
  .group(:user_id)
  .having { count(:*) > 5 }

puts complex_query.explain
# Database-specific execution plan output

Production Patterns

Application integration with web frameworks requires careful database connection management, transaction handling, and error recovery strategies. Sequel integrates seamlessly with Rack-based applications and provides middleware for connection management.

# Rails integration
class ApplicationController < ActionController::Base
  around_action :wrap_in_transaction, only: [:create, :update, :destroy]
  
  private
  
  def wrap_in_transaction
    DB.transaction(rollback: :reraise) do
      yield
    rescue ActiveRecord::RecordInvalid => e
      # Handle validation errors gracefully
      render json: { errors: e.record.errors.full_messages }, 
        status: 422
      raise Sequel::Rollback
    end
  end
end

# Sinatra integration with connection management
class APIApp < Sinatra::Base
  before do
    # Ensure fresh connection for each request
    DB.test_connection
  rescue Sequel::DatabaseDisconnectError
    DB.disconnect
    DB.test_connection
  end
  
  after do
    # Clean up any open transactions
    DB.rollback_on_exit = false
  end
end

Background job processing requires robust error handling and transaction management to ensure data consistency across job failures and retries.

class OrderProcessor
  include Sidekiq::Worker
  sidekiq_options retry: 3, backtrace: true
  
  def perform(order_id)
    DB.transaction do
      order = Order[order_id]
      raise "Order not found: #{order_id}" unless order
      
      # Process payment
      payment_result = PaymentService.charge(order.total, order.payment_method)
      
      # Update order status
      order.update(
        status: 'paid',
        payment_id: payment_result.id,
        processed_at: Time.now
      )
      
      # Send confirmation email
      OrderMailer.confirmation(order.id).deliver_now
      
      # Log successful processing
      Rails.logger.info "Order #{order_id} processed successfully"
    end
  rescue => e
    # Log error details for debugging
    Rails.logger.error "Order processing failed: #{e.message}"
    Rails.logger.error e.backtrace.join("\n")
    raise  # Re-raise for Sidekiq retry logic
  end
end

Monitoring and observability require instrumentation of database operations, connection pool metrics, and query performance tracking. Integration with APM tools provides production insights.

# Custom instrumentation
module DatabaseInstrumentation
  def self.instrument_queries
    DB.extension :connection_validator
    DB.pool.connection_validation_timeout = 3600
    
    # Hook into query execution
    DB.log_info_level = :debug
    DB.sql_log_level = :debug
    
    # Track slow queries
    DB.define_singleton_method(:log_duration) do |duration, sql|
      if duration > 1.0  # Queries slower than 1 second
        Rails.logger.warn "Slow query (#{duration}s): #{sql}"
        # Send to monitoring service
        StatsD.histogram('database.slow_query', duration, tags: ['table:users'])
      end
      super(duration, sql)
    end
  end
end

# Health check endpoint
class HealthController < ApplicationController
  def database
    start_time = Time.now
    DB.test_connection
    duration = Time.now - start_time
    
    pool_info = {
      size: DB.pool.size,
      max_size: DB.pool.max_size,
      available: DB.pool.available_connections.size
    }
    
    render json: {
      status: 'healthy',
      response_time: "#{(duration * 1000).round(2)}ms",
      pool: pool_info
    }
  rescue => e
    render json: { 
      status: 'unhealthy', 
      error: e.message 
    }, status: 503
  end
end

Deployment considerations include database migrations, schema management, and zero-downtime deployment strategies. Sequel provides migration tools that support incremental schema changes.

# Migration management
Sequel.migration do
  up do
    create_table(:user_preferences) do
      primary_key :id
      foreign_key :user_id, :users, null: false
      String :key, null: false
      Text :value
      DateTime :created_at, null: false
      DateTime :updated_at, null: false
      
      index [:user_id, :key], unique: true
    end
    
    # Populate default preferences for existing users
    from(:users).select(:id).each do |user|
      from(:user_preferences).insert(
        user_id: user[:id],
        key: 'email_notifications',
        value: 'true',
        created_at: Time.now,
        updated_at: Time.now
      )
    end
  end
  
  down do
    drop_table(:user_preferences)
  end
end

# Deployment script
class DatabaseMigrator
  def self.migrate_safely
    # Check for pending migrations
    Sequel::Migrator.check_current(DB, 'db/migrations')
    
    # Run migrations with timeout
    Timeout::timeout(300) do  # 5 minute timeout
      Sequel::Migrator.run(DB, 'db/migrations', 
        allow_missing_migration_files: true
      )
    end
    
    puts "Database migration completed successfully"
  rescue => e
    puts "Migration failed: #{e.message}"
    exit 1
  end
end

Reference

Database Connection Methods

Dataset Query Methods

Dataset Execution Methods

Model Class Methods

Model Instance Methods

Association Methods

Common Options

Connection Options:

• :max_connections - Pool size (Integer)
• :pool_timeout - Connection wait timeout (Numeric)
• :retry_on_disconnect - Retry failed connections (Boolean)
• :prepared_statements - Enable prepared statements (Boolean)

Query Options:

• :server - Database server/shard to use (Symbol)
• :graph - Include associated objects (Hash)
• :eager - Eager load associations (Symbol/Array)
• :lock - Row locking mode (String/Symbol)

Validation Plugin Methods:

• validates_presence(attributes, **opts)
• validates_unique(attributes, **opts)
• validates_format(regexp, attributes, **opts)
• validates_length_range(range, attributes, **opts)
• validates_type(type, attributes, **opts)

Error Classes