The original JPetStore boots by executing two SQL files into an in-memory HSQLDB: jpetstore-hsqldb-schema.sql builds thirteen tables, jpetstore-hsqldb-dataload.sql fills them with fish. Every restart, the whole database is rebuilt from scratch and your orders evaporate. Charming for a demo; not what we’re building.

This lesson ports both files into SQLx migrations against a real SQLite file. By the end, cargo run creates and seeds the database on first launch, and the home page proves it with live numbers. Checkpoint: lesson-03.

Migrations, because databases have a history

The original’s schema file describes one moment in time, and rebuilding from it on every boot is what makes that workable. A persistent database needs something else: an ordered record of every change it has ever absorbed, applied exactly once. That’s all migrations are.

SQLx’s tooling is refreshingly small. Files live in migrations/, named 0001_schema.sql, 0002_seed.sql, and one macro call in our db module applies whatever hasn’t run yet:

sqlx::migrate!().run(&pool).await?;

Note what migrate! is: a macro, expanded at compile time, which means the SQL files are read at build and embedded into the binary. There is no migrations/ directory to ship, no classpath resource to forget. The deployment artifact stays exactly one file. Flyway does this job well in the Java world; here it’s a dependency we already had.

Porting the DDL: what survived and what didn’t

Most of the schema crossed over character for character. SQLite is happy with varchar(80), named constraints, and the original’s indexes, and I kept every table and column name identical so the MyBatis mapper SQL we port in later lessons reads the same against both databases. But the port plan promised deliberate fixes, and three of them plus a money decision happen right here, in the DDL:

The sequence table is gone. The original generates order IDs by reading a counter row, incrementing it, and writing it back — application code doing, less safely, what the database does natively. orders.orderid is now integer primary key autoincrement, and lesson 10 gets to delete an entire class of code because of this line.

orders has no creditcard column. The original’s checkout writes the full card number and expiry into the orders table, in the clear. We keep cardtype — a brand name reveals nothing — and the columns for the number and expiry simply do not exist, so no future bug can write them. Lesson 9 covers what real systems do instead.

signon.password grew from varchar(25) to varchar(255). Read the original column size like an archaeologist: 25 characters was plenty, because the password itself went in. Ours stores an argon2 hash, which needs the room.

And every money column became integer cents. decimal(10,2) in SQLite quietly decays into floating point, and floats cannot represent most cent values exactly — 0.1 + 0.2 famously refuses to be 0.3. Java answers this with BigDecimal ceremony; we answer it by storing 1650 where the original stored 16.50 and letting i64 arithmetic be exact. Formatting dollars out of cents is a template concern, and lesson 5 owns it.

Porting the seed data

The dataload file came across nearly verbatim: five categories, sixteen products, twenty-eight items, two suppliers, two accounts. Prices got the cents treatment (16.50 → 1650). The twenty-eight identical inventory rows collapsed into one statement, because SQL can say that:

INSERT INTO inventory (itemid, qty) SELECT itemid, 10000 FROM item;

One thing deliberately did not come across. The original seeds signon with ('j2ee','j2ee') — username and password, both in the clear, which you found in lesson 1’s scavenger hunt. Our seed leaves signon empty. The same demo users return in lesson 8, hashed properly, once the app can do the hashing.

Both migration files carry header comments listing every delta and crediting the Apache-2.0 originals. When you port someone’s work, write down what you changed; future-you is the first beneficiary.

A pool, not a DataSource

The connection plumbing is eleven lines, in src/db.rs:

pub async fn pool(url: &str) -> Result<SqlitePool, DbInitError> {
    let options: SqliteConnectOptions =
        url.parse::<SqliteConnectOptions>()?.create_if_missing(true);
    let pool = SqlitePool::connect_with(options).await?;
    sqlx::migrate!().run(&pool).await?;
    Ok(pool)
}

This is the job Spring’s DataSource bean, the JNDI lookup, and the connection-pool XML did in the original, as one function you can read. create_if_missing gives us the HSQLDB convenience — first run conjures the database — without the amnesia. And DbInitError is lesson 2’s pattern at smaller scale: a two-variant enum naming exactly what can fail before the first request, which main handles by printing and exiting, per the startup rule.

The pool reaches the handlers

Axum’s dependency injection is a value, like everything else in this stack. The router registers the pool once:

Router::new()
    .route("/", get(home::home))
    .fallback(not_found)
    .with_state(pool)

and any handler that wants it asks with an extractor argument:

pub async fn home(State(pool): State<SqlitePool>) -> AppResult<Html<String>> {
    let categories: i64 = sqlx::query_scalar("SELECT count(*) FROM category")
        .fetch_one(&pool)
        .await?;
    // ...
}

No @Autowired, no proxy, no container lifecycle: with_state puts a value in, State takes it out, and if the types don’t line up it fails at compile time. The ? on the query is lesson 2 paying off — AppError grew a Database(#[from] sqlx::Error) variant, three lines, and every handler in the app can now propagate query failures to the error page.

These query_scalar calls are checked at runtime only; typo the table name and you find out on page load. That’s MyBatis-level safety, and it’s temporary. Next lesson brings the compile-time-checked queries that are the whole reason SQLx is in the toolbox.

Prove it

cargo run

First launch prints the startup line, creates jpetstore.db next to the project, and the home page at http://localhost:8081/ now reports 16 products across 5 categories, straight from live queries. Then open the file directly:

sqlite3 jpetstore.db 'SELECT itemid, listprice FROM item LIMIT 3;'
sqlite3 jpetstore.db 'SELECT count(*) FROM signon;'

Twenty-eight items priced in cents; zero signon rows, exactly as planned. Delete jpetstore.db and run again — migrations rebuild it. That delete-and-rerun loop is your reset button for the rest of the course.

Build it

File Action What goes in it
Cargo.toml generated cargo add sqlx --features runtime-tokio,sqlite,migrate
migrations/0001_schema.sql copy + write Porting 150 lines of DDL by hand teaches nothing — copy the original’s schema file, then make the four deltas yourself: delete sequence, make orderid autoincrement, drop the card columns, resize password, switch money to integer. Then diff your migration against mine
migrations/0002_seed.sql copy + write Same deal: copy the dataload, then apply the deltas — cents, no signon rows, and try the INSERT..SELECT for inventory
src/db.rs write DbInitError and pool(), shown above
src/web/error.rs modify Add the Database(#[from] sqlx::Error) variant and its status-code arm
src/web/mod.rs modify router(pool) takes the pool, registers it with .with_state(pool)
src/web/home.rs write The State-extracting handler with two query_scalar counts, shown above
src/main.rs modify Declare mod db;, build the pool, pass it to the router
templates/home.html modify Render {{ products }} and {{ categories }}

Done when: the home page reports 16 products across 5 categories, and sqlite3 jpetstore.db 'SELECT count(*) FROM signon;' says 0.

Answer key: lesson-02...lesson-03.

Challenge: count the stock

The home page counts categories and products. Add total inventory to it: sum inventory.qty and render something like “280,000 animals in stock.” You’ll touch the template struct, the handler, and one new query — the full width of what this lesson built. SUM returns NULL on an empty table, so notice what type query_scalar wants to hand you back.

Next lesson the rows become types: modeling the catalog so that an item without a product, or a price that isn’t money, can’t even be constructed.