Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 38 ยท Testing and Deployment

Integration Tests with a Test Database

Test Axum and SQLx code against a real database with sqlx::test, isolate every test, load fixtures, and swap repositories for fakes, with runnable Rust stand-ins.

Handler tests with a fake in memory are fast, but they cannot catch a wrong SQL query, a missing migration or a constraint you forgot. For that you need integration tests that talk to a real database. The skill is making them reliable: every test must start from a known state and must not disturb any other test.

This lesson covers the two common strategies (a fresh database per test, or a fake behind a trait) with the real SQLx code shown read-only and runnable stand-ins for the isolation ideas.

Fresh database per test: sqlx::test

SQLx ships a test macro that does the isolation work for you. It creates a brand-new database for each test, runs your migrations, hands you a pool and drops the database afterwards.

Rust
use sqlx::PgPool;

#[sqlx::test(migrations = "./migrations")]
async fn creates_and_reads_a_todo(pool: PgPool) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO todos (title) VALUES ($1)")
        .bind("test the pool")
        .execute(&pool)
        .await?;

    let count: i64 = sqlx::query_scalar("SELECT count(*) FROM todos")
        .fetch_one(&pool)
        .await?;

    assert_eq!(count, 1);
    Ok(())
}

Because each test owns its database, tests can run in parallel and never see each other's rows. The macro reads DATABASE_URL and needs a user that may create databases.

You can also load fixtures: SQL files that insert known rows before the test body runs.

Rust
#[sqlx::test(migrations = "./migrations", fixtures("users", "todos"))]
async fn lists_seeded_todos(pool: PgPool) -> sqlx::Result<()> {
    let n: i64 = sqlx::query_scalar("SELECT count(*) FROM todos WHERE done = false")
        .fetch_one(&pool)
        .await?;
    assert_eq!(n, 3);
    Ok(())
}

Fixtures live in fixtures/users.sql and fixtures/todos.sql next to your tests. Keep them small and boring: a fixture that tries to model the whole world makes every test depend on it.

Combining the pool with the router

Pass the pool into the same app() constructor production uses, then use oneshot as in the previous lesson:

Rust
#[sqlx::test(migrations = "./migrations")]
async fn post_todo_returns_created(pool: PgPool) {
    let app = crate::app(AppState { pool });

    let req = Request::builder()
        .method("POST")
        .uri("/todos")
        .header("content-type", "application/json")
        .body(Body::from(r#"{"title":"ship it"}"#))
        .unwrap();

    let res = app.oneshot(req).await.unwrap();
    assert_eq!(res.status(), StatusCode::CREATED);
}

This tests the handler, the extractors, the SQL and the schema together.

Run Postgres in Docker for tests (docker run -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:17) and set DATABASE_URL in your shell or .env. In CI, start the same container as a service.

Why isolation matters

flowchart TB
    subgraph Shared["Shared database (fragile)"]
      A1["Test A inserts row"] --> S[("one DB")]
      B1["Test B counts rows"] --> S
    end
    subgraph Isolated["One database per test (reliable)"]
      A2["Test A"] --> DA[("DB A")]
      B2["Test B"] --> DB[("DB B")]
    end

With a shared database, test B can see test A's row and fail depending on run order.

The cheapest isolation is a database per test. A second option is wrapping each test in a transaction that is rolled back at the end, which is fast but cannot test code that itself commits transactions.

Stand-in: a template database cloned per test

The runnable model below keeps a "template" with the schema and fixtures, and gives each test its own clone. Rows added in one test never appear in another.

Rust One fresh database per test (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with io::stdin().read_line()), it gets the next line from this box.

Stand-in: swapping the database for a fake

When a test does not care about SQL, depend on a trait instead of a pool. Production uses the SQLx implementation; tests use an in-memory one. Handlers receive Arc<dyn TodoRepo> in state.

Rust A repository trait with an in-memory fake (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with io::stdin().read_line()), it gets the next line from this box.

The trade-off: a fake is fast but can drift from the real database's behavior (unique constraints, ordering, null handling). Use fakes for handler logic and a real database for the repository layer itself.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does #[sqlx::test] give each test?

    That per-test database is what makes tests isolated and safe to run in parallel.
  2. 2. When is a fake repository the wrong tool?

    A fake cannot reveal mistakes in real SQL or the schema.
  3. 3. Why should fixtures be small?

    Tests should state what they need. A huge fixture hides that and makes changes risky.
  4. 4. Why wrap a test in a rolled-back transaction instead of a new database?

    Rollback isolation is cheap but breaks when the code under test commits.

Summary

  • Integration tests catch SQL, schema and constraint bugs that fakes cannot.
  • #[sqlx::test] creates, migrates and drops a database per test, so tests run in parallel safely.
  • Fixtures seed known rows; keep them small.
  • Build the real app() with the test pool and call it with oneshot.
  • Use a repository trait and an in-memory fake when you only test handler logic.
  • Isolation, not speed, is what makes a test suite trustworthy.

Next: Configuration, Docker and Deployment