Axum and SQLx Together for a Typed Database Layer Explained

Most Axum tutorials stop at "return some JSON". The first time the handler needs a row from Postgres, three questions arrive at once: where does the connection pool live, where does the SQL go, and what does a failed query turn into on the wire. This guide answers them with a small users API, and every block is the code that was compiled and run against a real PostgreSQL 17.11.

The stack is Rust 1.99 on edition 2024 with axum 0.8.9, sqlx 0.9.0 (features runtime-tokio, postgres, migrate and macros) and tokio 1.54. The service sits on the layout from the production Axum service and the pool settings are explained in SQLx pool sizing.

Start with the schema and the dependency list

The API manages users, and every signup writes an audit row, which gives us a reason to have a transaction. The schema is a migration file, so a fresh database and a production database are built by the same text:

CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);

CREATE TABLE audit_log (
    id      BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users (id),
    event   TEXT NOT NULL
);

The manifest is short. The macros feature is what provides query_as!, and it is off in no-default-features builds, so name it explicitly:

[package]
name = "usersapi"
version = "0.1.0"
edition = "2024"

[dependencies]
axum = "0.8.9"
serde = { version = "1.0.229", features = ["derive"] }
serde_json = "1.0.154"
sqlx = { version = "0.9.0", features = ["runtime-tokio", "postgres", "migrate", "macros"] }
thiserror = "2.0.21"
tokio = { version = "1.54.1", features = ["full"] }

[dev-dependencies]
http-body-util = "0.1.5"
tower = { version = "0.5.3", features = ["util"] }

For the compile-time checks you also need a database to ask. A throwaway container does it, with the connection string in a .env file that sqlx reads at build time:

docker run -d --name pg -p 55432:5432 -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=app postgres:17
echo 'DATABASE_URL=postgres://postgres:secret@localhost:55432/app' > .env
cargo install sqlx-cli --no-default-features --features postgres,rustls
sqlx migrate run

sqlx migrate run applies migrations/0001_users.sql to that database and prints Applied 1/migrate users. The macros need the tables to exist before the crate compiles, which is the one real cost of compile-time checking: your build depends on a database being reachable. The offline mode later in this guide removes that dependency for CI.

The pool belongs in state

AppState holds a PgPool. The pool is an Arc around the real connection set, so Axum cloning the state for every request copies a pointer, never a connection:

pub mod db;
pub mod error;

use axum::{
    Json, Router,
    extract::{Path, Query, State},
    http::StatusCode,
    routing::get,
};
use error::AppError;
use serde::Deserialize;
use sqlx::PgPool;

#[derive(Clone)]
pub struct AppState {
    pub pool: PgPool,
}

#[derive(Deserialize)]
struct NewUser {
    email: String,
    name: String,
}

#[derive(Deserialize)]
struct ListParams {
    limit: Option<i64>,
    name: Option<String>,
    domain: Option<String>,
}

async fn show(State(s): State<AppState>, Path(id): Path<i64>) -> Result<Json<db::User>, AppError> {
    Ok(Json(db::get_user(&s.pool, id).await?))
}

async fn index(
    State(s): State<AppState>,
    Query(p): Query<ListParams>,
) -> Result<Json<Vec<db::User>>, AppError> {
    if p.name.is_some() || p.domain.is_some() {
        let found = db::search_users(&s.pool, p.name.as_deref(), p.domain.as_deref()).await?;
        return Ok(Json(found));
    }
    let limit = p.limit.unwrap_or(20).clamp(1, 100);
    Ok(Json(db::list_users(&s.pool, limit).await?))
}

async fn create(
    State(s): State<AppState>,
    Json(input): Json<NewUser>,
) -> Result<(StatusCode, Json<db::User>), AppError> {
    let user = db::create_user(&s.pool, &input.email, &input.name).await?;
    Ok((StatusCode::CREATED, Json(user)))
}

