Adding SQLx and connecting to Postgres

Step 5 of 15. Step 4 left every handler answering through one ApiResponse<T> — but every byte of data behind those answers is hardcoded, and /health says ok without checking anything. This step adds the database: SQLx goes into Cargo.toml, main builds one connection pool from DATABASE_URL before the server starts, an Extension layer hands the pool to the handlers, and /health takes on the job the contract gave it — a round trip to Postgres, not a string.

Start from where step 4 ended: the shortlink project with three routes answering through the envelope. git checkout api-ch04 in your clone of the examples repository puts that state on disk.

The dependency

Cargo.toml

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

cargo add sqlx --features runtime-tokio,tls-rustls,postgres,macros writes the same line — it pins the exact version, 0.9.0, which the range above accepts. Four features, each earning its place:

• runtime-tokio — SQLx is runtime-agnostic: connections are driven by whichever async runtime you compile in, and this track picked tokio in step 2. Forget this feature and everything still compiles; the failure waits for the first connect:

  thread 'main' (12760002) panicked at /Users/rango/.cargo/registry/src/index.crates.io-1949cf8c6b5b557f/sqlx-core-0.9.0/src/pool/inner.rs:57:24:
  one of the `runtime` features of SQLx must be enabled

  The thread id and the registry hash in the path are machine-specific; the message is the point.

• tls-rustls — TLS for connections that need it, implemented in Rust. SQLx insists you name one TLS backend; the alternative, tls-native-tls, delegates to the platform’s (OpenSSL on Linux, Secure Transport on macOS). The local Postgres below uses no TLS at all, and the backend still has to be named.

• postgres — the driver for one database. SQLx also speaks MySQL and SQLite; without this feature PgPool and PgPoolOptions do not exist.

• macros — compile-time checked queries, query! and friends. Nothing in this step uses it; step 7 does. It is inert until then and part of the track’s Cargo.toml from here on.

A Postgres to point at

docker run --name shortlink-pg -e POSTGRES_PASSWORD=dev -p 5432:5432 -d postgres:16
docker exec shortlink-pg psql -U postgres -c 'CREATE DATABASE shortlink;'

The first command starts Postgres 16 with password dev, mapped to port 5432 on your machine. The second creates the database the service connects to — the postgres image gives you a server, not your database. Skipping that second command is the first wrong DATABASE_URL you will write, and the panics below show exactly what it costs you.

The pool

Two lines in main before the router exists:

let url = std::env::var("DATABASE_URL").expect("DATABASE_URL is not set");
let pool = PgPoolOptions::new()
    .max_connections(5)
    .acquire_timeout(std::time::Duration::from_secs(3))
    .connect(&url)
    .await
    .expect("failed to connect to DATABASE_URL");

Why a pool and not a connection: axum runs handlers concurrently on the shared tokio runtime, and several in-flight requests each want a database session at the same time. Opening a TCP connection and running the Postgres handshake per request would put that cost on every call; PgPool keeps up to max_connections sessions open and lends them out. Five fits this service — Postgres’s own default limit is 100 sessions, and a second instance of the API would get its own pool.

acquire_timeout is how long a handler waits for a session before giving up. Three seconds is a deliberate choice — the default is 30, and the health check section below shows why 30 is the wrong number for this service.

connect(&url).await is eager: it opens a real connection now, so a wrong password kills the process at startup rather than on the first request. The .expect calls are the right failure mode for a service that cannot serve — a panic before the listener binds means the process exits with code 101 instead of pretending to be up. Here is what each one looks like, all real:

thread 'main' (12768723) panicked at src/main.rs:110:45:
DATABASE_URL is not set: NotPresent

thread 'main' (12768687) panicked at src/main.rs:116:10:
failed to connect to DATABASE_URL: Database(PgDatabaseError { severity: Fatal, code: "3D000", message: "database \"doesnotexist\" does not exist", detail: None, hint: None, position: None, where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("postinit.c"), line: Some(1021), routine: Some("InitPostgres") })

