Integration tests against a real database

Step 14 of 15. The service now has routes, real SQL, an auth layer, a rate limiter and a request id on every log line - and not one line of it has been tested automatically. The only proof so far has been the curl sequence, which has to be repeated by hand after every change. This step fixes that: the router becomes a value the tests can drive directly, and sqlx::test hands every test a Postgres database of its own, migrations included, so the tests run against the same engine production talks to.

Start from step 13’s end state: git checkout api-ch13 in your clone of the examples repository, inside axum-sqlx-api/, with the Postgres container from step 5 running and DATABASE_URL exported as before. One line of Cargo.toml changes - tower gains a feature, which the section below explains:

[dependencies]
axum = { version = "0.8", features = ["macros"] }
serde = { version = "1", features = ["derive"] }
serde_json = "1"
sqlx = { version = "0.9", features = ["runtime-tokio", "tls-rustls", "postgres", "macros"] }
thiserror = "2"
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }
tower = { version = "0.5", features = ["util"] }
tower-http = { version = "0.7.1", features = ["trace", "request-id"] }
tracing = "0.1.44"
tracing-subscriber = { version = "0.3.23", features = ["env-filter"] }
validator = { version = "0.21", features = ["derive"] }

The router becomes a value

Until now the routes were built inline in main, tangled with the listener and the log setup, and unreachable from a test. They move into their own function:

// The whole service as a value. main adds the observability layers from
// step 13 on top and serves it; the tests below drive it directly - no
// listener, no port, no second process. Building the router needs a pool
// handle but opens no connection, so a test can hold one before any query
// has run.
fn app(pool: PgPool, key: impl Into<Arc<str>>) -> Router {
    // Two routers, because the routes have different audiences: browsers
    // hitting /{code} send no headers, and a load balancer polling /health
    // has none to offer. Only /links - the routes that change or read data -
    // sit behind the layers. route_layer, not layer: it runs only once a
    // route has matched, so a typo like /foo/bar stays a 404 instead of
    // being told to authenticate against a path that does not exist.
    // Both route_layers wrap the same routes, and the last one added is the
    // outermost: a request meets auth before the limiter, because the
    // limiter needs the identity auth leaves in the extensions.
    let protected = Router::new()
        .route("/links", get(list_links).post(create_link))
        .route_layer(RateLimit::new(10.0, 5.0))
        .route_layer(RequireApiKey::new(key));

    Router::new()
        .route("/health", get(health))
        .route("/{code}", get(resolve_link))
        .merge(protected)
        .layer(Extension(pool))
}

(The block is unverified because the checker compiles each snippet as a standalone crate, and health, create_link and the two layers do not travel with it. The tag holds the whole file, and cargo test at the end of this page compiles it for real.)

main keeps the subscriber, the pool and the migration line, then calls app and wraps the result in the three observability layers from step 13. The routing code is the only thing that moved; the tail of main now reads:

    let router = app(pool, key)
        .layer(
            TraceLayer::new_for_http()
                .make_span_with(RequestSpan)
                .on_response(DefaultOnResponse::new().level(Level::INFO)),
        )
        .layer(PropagateRequestIdLayer::x_request_id())
        .layer(SetRequestIdLayer::x_request_id(MakeRequestUuid));

    let listener = tokio::net::TcpListener::bind("0.0.0.0:8000").await.unwrap();
    tracing::info!("listening on {}", listener.local_addr().unwrap());
    axum::serve(listener, router).await.unwrap();

Nothing about a running server changes - the curls from earlier steps come back the same.

One request, no server

The tests never bind a port. tower::ServiceExt::oneshot pushes one request through the router and returns the response - the same call axum::serve makes for every connection, minus the listener. The util feature added to tower above is what puts ServiceExt in scope. A stripped-down version of the idea, runnable on its own:

use axum::{body::Body, http::{Request, StatusCode}, routing::get, Router};
use tower::ServiceExt;

#[tokio::main]
async fn main() {
    let app = Router::new().route("/health", get(|| async { "ok" }));

    // oneshot sends one request through the router and hands back the
    // response - what axum::serve does for every connection, minus the
    // listener.
    let res = app
        .oneshot(Request::builder().uri("/health").body(Body::empty()).unwrap())
        .await
        .unwrap();

    assert_eq!(res.status(), StatusCode::OK);
    let bytes = axum::body::to_bytes(res.into_body(), 1024).await.unwrap();
    println!("{}", std::str::from_utf8(&bytes).unwrap());
}

ok