pub fn app(pool: PgPool) -> Router {
    Router::new()
        .route("/users", get(index).post(create))
        .route("/users/{id}", get(show))
        .with_state(AppState { pool })
}

Each handler is three lines because everything interesting lives elsewhere. show extracts the path id and the state, calls db::get_user and wraps the result in Json. The ? converts sqlx::Error into AppError through a From impl you will see in a moment, so a missing row becomes a 404 without the handler mentioning it. index clamps the limit query parameter to 1 through 100 before it reaches SQL; never pass an unbounded client number straight into LIMIT, because ?limit=100000000 is an easy way to make a service allocate its way to the memory limit.

main builds the pool with explicit settings and runs migrations before it starts listening:

use sqlx::postgres::PgPoolOptions;
use std::time::Duration;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let url = std::env::var("DATABASE_URL")?;
    let pool = PgPoolOptions::new()
        .max_connections(10)
        .acquire_timeout(Duration::from_secs(3))
        .connect(&url)
        .await?;

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

    let listener = tokio::net::TcpListener::bind("127.0.0.1:3000").await?;
    axum::serve(listener, usersapi::app(pool)).await?;
    Ok(())
}

Two numbers matter. max_connections(10) caps how many queries run at once from this process; Postgres has its own max_connections (100 by default) shared by every client of the database, so ten replicas of this service with a pool of ten each already consume all of it. acquire_timeout(3s) is the setting that turns pool exhaustion into an error instead of a hang. With the default of thirty seconds a saturated pool makes every request wait half a minute, which a client experiences as an outage. Three seconds fails fast and gives your logs a timeout to alert on. sqlx::migrate!() embeds the migrations directory in the binary at compile time, so the deployed artifact carries its own schema.

SQL goes in one module

The db module is where the compile-time checking pays for itself:

use serde::Serialize;
use sqlx::PgPool;

#[derive(Debug, Serialize, sqlx::FromRow)]
pub struct User {
    pub id: i64,
    pub email: String,
    pub name: String,
}

pub async fn get_user(pool: &PgPool, id: i64) -> Result<User, sqlx::Error> {
    sqlx::query_as!(User, "SELECT id, email, name FROM users WHERE id = $1", id)
        .fetch_one(pool)
        .await
}

pub async fn list_users(pool: &PgPool, limit: i64) -> Result<Vec<User>, sqlx::Error> {
    sqlx::query_as!(
        User,
        "SELECT id, email, name FROM users ORDER BY id LIMIT $1",
        limit
    )
    .fetch_all(pool)
    .await
}

/// Two statements that must succeed or fail together.
pub async fn create_user(pool: &PgPool, email: &str, name: &str) -> Result<User, sqlx::Error> {
    let mut tx = pool.begin().await?;

    let user = sqlx::query_as!(
        User,
        "INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id, email, name",
        email,
        name
    )
    .fetch_one(&mut *tx)
    .await?;

    sqlx::query!(
        "INSERT INTO audit_log (user_id, event) VALUES ($1, 'created')",
        user.id
    )
    .execute(&mut *tx)
    .await?;

    tx.commit().await?;
    Ok(user)
}

/// Filters are optional, so the SQL is assembled at runtime and no macro can check it.
pub async fn search_users(
    pool: &PgPool,
    name_contains: Option<&str>,
    email_domain: Option<&str>,
) -> Result<Vec<User>, sqlx::Error> {
    let mut query = sqlx::QueryBuilder::new("SELECT id, email, name FROM users WHERE true");

    if let Some(term) = name_contains {
        query
            .push(" AND name ILIKE ")
            .push_bind(format!("%{term}%"));
    }
    if let Some(domain) = email_domain {
        query
            .push(" AND email LIKE ")
            .push_bind(format!("%@{domain}"));
    }
    query.push(" ORDER BY id LIMIT 50");

    query.build_query_as::<User>().fetch_all(pool).await
}

