Day 9 · State & sqlx

In Day 8 every handler was self-contained — it returned a hard-coded value. A real API needs to share something across all of them: a database connection. This day is about two things that turn out to be one: how axum hands shared state to every handler, and how sqlx talks to SQLite asynchronously. The connecting thread is ownership — many tasks, one pool, no races.

The shared-state problem

Every handler needs the database. You could open a new connection inside each one, but connecting is expensive (a handshake, a file open) and you’d have no limit on how many pile up. What you want is one pool, created at startup, that every handler borrows a connection from and returns it to.

So handlers need access to a value created in main. axum’s answer is the State extractor. You build a state struct, attach it to the router with .with_state(...), and any handler can ask for it:

use sqlx::SqlitePool;

#[derive(Clone)]
struct AppState {
    pool: SqlitePool,
}

let app = Router::new()
    .route("/notes", get(list_notes))
    .with_state(AppState { pool });

async fn list_notes(State(state): State<AppState>) -> /* ... */ {
    // state.pool is right here
}

Notice #[derive(Clone)]. axum clones the state into each request’s task — which sounds expensive until you see what’s inside.

Under the hood — why cloning the pool is nearly free

SqlitePool is a handle, not the pool itself. Internally it’s an Arc (atomically reference- counted pointer) around the real connection set. Cloning it doesn’t copy any connections — it bumps a reference count and hands back a second pointer to the same pool:

   AppState (task A)  ─┐
   AppState (task B)  ─┼──►  Arc<RealPool>  ──►  [ conn, conn, conn, conn, conn ]
   AppState (task C)  ─┘        (one set of connections, shared)

This is the ownership rule doing exactly what it did in kvlite: Arc lets many owners share one value safely, and the count makes sure the connections are freed exactly once — when the last handle drops. The compiler guarantees you can’t accidentally use a pool after it’s gone, even with thousands of tasks holding clones.

sqlx: real SQL, async, no macros at build time

sqlx is an async SQL toolkit. It has two ways to write queries, and choosing the right one matters for whether your project even builds:

• The compile-time-checked macros (sqlx::query!, query_as!) connect to a live database during cargo build to verify your SQL and infer types. Powerful — but your build now needs a database.
• The runtime API (sqlx::query, sqlx::query_as) checks nothing at compile time; the SQL is an ordinary string sent to the database when the query runs.

apilite uses the runtime API on purpose: cargo build and cargo test work with no database present and no extra CLI installed. You trade compile-time SQL checking for a build that just works — the right trade for a teaching project (and for any CI that shouldn’t depend on a live DB).

// query_as maps result rows onto a struct that derives FromRow:
let notes = sqlx::query_as::<_, Note>(
    "SELECT id, title, body, created_at FROM notes ORDER BY id",
)
.fetch_all(&state.pool)
.await?;

FromRow is the glue: derive it on Note and sqlx maps each column to the matching field by name.

Never build SQL with format!() — bind instead

Here is the single most important habit in this whole project. To put a value into a query, you use a placeholder (? in SQLite) and .bind(value) — you do not interpolate it into the string:

// RIGHT — the value is data, sent separately from the SQL:
sqlx::query("SELECT * FROM notes WHERE id = ?")
    .bind(id)
    .fetch_optional(&pool)
    .await?;

// WRONG — never do this:
// let sql = format!("SELECT * FROM notes WHERE id = {id}");

With .bind, the value travels to the database as a parameter, never as SQL syntax. The database cannot mistake a note’s title for a command — which is the entire defense against SQL injection.

It actually happened — Heartland, 2009

In January 2009, payment processor Heartland Payment Systems disclosed a breach that exposed on the order of 130 million credit-card numbers — one of the largest of its era. The initial foothold was a SQL injection flaw in a web application: attacker-controlled input was concatenated into a query and executed as code. The fix is exactly the discipline above — parameterize every query so input can never become SQL. Rust doesn’t stop you from writing format! into a query, but sqlx makes .bind the path of least resistance, and that habit is what closes this entire bug class.

Schema on startup

apilite has no migration tool. Instead it runs CREATE TABLE IF NOT EXISTS at boot — idempotent, so it’s safe on every start:

pub async fn init_schema(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS notes (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            title      TEXT NOT NULL,
            body       TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )",
    )
    .execute(pool)
    .await?;
    Ok(())
}

For real production systems you’d graduate to a migration tool (sqlx has one). For learning, self- bootstrapping schema keeps the whole project to cargo run.

CRUD, one handler at a time

With the pool in State and a schema in place, each operation is a few lines. Two patterns carry most of the weight: RETURNING to get the written row back in one round trip, and .fetch_optional plus .ok_or to turn “no row” into a clean 404 (the AppError arrives in Day 10).

