Concept: sqlx, SQLite, and Migrations

Kind: Concept. New crate: sqlx — this chapter shows it working before you build with it.

This is the persistence arc. Everything before it kept state in memory or in an append-only file. Now the control plane needs a place where a run's status survives a process restart, where two schedulers can both look at the same queue, and where "this job finished" and "the run's done-count went up" are recorded as one indivisible fact. That place is a SQLite database, and the crate that gets Rust talking to it is sqlx.

This chapter is about the crate, not the schema — the schema is the next chapter's build. Here we answer three questions that trip people up the first time: what sqlx is (and the one big decision we make about how to use it), how you open a database and keep it alive, and how the table definitions get created. We do all of it on a toy notes table so nothing here can be mistaken for the answer key.

What sqlx is, and the decision that shapes the whole course

sqlx is an async SQL toolkit. You hand it SQL as text, it sends that text to the database, and it hands you back rows you pull columns out of. It is not an ORM — there is no Note.save(), no query builder that writes SQL for you out of method chains. You write SQL; sqlx runs it asynchronously and maps the results.

Here is the fork in the road, and it is worth understanding before you type anything, because it explains an oddity you would otherwise trip over. sqlx offers two ways to run a query:

• The compile-time-checked macros (sqlx::query!, sqlx::query_as!). These connect to a real database at compile time, run your SQL against it, and verify the column names and types match the Rust you wrote. A typo in a column name becomes a build error.
• The runtime query API (sqlx::query, sqlx::query_as — no !). These treat the SQL as an ordinary string that is checked only when it runs.

The macros sound strictly better — who would not want their SQL checked at compile time? But they carry a price: to check your SQL at build time, the compiler needs a live database to check against, which sqlx locates through a DATABASE_URL environment variable. Watch what happens when it is not set:

error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
 --> examples/compile_time.rs:5:15
  |
5 |     let row = sqlx::query!("SELECT 1 as one").fetch_one(&pool).await?;
  |               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

That is a build failure. It means anyone who clones this repo and types cargo test needs a database provisioned and an environment variable pointing at it, or the workspace does not compile. For a teaching codebase — and for CI — that is a heavy tax.

Say this back to yourself once: runtime API, because there is no DATABASE_URL at build time, so SQL errors surface in tests rather than at compile time. That sentence is the answer to the first quiz question and the reason the store code looks the way it does.

Connecting: the pool, and two options that matter

You do not hand sqlx a bare connection; you hand it a pool. A pool is a small set of reusable connections. Even when the set has size one, the pool is what owns the connection's lifetime. Here is the toy store's connect, which is the exact shape you will write for the real one:

use std::str::FromStr;
use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
use sqlx::SqlitePool;

async fn connect(url: &str) -> anyhow::Result<SqlitePool> {
    let opts = SqliteConnectOptions::from_str(url)?
        .create_if_missing(true);        // make the file if it is not there yet
    let pool = SqlitePoolOptions::new()
        .max_connections(1)              // one writer; keeps :memory: alive
        .connect_with(opts)
        .await?;
    sqlx::migrate!().run(&pool).await?;  // create the tables (next section)
    Ok(pool)
}

Two of those lines are load-bearing, and both have a why worth holding onto:

• create_if_missing(true) — by default, opening sqlite://data.db on a path that does not exist is an error. This flag says "if the file is not there, create it." Without it, the very first run of a fresh checkout fails because the database file does not exist yet.

• max_connections(1) — this looks like a performance knob and is really a correctness one, for two reasons. First, SQLite is a single-writer database: only one connection may write at a time, so a larger pool buys you contention, not throughput. Second, and this is the subtle one, an in-memory database (sqlite::memory:) is owned by its connection — when that connection closes, the database and everything in it vanishes. A pool of one keeps exactly one connection alive for the pool's whole life, so the in-memory database stays alive too. A pool of five in-memory connections would be five different, empty databases. Pin it to one.

Migrations: how the tables come to exist

A fresh database has no tables. Migrations are the ordered SQL scripts that build the schema up from nothing. sqlx's migrate!() macro embeds every .sql file from a migrations/ directory into your binary at compile time, and .run(&pool) executes any that have not been applied yet, tracking which ran in a bookkeeping table it manages for you.

For the toy, migrations/0001_init.sql is one statement:

CREATE TABLE notes (
    id         TEXT PRIMARY KEY,
    body       TEXT NOT NULL,
    created_at TEXT NOT NULL
);

The file name matters: sqlx applies migrations in lexical order, so the numeric prefix (0001_, 0002_, …) is the version sequence. Run migrate!() against a fresh database and this table appears; run it again and sqlx sees the migration is already applied and does nothing. That idempotence is why calling connect at the start of every test is safe — the second, hundredth, thousandth call all converge on the same schema.

Note also that migrate!() embeds the SQL at compile time from a fixed directory — which is a different, and cheaper, use of a macro than query!. It reads local files; it needs no live database. That is why we can keep migrations as a macro while rejecting the query macros.

The whole toy, working

