Per Test Databases in Rust With SQLx Test Attribute

The standard database test problem is isolation: tests that share tables interfere, tests that clean up after themselves forget to, and tests that must run serially are slow. SQLx's test attribute removes the problem by giving each test its own database. This post shows how it works and the knobs you need. Versions: Rust 1.90 with edition 2024, SQLx 0.8, PostgreSQL 17.

The basic form

[dev-dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "migrate", "macros"] }
tokio = { version = "1.47", features = ["full"] }

use sqlx::PgPool;

#[sqlx::test]
async fn inserts_a_user(pool: PgPool) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO users (email) VALUES ($1)")
        .bind("[email protected]")
        .execute(&pool)
        .await?;

    let n: i64 = sqlx::query_scalar("SELECT count(*) FROM users").fetch_one(&pool).await?;
    assert_eq!(n, 1);
    Ok(())
}

export DATABASE_URL=postgres://postgres:postgres@localhost:5432/app_dev
cargo test

Before the test body runs, SQLx connects to DATABASE_URL, creates a database with a generated name in a bookkeeping schema, applies every migration in ./migrations (the path sqlx migrate add uses), and passes a pool connected to that database. After the test it drops the database. The DATABASE_URL database itself is only used to issue CREATE DATABASE; your tests never touch its tables.

Fixtures

fixtures/users.sql
fixtures/posts.sql

-- fixtures/users.sql
INSERT INTO users (id, email) VALUES (1, '[email protected]'), (2, '[email protected]');

#[sqlx::test(fixtures("users", "posts"))]
async fn lists_posts_by_author(pool: PgPool) -> sqlx::Result<()> {
    let n: i64 = sqlx::query_scalar("SELECT count(*) FROM posts WHERE author_id = 1")
        .fetch_one(&pool)
        .await?;
    assert!(n > 0);
    Ok(())
}

Fixtures are plain SQL scripts looked up in fixtures/ next to the test file's crate root (or a path you set with path = "..." and scripts(...)), executed in the order given, inside the new database after migrations. Order matters when foreign keys connect them. Keep fixtures small and specific; a giant shared fixture recreates the coupling that per-test databases avoid.

Options

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

// custom migrations location
#[sqlx::test(migrations = "./db/migrations")]
async fn custom_path(pool: PgPool) {}

// no automatic migrations (the test manages its own schema)
#[sqlx::test(migrations = false)]
async fn bare(pool: PgPool) {}

// full control over the pool
#[sqlx::test]
async fn tuned(pool_opts: PgPoolOptions, conn_opts: PgConnectOptions) -> sqlx::Result<()> {
    let pool = pool_opts.max_connections(2).connect_with(conn_opts).await?;
    sqlx::query("SELECT 1").execute(&pool).await?;
    Ok(())
}

You can inject PgPool, or PgPoolOptions and PgConnectOptions to build your own. Use the options form to test code that needs a specific pool size or connection settings, or to create several pools in one test.

Testing an Axum app

use axum::{body::Body, http::Request};
use tower::ServiceExt;

#[sqlx::test(fixtures("users"))]
async fn get_user(pool: PgPool) {
    let app = my_app::router(pool);
    let res = app
        .oneshot(Request::get("/users/1").body(Body::empty()).unwrap())
        .await
        .unwrap();
    assert_eq!(res.status(), 200);
}

Construct the router with the injected pool and drive it with oneshot, so the whole stack runs against a real, private database (Axum integration tests).

Troubleshooting

• "DATABASE_URL must be set": export it or add it to .env.
• "permission denied to create database": grant CREATEDB to the role.
• Too many connections: many parallel tests each open a pool; lower --test-threads or raise max_connections on the server, and keep per-test pool sizes small.
• Leftover databases: after a crashed run, drop the _sqlx_test databases manually or via sqlx database drop on the test server; SQLx also cleans stale ones on the next run.
• Migrations edited after being run: not an issue here, since each test starts from scratch, which is a benefit over a shared dev database.

If you prefer an ephemeral server too, point DATABASE_URL at a container started by your test setup (test containers). For offline compile-time checking of queries, see SQLx offline mode.