Chapter 4: Database

1 Ruby Database Ecosystem

Ruby has a rich database ecosystem. Unlike PHP's built-in PDO, Ruby relies on gems for database access. The two dominant ORMs are ActiveRecord (from Rails) and Sequel, each with distinct philosophies. For direct database access, gems like sqlite3 and pg provide low-level drivers.

Key Database Gems

Installation

# Gemfile
gem 'sqlite3'          # SQLite driver
gem 'pg'               # PostgreSQL driver
gem 'activerecord'     # ActiveRecord ORM (standalone, no Rails needed)
gem 'sequel'           # Sequel ORM

bundle install

2 Direct SQLite3 Operations

The sqlite3 gem provides direct access to SQLite databases without an ORM. This is useful for scripts, CLI tools, and simple applications where a full ORM is overkill.

Connect & Create Table

require 'sqlite3'

db = SQLite3::Database.new('myapp.db')
db.results_as_hash = true

db.execute <<-SQL
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
SQL

Insert & Query

db.execute('INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
           ['Alice', 'alice@example.com', 28])

new_id = db.last_insert_row_id
puts "New user ID: #{new_id}"

users = db.execute('SELECT * FROM users WHERE age >= ?', [25])
users.each do |row|
  puts "#{row['name']} (#{row['age']}) - #{row['email']}"
end

user = db.get_first_row('SELECT * FROM users WHERE id = ?', [1])
puts user['name'] if user

Prepared Statements

stmt = db.prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)')

[
  ['Bob',   'bob@example.com',   32],
  ['Carol', 'carol@example.com', 25],
  ['Dave',  'dave@example.com',  30]
].each do |user|
  stmt.execute(user)
end

stmt.close

Update & Delete

db.execute('UPDATE users SET name = ?, age = ? WHERE id = ?',
           ['Alice Smith', 29, 1])
puts "Rows updated: #{db.changes}"

db.execute('DELETE FROM users WHERE id = ?', [3])
puts "Rows deleted: #{db.changes}"

3 ActiveRecord Standalone

ActiveRecord can be used outside of Rails. It provides migrations, validations, associations, and query interface — all available as a standalone gem.

Setup Without Rails

require 'active_record'

ActiveRecord::Base.establish_connection(
  adapter:  'sqlite3',
  database: 'myapp.db'
)

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
    t.string  :title,  null: false
    t.text    :body
    t.string  :status, default: 'draft'
    t.timestamps
  end

  create_table :comments, force: true do |t|
    t.references :post, foreign_key: true
    t.string     :author
    t.text       :content
    t.timestamps
  end
end

Define Models

class Post < ActiveRecord::Base
  has_many :comments, dependent: :destroy

  validates :title, presence: true, length: { maximum: 200 }
  validates :status, inclusion: { in: %w[draft published archived] }

  scope :published, -> { where(status: 'published') }
  scope :recent,    -> { order(created_at: :desc) }
end

class Comment < ActiveRecord::Base
  belongs_to :post

  validates :author,  presence: true
  validates :content, presence: true
end

CRUD Operations

post = Post.create!(
  title:  'Getting Started with Ruby',
  body:   'Ruby is a dynamic, object-oriented language...',
  status: 'published'
)

post.comments.create!(author: 'Bob', content: 'Great article!')
post.comments.create!(author: 'Carol', content: 'Very helpful, thanks!')

all_posts = Post.all
published  = Post.published.recent
found      = Post.find_by(title: 'Getting Started with Ruby')

post.update!(title: 'Getting Started with Ruby 3.3')

old_post = Post.find(1)
old_post.destroy

Query Interface

Post.where(status: 'published')
    .where('created_at > ?', 1.week.ago)
    .order(created_at: :desc)
    .limit(10)

Post.where(status: %w[draft published])
Post.where('title LIKE ?', '%Ruby%')

count = Post.where(status: 'published').count
exists = Post.exists?(title: 'Hello')

Post.select(:id, :title, :status)
    .where.not(status: 'archived')
    .pluck(:title)

Validations in Action

post = Post.new(title: '', status: 'invalid')

unless post.valid?
  post.errors.full_messages.each do |msg|
    puts "Validation error: #{msg}"
  end
end

begin
  Post.create!(title: '')
