How to test against a real database

Short answer: replace #[tokio::test] with #[sqlx::test]. Before the test body runs, sqlx connects to the server DATABASE_URL points at, creates a database that exists for this one test, applies the migrations to it, and hands the body a pool connected to it. When the test ends, the database is dropped. No setup, no cleanup code — and because cargo runs tests in parallel threads, the per-test database is also what keeps two tests from trampling each other.

The pages so far talked to one database from one main function. Tests change the picture: there are several of them, cargo starts them together, and every one of them expects the database in a known state.

Two tests, one database

The obvious arrangement is one database that every test shares. It works while there is one test. The program below plays two tests against a shared database — the first creates a table, writes a row, and ends; the second starts and asserts the table is empty:

use sqlx::postgres::PgPoolOptions;
use sqlx::Row;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/postgres")
        .await
        .expect("failed to connect to Postgres");

    // "test 1" runs against the shared database and leaves its rows behind.
    sqlx::query("DROP TABLE IF EXISTS demo_items")
        .execute(&pool)
        .await
        .expect("drop failed");
    sqlx::query("CREATE TABLE demo_items (id INT PRIMARY KEY, name TEXT NOT NULL)")
        .execute(&pool)
        .await
        .expect("create failed");
    sqlx::query("INSERT INTO demo_items (id, name) VALUES (1, 'left behind')")
        .execute(&pool)
        .await
        .expect("insert failed");

    // "test 2" starts and asserts an empty table.
    let row = sqlx::query("SELECT COUNT(*) AS n FROM demo_items")
        .fetch_one(&pool)
        .await
        .expect("count failed");
    println!("test 2 expected 0 rows, found {}", row.get::<i64, _>("n"));

    // And cargo runs tests in parallel threads: two tests creating the same
    // table on the shared database race.
    sqlx::query("DROP TABLE IF EXISTS demo_items")
        .execute(&pool)
        .await
        .expect("drop failed");
    let mut handles = Vec::new();
    for _ in 0..2 {
        let pool = pool.clone(); // a PgPool is a cheap handle, like an Arc
        handles.push(tokio::spawn(async move {
            sqlx::query("CREATE TABLE demo_items (id INT PRIMARY KEY, name TEXT NOT NULL)")
                .execute(&pool)
                .await
        }));
    }
    let mut ok = 0;
    let mut err = String::new();
    for h in handles {
        match h.await.unwrap() {
            Ok(_) => ok += 1,
            Err(e) => err = e.to_string(),
        }
    }
    println!("{ok} of the 2 CREATE TABLEs succeeded; the other failed with:");
    println!("  {err}");
}

test 2 expected 0 rows, found 1
1 of the 2 CREATE TABLEs succeeded; the other failed with:
  error returned from database: relation "demo_items" already exists at line 1169

(The at line 1169 is a server-internal position marker; the number can differ.)

Two failure modes, one root. The first is state bleed: the second test inherited the first one’s rows, so its assumption about a clean table was wrong before it ran a single statement. The second is a race: cargo runs tests in parallel threads on one machine, so two tests that create the same table on the shared database race, and exactly one of them loses with already exists.

Cleanup between tests — a TRUNCATE after each one, or a drop-and-recreate of the schema — fixes the first mode. It does not fix the second without also serializing the tests, and it is one more thing to write and forget. The fix that needs no cleanup code is a fresh database per test, which is exactly what #[sqlx::test] automates.

The crate and the code under test

Start from a fresh crate — cargo new dishes — with two dependencies:

[dependencies]
sqlx = { version = "0.9", features = ["runtime-tokio", "tls-rustls", "postgres", "macros"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

The macros feature is the one the query! page added, and #[sqlx::test] comes with the same feature.

The code under test is the same code the program calls — nothing about it exists for the tests’ benefit:

use sqlx::postgres::PgPoolOptions;
use sqlx::{PgPool, Row};

pub async fn insert_dish(pool: &PgPool, name: &str, price_cents: i32) -> Result<(), sqlx::Error> {
    sqlx::query("INSERT INTO dishes (name, price_cents) VALUES ($1, $2)")
        .bind(name)
        .bind(price_cents)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn count_dishes(pool: &PgPool) -> Result<i64, sqlx::Error> {
    let row = sqlx::query("SELECT COUNT(*) AS n FROM dishes")
        .fetch_one(pool)
        .await?;
    Ok(row.get("n"))
}

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/postgres")
        .await
        .expect("failed to connect to Postgres");
    let name: String = sqlx::query_scalar("SELECT current_database()")
        .fetch_one(&pool)
        .await
        .expect("query failed");
    println!("connected to {name}");
}

connected to postgres

The tests

The tests live in the same file, in a #[cfg(test)] module:

#[cfg(test)]
mod tests {
    use super::*;

    // No migrations attribute: when it is absent, the macro looks for a
    // migrations/ directory next to Cargo.toml and applies it if it exists.
    #[sqlx::test]
    async fn the_bare_attribute_applies_migrations_too(pool: PgPool) {
        assert_eq!(count_dishes(&pool).await.unwrap(), 0);
    }

    #[sqlx::test(migrations = "./migrations")]
    async fn a_dish_survives_a_round_trip(pool: PgPool) {
        insert_dish(&pool, "ramen", 1450).await.unwrap();
        assert_eq!(count_dishes(&pool).await.unwrap(), 1);
    }

    // The isolation guarantee, stated as a test: the database is fresh, so a
    // table that was never written to is empty — and if the tests ever start
    // sharing a database, this assertion fails on the second one that runs.
    #[sqlx::test(migrations = "./migrations")]
    async fn each_test_starts_from_an_empty_database(pool: PgPool) {
        assert_eq!(count_dishes(&pool).await.unwrap(), 0);
    }

    #[sqlx::test(migrations = "./migrations")]
    async fn the_database_is_private_to_this_test(pool: PgPool) {
        let name: String = sqlx::query_scalar("SELECT current_database()")
            .fetch_one(&pool)
            .await
            .unwrap();
        println!("{name}");
    }
}

(The checker compiles each snippet as a standalone program, and there is no test harness to receive this one, so the block is not verified in isolation — the cargo test output below is what compiling and running it produces.)

The signature is the whole contract: the body takes a pool: PgPool argument, and the macro fills it in. The schema comes from the migrations directory — the same plain-SQL files the migrations page manages. Create it next to Cargo.toml with one file:

CREATE TABLE dishes (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    price_cents INT NOT NULL
);

(The timestamp prefix is the migration’s version; yours will differ.)

What one test gets

DATABASE_URL=postgres://postgres:dev@localhost:5432/postgres cargo test

     Running unittests src/main.rs (target/debug/deps/dishes-e98115ef1c07eb21)

running 4 tests
test tests::the_database_is_private_to_this_test ... ok
test tests::the_bare_attribute_applies_migrations_too ... ok
test tests::each_test_starts_from_an_empty_database ... ok
test tests::a_dish_survives_a_round_trip ... ok

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

(The path in the Running unittests line, its hash, and the test order are not stable — they vary per build and per machine.)

Four tests, four databases, a third of a second. What one test actually received is worth seeing — run the last one alone with --nocapture so its println! gets through:

DATABASE_URL=postgres://postgres:dev@localhost:5432/postgres \
  cargo test the_database_is_private -- --nocapture

running 1 test
_sqlx_test_z__mNaWx_m4npKg3CFIFadjuQozUWpDfRbu6sxUdfI_RwJnb2hEy
test tests::the_database_is_private_to_this_test ... ok

test result: ok. 1 passed; 0 failed; 0 ignored; 0 measured; 3 filtered out; finished in 0.21s

The pool was connected to a database whose name starts with _sqlx_test_ and ends in random characters — yours will differ. It did not exist before the test and does not exist after: in the green run above, every database was dropped when its test ended. A test that panics is the exception — when an earlier run of this suite had one failing test, that test’s database was still on the server afterwards. If one turns up, DROP DATABASE it by hand; sqlx does not reuse names, so it never gets in the way of the next run.

Two details about the migrations attribute. The bare #[sqlx::test] on the first test is not an empty-database mode: when the attribute carries no path, the macro looks for migrations/ next to Cargo.toml and applies it if the directory exists. Naming the path explicitly is the clearer habit — and the path must be ./migrations, not migrations: the macro rejects a path with no parent segment. Why, and what the same macro looks like wired into a running web service, is in the axum track’s integration-test step.

That a whole suite against real Postgres finishes in a fraction of a second is the per-test database doing its job: no shared state, no cleanup, no locks. The cost is the server itself — these tests need a live Postgres, so a CI run needs one as well. The usual arrangement is a Postgres service container with DATABASE_URL pointed at it, which is the same URL shape this page used.