12

SQLite Workflows from Basic Operations to Prepared Statements

Ruby source files are converted directly from the English source tree.

en/topic_12_sqlite_queries

Overview

Why this topic matters 💡

Small applications often need real persistence before they need a full framework. SQLite is a strong teaching tool because students can practice schema creation, inserts, updates, simple queries, and prepared statements without setting up a database server.

Learning outcomes 🎯

By the end of this topic, students should be able to:

  • open a SQLite database and create a simple table;
  • insert and read rows through the Ruby sqlite3 driver;
  • explain the difference between basic SQL queries and parameterized queries;
  • write a focused query object instead of mixing SQL everywhere;
  • explain why prepared statements matter for safety and reuse.

Assessment focus ✅

Students should be able to explain why schema setup, querying, and parameter binding are different concerns even in a small project.

Short Note

SQLite is useful in teaching because it introduces real persistence with very low ceremony. The important lesson is not "Ruby can run SQL." The lesson is that data storage becomes cleaner when responsibilities are separated:

  • one object sets up or owns the schema;
  • another object asks focused business questions;
  • parameterized SQL protects the boundary between data and code.

Ruby beauty in this topic:

  • the sqlite3 gem gives direct, readable access to SQL;
  • hashes and small repository objects keep the Ruby side easy to inspect;
  • a prepared statement can stay compact while teaching an important safety habit.

Ruby caution in this topic:

  • raw SQL strings scattered through the code quickly become hard to maintain;
  • interpolating values directly into SQL is an attractive mistake;
  • database setup code should not be duplicated casually.

Reflection prompt:

  • Why is parameter binding a design improvement, not just a security trick?

Worked Examples

Example 1: Local catalog or admin tool 💡

A small admin or desktop-style tool often needs persistence but does not need a full database server. SQLite is a realistic choice for:

  • inventory lists;
  • imported CSV snapshots;
  • local prototypes;
  • embedded application state.

That makes it a better teaching example than abstract SQL snippets without context.

Example 2: Query service with parameter binding 💡

Applications regularly ask focused questions such as:

  • find a product by SKU;
  • list all products in a category;
  • return products cheaper than a threshold.

Prepared statements are useful because:

  • the SQL shape remains stable;
  • values are passed separately from the query text;
  • the code models a safer production habit from the beginning.

Cheatsheet

Open a database

db = SQLite3::Database.new(path)
db.results_as_hash = true
cheatsheet.md
ruby

Create a table

db.execute <<~SQL
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    price_cents INTEGER NOT NULL,
    category TEXT NOT NULL
  )
SQL
cheatsheet.md
ruby

Execute a simple query

db.execute("SELECT * FROM products ORDER BY id")
cheatsheet.md
ruby

Prepared statement

statement = db.prepare("SELECT * FROM products WHERE sku = ?")
row = statement.execute("B100").next_hash
statement.close
cheatsheet.md
ruby

Sprint mindset 🏃

  • Sprint 1: schema and inserts
  • Sprint 2: simple query object
  • Sprint 3: prepared statements and parameter binding

Study Guide

This topic introduces persistence in small steps. The SQL itself matters, but the larger lesson is how Ruby code should behave once data lives outside the process. As soon as a program writes to a database, questions of structure, safety, and responsibility become more important.

Suggested Study Order

Work through the topic in this order:

  1. Read overview.md.
  2. Read shortnote.md.
  3. Study worked_examples.md.
  4. Keep cheatsheet.md nearby while you read example.rb.
  5. Complete the basic exercise.
  6. Complete the advanced exercise.

Try not to rush into the exercises before reading the notes. In this topic, a small amount of design thinking up front saves a great deal of confusion later.

Sprint Plan

Sprint 1: Basic Operations

In the first sprint, make the database feel ordinary. You should be able to:

  • open a database
  • create the table if needed
  • insert rows
  • read rows back

The goal is not abstraction yet. The goal is to become comfortable with the basic shape of the workflow.

Sprint 2: A Small Query Object