query_as!(User, "SELECT ...", id) is a macro, not a function. At build time it connects to the database named by DATABASE_URL, asks Postgres to describe the statement, and generates code that binds id as the parameter and builds a User from the returned columns. Because the description comes from the server, three mistakes are impossible to ship.

A misspelled column fails the build. Change email to emial in get_user and the compiler reports what Postgres said:

error: error returned from database: column "emial" does not exist at line 3723
  --> src/db.rs:12:5
   |
12 |     sqlx::query_as!(User, "SELECT id, emial, name FROM users WHERE id = $1", id)
   |     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `usersapi` (lib) due to 1 previous error

A mismatched Rust type fails the build too. The column id is BIGSERIAL, which is i64. Declare the field as i32 and the macro refuses, in terms that read like a Rust error rather than a database one:

error[E0277]: the trait bound `i32: From<i64>` is not satisfied
  --> src/db.rs:12:5
   |
12 |     sqlx::query_as!(User, "SELECT id, email, name FROM users WHERE id = $1", id)
   |     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `From<i64>` is not implemented for `i32`
   |
   = help: `i32` implements trait `From<T>`:

And nullability is checked: a nullable column maps to Option<T> and a NOT NULL one to T, so the unwrap you would otherwise write to silence the compiler has nowhere to hide. The price is that the SQL is a string literal; the macros cannot see through a format!. For queries assembled from optional filters, use the runtime query_as function or the query builder, and test those more heavily, because nothing checks them for you.

create_user is the transaction. pool.begin() checks out one connection and starts BEGIN; every statement then runs on &mut *tx so it uses that same connection; commit() ends it. If any statement returns an error, the ? leaves the function, the Transaction is dropped without a commit and SQLx rolls it back. That is the whole contract: there is no explicit rollback call to forget. Transactions in SQLx covers savepoints and isolation levels.

Turn database errors into responses

The handler returns AppError, and the conversion from sqlx::Error is the single place that knows what a failed query means over HTTP:

use axum::{
    Json,
    http::StatusCode,
    response::{IntoResponse, Response},
};
use serde_json::json;

