第四章：数据库

1 Ruby 数据库生态概览

Ruby 拥有成熟的数据库工具链。与 PHP 的 PDO 统一底层不同，Ruby 生态更倾向于通过 ORM 层屏蔽数据库差异，同时也保留了直接操作数据库的能力。

安装常用数据库 Gem

# SQLite3 驱动 + ActiveRecord
gem install activerecord sqlite3

# Sequel ORM
gem install sequel sqlite3

# PostgreSQL
gem install pg

# 或在 Gemfile 中声明
# gem 'activerecord', '~> 7.1'
# gem 'sqlite3', '~> 1.7'

2 SQLite3 直接操作

sqlite3 gem 提供了对 SQLite 数据库的直接访问，适合脚本、CLI 工具和轻量级应用。无需 ORM 的开销，直接写 SQL。

创建数据库与建表

require 'sqlite3'

db = SQLite3::Database.new('app.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

插入数据

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

new_id = db.last_insert_row_id
puts "新用户 ID: #{new_id}"

users = [
  ['李四', 'lisi@example.com', 32],
  ['王五', 'wangwu@example.com', 25],
]
users.each do |name, email, age|
  db.execute('INSERT INTO users (name, email, age) VALUES (?, ?, ?)', [name, email, age])
end

查询数据

user = db.get_first_row('SELECT * FROM users WHERE id = ?', [1])
puts "#{user['name']} - #{user['email']}" if user

db.execute('SELECT * FROM users WHERE age >= ? ORDER BY created_at DESC', [25]) do |row|
  puts "#{row['name']} (#{row['age']}岁)"
end

count = db.get_first_value('SELECT COUNT(*) FROM users WHERE age >= ?', [25])
puts "符合条件: #{count} 人"

Prepared Statements

stmt = db.prepare('SELECT * FROM users WHERE name LIKE ? AND age > ?')
results = stmt.execute('%张%', 20)
results.each { |row| puts row['name'] }
stmt.close

stmt = db.prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)')
[
  ['赵六', 'zhaoliu@example.com', 30],
  ['钱七', 'qianqi@example.com', 27],
].each { |data| stmt.execute(data) }
stmt.close

更新与删除

db.execute('UPDATE users SET name = ?, age = ? WHERE id = ?', ['张三丰', 29, 1])
puts "更新了 #{db.changes} 条记录"

db.execute('DELETE FROM users WHERE id = ?', [3])
puts "删除了 #{db.changes} 条记录"

3 ActiveRecord（独立使用）

ActiveRecord 是 Rails 的核心组件，但完全可以脱离 Rails 独立使用。它实现了 Active Record 设计模式——每个模型类对应一张数据库表，每个实例对应一行记录。

建立连接

require 'active_record'

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

ActiveRecord::Base.logger = Logger.new($stdout)

定义 Schema（迁移）

ActiveRecord::Schema.define do
  create_table :users, force: false, if_not_exists: true do |t|
    t.string  :name,  null: false
    t.string  :email, null: false
    t.integer :age
    t.timestamps
  end

  add_index :users, :email, unique: true, if_not_exists: true

  create_table :posts, force: false, if_not_exists: true do |t|
    t.references :user, foreign_key: true
    t.string  :title, null: false
    t.text    :body
    t.timestamps
  end
end

定义模型

class User < ActiveRecord::Base
  has_many :posts, dependent: :destroy

  validates :name,  presence: true, length: { maximum: 100 }
  validates :email, presence: true, uniqueness: true,
                    format: { with: URI::MailTo::EMAIL_REGEXP }
  validates :age,   numericality: { greater_than: 0, allow_nil: true }

  before_save :normalize_email

  scope :adults, -> { where('age >= ?', 18) }
  scope :recent, -> { order(created_at: :desc) }

  private

  def normalize_email
    self.email = email.downcase.strip
  end
end

class Post < ActiveRecord::Base
  belongs_to :user
  validates :title, presence: true
end

CRUD 操作

# CREATE
user = User.create!(name: '张三', email: 'zhangsan@example.com', age: 28)

user = User.new(name: '李四', email: 'lisi@example.com', age: 32)
user.save!

# READ
user = User.find(1)
user = User.find_by(email: 'zhangsan@example.com')

adults = User.adults.recent.limit(10)
adults.each { |u| puts "#{u.name} (#{u.age}岁)" }

User.where('age BETWEEN ? AND ?', 25, 35).order(:name).each do |u|
  puts u.name
end

count = User.where('age >= ?', 25).count

# UPDATE
user = User.find(1)
user.update!(name: '张三丰', age: 29)