Once the basic operations are working, separate setup from reporting. You should be able to:

  • write focused SQL queries for common questions
  • keep those queries out of the schema or setup object
  • return ordinary Ruby hashes for the rest of the application

This is the first step toward a better boundary. Schema creation and business queries usually do not belong in the same object.

Sprint 3: Prepared Statements

Now make the code safer and more deliberate. You should be able to:

  • parameterize values instead of interpolating them into SQL
  • explain why parameterization helps with safety and clarity
  • keep statement setup and use small and explicit

Prepared statements matter for security, but they also matter for readability. They make it clearer which parts of a query are fixed structure and which parts come from data.

Sprint 4: Optional Extensions

If you want to push the topic a little further, try one of these:

  • update and delete operations
  • transactions
  • unique-constraint error handling
  • query objects that cast data into domain objects

A Common Mistake To Watch For

The most common mistake in this topic is interpolating values directly into SQL strings because it feels quick and harmless. That shortcut usually makes the code worse twice: it is less safe, and it hides the boundary between query structure and input data.

If a query takes outside input, treat that fact as part of the design.

Reflection Questions

When you finish the topic, make sure you can answer these:

  • Why should schema bootstrap and reporting queries live in different objects?
  • What risk appears when values are interpolated directly into SQL?
  • When would a prepared statement still be useful even if security were not the main concern?

Source Files and Tests

Ruby source files are converted directly from the English source tree.

# EXAMPLE CODE
# Topic: topic_12_sqlite_queries
#
# Purpose:
# - This file demonstrates basic SQLite operations through the Ruby driver.
# - It should pass tests from the beginning.
# - Read it before solving the query and prepared-statement exercises.

require "sqlite3"

class ProductStore
  def initialize(path)
    @db = SQLite3::Database.new(path)
    @db.results_as_hash = true
  end

  def setup_schema
    @db.execute <<~SQL
      CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        sku TEXT NOT NULL UNIQUE,
        name TEXT NOT NULL,
        price_cents INTEGER NOT NULL,
        category TEXT NOT NULL
      )
    SQL
  end

  def add_product(sku:, name:, price_cents:, category:)
    @db.execute(
      "INSERT INTO products (sku, name, price_cents, category) VALUES (?, ?, ?, ?)",
      [sku, name, price_cents, category]
    )
  end

  def all_products
    @db.execute("SELECT sku, name, price_cents, category FROM products ORDER BY id")
      .map { |row| normalize_row(row) }
  end

  private

  def normalize_row(row)
    {
      "sku" => row["sku"],
      "name" => row["name"],
      "price_cents" => row["price_cents"],
      "category" => row["category"]
    }
  end
end
en/topic_12_sqlite_queries/example.rb
Ruby course source
# STUDENT TASK (BASIC)
# Topic: topic_12_sqlite_queries
#
# What to do:
# - Build a focused query object on top of the product table.
# - Keep the class about asking questions, not about schema creation.
# - Use plain SQL queries first before moving to prepared statements in the advanced step.
#
# Expected outcome:
# - You can answer a couple of small business questions from stored data.

require "sqlite3"

class ProductQueries
  def initialize(path)
    @db = SQLite3::Database.new(path)
    @db.results_as_hash = true
  end

  def names_in_category(category)
    @db.execute(
      "SELECT name FROM products WHERE category = '#{category}' ORDER BY name"
    ).map { |row| row["name"] }
  end

  def average_price_in_category(category)
    row = @db.get_first_row(
      "SELECT AVG(price_cents) AS average_price FROM products WHERE category = '#{category}'"
    )

    row["average_price"]&.to_f
  end
end
en/topic_12_sqlite_queries/basic_exercise.rb
Ruby course source
# STUDENT TASK (ADVANCED)
# Topic: topic_12_sqlite_queries
#
# Academic purpose:
# - Move from simple SQL strings to prepared statements with parameter binding.
# - Learn that database safety and clarity improve when values are passed separately from SQL text.
#
# Real-world use case:
# - Production systems often look up records by identifiers such as SKU, email, or external ID.
# - Prepared statements make those repeated lookups safer and easier to reason about.
#
# Why Ruby is beautiful here:
# - The `sqlite3` gem exposes prepared statements directly without heavy ceremony.
# - The method stays compact while still teaching a serious database practice.
# - A tiny repository object can make low-level SQL behavior feel approachable.
#
# What to do:
# - Build lookup methods using prepared statements.
# - Close statements explicitly after use.
# - Return ordinary Ruby hashes so the caller stays decoupled from statement objects.
#
# Expected outcome:
# - Advanced tests pass and you can explain why parameter binding is better than interpolation.

