SQLite under a hypermedia app

The previous course settled the driver: modernc.org/sqlite, pure Go, registered as "sqlite", so CGO_ENABLED=0 static builds keep working. What a hypermedia app adds is pressure: every click is a request, and many of them write. That collides with SQLite's core rule — one writer at a time. A second connection that tries to write while the lock is held fails immediately with SQLITE_BUSY: the infamous database is locked.

You don't fix that in code. You fix it in the DSN, once:

db, err := sql.Open("sqlite",
    "file:app.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)")

Each pragma earns its place:

• journal_mode(WAL) — the write-ahead log lets readers keep reading while a write is in progress (the default rollback journal blocks them). For a hypermedia app that's most of your concurrency problem solved: GETs never queue behind a POST.
• busy_timeout(5000) — the second writer no longer fails instantly; it retries for up to 5 seconds until the lock frees. Since handler writes take microseconds, in practice writers just take turns.
• foreign_keys(1) — SQLite ships with foreign key enforcement off for historical reasons. Turn it on or your REFERENCES clauses are decoration.

The query layer then falls into the same two shapes as the templates — a list query behind the full page, a row query behind the fragment. RETURNING (SQLite 3.35+) makes insert-then-render one statement:

func (s *Store) List() ([]Todo, error) {
    rows, err := s.db.Query(`SELECT id, title, done FROM todos ORDER BY id`)
    // ... defer rows.Close(); scan each row; return the slice
}

func (s *Store) Add(title string) (Todo, error) {
    var t Todo
    err := s.db.QueryRow(
        `INSERT INTO todos (title) VALUES (?) RETURNING id, title, done`,
        title,
    ).Scan(&t.ID, &t.Title, &t.Done)
    return t, err
}

List feeds {{range .Todos}}{{template "todo-item" .}}{{end}}; Add feeds one todo-item execution. Query, partial, and swap line up one-to-one — that symmetry is most of the architecture.

The challenge is a single-writer lock simulator: three overlapping writes, run once with no timeout and once with busy_timeout, to see who errors and who waits.

Next: the honest question — when is this stack the right call, and when should you reach for a React SPA instead?