Auth, the limiter and the handlers all sit inside the router, so a request that goes through oneshot meets every layer a real request would.

A database per test

Driving the router is half the step. The other half is what the handlers talk to: SQL only fails at runtime, and a test that mocks the database tests the mock. #[sqlx::test] replaces #[tokio::test]: before the test body runs, sqlx connects to the server DATABASE_URL points at, creates a database named for that one test, applies the migrations to it, and hands the body a pool connected to it. When the test ends, the database is dropped. Each test gets a fresh, empty schema with no setup and no cleanup, and because cargo runs tests in parallel threads, the per-test database is also what keeps them from trampling each other.

The tests themselves live in main.rs, in a #[cfg(test)] module next to the rate-limiter tests from step 12. (A binary crate has no library for a tests/ directory to import, so in-crate tests are the honest choice here

no second crate just for test access.)

#[cfg(test)]
mod api_tests {
    use super::*;
    use axum::{body::Body, http::{Method, Request}, response::Response};
    use serde_json::Value;
    use tower::ServiceExt as _; // .oneshot: drive the router without a server

    // The tests read as request in, response out, so the two helpers below
    // keep the plumbing in one place.
    fn req(method: axum::http::Method, uri: &str, key: Option<&str>, body: Option<String>) -> Request<Body> {
        let mut builder = Request::builder().method(method).uri(uri);
        if let Some(key) = key {
            builder = builder.header("authorization", format!("Bearer {key}"));
        }
        if body.is_some() {
            builder = builder.header("content-type", "application/json");
        }
        builder.body(Body::from(body.unwrap_or_default())).unwrap()
    }

    async fn send(app: Router, request: Request<Body>) -> Response {
        app.oneshot(request).await.unwrap()
    }

    async fn body_string(res: Response) -> String {
        let bytes = axum::body::to_bytes(res.into_body(), usize::MAX).await.unwrap();
        String::from_utf8(bytes.to_vec()).expect("body is utf-8")
    }

    #[sqlx::test(migrations = "./migrations")]
    async fn health_reports_ok(pool: PgPool) {
        let res = send(app(pool, "test-key"), req(Method::GET, "/health", None, None)).await;
        assert_eq!(res.status(), StatusCode::OK);
        assert_eq!(body_string(res).await, "ok");
    }

    #[sqlx::test(migrations = "./migrations")]
    async fn links_reject_a_missing_api_key(pool: PgPool) {
        let body = serde_json::to_string(&serde_json::json!({ "url": "https://example.org" })).unwrap();
        let res = send(
            app(pool, "test-key"),
            req(Method::POST, "/links", None, Some(body)),
        )
        .await;
        assert_eq!(res.status(), StatusCode::UNAUTHORIZED);
    }

    #[sqlx::test(migrations = "./migrations")]
    async fn a_created_link_resolves(pool: PgPool) {
        let router = app(pool, "test-key");
        let body = serde_json::to_string(&serde_json::json!({ "url": "https://example.org/page" })).unwrap();
        let res = send(
            router.clone(),
            req(Method::POST, "/links", Some("test-key"), Some(body)),
        )
        .await;
        assert_eq!(res.status(), StatusCode::CREATED);
        let created: Value = serde_json::from_str(&body_string(res).await).unwrap();
        assert_eq!(created["url"], "https://example.org/page");
        let code = created["code"].as_str().unwrap();

        let res = send(
            router,
            req(Method::GET, &format!("/{code}"), None, None),
        )
        .await;
        assert_eq!(res.status(), StatusCode::FOUND);
        assert_eq!(
            res.headers()["location"],
            "https://example.org/page"
        );
    }

    #[sqlx::test(migrations = "./migrations")]
    async fn an_unknown_code_is_a_404(pool: PgPool) {
        let res = send(app(pool, "test-key"), req(Method::GET, "/zzzzzzz", None, None)).await;
        assert_eq!(res.status(), StatusCode::NOT_FOUND);
    }

    #[sqlx::test(migrations = "./migrations")]
    async fn an_invalid_url_is_a_422(pool: PgPool) {
        let body = serde_json::to_string(&serde_json::json!({ "url": "not a url" })).unwrap();
        let res = send(
            app(pool, "test-key"),
            req(Method::POST, "/links", Some("test-key"), Some(body)),
        )
        .await;
        assert_eq!(res.status(), StatusCode::UNPROCESSABLE_ENTITY);
        assert!(body_string(res).await.contains("must be a valid URL"));
    }

