Capstone — Building a Local-First CLI Tool With SQLite

Nine chapters covered SQLite's own architecture, type system, concurrency, transactions, real-world deployment, decision framework, application code, and limitations. This capstone combines them into one real, working personal expense tracker — a genuine demonstration of the "no server, just a file" workflow, not an abstract description of it.

The Scenario

A personal expense tracker a single person runs on their own machine to log and review their own spending — exactly the use case sqlite1-7's own decision framework identifies as SQLite's strongest, least-debatable fit: a single embedded application, no server needed at all.

The Schema

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
) STRICT;

CREATE TABLE expenses (
  id INTEGER PRIMARY KEY,
  description TEXT NOT NULL,
  amount REAL NOT NULL,
  category_id INTEGER NOT NULL REFERENCES categories(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

Both tables use STRICT — sqlite1-3's own opt-in fix, applied here as a real design decision rather than an abstract example: amount accidentally storing a text value instead of a number would silently corrupt every later SUM()-based report, which is exactly the class of bug STRICT exists to prevent at insert time.

The Application Code

import sqlite3

def get_connection():
    conn = sqlite3.connect("expenses.db")
    conn.execute("PRAGMA foreign_keys = ON")  # sqlite1-5's own gotcha, deliberately not forgotten
    return conn

def add_category(name):
    with get_connection() as conn:
        conn.execute("INSERT INTO categories (name) VALUES (?)", (name,))

def add_expense(description, amount, category_name):
    with get_connection() as conn:
        row = conn.execute(
            "SELECT id FROM categories WHERE name = ?", (category_name,)
        ).fetchone()
        if row is None:
            raise ValueError(f"Unknown category: {category_name}")
        conn.execute(
            "INSERT INTO expenses (description, amount, category_id) VALUES (?, ?, ?)",
            (description, amount, row[0]),
        )

def list_expenses():
    with get_connection() as conn:
        return conn.execute("""
            SELECT expenses.description, expenses.amount, categories.name, expenses.created_at
            FROM expenses JOIN categories ON expenses.category_id = categories.id
            ORDER BY expenses.created_at DESC
        """).fetchall()

def summary_by_category():
    with get_connection() as conn:
        return conn.execute("""
            SELECT categories.name, SUM(expenses.amount) AS total
            FROM expenses JOIN categories ON expenses.category_id = categories.id
            GROUP BY categories.name
            ORDER BY total DESC
        """).fetchall()

Every query is parameterized, per sqlite1-8's own security material — no string concatenation of user-supplied values anywhere. Every connection turns on PRAGMA foreign_keys = ON explicitly, closing the exact gap sqlite1-5's own warn-box named. Connections are opened and closed via context managers, per sqlite1-8's own resource-management guidance.

Why This Is Genuinely Local-First

This tool runs entirely on the user's own machine and works completely offline — sqlite1-6's own local-first strength, not a theoretical one. The entire dataset lives in one file, expenses.db, which the user could back up with nothing more than cp expenses.db backup.db — sqlite1-1's own opening example, now genuinely exercised end to end rather than just described.

Chapter Attribution

Hands-On Exercises