require "sqlite3"

class PreparedProductQueries
  def initialize(path)
    @db = SQLite3::Database.new(path)
    @db.results_as_hash = true
  end

  def find_by_sku(sku)
    statement = @db.prepare("SELECT sku, name, price_cents, category FROM products WHERE sku = ?")
    result = statement.execute(sku)
    row = result.next_hash
    normalize_row(row)
  ensure
    safely_close(result)
    safely_close(statement)
  end

  def cheaper_than(max_price_cents)
    statement = @db.prepare(
      "SELECT sku, name, price_cents, category FROM products WHERE price_cents < ? ORDER BY price_cents ASC"
    )
    result = statement.execute(max_price_cents)
    rows = result.map { |row| normalize_row(row) }
    rows
  ensure
    safely_close(result)
    safely_close(statement)
  end

  private

  def normalize_row(row)
    return nil unless row

    {
      "sku" => row["sku"],
      "name" => row["name"],
      "price_cents" => row["price_cents"],
      "category" => row["category"]
    }
  end

  def safely_close(resource)
    resource&.close
  rescue SQLite3::Exception
    nil
  end
end
en/topic_12_sqlite_queries/adv_exercise.rb
Ruby course source
# ANSWER KEY (BASIC)
# Topic: topic_12_sqlite_queries
#
# Solution idea:
# - Keep the setup/store object separate from the reporting-style query object.
# - Start with straightforward SQL so the student can focus on what each query asks.
# - Return Ruby values rather than database driver objects.

require "sqlite3"

class ProductQueries
  def initialize(path)
    @db = SQLite3::Database.new(path)
    @db.results_as_hash = true
  end

  def names_in_category(category)
    @db.execute(
      "SELECT name FROM products WHERE category = '#{category}' ORDER BY name"
    ).map { |row| row["name"] }
  end

  def average_price_in_category(category)
    row = @db.get_first_row(
      "SELECT AVG(price_cents) AS average_price FROM products WHERE category = '#{category}'"
    )

    row["average_price"]&.to_f
  end
end
en/topic_12_sqlite_queries/answer_basic_exercise.rb
Ruby course source
# ANSWER KEY (ADVANCED)
# Topic: topic_12_sqlite_queries
#
# Solution idea:
# - Replace interpolation with prepared statements and bound parameters.
# - Keep statement lifecycle local to each method.
# - Normalize returned rows so the rest of the code only sees simple hashes.

require "sqlite3"

class PreparedProductQueries
  def initialize(path)
    @db = SQLite3::Database.new(path)
    @db.results_as_hash = true
  end

  def find_by_sku(sku)
    statement = @db.prepare("SELECT sku, name, price_cents, category FROM products WHERE sku = ?")
    result = statement.execute(sku)
    row = result.next_hash
    normalize_row(row)
  ensure
    safely_close(result)
    safely_close(statement)
  end

  def cheaper_than(max_price_cents)
    statement = @db.prepare(
      "SELECT sku, name, price_cents, category FROM products WHERE price_cents < ? ORDER BY price_cents ASC"
    )
    result = statement.execute(max_price_cents)
    rows = result.map { |row| normalize_row(row) }
    rows
  ensure
    safely_close(result)
    safely_close(statement)
  end

  private

  def normalize_row(row)
    return nil unless row

    {
      "sku" => row["sku"],
      "name" => row["name"],
      "price_cents" => row["price_cents"],
      "category" => row["category"]
    }
  end

  def safely_close(resource)
    resource&.close
  rescue SQLite3::Exception
    nil
  end