// CREATE — insert and get the full row (with server-assigned id) back at once
let note = sqlx::query_as::<_, Note>(
    "INSERT INTO notes (title, body) VALUES (?, ?)
     RETURNING id, title, body, created_at",
)
.bind(input.title)
.bind(input.body)
.fetch_one(&state.pool)
.await?;

// READ ONE — None becomes a 404 instead of a 500
let note = sqlx::query_as::<_, Note>(
    "SELECT id, title, body, created_at FROM notes WHERE id = ?",
)
.bind(id)
.fetch_optional(&state.pool)
.await?
.ok_or(AppError::NotFound)?;

// DELETE — rows_affected() == 0 means it wasn't there
let result = sqlx::query("DELETE FROM notes WHERE id = ?")
    .bind(id)
    .execute(&state.pool)
    .await?;

By the numbers — sizing the pool, and the RETURNING round trip

A connection pool trades memory for latency. With max_connections(5), at most five queries run concurrently; a sixth waits for one to free up. Sizing is a balance:

   too small → requests queue behind a few connections (latency spikes under load)
   too large → the database thrashes on context-switching and memory

RETURNING (SQLite 3.35.0, March 2021) saves a round trip: INSERT ... RETURNING hands back the new row — including the auto-assigned id and the datetime('now') default — in one query instead of an insert followed by a select. One fewer round trip per create, across every write.

Under the hood — the in-memory SQLite trap

For tests we use sqlite::memory:. There’s a sharp edge worth knowing: each SQLite connection to :memory: gets its own private, empty database. With a multi-connection pool, your CREATE TABLE might run on connection #1, then a query lands on connection #2 — which has never seen the table:

   pool (max 5) ─┬─ conn #1  → has the "notes" table   (CREATE ran here)
                 └─ conn #2  → empty :memory: db        → "no such table: notes"  ✗

apilite’s create_pool defends against this: when the URL contains :memory:, it pins the pool to a single connection (and keeps min_connections(1) so the in-memory database — which lives only as long as a connection is open — is never dropped between requests). For a file database the table is shared on disk, so the full pool is fine. This is the kind of bug the compiler can’t catch for you — it’s runtime behavior of the database, not the language — which is a useful reminder of where Rust’s guarantees end and your understanding has to take over.

The thread

What does building this force you to understand — and what is Rust’s compiler protecting you from? Sharing a database across hundreds of concurrent tasks is precisely the scenario that wrecks unsafe-by-default languages: a connection used after it’s closed, a pool freed while a request still holds it. Rust’s answer is the same Arc ownership you met in kvlite — many handles, one pool, freed exactly once — now load-bearing under real concurrency. The compiler protects the lifecycle of the pool; you still have to understand the database’s own rules (like :memory: isolation), and knowing where that line falls is half of writing correct systems.

Next: turn the ?s and AppErrors above into real HTTP responses, and add logging. Day 10 · Errors & middleware →

Check your understanding

1. Why does AppState derive Clone, and why is cloning a SqlitePool cheap rather than copying every connection?
2. apilite deliberately uses sqlx::query / query_as instead of the query! / query_as! macros. What does that choice buy the build, and what does it give up?
3. What does .bind(value) do that string interpolation doesn’t — and which bug class does that close?
4. In a read handler, why prefer .fetch_optional(...).await?.ok_or(AppError::NotFound)? over .fetch_one(...)? What HTTP outcome does each produce when the row is missing?
5. Why can a multi-connection pool over sqlite::memory: intermittently fail with “no such table,” and how does apilite avoid it?

Show answers

1. axum clones the state into every request’s task, so the state must be Clone. SqlitePool is an Arc-backed handle: cloning it bumps a reference count and returns a second pointer to the same connection set — no connections are duplicated.
2. The runtime API checks nothing at compile time, so cargo build/cargo test need no live database and no sqlx-cli — the build just works (great for CI and learning). It gives up compile-time verification of the SQL and column types that the macros provide against a real DB.
3. .bind sends the value to the database as a parameter, separate from the SQL text, so it can never be parsed as SQL syntax. That closes the SQL-injection class; interpolation (format!) reopens it by letting input become code.
4. .fetch_optional returns Option<Note>, so None maps cleanly to AppError::NotFound → a 404. .fetch_one errors when there’s no row, which (without special handling) surfaces as a generic 500. The optional path expresses “missing is a normal outcome,” not a server failure.
5. Each connection to :memory: has its own private empty database, so CREATE TABLE on one connection isn’t visible to another. apilite pins :memory: URLs to a single connection (max_connections(1), min_connections(1)), so every query sees the same database.