thread 'main' (12768704) panicked at src/main.rs:116:10:
failed to connect to DATABASE_URL: Database(PgDatabaseError { severity: Fatal, code: "28P01", message: "password authentication failed for user \"postgres\"", detail: None, hint: None, position: None, where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("auth.c"), line: Some(331), routine: Some("auth_failed") })

The line numbers refer to the main.rs shown below, so a reader following along sees the same ones; the thread ids are machine-specific. 3D000 is Postgres’s SQLSTATE code — the code field carries the server’s five-character error code, which is more stable than the message text — and 3D000 means invalid_catalog_name: the database in the URL does not exist. 28P01 is invalid_password. One more: stop the container entirely and start the service, and after the three-second acquire_timeout you get

thread 'main' (12767505) panicked at src/main.rs:116:10:
failed to connect to DATABASE_URL: PoolTimedOut

— the pool retried the dead connection for the whole window and then gave up.

Handing the pool to the handlers

The layer goes on after the routes:

let app = Router::new()
    .route("/health", get(health))
    .route("/links", get(list_links).post(create_link))
    .route("/{code}", get(resolve_link))
    .layer(Extension(pool));

Extension(pool) stores the pool in the request’s extension map, and every handler below the layer can pull it out by type: Extension(pool): Extension<PgPool> in the argument list. Axum also has .with_state for shared state; Extension is the lighter option, and one pool is all this service shares.

/health does its real job

async fn health(Extension(pool): Extension<PgPool>) -> Result<&'static str, StatusCode> {
    match sqlx::query_scalar::<_, i32>("SELECT 1").fetch_one(&pool).await {
        Ok(_) => Ok("ok"),
        Err(_) => Err(StatusCode::SERVICE_UNAVAILABLE),
    }
}

query_scalar runs a query and takes the first column of the first row — SELECT 1 comes back as an i32. This is the runtime-checked API: nothing about the query is examined until it executes. The compile-checked macros that would reject a misspelled column at build time are step 7.

The return type changed from &'static str to Result<&'static str, StatusCode>, because both sides implement IntoResponse: Ok keeps the ok body that step 4’s article justified, and the Err side answers 503 with an empty body. With the server running and the database up:

curl -si http://127.0.0.1:8000/health

HTTP/1.1 200 OK
content-type: text/plain; charset=utf-8
content-length: 2
date: Sun, 18 Oct 2026 04:31:00 GMT

ok

Now stop the container while the server keeps running:

docker stop shortlink-pg
time curl -si http://127.0.0.1:8000/health

HTTP/1.1 503 Service Unavailable
content-length: 0
date: Sun, 18 Oct 2026 04:27:55 GMT

real  0m3.044s

Fifty-oh-three, empty body, three seconds — and this is the part the acquire_timeout line buys. The first version of this endpoint, built without it, answered the same request after 30.04 seconds: the pool’s default acquire timeout is 30, and it spends the whole window retrying the dead connection before the handler’s Err branch runs. A load balancer with a shorter timeout marks the instance dead and replaces it before the health check can say anything. Three seconds is honest: unhealthy, in writing, fast.

Start the container again and the next /health answers 200 ok — no server restart needed, because the pool reconnects when the handler asks for a session:

HTTP/1.1 200 OK
content-type: text/plain; charset=utf-8
content-length: 2
date: Sun, 18 Oct 2026 04:27:58 GMT

ok

The whole main.rs now

src/main.rs

use axum::{
    Json, Router,
    extract::{Extension, Path, Query},
    http::StatusCode,
    response::{IntoResponse, Response},
    routing::get,
};
use serde::{Deserialize, Serialize};
use sqlx::{PgPool, postgres::PgPoolOptions};

#[derive(Deserialize)]
struct Pagination {
    page: Option<usize>,
    limit: Option<usize>,
}

