This course, like postgres1, assumes real SQL knowledge already covered by mysql2/mysql3 and won't re-teach SELECT, JOINs, or query fundamentals. But SQLite's own real differentiator runs deeper than Postgres's did — it isn't a client-server database with a different feature set. It isn't a server at all.

Client-Server vs. Embedded — A Fundamentally Different Model

MySQL and Postgres are both client-server systems: a separate server process runs continuously, listening on a network port, and applications connect to it as a separate, independent process — even on the same machine, the database and the application are two different running programs communicating over a socket. This is exactly what mysql1 and postgres1-2 both walked through: install and start a server, then connect a client to it.

SQLite has no server process at all. SQLite is a C library, linked directly into the application's own process. When application code "talks to" SQLite, it isn't sending anything over a network or even a local socket — it's making ordinary in-process function calls to library code running in the exact same process as the application itself.

A real, concrete consequence: there's no "SQLite server" to install, start, stop, or crash independently. If the application process ends, database access ends with it — though the data itself persists safely on disk — and there's no separate service whose uptime needs monitoring at all.

A "Database" Is Just a File

In SQLite, the entire database — schema, tables, indexes, all data — lives in a single ordinary file on disk (commonly given a .db, .sqlite, or .sqlite3 extension, though the extension itself means nothing to SQLite). Opening a database is as simple as opening that file.

In MySQL or Postgres, a "database" is a logical construct managed by a running server process, spread across the server's own internal storage structures (and, per postgres1-2, potentially spanning multiple schemas) — copying or backing up a database means using the server's own dump/export tooling. In SQLite, copying the database is literally copying the file:

cp mydata.db backup.db

That single command is a complete, valid backup — with one honest caveat, worth flagging now and covered properly in sqlite1-5: it's only safe to copy the raw file this simply when no write is currently in progress.

The C Library, Linked Directly

SQLite is written in C and either compiled directly into an application or loaded as a shared library the application links against. Application code written in Python, Node.js, Java, or any other language uses a language-specific binding that ultimately calls into this same underlying C library. This is exactly why there's no separate "connection" step in the client-server sense — "connecting" to a SQLite database really just means opening the file through the library, an operation that completes almost instantly, since no network handshake or authentication step is involved at all.

This Course's Own Throughline

Evaluating SQLite as "MySQL but smaller, or weaker" is a category error — it isn't attempting to solve the same problem MySQL and Postgres solve. MySQL and Postgres exist to let many separate applications or processes, potentially on different machines, share reliable, concurrent access to the same data over a network. SQLite exists to let a single application embed a real, full-featured, ACID-compliant database directly into itself, with zero server infrastructure required at all. The real question this course keeps returning to isn't "which database is better" — it's "does this application need a server at all."

Hands-On Exercises

sqlite1-1 claimed there's no server to install. This chapter makes that claim concrete.

There's No Server to Install

mysql1 walked through installing a MySQL server package, starting the service, and then connecting a client to it. postgres1-2 walked through installing Postgres, running initdb, starting the service, and connecting via psql. SQLite has no equivalent first step at all — the sqlite3 command-line tool doesn't connect to anything running; it opens or creates a file directly.

The sqlite3 CLI

sqlite3 mydata.db

That single command opens mydata.db if it already exists, or creates a brand-new, empty database at that path if it doesn't — and immediately drops into an interactive prompt. No authentication step, no host or port to specify.

Basic meta-commands, deliberately echoing postgres1-2's own backslash-prefixed commands but using a dot prefix instead:

Creating and Populating a Database

sqlite3 notes.db
CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('First real note');
SELECT * FROM notes;

That's the entire workflow, start to finish — no separate "create the database" step distinct from creating the file itself.

In-Memory Databases

A genuinely useful, distinct mode: sqlite3 :memory: (or the special string ":memory:" passed in application code) creates a database that exists only in RAM, never touching disk at all, and disappears completely the moment the connection closes.