User.where('age < ?', 18).update_all(status: 'minor')

# DELETE
user.destroy
User.where('created_at < ?', 1.year.ago).destroy_all

关联查询

user = User.find(1)
user.posts.create!(title: '学习 Ruby', body: 'Ruby 是一门优雅的语言...')

user.posts.each { |post| puts post.title }

posts_with_users = Post.includes(:user).where('created_at > ?', 1.week.ago)
posts_with_users.each do |post|
  puts "#{post.title} by #{post.user.name}"
end

4 Sequel ORM

Sequel 是 Ruby 社区另一个流行的数据库工具，风格更接近 SQL 本身。它既是查询构建器，也是 ORM，你可以只用 Dataset API（不定义 Model），也可以两者结合。

连接与建表

require 'sequel'

DB = Sequel.sqlite('app.db')

DB.create_table? :users do
  primary_key :id
  String  :name,  null: false
  String  :email, null: false, unique: true
  Integer :age
  DateTime :created_at, default: Sequel::CURRENT_TIMESTAMP
end

Dataset API（无需定义 Model）

users = DB[:users]

users.insert(name: '张三', email: 'zhangsan@example.com', age: 28)

all_users = users.all
puts all_users.inspect

adults = users.where { age >= 25 }.order(Sequel.desc(:created_at)).limit(10)
adults.each { |u| puts "#{u[:name]} (#{u[:age]}岁)" }

user = users.where(id: 1).first
puts user[:name] if user

users.where(id: 1).update(name: '张三丰', age: 29)

users.where(id: 3).delete

count = users.where { age >= 25 }.count
puts "成年用户: #{count}"

users.where(name: %w[张三 李四]).each { |u| puts u[:email] }

avg_age = users.avg(:age)
puts "平均年龄: #{avg_age}"

Sequel Model 用法

class User < Sequel::Model
  one_to_many :posts

  def validate
    super
    errors.add(:name, '不能为空') if name.nil? || name.empty?
    errors.add(:email, '格式不正确') unless email&.match?(/\A[\w+\-.]+@[a-z\d\-]+(\.[a-z\d\-]+)*\.[a-z]+\z/i)
  end

  dataset_module do
    def adults
      where { age >= 18 }
    end

    def recent
      order(Sequel.desc(:created_at))
    end
  end
end

user = User.create(name: '王五', email: 'wangwu@example.com', age: 25)
user.update(age: 26)

User.adults.recent.limit(5).each { |u| puts u.name }

5 事务处理

事务将多条数据库操作打包为原子单元——要么全部成功提交，要么全部回滚。Ruby 的两大 ORM 都提供了优雅的事务 API。

ActiveRecord 事务

ActiveRecord::Base.transaction do
  from_account = Account.lock.find(from_id)
  to_account   = Account.lock.find(to_id)

  raise '余额不足' if from_account.balance < amount

  from_account.update!(balance: from_account.balance - amount)
  to_account.update!(balance: to_account.balance + amount)

  Transfer.create!(
    from_account_id: from_id,
    to_account_id:   to_id,
    amount:          amount
  )
end

在 transaction 块内抛出异常会自动回滚。ActiveRecord::Rollback 是特殊异常，触发回滚但不会向外传播：

ActiveRecord::Base.transaction do
  user = User.create!(name: '测试用户', email: 'test@example.com')

  if some_condition_fails?
    raise ActiveRecord::Rollback
  end

  user.posts.create!(title: '第一篇')
end

User.transaction do
  User.transaction(requires_new: true) do
    User.create!(name: '内层操作', email: 'inner@example.com')
    raise ActiveRecord::Rollback
  end

  User.create!(name: '外层操作', email: 'outer@example.com')
end

Sequel 事务

DB.transaction do
  from_account = DB[:accounts].where(id: from_id).for_update.first
  raise Sequel::Rollback if from_account[: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

DB.transaction(savepoint: true) do
  DB[:users].insert(name: '外层', email: 'outer@example.com')

  DB.transaction(savepoint: true) do
    DB[:users].insert(name: '内层', email: 'inner@example.com')
    raise Sequel::Rollback
  end
end

6 跨语言 ORM 对比

# Ruby ActiveRecord
user = User.find_by(email: 'zhangsan@example.com')
users = User.where('age >= ?', 25).order(created_at: :desc).limit(10)

# PHP PDO
# $stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
# $stmt->execute(['zhangsan@example.com']);

# Python SQLAlchemy
# user = session.query(User).filter_by(email='zhangsan@example.com').first()

# Node.js Prisma
# const user = await prisma.user.findUnique({ where: { email: 'zhangsan@example.com' } })

7 本章要点