rescue ActiveRecord::RecordInvalid => e
  puts "Failed: #{e.message}"
end

4 Sequel ORM

Sequel is a lightweight alternative to ActiveRecord. It provides both a powerful dataset API (query builder) and an optional model layer. Sequel is known for its clean design, excellent documentation, and support for advanced SQL features.

Connect & Create Table

require 'sequel'

DB = Sequel.sqlite('myapp.db')
# DB = Sequel.connect('postgres://user:pass@localhost/mydb')

DB.create_table? :products do
  primary_key :id
  String  :name,     null: false
  Float   :price,    null: false
  Integer :stock,    default: 0
  String  :category
  DateTime :created_at, default: Sequel::CURRENT_TIMESTAMP
end

Dataset API (Query Builder)

products = DB[:products]

products.insert(name: 'Ruby Book', price: 29.99, stock: 100, category: 'books')
products.insert(name: 'Keyboard',  price: 89.99, stock: 50,  category: 'electronics')
products.insert(name: 'Mouse',     price: 39.99, stock: 200, category: 'electronics')

all = products.all
puts all.inspect

cheap = products.where { price < 50 }.order(:price).all
electronics = products.where(category: 'electronics')
                      .select(:name, :price)
                      .all

total = products.sum(:price)
count = products.where(category: 'books').count
avg   = products.avg(:price)

products.where(id: 1).update(price: 24.99, stock: 95)

products.where { stock < 10 }.delete

Sequel Model Layer

class Product < Sequel::Model
  plugin :validation_helpers
  plugin :timestamps, update_on_create: true

  def validate
    super
    validates_presence [:name, :price]
    validates_numeric :price
    validates_min_length 1, :name
  end
end

product = Product.create(name: 'Monitor', price: 299.99, stock: 30)
product.update(price: 279.99)

expensive = Product.where { price > 100 }.order(Sequel.desc(:price)).all
Product[1]              # find by primary key
Product.first           # first record
product.destroy

5 Transaction Handling

Transactions ensure that a group of database operations either all succeed or all fail. Both ActiveRecord and Sequel provide clean transaction APIs.

ActiveRecord Transactions

ActiveRecord::Base.transaction do
  sender   = Account.lock.find(from_id)
  receiver = Account.lock.find(to_id)

  raise 'Insufficient balance' if sender.balance < amount

  sender.update!(balance: sender.balance - amount)
  receiver.update!(balance: receiver.balance + amount)

  Transfer.create!(
    from_account: sender,
    to_account:   receiver,
    amount:       amount
  )
end

begin
  ActiveRecord::Base.transaction do
    order = Order.create!(user_id: user.id, total: cart_total)

    cart_items.each do |item|
      OrderItem.create!(order: order, product_id: item.product_id, quantity: item.qty)
      product = Product.lock.find(item.product_id)
      raise 'Out of stock' if product.stock < item.qty
      product.update!(stock: product.stock - item.qty)
    end

    Payment.create!(order: order, amount: cart_total, status: 'pending')
  end
  puts 'Order placed successfully'
rescue ActiveRecord::RecordInvalid => e
  puts "Validation failed: #{e.message}"
rescue StandardError => e
  puts "Transaction rolled back: #{e.message}"
end

Sequel Transactions

DB.transaction do
  from = DB[:accounts].where(id: from_id).for_update.first
  raise Sequel::Rollback if from[:balance] < amount

  DB[:accounts].where(id: from_id).update(balance: Sequel[:balance] - amount)
  DB[:accounts].where(id: to_id).update(balance: Sequel[:balance] + amount)

  DB[:transfers].insert(
    from_id: from_id,
    to_id:   to_id,
    amount:  amount,
    created_at: Time.now
  )
end

Nested Transactions (Savepoints)

ActiveRecord::Base.transaction do
  User.create!(name: 'Alice')

  ActiveRecord::Base.transaction(requires_new: true) do
    User.create!(name: 'Bob')
    raise ActiveRecord::Rollback
  end
end

DB.transaction do
  DB[:users].insert(name: 'Alice')

  DB.transaction(savepoint: true) do
    DB[:users].insert(name: 'Bob')
    raise Sequel::Rollback
  end
end

6 ORM Comparison

How do Ruby's database tools compare with those in other languages? Here's a side-by-side view.

7 Chapter Summary