This matters practically for two real reasons: it's extremely fast, with no disk I/O at all, and it's a genuinely common real-world pattern in automated testing — spinning up a fresh, empty in-memory database for each test run guarantees complete isolation between runs with zero cleanup required, since the "database" simply ceases to exist the moment the test process ends. This mode obviously can't help where persistence across restarts actually matters — a limitation worth naming now and covered properly in sqlite1-9.

Opening a Database From Application Code

Most language bindings open a SQLite database with nothing more than a file path string — Python's sqlite3.connect('mydata.db') is a representative example (covered more fully in sqlite1-8). No host, no port, no username, no password, no connection-pool configuration — a direct, concrete fulfillment of sqlite1-1's own "near-instant, no handshake" claim.

Side-by-Side — The Full Workflow Compared

Hands-On Exercises

This is the first item from sqlite1-1's own roadmap table, and it's genuinely the most surprising difference for anyone coming from mysql2/mysql3 or postgres1.

What "Type Affinity" Actually Means

MySQL and Postgres both use static, strict column typing — a column declared INTEGER can only ever store integer values (or NULL); inserting 'hello' into it raises a real, hard error at insert time.

SQLite works differently: it's dynamically typed at the value level, not the column level. A column's declared type is really just a hint — a type affinity — that SQLite uses to decide how to try to convert an incoming value, but it does not reject a value just because it doesn't match. A column declared INTEGER can still end up storing a text string, if SQLite's own conversion rules can't reasonably coerce the value — it's stored as-is, using whichever of SQLite's five actual storage classes (NULL, INTEGER, REAL, TEXT, BLOB) genuinely fits it.

There are five type affinities — TEXT, NUMERIC, INTEGER, REAL, BLOB — assigned to a column based on textual matching rules applied to its declared type (a declared type containing "INT" gets INTEGER affinity; "CHAR", "CLOB", or "TEXT" gets TEXT affinity, and so on).

A Concrete Demonstration

CREATE TABLE example (id INTEGER, quantity INTEGER);
INSERT INTO example VALUES (1, 'five');
SELECT * FROM example;
-- 1 | five

This succeeds in SQLite by default, even though 'five' is not a number — SQLite only tries to coerce a value toward its column's own affinity, and simply stores it as-is when coercion isn't reasonably possible, rather than rejecting the insert outright. The identical statement against a MySQL table in strict mode, or against a Postgres table, would fail immediately.

Why This Is a Real Design Trade-off, Not Just a Flaw

This isn't an accident or an oversight — it's documented, deliberate design, tracing directly back to sqlite1-1's own embedding material. SQLite was built as a general-purpose, embedded storage engine meant to work smoothly from dynamically-typed scripting languages, where a value of the "wrong" type shouldn't necessarily hard-fail an entire operation the way it might need to in a strict, statically-typed system serving many different, independently-written applications with different assumptions about the data.

This flexibility is genuinely useful for SQLite's own actual use cases — loosely-typed application data, rapid prototyping, embedded contexts where the application itself is the sole writer and already controls data quality directly. It's also, honestly, the single most commonly cited source of real bugs for anyone arriving from a strict-typing background. Both things are true at once — a real, double-edged design choice worth respecting rather than dismissing outright as a mistake.

STRICT Tables — The Modern Opt-In Fix

SQLite 3.37 (2021) added STRICT tables — an opt-in mechanism giving real, MySQL/Postgres-like enforcement to a specific table:

CREATE TABLE example (id INTEGER, quantity INTEGER) STRICT;
INSERT INTO example VALUES (1, 'five');
-- Error: cannot store TEXT value in INTEGER column quantity

It's worth naming explicitly that this is opt-in and per-table, not the default — most existing SQLite code, and most tutorials, still use the traditional, flexible typing described above. Understanding both modes matters, not just the newer, stricter one.

The Storage Classes Underneath

Regardless of a column's declared type or affinity, every value in SQLite ultimately has one of five actual storage classes: NULL, INTEGER, REAL, TEXT, or BLOB. Affinity influences which storage class a value ends up using — it doesn't guarantee a fixed class the way MySQL/Postgres's own strict types do.

Hands-On Exercises