#[derive(Debug, thiserror::Error)]
pub enum AppError {
    #[error("not found")]
    NotFound,
    #[error("that email is already registered")]
    Conflict,
    #[error("internal error")]
    Database(#[source] sqlx::Error),
}

impl From<sqlx::Error> for AppError {
    fn from(err: sqlx::Error) -> Self {
        match &err {
            sqlx::Error::RowNotFound => AppError::NotFound,
            sqlx::Error::Database(db) if db.is_unique_violation() => AppError::Conflict,
            _ => AppError::Database(err),
        }
    }
}

impl IntoResponse for AppError {
    fn into_response(self) -> Response {
        let status = match &self {
            AppError::NotFound => StatusCode::NOT_FOUND,
            AppError::Conflict => StatusCode::CONFLICT,
            AppError::Database(err) => {
                // The client gets "internal error"; the operator gets the cause.
                eprintln!("database error: {err}");
                StatusCode::INTERNAL_SERVER_ERROR
            }
        };
        (status, Json(json!({ "error": self.to_string() }))).into_response()
    }
}

RowNotFound is what fetch_one returns for zero rows, and it means "404", not "the database is broken". A unique violation is Postgres error code 23505; db.is_unique_violation() asks the driver for it without you matching on a string, and it means the client tried to register an email that exists, so 409. You can see the exact text the server produces for it:

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

Everything else is a real fault, the database being down or a bug in the SQL. The client gets internal error and nothing more, while the operator gets the underlying error on stderr; swap eprintln! for tracing::error! once you have the logging setup. Never put err.to_string() into the response body. A Postgres message can contain a table name, a constraint name and in some cases the value that violated it.

One decision here is easy to miss: unique violations are only 409 for this table. A violation on some other constraint, such as a foreign key, has a different code and falls into the 500 arm, which is correct. If you add a second unique column and want different messages, inspect db.constraint() to tell them apart.

Test against real Postgres, one database per test

A mocked database tests your mock. #[sqlx::test] instead creates a brand new database for each test function, applies everything in ./migrations, hands you a PgPool for it and drops the database afterwards. Tests can run in parallel and cannot see each other's rows:

use axum::{
    body::Body,
    http::{Request, StatusCode},
};
use http_body_util::BodyExt;
use sqlx::{PgPool, postgres::PgPoolOptions};
use std::time::Duration;
use tower::ServiceExt;

fn post_user(email: &str) -> Request<Body> {
    Request::post("/users")
        .header("content-type", "application/json")
        .body(Body::from(format!(r#"{{"email":"{email}","name":"Ada"}}"#)))
        .unwrap()
}

async fn text(res: axum::response::Response) -> String {
    String::from_utf8(res.into_body().collect().await.unwrap().to_bytes().to_vec()).unwrap()
}

// `#[sqlx::test]` creates a fresh database, runs ./migrations, and drops it afterwards.
#[sqlx::test]
async fn create_then_get(pool: PgPool) {
    let app = usersapi::app(pool);

    let res = app
        .clone()
        .oneshot(post_user("[email protected]"))
        .await
        .unwrap();
    assert_eq!(res.status(), StatusCode::CREATED);
    assert_eq!(
        text(res).await,
        r#"{"id":1,"email":"[email protected]","name":"Ada"}"#
    );

    let res = app
        .oneshot(Request::get("/users/1").body(Body::empty()).unwrap())
        .await
        .unwrap();
    assert_eq!(res.status(), StatusCode::OK);
}

#[sqlx::test]
async fn duplicate_email_is_409_and_leaves_no_audit_row(pool: PgPool) {
    let app = usersapi::app(pool.clone());

    app.clone()
        .oneshot(post_user("[email protected]"))
        .await
        .unwrap();
    let res = app.oneshot(post_user("[email protected]")).await.unwrap();
    assert_eq!(res.status(), StatusCode::CONFLICT);
    assert_eq!(
        text(res).await,
        r#"{"error":"that email is already registered"}"#
    );

    let rows: i64 = sqlx::query_scalar("SELECT count(*) FROM audit_log")
        .fetch_one(&pool)
        .await
        .unwrap();
    assert_eq!(rows, 1, "only the first signup is audited");
}

#[sqlx::test]
async fn unknown_user_is_404(pool: PgPool) {
    let res = usersapi::app(pool)
        .oneshot(Request::get("/users/99").body(Body::empty()).unwrap())
        .await
        .unwrap();
    assert_eq!(res.status(), StatusCode::NOT_FOUND);
}

#[sqlx::test]
async fn filters_combine(pool: PgPool) {
    let app = usersapi::app(pool);
    for email in ["[email protected]", "[email protected]", "[email protected]"] {
        app.clone().oneshot(post_user(email)).await.unwrap();
    }

    let res = app
        .oneshot(
            Request::get("/users?domain=other.test")
                .body(Body::empty())
                .unwrap(),
        )
        .await
        .unwrap();
    assert_eq!(
        text(res).await,
        r#"[{"id":3,"email":"[email protected]","name":"Ada"}]"#
    );
}

#[sqlx::test]
async fn an_exhausted_pool_is_a_500_not_a_hang(pool: PgPool) {
    // A pool of one connection, which the test then keeps for itself.
    let tiny = PgPoolOptions::new()
        .max_connections(1)
        .acquire_timeout(Duration::from_millis(200))
        .connect_with((*pool.connect_options()).clone())
        .await
        .unwrap();
    let _held = tiny.acquire().await.unwrap();

    let started = std::time::Instant::now();
    let res = usersapi::app(tiny)
        .oneshot(Request::get("/users/1").body(Body::empty()).unwrap())
        .await
        .unwrap();

    assert_eq!(res.status(), StatusCode::INTERNAL_SERVER_ERROR);
    assert_eq!(text(res).await, r#"{"error":"internal error"}"#);
    assert!(started.elapsed() < Duration::from_secs(2));
}

test result: ok. 0 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s
test result: ok. 0 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s
test unknown_user_is_404 ... ok
test duplicate_email_is_409_and_leaves_no_audit_row ... ok
test filters_combine ... ok
test create_then_get ... ok
test an_exhausted_pool_is_a_500_not_a_hang ... ok
test result: ok. 5 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.51s
test result: ok. 0 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

The first test posts a user, checks the 201 and the exact body, then fetches it. The id is 1 in the expected JSON because the database is new; with a shared database that assertion would be flaky. The second test is the one that guards behaviour: registering the same email twice must return 409 with the specific message, and the audit_log table must still hold only the first signup. The third pins the 404. Two more tests, covered below, check the dynamic filters and the exhausted pool.

All three drive the router through tower::ServiceExt::oneshot, as in testing Axum with oneshot, so no sockets are opened; the only network traffic is to the local Postgres. The whole file ran in about half a second, with a database created and dropped for each test. #[sqlx::test] reads DATABASE_URL and needs a role that may create databases, which the superuser in the container is. For the larger picture, including fixtures, see integration tests with a real database.

When the SQL cannot be a literal

Search pages have optional filters, and query_as! cannot express "add this clause only if the user filled the box". For that, db::search_users uses QueryBuilder. It builds the statement piece by piece and, crucially, binds every user value with push_bind instead of formatting it into the string. The text ILIKE and LIKE are pushed as SQL; the term is sent as a parameter, so a value like '; DROP TABLE users; -- is just a string to search for. The % wildcards are added to the *value* before binding, not to the SQL, which is the other way people accidentally open an injection hole.

The cost is that nothing checks this query at compile time. A typo in " AND emial LIKE " would compile and fail at runtime, so give such functions a test per branch. filters_combine in the file above posts three users and asks for domain=other.test; only the third matches, and the exact JSON is asserted. User derives sqlx::FromRow, which is what lets build_query_as::<User>() map the rows without a macro. Keep both styles in one module and the rule is simple: literal SQL gets a macro, assembled SQL gets a builder and a test.

One more caution on the LIKE filter: % and _ inside the user's term act as wildcards. For a search box that is usually acceptable, for an exact-match lookup it is a bug, and the fix is = rather than LIKE.

What happens when the pool runs dry

Everything above assumes a connection is available. The test an_exhausted_pool_is_a_500_not_a_hang creates the situation on purpose: a second pool with max_connections(1) and a 200 ms acquire_timeout, whose only connection the test borrows and never returns. Then it requests /users/1. The handler cannot get a connection, sqlx::Error::PoolTimedOut comes back after 200 ms, it falls through the From impl to AppError::Database, and the client receives 500 with {"error":"internal error"}. The final assertion checks the whole round trip finished in under two seconds.

This is the failure mode you will meet in production, and it rarely looks like a database problem. A slow downstream, a query without an index or a transaction that stays open too long means the ten connections are all busy, and the eleventh request waits. With a short acquire_timeout it fails quickly and loudly, which lets a load balancer retry elsewhere and lets you page someone. With a long one the whole service looks hung while its CPU sits idle. When you see PoolTimedOut in logs, do not raise max_connections first; find what is holding connections. SELECT pid, state, query FROM pg_stat_activity WHERE state = 'idle in transaction' lists the usual culprit, a transaction a handler began and never finished.

Look at the plan your layer produces

A typed layer makes queries easy to write, which makes slow ones easy to write. After loading 20,000 rows into users, two lookups ran under EXPLAIN (ANALYZE) in the same container:

-- lookup by email, 20,000 rows loaded
 Index Scan using users_email_key on users (actual rows=1 loops=1)
   Index Cond: (email = '[email protected]'::text)
 Execution Time: 0.068 ms

-- name ILIKE '%777%' on the same table
 Seq Scan on users (actual rows=38 loops=1)
   Filter: (name ~~* '%777%'::text)
   Rows Removed by Filter: 19962
 Execution Time: 6.876 ms

The first is the shape you want. The UNIQUE constraint on email created an index called users_email_key, so an equality lookup finds one row in 0.068 ms without anyone declaring an index. The second is what search_users generates when name is given: ILIKE '%777%' has a leading wildcard, no ordinary B-tree index can serve it, and Postgres reads every row, removing 19,962 of them to return 38. At 20,000 rows that costs 6.9 ms, which is invisible. At 20 million rows it is a full table scan on every keystroke of a search box. Nothing in Rust warns you, because the code is correct; the plan is where this shows up. The usual fixes are a trigram index (pg_trgm) for substring search or a prefix match (name ILIKE 'ada%') that an index on lower(name) can use.

The habit to build is small: when you add a query to db.rs, run it once under EXPLAIN (ANALYZE) against a table with realistic size and look at whether it says Seq Scan or Index Scan. The measurements above come from one small container on a laptop, so treat the milliseconds as a ratio, not a promise about your hardware.

Build without a database

CI should not need a Postgres just to compile. SQLx stores what the macros learned in a directory of JSON files, one per query, and uses them when SQLX_OFFLINE=true:

cargo sqlx prepare

query data written to .sqlx in the current directory; please check this into version control

That wrote four files into .sqlx/, one for each macro invocation in db.rs. Commit them. A build with SQLX_OFFLINE=true now succeeds with no database reachable, and CI can run cargo sqlx prepare --check to fail when someone changed a query without regenerating the cache. Forgetting that step is the commonest reason a pull request builds locally and fails in CI. The details are in SQLx offline mode.

Who runs the migrations

main calls sqlx::migrate!().run(&pool) at startup, which is convenient and has a sharp edge. With three replicas starting at once, all three try to migrate. SQLx takes an advisory lock in the database so only one applies the files and the others wait, which is safe, but a migration that takes ten minutes (an index on a large table) keeps every replica from starting for ten minutes, and a rolling deploy then looks like an outage. The other approach is to run sqlx migrate run as a separate release step before the new version rolls out, and make each migration backwards compatible with the old binary, so adding a nullable column first and dropping the old one in a later release. Small services should migrate at startup and accept the edge; once a table is large enough that a migration is an event, move it out of the request path.

Four mistakes this layout prevents

• Holding a connection while doing slow work. Every .await on the pool borrows a connection only for the duration of the query. A handler that begins a transaction and then calls an external API inside it holds that connection, and one slow upstream empties the pool. Do the HTTP call first, then open the transaction.
• Passing &PgPool where a transaction is needed. db::create_user owns its transaction. A function that must join a caller's transaction should instead accept &mut PgConnection (pass &mut *tx), so the caller decides the boundary.
• Returning the database struct as the API contract. User derives Serialize here because the fields are the response. The first time a table gains a password_hash column, a struct that is both the row and the response leaks it. Give the response its own type as soon as the two differ.
• Letting the pool size drift. If a deploy raises the replica count, the sum of all pools can exceed Postgres' limit and new connections start failing at the worst moment. Put the pool size in configuration, and do the arithmetic when you scale.

Where this layout is the wrong tool

If the whole service is one table and five queries, the db module is ceremony: write the queries in the handlers until a second use appears. If your queries are built dynamically (search pages with ten optional filters), compile-time macros help less, and SQLx against Diesel and SeaORM is the comparison to read. And if you need to run against several databases at once, the macros bind to the single DATABASE_URL and you will use the runtime functions.

Next steps

Add pagination with a keyset rather than OFFSET using keyset pagination in SQLx, move the connection details out of .env and into real configuration, and run the pool size through the sizing guide. The same pool-in-state pattern for the other major framework is in Actix Web with SQLx.