Working with Databases

Most real applications store data in a database. This lesson uses SQLite — a complete SQL database in a single file, with nothing to install beyond a gem — and the ideas carry over directly to PostgreSQL and MySQL.

bundle add sqlite3

Creating a table and inserting rows

require "sqlite3"

db = SQLite3::Database.new(":memory:")   # or a file: "app.db"
db.results_as_hash = true

db.execute <<~SQL
  CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
  )
SQL

db.execute("INSERT INTO users (name, email) VALUES (?, ?)", ["Ada", "ada@example.com"])
db.execute("INSERT INTO users (name, email) VALUES (?, ?)", ["Linus", "linus@example.com"])

rows = db.execute("SELECT id, name FROM users WHERE name LIKE ?", ["A%"])
rows.each { |row| puts "#{row["id"]}: #{row["name"]}" }

p db.get_first_value("SELECT COUNT(*) FROM users")

1: Ada
2

Always use placeholders

The ? marks are placeholders. The values are sent separately from the SQL, so the database never treats them as SQL code. Never build SQL by inserting values into the string:

# DANGEROUS — SQL injection
db.execute("SELECT * FROM users WHERE email = '#{params[:email]}'")

If someone submits ' OR '1'='1 as their email, that query returns every user. Placeholders make this whole class of attack impossible. The SQL Formatter is useful for reading long queries.

Transactions

A transaction groups several changes so they all succeed or all fail together — essential for anything like moving money between accounts:

db.transaction do
  db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [50, 1])
  db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [50, 2])
end

If an exception is raised inside the block, both updates are rolled back.

ORMs and query builders

Writing SQL by hand gets repetitive. Most Ruby projects use a library:

• Active Record (part of Rails) — an ORM (object-relational mapper): each table is a class, each row an object — User.where(active: true).order(:name).
• Sequel — a flexible toolkit usable with or without models, popular outside Rails.

Both use placeholders for you. Both also make N+1 queries easy to write by accident — loading 100 users, then running one extra query per user for their orders. Load related data up front (User.includes(:orders) in Active Record) instead.

Common mistakes

• String interpolation in SQL. Always use placeholders or the ORM's query methods.
• Running a query inside a loop over query results (N+1).
• Doing multi-step changes without a transaction.