This chapter delivers on sqlite1-1's own roadmap entry for concurrency — and makes explicit contact with postgres1-9's own MVCC chapter, honestly, rather than treating SQLite's simpler model as a lesser version of it.

Why SQLite's Concurrency Model Is Different by Design

sqlite1-1's own throughline applies directly here: SQLite solves a different problem than MySQL/Postgres, so its concurrency answer is naturally different too — not worse. A single embedded application, or a small number of processes sharing one local file, has fundamentally different concurrency needs than many independent, networked clients.

Traditional Locking — Single-Writer, Multiple-Reader

SQLite's traditional locking model (the original rollback-journal mode): at any given moment, either multiple readers can access the database simultaneously, or exactly one writer can — never both at once, and never multiple simultaneous writers. This is enforced through ordinary operating-system file locks on the database file itself — a genuinely different mechanism from Postgres's own MVCC snapshot-based approach from postgres1-9.

A real practical consequence: in traditional mode, a write blocks all reads for its duration — a genuine limitation for any use case with meaningfully concurrent readers and writers.

WAL Mode — The Modern Improvement

PRAGMA journal_mode=WAL;

Write-Ahead Logging (WAL) mode is a real, meaningful improvement: readers can continue reading a consistent, slightly-older snapshot of the database while a write is in progress, since new writes are appended to a separate WAL file rather than modifying the main database file directly, and are only periodically "checkpointed" back into it.

It's important to be precise about what WAL does and doesn't fix: it still allows only one writer at a time. WAL improves reader/writer concurrency — readers no longer block on a write in progress — but it does not add support for multiple simultaneous writers. This is worth stating clearly rather than glossing over.

Honest Comparison With Postgres's Own MVCC

postgres1-9's MVCC exists to let many separate client connections — potentially dozens or hundreds — read and write concurrently without blocking each other, via genuine row-versioning (xmin/xmax). It solves the problem of many independent, networked clients sharing one actively-written dataset.

SQLite's own locking, traditional or WAL, exists to let a small number of processes or threads sharing one local file coordinate safely, without needing anything as sophisticated as MVCC's own row-versioning machinery — because the actual concurrency demands of a typically-single-application embedded context are fundamentally lower. It's tempting to read this as "SQLite's concurrency is worse than Postgres's," but that's exactly the same category error sqlite1-1 warned against — SQLite's locking is simpler because the problem it's solving is simpler, not because it's a weaker attempt at Postgres's own problem.

When This Actually Matters

An embedded mobile app or a CLI tool essentially never has meaningful write concurrency at all — often literally one process, sometimes one thread — making this whole topic close to moot in that context. A shared, multi-process server-side use case (SQLite used as an actual production database behind a web application, previewed in sqlite1-6 and sqlite1-7) is exactly where WAL mode's own single-writer limit starts to become a real, practical constraint worth understanding in advance.

Hands-On Exercises

sqlite1-1 flagged a caveat about copying the database file mid-write. This chapter delivers the full explanation — and corrects a genuine, common misconception along the way.

Countering a Real Misconception

"It's just a file, how can it be reliably transactional?" is a real, common skepticism worth answering directly rather than dismissing. SQLite genuinely provides full ACID guarantees — Atomicity, Consistency, Isolation, Durability — despite having no server process at all. This is a real, extensively tested, well-documented fact, not a marketing claim.

Atomicity — All or Nothing, Even on a Crash

A transaction (BEGIN ... COMMIT) either fully applies or has no effect at all — even if the application crashes, the operating system crashes, or power is lost mid-write. This is the specific guarantee that answers the "just a file" skepticism directly: SQLite achieves it through careful, deliberate low-level file operations, not by simply hoping a write completes.

The Rollback Journal

SQLite's original mechanism (still the default outside WAL mode): before modifying the actual database file, SQLite first writes the original, pre-modification content of the pages about to change into a separate journal file. If the transaction completes successfully, the journal is deleted. If the process crashes mid-write, the next time the database is opened, SQLite detects the leftover journal file and automatically uses it to roll the (possibly partially-written) main file back to its last known-good state.

