Persistence with sqlx

Goal: you can store and query data with sqlx on SQLite: typed parameterized queries, schema-as-code, and constraints enforced by the database itself.

Why sqlx (and not an ORM)

House decision: explicit SQL, compile-time checked when used with the query macro, no DSL to learn. Sinau LMS runs on exactly this. The lab uses the runtime API (sqlx::query(...).bind(...)) so everything works offline; the production pattern adds the query! macro checked against a real schema at build time.

Connect + schema-as-code

#![allow(unused)]
fn main() {
let opts = SqliteConnectOptions::from_str("sqlite:/tmp/app.db")?.create_if_missing(true);
let pool = SqlitePoolOptions::new().max_connections(5).connect_with(opts).await?;
sqlx::query("CREATE TABLE IF NOT EXISTS notes (...)").execute(&pool).await?;
}

A Pool is cloneable and shared — hand one to your axum State. Real projects put the DDL into ordered migrations/*.sql (that is literally Sinau’s server/migrations/ layout); the idea is the same: schema is versioned code, not a GUI adventure.

The query patterns you need

#![allow(unused)]
fn main() {
// insert and get the id back — RETURNING saves a round trip
let id: i64 = sqlx::query("INSERT INTO notes (title) VALUES (?1) RETURNING id")
    .bind(title).fetch_one(&pool).await?.get(0);

// list — map rows by position (or .try_get by column name)
let rows = sqlx::query("SELECT id, title FROM notes ORDER BY id")
    .fetch_all(&pool).await?;

// optional single row
let maybe = sqlx::query("... WHERE id = ?1").bind(id).fetch_optional(&pool).await?;
}

Always .bind() values — never format them into the string. Positional ?1 ?2 keeps the mapping visible.

Constraints: the DB has the last word

title TEXT NOT NULL CHECK (length(trim(title)) > 0)

The app validates for friendly 400s (M5); the constraint guarantees it even if a future code path forgets. Defense in depth — the same reason Sinau’s migrations carry real REFERENCES ... ON DELETE CASCADE clauses.

Lab

labs/m6-notes-db — embedded SQLite file DB, RETURNING inserts, ordered list, optional find, and a demo of the CHECK constraint rejecting empty titles. Tests (4): sequential ids, insert-order listing, constraint rejection (empty AND whitespace), missing-row None.

Done when: tests green, and you can explain what fetch_one vs fetch_optional vs fetch_all each guarantee — and which one you reach for when “not found” is a normal outcome.