The original’s domain layer is a package of JavaBeans: Category, Product, Item, each a bag of strings and BigDecimals with getters and setters, each constructible in any state including nonsense. Nothing stops a Product whose category is an item id, or an Item whose price is null. In practice MyBatis fills them correctly and everyone stays polite. “In practice” is doing a lot of work in that sentence.

This lesson builds the catalog’s types so the nonsense states don’t compile, then queries them with SQL that’s checked before the app ever runs. Two tools, one idea: move failure earlier. Checkpoint: lesson-04.

Ids that refuse to be swapped

The catalog has three kinds of identifier — FISH, FI-SW-01, EST-1 — and in the original all three travel as String, so the compiler shrugs when one lands in the wrong parameter. The fix costs a few lines each:

#[derive(Debug, Clone, PartialEq, Eq, sqlx::Type)]
#[sqlx(transparent)]
pub struct CategoryId(String);

impl TryFrom<String> for CategoryId {
    type Error = InvalidId;
    fn try_from(raw: String) -> Result<Self, InvalidId> {
        validate_id(&raw)?;
        Ok(Self(raw))
    }
}

ProductId and ItemId get the same treatment (via a small macro_rules! so the boilerplate is written once). This is the newtype pattern, and it buys two things. First, products_in_category(&pool, &item_id) is now a type error instead of an empty result set you debug at midnight. Second, because the only way to construct one is TryFrom, which runs validate_id, an existing CategoryId is proof the value passed its checks.

That second property has a name: parse, don’t validate. Validation sprinkled through the codebase checks the same string over and over because no function can trust its caller. Parsing converts the string once, at the boundary, into a type that can only hold a valid value — and every function past the boundary trusts the type. The check the constructor runs is deliberately modest (non-empty, fits the schema’s varchar(10)), because a boundary doesn’t have to be clever. It has to be singular.

Money gets a newtype too:

#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, sqlx::Type)]
#[sqlx(transparent)]
pub struct Cents(pub i64);

No Add, no Display, nothing else — the cart adds arithmetic in lesson 7 and the templates add formatting in lesson 5, each when something needs it. An Item.list_price is a Cents, and handing it a float is no longer a bug you can write.

SQL that fails on Tuesday, not in production

Lesson 3’s query_scalar calls were runtime-checked: typo the table name, find out on page load — the MyBatis deal. The query_as! macro moves that discovery into cargo build:

pub async fn item(pool: &SqlitePool, id: &ItemId) -> Result<Option<Item>, sqlx::Error> {
    sqlx::query_as!(
        Item,
        r#"SELECT i.itemid as "id: ItemId", i.productid as "product_id: ProductId",
                  i.listprice as "list_price!: Cents", i.attr1 as "attribute",
                  inv.qty as "quantity!: i64"
           FROM item i JOIN inventory inv ON inv.itemid = i.itemid
           WHERE i.itemid = ?1"#,
        id
    )
    .fetch_optional(pool)
    .await
}

At compile time, the macro connects to a development database, asks SQLite to describe this query, and verifies everything: the tables exist, the columns exist, the parameter count matches, and every output column maps to its struct field. Rename a column in a future migration and every query touching it breaks the build, with file and line. This is the MyBatis philosophy — SQL stays SQL, visible and tunable — with the safety net JPA promised and SQL-in-XML never had.

Two pieces of the override syntax carry the domain model:

  • as "id: ItemId" maps a column into a newtype instead of a bare String — the #[sqlx(transparent)] derive is what makes that legal.
  • as "list_price!: Cents" — the ! asserts non-null. The 2002 schema declared listprice nullable and the data never is; the assertion documents that fact at the exact place it matters, instead of an Option<Cents> polluting every template that renders a price.

The functions live in db/catalog.rs as plain async fns. No CatalogRepository interface, no implementation class — the port plan calls for extracting a trait when testing pressure demands one, and lesson 4 has no such pressure. Six functions, six queries, done.

The fixture that can’t lie

Those functions need tests, and the test setup is one line:

async fn test_pool() -> SqlitePool {
    crate::db::pool("sqlite::memory:").await.expect("test db")
}

Point lesson 3’s db::pool at sqlite::memory: and you get a fresh database per test, built by the production migrations and filled with the production seed. No fixture files, no mocks, no test doubles that drift out of sync with the schema, because the fixture is the schema. Then the tests read like catalog facts:

#[tokio::test]
async fn large_angelfish_costs_16_50() {
    let pool = test_pool().await;
    let est1 = item(&pool, &id("EST-1")).await.unwrap().expect("EST-1 exists");
    assert_eq!(est1.list_price, Cents(1650));
    assert_eq!(est1.quantity, 10000);
}

cargo test: six tests, well under a second, including the one asserting that unknown ids come back as None rather than errors — a page that doesn’t exist is a 404, not a 500, and that distinction starts here.

The cache that unbreaks the chicken and egg

One practicality before the challenge. If the macros need a database at compile time, how does a fresh clone build before it has ever run? Answer: a committed cache. Install the CLI once:

cargo install sqlx-cli --no-default-features --features sqlite

then, whenever queries change, cargo sqlx prepare writes each query’s verified description into a .sqlx/ directory that gets committed. Builds use the live database when DATABASE_URL points at one (drop DATABASE_URL=sqlite:jpetstore.db in a .env file for development) and fall back to the cache when it doesn’t — which is exactly what happens when you clone a checkpoint and build cold. CI setups run with SQLX_OFFLINE=true to force the cache and prove it’s current.

You’ll also notice cargo build now prints a pile of dead-code warnings: six query functions and three domain types, none reachable from main yet. The compiler is keeping our to-do list. Lesson 5 empties it.

Build it

File Action What goes in it
src/domain/mod.rs write pub mod catalog;
src/domain/catalog.rs write + copy CategoryId and its TryFrom are shown above — write those, plus InvalidId, validate_id, Cents, and the three structs. The macro_rules! block that stamps out the other two ids is mechanism, not pattern: copy it
src/db/mod.rs modify Move db.rs here; declare pub mod catalog;
src/db/catalog.rs write item() is shown in full. Write the other five queries from it: categories, category, products_in_category, product, items_for_product — this is the lesson’s real workout
same file, mod tests write test_pool() and the price test are shown; write the other three: sorted categories, four fish products, unknown ids are None
src/main.rs modify Declare mod domain;
.env write One line: DATABASE_URL=sqlite:jpetstore.db (stays uncommitted)
.sqlx/ generated cargo sqlx prepare — commit the result

Done when: cargo test shows 6 passing, and a build with SQLX_OFFLINE=true still succeeds.

Answer key: lesson-03...lesson-04.

Challenge: search, one lesson early

Write search_products(pool, keyword) in db/catalog.rs: products whose name or description contains the keyword, case-insensitive (LIKE and lower() will get you there), plus a test proving "fish" finds four products and "zebra" finds none. Lesson 6 builds the search page on exactly this function, so you’re writing next week’s code — compare signatures when you get there.

Next lesson, the storefront: five category pages, sixteen product pages, and the moment this app first looks like JPetStore.