This is exactly the mechanism sqlite1-1's own caveat pointed toward: copying the main database file mid-write, without its accompanying journal, can capture a half-written, inconsistent state. A copy taken while the database is idle — or via a proper backup mechanism — avoids this entirely.

WAL Mode's Own Different Durability Mechanism

sqlite1-4 covered WAL mode for its concurrency benefits; this chapter revisits it for its own durability angle. Rather than a before-image journal, new changes are appended to the WAL file, and the original main database file is left untouched until a checkpoint. A crash mid-write in WAL mode still leaves a valid, intact main database file — the old, pre-transaction state — with the WAL file itself either fully containing a completed transaction (recoverable) or an incomplete one (safely discarded on the next open). Same atomicity guarantee, a genuinely different physical mechanism from the rollback journal.

Durability & fsync

The actual mechanism making any of this real, rather than theoretical: SQLite calls fsync() (or the platform equivalent) at the right moments to force the operating system to actually flush data to physical storage, rather than leaving it sitting in an in-memory OS buffer that could be lost on a power failure.

PRAGMA synchronous controls how aggressively this happens: FULL is safest and slowest; NORMAL is a real, documented, still-safe-in-WAL-mode compromise; OFF is fast but genuinely risks corruption on power loss. This is an honest, concrete acknowledgment that durability has a real, tunable performance cost — the same kind of trade-off postgres1-11's own synchronous-vs-asynchronous replication material illustrated for a completely different system.

Isolation & Consistency

SQLite transactions provide real isolation — a reader never sees a partially-completed transaction's own intermediate state — and real consistency, with constraints (including foreign keys, when enabled) enforced within a transaction. This isn't covered in as much depth here, since it isn't SQLite's own most distinguishing feature the way atomicity-and-durability-despite-being-a-file is.

Hands-On Exercises

sqlite1-1 corrected the "SQLite is a toy" misconception in the abstract. This chapter grounds that correction in real, concrete deployment.

You've Probably Already Used SQLite Today

Android ships SQLite as its own default local storage engine for apps; iOS makes it directly available as part of the system libraries and underlies Core Data's own storage. Genuinely almost every smartphone user interacts with SQLite databases dozens of times a day without ever knowing it. Chrome and Firefox both use SQLite internally for various local storage needs — browsing history is commonly stored in an actual SQLite database sitting on disk, alongside cookies and bookmarks data.

Why Mobile & Browsers Chose SQLite Specifically

This connects directly back to two earlier chapters. sqlite1-1's own embedded, no-server model is exactly what a mobile app or a browser tab needs — there's no possibility of running a separate database server process on a phone or inside a browser process, so an embeddable, in-process library is close to the only architecturally sensible choice for this entire category of application.

sqlite1-5's own real ACID guarantees matter enormously here too. A phone can lose power at any moment — a dead battery, a forced restart — and an app's own local data still needs to survive that reliably. This is exactly the atomicity guarantee sqlite1-5 covered, now shown solving a real, everyday, concrete problem rather than an abstract one.

Desktop Applications

Beyond mobile and browsers, countless well-known desktop applications embed SQLite for their own local file formats and data storage — a genuinely common, unremarkable choice for any application needing structured, reliable local storage without the overhead of a separate database server.

SQLite as an Actual Production Server-Side Database

A more surprising, genuinely growing pattern: SQLite used as the actual backing database for a real, deployed web application — not just embedded in a client. This is a real, legitimate, and growing choice, not a fringe idea, particularly for single-server, low-to-moderate-traffic web applications.

Why this works: sqlite1-4's own WAL mode provides genuinely solid read concurrency; a single-server deployment means the "no multiple writers across machines" limitation from that same chapter simply doesn't apply the way it would in a genuinely distributed, multi-server context; and it eliminates an entire category of operational complexity — no separate database server to provision, patch, monitor, or independently scale. This is sqlite1-1's own "zero server infrastructure" framing, now paying off in a genuine production context, not just an embedded, local one.

Hands-On Exercises

This is this course's own central chapter — the point where six chapters of architecture, type behavior, concurrency, transactions, and real-world deployment stop being separate facts and become one honest, usable decision framework.