Here is notes end to end — connect, insert, get, and a miss — the same three-method shape (connect/in_memory, insert, get) the real store will have. It uses the runtime API throughout: SQL as strings, ? placeholders filled by .bind(...), columns pulled out by name with try_get.

use std::str::FromStr;
use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions, SqliteRow};
use sqlx::{Row, SqlitePool};

#[derive(Debug, PartialEq, Eq)]
struct Note {
    id: String,
    body: String,
    created_at: String,
}

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

impl NoteStore {
    async fn connect(url: &str) -> anyhow::Result<Self> {
        let opts = SqliteConnectOptions::from_str(url)?.create_if_missing(true);
        let pool = SqlitePoolOptions::new()
            .max_connections(1)
            .connect_with(opts)
            .await?;
        sqlx::migrate!().run(&pool).await?;
        Ok(Self { pool })
    }

    // One fresh, private in-memory database — the per-test entry point.
    async fn in_memory() -> anyhow::Result<Self> {
        Self::connect("sqlite::memory:").await
    }

    async fn insert_note(&self, note: &Note) -> anyhow::Result<()> {
        sqlx::query("INSERT INTO notes (id, body, created_at) VALUES (?, ?, ?)")
            .bind(&note.id)
            .bind(&note.body)
            .bind(&note.created_at)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    async fn get_note(&self, id: &str) -> anyhow::Result<Option<Note>> {
        let row = sqlx::query("SELECT * FROM notes WHERE id = ?")
            .bind(id)
            .fetch_optional(&self.pool)   // Option: the row may not exist
            .await?;
        Ok(row.map(row_to_note).transpose()?)
    }
}

// Turn a raw row into our type. This hand-mapping is the price of the
// runtime API — the macros would generate it, but would need DATABASE_URL.
fn row_to_note(row: SqliteRow) -> anyhow::Result<Note> {
    Ok(Note {
        id: row.try_get("id")?,
        body: row.try_get("body")?,
        created_at: row.try_get("created_at")?,
    })
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let store = NoteStore::in_memory().await?;
    let note = Note {
        id: "n1".into(),
        body: "buy milk".into(),
        created_at: "2026-07-21T10:00:00Z".into(),
    };
    store.insert_note(&note).await?;

    let fetched = store.get_note("n1").await?;
    println!("fetched: {fetched:?}");
    println!("round-trips equal: {}", fetched.as_ref() == Some(&note));

    let missing = store.get_note("nope").await?;
    println!("missing id -> {missing:?}");
    Ok(())
}

Run it and you see the round trip, plus the deliberate miss:

fetched: Some(Note { id: "n1", body: "buy milk", created_at: "2026-07-21T10:00:00Z" })
round-trips equal: true
missing id -> None

Three details to lock, because each recurs in the build:

• .bind(...) for every ?. You never format values into the SQL string yourself. The placeholders keep the value and the query text separate — which is both how sqlx knows the value's type and why SQL injection is a non-issue: a bound value can never be read as SQL.
• fetch_optional returns Option<Row>. A lookup by id might find nothing; the type says so. That is why get_note returns Option<Note> and the miss prints None. (Its siblings: fetch_one when a row must exist, fetch_all for many.)
• try_get("column") pulls a typed value out by name. This is the manual mapping the runtime API costs you — the exact code the query_as! macro would have generated, written by hand instead, in exchange for not needing a build-time database.

These examples use sqlx, which is not on the Rust playground, so there is no play button. To run them yourself: cargo new notes-scratch, add sqlx = { version = "0.8", features = ["runtime-tokio", "sqlite", "macros", "migrate"] }, tokio = { version = "1", features = ["full"] }, and anyhow; put the CREATE TABLE notes script in migrations/0001_init.sql; paste the code into main.rs. That is the same crate set — and the same runtime-API style — the store crate declares.

Why this is the right foundation for the arc

The next chapter builds the real store, and the mapping is one-for-one:

• notes table ↔ runs table (plus a jobs table alongside it)
• NoteStore::connect / in_memory ↔ Store::connect / in_memory — identical, down to create_if_missing(true) and max_connections(1)
• insert_note ↔ insert_run (bind fields, execute)
• get_note ↔ get_run (fetch_optional, try_get, return Option)
• migrations/0001_init.sql (one CREATE TABLE) ↔ migrations/0001_init.sql (two tables plus indexes)

The domain changes from grocery notes to eval runs; the shape does not move. Get the shape solid here and the store chapter is mostly translation. What it adds — the atomic claim and the transactional record — is the concept two chapters from now, and it is where SQL stops being a filing cabinet and starts being the thing that keeps two schedulers from stepping on each other.

Questions to lock

Genuinely pause on each. If one is fuzzy, that is the signal to re-read.

1. Why does this course use the runtime query API instead of the compile-time query! macros — and what concretely would break for someone running cargo test if we used the macros?
2. What are create_if_missing(true) and max_connections(1) each protecting against? (For the second one, why does an in-memory database make it correctness, not just performance?)
3. What does migrate!() do, why is calling it at the start of every test safe, and why can it stay a macro when query! cannot?

Next chapter is the first build of this arc: the runs and jobs schema, and Store::connect/in_memory/insert_run/get_run/insert_jobs.