end
en/topic_12_sqlite_queries/answer_adv_exercise.rb
Ruby course source
# This spec is your learning companion for topic_12_sqlite_queries.
#
# How to use this file:
# 1) Run tests and observe failures or successes.
# 2) Keep the EXAMPLE specs green from the beginning.
# 3) Implement the BASIC exercise as sprint 2: simple query methods.
# 4) Implement the ADVANCED exercise as sprint 3: prepared statements.
#
# Expected final result:
# - All examples in this file pass.
# - You understand SQLite work as schema setup, query design, and parameter binding.

require "tempfile"

require_relative "../example"
require_relative "../basic_exercise"
require_relative "../adv_exercise"

RSpec.describe "topic_12_sqlite_queries" do
  def with_db_path
    file = Tempfile.new(["products", ".sqlite3"])
    file.close
    yield file.path
  ensure
    File.delete(file.path) if file && File.exist?(file.path)
  end

  describe "EXAMPLE purpose: understand basic SQLite operations before adding query objects" do
    it "creates schema, inserts rows, and reads them back" do
      with_db_path do |path|
        store = ProductStore.new(path)
        store.setup_schema
        store.add_product(sku: "B100", name: "Ruby Book", price_cents: 3000, category: "books")
        store.add_product(sku: "A200", name: "Desk Lamp", price_cents: 4500, category: "home")

        expect(store.all_products).to eq(
          [
            { "sku" => "B100", "name" => "Ruby Book", "price_cents" => 3000, "category" => "books" },
            { "sku" => "A200", "name" => "Desk Lamp", "price_cents" => 4500, "category" => "home" }
          ]
        )
      end
    end
  end

  describe "BASIC EXERCISE purpose: ask simple questions from persisted rows" do
    it "returns names and average price for a category" do
      with_db_path do |path|
        store = ProductStore.new(path)
        store.setup_schema
        store.add_product(sku: "B100", name: "Ruby Book", price_cents: 3000, category: "books")
        store.add_product(sku: "B200", name: "Refactoring", price_cents: 5000, category: "books")
        store.add_product(sku: "A200", name: "Desk Lamp", price_cents: 4500, category: "home")

        queries = ProductQueries.new(path)

        expect(queries.names_in_category("books")).to eq(["Refactoring", "Ruby Book"])
        expect(queries.average_price_in_category("books")).to eq(4000.0)
      end
    end
  end

  describe "ADVANCED EXERCISE purpose: use prepared statements for focused lookups" do
    it "finds products by sku and filters by price threshold" do
      with_db_path do |path|
        store = ProductStore.new(path)
        store.setup_schema
        store.add_product(sku: "B100", name: "Ruby Book", price_cents: 3000, category: "books")
        store.add_product(sku: "B200", name: "Refactoring", price_cents: 5000, category: "books")
        store.add_product(sku: "A200", name: "Desk Lamp", price_cents: 4500, category: "home")

        queries = PreparedProductQueries.new(path)

        expect(queries.find_by_sku("B200")).to eq(
          { "sku" => "B200", "name" => "Refactoring", "price_cents" => 5000, "category" => "books" }
        )
        expect(queries.cheaper_than(4600)).to eq(
          [
            { "sku" => "B100", "name" => "Ruby Book", "price_cents" => 3000, "category" => "books" },
            { "sku" => "A200", "name" => "Desk Lamp", "price_cents" => 4500, "category" => "home" }
          ]
        )
      end
    end
  end
end
en/topic_12_sqlite_queries/tests/topic_12_sqlite_queries_spec.rb
Ruby course source
#!/usr/bin/env bash
set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
ROOT_DIR="$(cd "${SCRIPT_DIR}/.." && pwd)"

exec "${ROOT_DIR}/run_tests.sh" "$(basename "${SCRIPT_DIR}")"
en/topic_12_sqlite_queries/run_topic_tests.sh
Ruby course source

Study Prompts

  1. Read the spec first and identify which behaviors belong to the example, basic exercise, and advanced exercise.

  2. Attempt the exercises before opening the answer files, then compare the design choices.