Databases

45.1 SQLite and PostgreSQL Drivers

require "sqlite3"
db = SQLite3::Database.new("app.db")
db.results_as_hash = true
db.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)")
db.execute("INSERT INTO users (name, email) VALUES (?, ?)", ["Ana", "[email protected]"])   # parameterized!
db.execute("SELECT * FROM users WHERE name LIKE ?", ["A%"]).each { |row| puts row["email"] }
db.transaction { db.execute("UPDATE users SET name = ? WHERE id = ?", ["Ann", 1]) }

require "pg"
conn = PG.connect(dbname: "shop", user: "postgres")
conn.exec_params("SELECT * FROM products WHERE price > $1", [100]).each { |row| p row }

45.2 Sequel (Toolkit + ORM)

require "sequel"
DB = Sequel.sqlite("app.db")       # or Sequel.connect(ENV["DATABASE_URL"])
DB.create_table?(:posts) do
  primary_key :id
  String :title, null: false
  Integer :views, default: 0
  DateTime :created_at
end
posts = DB[:posts]
posts.insert(title: "Hello", created_at: Time.now)
posts.where { views > 10 }.order(Sequel.desc(:views)).limit(5).all

class Post < Sequel::Model
  plugin :validation_helpers
  def validate = (super; validates_presence :title)
end
Post.create(title: "Second")

45.3 Active Record (Standalone or in Rails)

Concepts: migrations, associations, validations, callbacks, scopes, eager loading (avoiding N+1), transactions, locking, query interface (where, joins, group, pluck, find_each).

Other data stores: Redis (redis gem), MongoDB (mongo, Mongoid), ROM-rb, Elasticsearch/OpenSearch clients.

require "active_record"
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: "app.db")

ActiveRecord::Schema.define do
  create_table :authors, if_not_exists: true do |t|
    t.string :name
    t.timestamps
  end
  create_table :books, if_not_exists: true do |t|
    t.string :title
    t.references :author
    t.timestamps
  end
end

class Author < ActiveRecord::Base
  has_many :books, dependent: :destroy
  validates :name, presence: true
end

class Book < ActiveRecord::Base
  belongs_to :author
  scope :recent, -> { where(created_at: 1.week.ago..) }
end

author = Author.create!(name: "Matz")
author.books.create!(title: "The Ruby Way")
Book.includes(:author).recent.order(:title).each { puts "#{it.title} by #{it.author.name}" }
Author.joins(:books).group(:name).count