    // The isolation guarantee, stated as a test: every test gets a fresh
    // database, so a list that was never written to is empty - and if the
    // tests ever start sharing a database, this assertion fails on the
    // second test that runs.
    #[sqlx::test(migrations = "./migrations")]
    async fn each_test_starts_from_an_empty_database(pool: PgPool) {
        let res = send(app(pool, "test-key"), req(Method::GET, "/links", Some("test-key"), None)).await;
        assert_eq!(res.status(), StatusCode::OK);
        let page: Value = serde_json::from_str(&body_string(res).await).unwrap();
        assert_eq!(page["links"].as_array().unwrap().len(), 0);
    }
}

Like the app block, this one is unverified in isolation - it only compiles inside the crate, where app exists. The tag holds it, and the output at the end of this page is what compiling and running it produces. What the six tests cover: health answers “ok” through a real pool; /links refuses a request with no API key before any SQL runs; a created link round-trips to a 302 with the right Location header; an unknown code is a 404; a bad URL is a 422 carrying step 10’s validation message; and the last test pins the isolation guarantee itself. Step 9’s curl sequence is now four of these assertions.

The tests pass their own "test-key" to app, so SHORTLINK_API_KEY - which only main reads - is not needed for cargo test. The database comes from the environment: #[sqlx::test] needs DATABASE_URL at test time, and without it every test panics before its body runs, with a real message from inside sqlx (the thread id in parentheses varies per machine):

thread 'api_tests::health_reports_ok' (15240378) panicked at /Users/rango/.cargo/registry/src/index.crates.io-1949cf8c6b5b557f/sqlx-postgres-0.9.0/src/testing/mod.rs:93:44:
DATABASE_URL must be set: EnvVar(NotPresent)

One quirk in the migrations path

migrations = "./migrations" is not decoration, and the ./ matters. The path is resolved against the crate root, but a bare directory name is rejected at compile time:

error: paths relative to the current file's directory are not currently supported
   --> src/main.rs:647:31
    |
647 |     #[sqlx::test(migrations = "migrations")]
    |                               ^^^^^^^^^^^^

error: could not compile `shortlink` (bin "shortlink" test) due to 1 previous error

The message blames the file’s directory; the actual rule is that the macro refuses a path with no parent segment, so write ./migrations. (The line number is ours - yours will point at your own file.) Omitting the attribute entirely would also work here: when the attribute is absent, the macro looks for a migrations/ directory next to Cargo.toml and uses it if it finds one. Writing the path explicitly is the safer habit - it keeps working when the directory moves, and it documents where the schema comes from.

Running them

DATABASE_URL=postgres://postgres:[email protected]:5432/shortlink cargo test

     Running unittests src/main.rs (target/debug/deps/shortlink-9a3ffdcad091c378)

running 12 tests
test tests::a_fresh_bucket_bursts_to_capacity_then_rejects ... ok
test tests::elapsed_time_refills_the_bucket ... ok
test tests::keys_do_not_share_a_bucket ... ok
test tests::encodes_the_boundaries ... ok
test tests::the_sequence_keeps_moving ... ok
test tests::codes_are_seven_characters_from_the_alphabet ... ok
test api_tests::an_invalid_url_is_a_422 ... ok
test api_tests::links_reject_a_missing_api_key ... ok
test api_tests::health_reports_ok ... ok
test api_tests::each_test_starts_from_an_empty_database ... ok
test api_tests::an_unknown_code_is_a_404 ... ok
test api_tests::a_created_link_resolves ... ok

test result: ok. 12 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.34s

Twelve tests: the six from earlier steps, which touch no database and run offline, and the six new ones, each against its own database - the whole suite finishes in about a third of a second. Two details in the output vary on your machine: the hash in the Running unittests path is per-build, and cargo’s test order is not stable.

That a whole suite against real Postgres runs this fast is the per-test database doing its job. A shared database would need cleanup between tests and a lock to keep the parallel threads apart; a database that exists only for one test needs neither. The cost is honest too: these tests need a live Postgres, so a CI run needs one as well - most setups give the job a Postgres service container and point DATABASE_URL at it, which is the same arrangement this page ran under.

Where this leaves the service

Everything built since step 2 is now pinned by tests: the routes answer, the auth layer rejects, the handlers hit real SQL against a real engine, and every test runs in a database that exists for it alone. git checkout api-ch14 in axum-sqlx-api/ gets you here; cargo test with the container up runs all twelve.

One step is left, and it is about leaving the machine: a multi-stage Dockerfile, configuration from the environment, and putting the service on the open internet. The track overview keeps the full list current as articles land.