#[derive(Deserialize)]
struct CreateLink {
    url: String,
}

#[derive(Serialize)]
struct Link {
    code: String,
    url: String,
}

#[derive(Serialize)]
struct LinkPage {
    page: usize,
    limit: usize,
    links: Vec<Link>,
}

#[derive(Serialize)]
struct ResolvedLink {
    code: String,
    target: Option<String>,
}

struct ApiResponse<T> {
    status: StatusCode,
    data: T,
}

impl<T: Serialize> ApiResponse<T> {
    fn ok(data: T) -> Self {
        Self {
            status: StatusCode::OK,
            data,
        }
    }

    fn with_status(status: StatusCode, data: T) -> Self {
        Self { status, data }
    }
}

impl<T: Serialize> IntoResponse for ApiResponse<T> {
    fn into_response(self) -> Response {
        (self.status, Json(self.data)).into_response()
    }
}

async fn health(Extension(pool): Extension<PgPool>) -> Result<&'static str, StatusCode> {
    match sqlx::query_scalar::<_, i32>("SELECT 1").fetch_one(&pool).await {
        Ok(_) => Ok("ok"),
        Err(_) => Err(StatusCode::SERVICE_UNAVAILABLE),
    }
}

async fn create_link(Json(input): Json<CreateLink>) -> ApiResponse<Link> {
    // Nothing is stored yet - step 8 adds the insert, step 9 the code
    // generation. The stub code keeps the response shape right.
    ApiResponse::with_status(
        StatusCode::CREATED,
        Link {
            code: "stub".to_string(),
            url: input.url,
        },
    )
}

async fn list_links(Query(p): Query<Pagination>) -> ApiResponse<LinkPage> {
    // Still hardcoded - step 8 replaces this with a real query against
    // the links table.
    let links = vec![Link {
        code: "9xK2mQz".to_string(),
        url: "https://example.com/very/long/path".to_string(),
    }];
    ApiResponse::ok(LinkPage {
        page: p.page.unwrap_or(1),
        limit: p.limit.unwrap_or(20),
        links,
    })
}

async fn resolve_link(Path(code): Path<String>) -> ApiResponse<ResolvedLink> {
    // Nothing is stored yet, so there is nothing to look the target up
    // in. The table arrives with step 6's migration, the real lookup
    // with step 8.
    ApiResponse::ok(ResolvedLink {
        code,
        target: None,
    })
}

#[tokio::main]
async fn main() {
    let url = std::env::var("DATABASE_URL").expect("DATABASE_URL is not set");
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .acquire_timeout(std::time::Duration::from_secs(3))
        .connect(&url)
        .await
        .expect("failed to connect to DATABASE_URL");

    let app = Router::new()
        .route("/health", get(health))
        .route("/links", get(list_links).post(create_link))
        .route("/{code}", get(resolve_link))
        .layer(Extension(pool));

    let listener = tokio::net::TcpListener::bind("0.0.0.0:8000").await.unwrap();
    axum::serve(listener, app).await.unwrap();
}

What did not change matters as much as what did. list_links and resolve_link still answer hardcoded data — the pool exists, but the links table they need does not, and inventing queries against a table that is not there yet would be a lie the compiler cannot catch. The envelope from step 4 is untouched; the pool slots in underneath it.

Where this leaves the service

The service now refuses to start without a database, and /health means something: curl answers ok only while Postgres answers SELECT 1, and 503 when it does not. To run it from scratch: the two docker commands above, then

export DATABASE_URL='postgres://postgres:dev@localhost:5432/shortlink'
cargo run

The end state is tagged api-ch05 in the examples repository — git checkout api-ch05 inside axum-sqlx-api/ puts your tree where this article left it.

Next: the first migration. A plain SQL file that creates the links table, run by sqlx migrate — and why the migration being SQL you can read is worth more than a DSL. The track overview keeps the full list current as articles land.