Revisiting This Course's Own Throughline

sqlite1-1 opened this course by stating that SQLite isn't "MySQL but weaker" — it solves a different problem, and the real question is whether an application needs a server at all. This chapter turns that abstract claim into an actual, practical framework: does this specific application need a server, and if a server-side deployment is being considered, does it fit the single-server-plus-WAL shape sqlite1-6 described?

When SQLite Is Not the Right Choice

• High write concurrency across multiple separate servers — the single clearest, least-debatable case. sqlite1-4's own single-writer-at-a-time limitation becomes a genuine, hard architectural wall the moment more than one physically separate machine needs to write concurrently.
• Database-level, per-user access control — if different application users genuinely need different database-level permissions, not just application-level authorization, SQLite offers nothing comparable to mysql1's or postgres1-2's own role systems (previewed further in sqlite1-9).
• Very large, multi-terabyte analytical workloads across a distributed architecture — not SQLite's own design center at all.
• Real-time, multi-node replication for failover — SQLite has no native equivalent to postgres1-11's own streaming/logical replication; third-party extensions exist, but this isn't SQLite's own built-in strength.

When SQLite Genuinely Is the Right Choice

• Mobile, desktop, and embedded applications — sqlite1-6's own strongest, essentially uncontested case.
• CLI tools and small utilities needing structured local storage.
• Testing — sqlite1-2's own in-memory database material is a genuinely excellent fit.
• Local-first / offline-capable applications — data needs to work with zero network connection at all, a strength no client-server database can match by definition.
• Single-server, low-to-moderate-traffic web applications — sqlite1-6's own real, growing production pattern: read-heavy or moderately write-heavy workloads, one deployment target, and genuine value placed on operational simplicity over theoretical headroom that will likely never actually be needed.
• Prototyping and early-stage projects where migrating to MySQL/Postgres later remains a realistic, deliberately-deferred option — start simple, migrate only if genuine multi-server needs actually materialize, not preemptively.

A Practical Decision Framework

1. Will more than one physically separate server ever need to write to this data? If genuinely yes — not SQLite.
2. Does this need database-level, per-user access control? If genuinely yes — not SQLite.
3. Is this embedded in a single application, or does it need to serve many independent networked clients? Embedded/single application — a strong SQLite fit.
4. Is operational simplicity worth more than headroom for hypothetical future scale that may never materialize? Yes — SQLite is a legitimate, deliberate choice, not a compromise.

The Honest Middle Ground

Some cases are genuinely ambiguous, and reasonable people disagree. This chapter's own goal isn't a rigid flowchart that removes judgment — it's making sure that judgment is informed by the real trade-offs from sqlite1-1, sqlite1-4, sqlite1-5, and sqlite1-6, rather than either reflexive dismissal ("SQLite is a toy") or reflexive overclaiming ("SQLite can replace any database").

Hands-On Exercises

sqlite1-1 and sqlite1-2 described the "no connection ceremony" workflow. This chapter shows it in real, working application code, in two languages, so the simplicity claim is demonstrated rather than just asserted.

Python's Built-in sqlite3 Module

SQLite support is genuinely built into Python's own standard library — no pip install required at all. This is a real, distinctive fact worth naming: neither MySQL nor Postgres connectivity ships in Python's own standard library; both require a third-party package (mysql-connector-python, psycopg2) just to connect.

import sqlite3

with sqlite3.connect("notes.db") as conn:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")

    # Parameterized — never string-concatenate user input into SQL
    cursor.execute("INSERT INTO notes (body) VALUES (?)", ("First real note",))
    conn.commit()

    cursor.execute("SELECT * FROM notes")
    for row in cursor.fetchall():
        print(row)

The parameterized execute(sql, (param,)) form is deliberate — a direct callback to sqli1's own parameterization material and postgres1-8's own dynamic-SQL-injection warning. That lesson applies here unchanged, in a completely different language and context. The with sqlite3.connect(...) context-manager form is the idiomatic Python way to guarantee the connection is properly committed and closed.

Node.js's better-sqlite3

better-sqlite3 is a real, popular SQLite binding for Node — and it's deliberately, notably synchronous, in contrast to Node's usual async-everything idioms. The library's own documented reasoning: SQLite operations are so fast that the overhead of async/await machinery genuinely isn't worth it for typical use — a real design decision, not an oversight, and a direct, concrete instance of sqlite1-1's own "near-instant, no handshake" claim.

const Database = require('better-sqlite3');
const db = new Database('notes.db');

db.exec('CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)');

// Parameterized here too
db.prepare('INSERT INTO notes (body) VALUES (?)').run('First real note');

const notes = db.prepare('SELECT * FROM notes').all();
console.log(notes);

Side-by-Side — The Simplicity Gap

Closing Connections & Resource Management

Even though there's no network connection to manage, a SQLite connection object still wraps real operating-system file handles and should still be explicitly closed — via a context manager, a try/finally, or an explicit close() call — when done. It's easy to assume this doesn't matter "since it's just a file," but proper cleanup still matters, especially in the long-running server process context sqlite1-6 covered.

Hands-On Exercises

Eight chapters have made a fair case for SQLite's own real strengths. This chapter covers its genuine limitations with the same honesty — closing out sqlite1-1's own roadmap entirely.

No User/Permission System — A Structural Consequence, Not an Oversight

sqlite1-1's own "no server" model means there's genuinely nothing analogous to MySQL's user accounts or Postgres's own roles — there's no server process to authenticate against in the first place, so the entire concept of database-level user permissions has no place to live in SQLite's own architecture.

In practice, access control for a SQLite database is entirely a matter of operating-system file permissions (who can read or write the actual .db file) plus whatever the application itself chooses to implement. This is a genuinely different security model, not simply a lesser one — but it does mean the application and its deployment take on responsibility that MySQL/Postgres would otherwise absorb at the database layer itself. This is exactly what makes sqlite1-7's own "database-level per-user access control" disqualifying criterion structurally true, not just an arbitrary rule.

Historically Limited ALTER TABLE Support

SQLite's ALTER TABLE has real, historical, genuine limitations compared to MySQL/Postgres. For a long time, it could only rename a table, add a column, or rename a column — it could not drop a column, change a column's type, or add/remove constraints directly.

SQLite 3.35 (2021) added DROP COLUMN support — a real, recent improvement, worth naming honestly, echoing sqlite1-3's own STRICT-tables-as-a-recent-fix pattern.

For anything still beyond what ALTER TABLE supports, the classic, genuinely accepted SQLite idiom — not a hack — is: create a new table with the desired schema, copy the data over, drop the old table, and rename the new one into place.

No Procedural Language Equivalent to PL/pgSQL

postgres1-8 covered PL/pgSQL (and Postgres's own pluggable multi-language extensibility); MySQL has its own stored-procedure dialect. SQLite has neither. It does support triggers, but trigger bodies are limited to ordinary SQL statements — not a full procedural language with variables, loops, or control flow.

This is a genuine, structural limitation rather than just "SQLite being simpler." Embedding logic inside the database engine itself doesn't fit SQLite's own architecture naturally, since the entire point of SQLite is that the application already has direct, in-process access to the data. Most logic that would live in a stored procedure in MySQL/Postgres simply lives in ordinary application code instead when using SQLite — a genuinely different, not necessarily worse, division of responsibility.

Type Affinity Gotchas, Revisited

sqlite1-3's own warn-box already covered this in depth: mixed storage classes in one column can produce surprising sort and comparison results, since SQLite orders storage classes in a fixed sequence rather than comparing mixed values as one type. Worth restating briefly here as this chapter closes the loop on it, per sqlite1-1's own roadmap.

A Few Other Honest, Smaller Limitations

• Historically limited RIGHT JOIN/FULL OUTER JOIN support — modern SQLite has added support for both, worth an accurate, current note rather than an outdated claim.
• No native network/inet types the way postgres1-3 covered for Postgres.
• Database size is practically capped by available disk, though the theoretical limit (around 281 TB) is enormous — not an actual practical concern for the vast majority of real use cases.

Hands-On Exercises

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