Rust SQLx Postgres: Pools, Checked Queries, Safe Deploys

You've got an Axum service that needs Postgres, and you don't want an ORM fighting you over every join. SQLx hits a sweet spot here: you write plain SQL, the macros check it at compile time, and Tokio runs it all without blocking threads.

But the happy path hides real production questions. How many pool connections per pod? Where does DATABASE_URL come from in CI? What happens when a migration runs twice, or a transaction spans two handlers? You'll answer all of these before your first deploy if you set things up deliberately.

This guide walks the full loop the way senior Rust teams run it. You'll size PgPoolOptions with intent, choose between query! and query_as!, version your schema with sqlx-cli, wrap multi-table writes in transactions, and share one pool through Axum state cleanly.

You don't need Diesel experience or ORM habits here. If you can write Postgres SQL and read Rust async code, you've got enough. We'll explain each SQLx concept at the point you actually need it.

By the end you'll have a mental model you can defend in review: why checked queries beat stringly SQL, why pool math matters more than query style, and why migrations plus transactions are the real reliability story underneath the macros.

SQLx vs Diesel vs SeaORM: Choosing Your Postgres Stack

Pick SQLx when your team thinks in SQL and wants Postgres features on day one. You write the exact statement you would paste into psql — window functions, lateral joins, upserts with on conflict, advisory locks — and the macros prove it valid at build time. There is no DSL to learn, no schema module to regenerate before a new column is usable, and no translation layer standing between explain analyze output and the string in your codebase. Teams with a strong SQL culture ship faster here because every trick they know from years of Postgres transfers directly.

Pick Diesel when you want the compiler to own query construction end to end. Its DSL turns table definitions into Rust types, so a renamed column breaks every query that touches it at build time with a precise error. That guarantee costs fluency: engineers must learn the DSL, run the schema generator after each migration, and express advanced Postgres through escape hatches when the builder has no vocabulary for it. Shops with strict review cultures and less SQL depth often prefer this trade because the compiler catches what code review might miss.

SeaORM sits between these poles and deserves a clear-eyed look. It gives you entities, relations, and an async API that feels familiar if you arrive from Rails or Django. Code generation keeps entities in sync, and the query builder covers the common joins and filters without raw strings. The price shows up at the edges: unusual Postgres operators, partial indexes, or intricate CTEs push you back into raw SQL fragments that lose the safety story, and now you maintain two styles in one codebase.

Raw tokio-postgres is the fourth option nobody should start with but everyone should understand. It is fast, explicit, and unopinionated — you manage clients, statements, and transactions by hand. That control helps driver authors and proxy builders, but application teams pay for it in boilerplate: pooling, statement caching, type mapping, and migration discipline all become your code. Unless you are building infrastructure, the productivity gap against SQLx is hard to justify.

The decision that ages best is usually SQLx plus strong conventions. Keep SQL in versioned files or clearly named query functions, require explain output for new slow-path queries in review, and let the macros enforce the contract. You keep full Postgres expressiveness, you gain compile-time proof, and you skip the framework tax. If your team hates SQL or your domain is CRUD-heavy with simple relations, Diesel or SeaORM may genuinely fit better — choose the tool your on-call engineers will enjoy debugging at 3 AM.

# Cargo.toml — pick ONE stack per service
[dependencies]
# Option A: SQLx, query-first with Postgres
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "macros", "migrate", "uuid", "chrono"] }
tokio = { version = "1", features = ["full"] }

# Option B: Diesel, DSL-first with Postgres
# diesel = { version = "2.2", features = ["postgres"] }
# diesel_migrations = "2.2"

# What SQLx covers without new syntax:
# window functions, CTEs, lateral joins, upserts,
# advisory locks, listen/notify, custom enums

DATABASE_URL and .env Wiring That Survives Every Environment

DATABASE_URL is the single string every SQLx workflow keys off, so treat its handling as a design decision, not an afterthought. Locally it lives in a .env file at the repo root that dotenvy loads on startup. That file holds a developer-scoped Postgres URL pointing at localhost, with credentials that work nowhere else. It exists so cargo run and the query macros Just Work on a fresh clone after migrations run, with zero chat messages asking for connection strings.

The macros read the same variable at compile time, which surprises every newcomer once. When you invoke query!, the compiler connects to the database named by DATABASE_URL and asks Postgres itself to validate your SQL. That means builds need a reachable database with the current schema, or an offline cache covered later in this guide. Point that build-time URL at a throwaway local database with migrations applied — never at shared staging, and never at production. A build should not be able to harm real data, full stop.

In staging and production, .env must not exist and dotenvy::dotenv().ok() must be a harmless no-op. The platform injects DATABASE_URL from its secret store — Kubernetes secrets, ECS task secrets, Vault agent templates — so the value never touches disk in the image or the repo. Read it once at startup, validate the scheme and host, and fail fast with a message naming the missing variable before binding any port. A service that boots deaf without a database lies to the load balancer.

Log discipline around the URL pays for itself during incidents. Log the host, port, and database name at startup so on-call can tell which cluster a pod attached to, but never log the password or full URL. A common pattern is parsing with the url crate and printing everything except credentials. When a deploy lands on the wrong cluster, that one redacted log line saves twenty minutes of guessing.

Test the contract the way you test code. A startup integration test that connects, runs select 1, and checks the migrations table version catches rotated passwords, wrong hosts, and stale secrets before traffic arrives. Pair it with distinct URLs per environment — shop_dev, shop_test, shop_staging — so a copy-paste accident cannot point tests at production. Connection strings are credentials; give them the same rotation story and the same respect you give API keys.

# .env — local development only, never commit real secrets
DATABASE_URL=postgres://app_writer:dev_only_pw@localhost:5432/shop_dev
SQLX_OFFLINE=false
RUST_LOG=info

# Load it once at startup (src/config.rs)
use std::env;

pub struct Config {
    pub database_url: String,
}

impl Config {
    pub fn from_env() -> anyhow::Result<Self> {
        dotenvy::dotenv().ok(); // harmless in prod where env is injected
        let database_url = env::var("DATABASE_URL")
            .map_err(|_| anyhow::anyhow!("DATABASE_URL must be set"))?;
        if !database_url.starts_with("postgres://") {
            anyhow::bail!("DATABASE_URL must use postgres:// scheme");
        }
        Ok(Self { database_url })
    }
}

// Verify before binding ports:
// psql "$DATABASE_URL" -c 'select version();'

PgPoolOptions Sizing That Survives Traffic Spikes

PgPoolOptions is where intent becomes capacity, and every method on it answers a distinct production question. max_connections caps how many Postgres backends one pod may hold at once — the single number that, multiplied by replica count, must fit under the database limit. min_connections keeps warm standbys open so the first request after idle does not pay TCP plus TLS plus authentication latency. These two bound the steady state; everything else bounds behavior at the edges.

acquire_timeout decides how your service fails when the pool is exhausted. The default of 30 seconds parks each request for half a minute before returning PoolTimedOut, which converts a brief spike into a hallway full of queued work holding threads, memory, and often row locks. Three seconds is a saner production posture for request-driven services: shed fast, return 503 with Retry-After, and let the caller or load balancer retry elsewhere. Batch workers with no user waiting can afford longer, but they should say so explicitly with a comment.

idle_timeout and max_lifetime manage connection hygiene over hours, not seconds. idle_timeout closes connections that sit unused in the pool — ten minutes is a common starting point that reclaims sockets after traffic dips without churning during normal variance. max_lifetime caps total age regardless of use, defaulting to thirty minutes, which recycles connections before load balancers, NAT gateways, or cloud proxies silently drop them. If your platform kills idle TCP at 350 seconds, your idle_timeout must sit comfortably below that or you will serve stale sockets.

test_before_acquire adds a lightweight liveness probe before handing a pooled connection to your code. It costs a round trip on checkout, so ultra-hot paths sometimes disable it — but most services should keep it, because a dead pooled connection otherwise surfaces as a user-facing error on exactly the request that drew the short straw. Pair it with after_connect hooks that set statement_timeout or application_name per connection, so every backend is labeled and bounded from birth.

Sizing is arithmetic, not vibes. Start from Postgres max_connections, subtract headroom for migrations, background workers, and an admin psql session, then divide by max pod replicas. Eight pods against a 100-connection database with 10 reserved leaves about 11 per pod — set 8 and keep the margin. Load-test to confirm, watch pg_stat_activity during the test, and record pool.size plus num_idle in your metrics. When saturation hits, the fix order is: lower per-pod max first, add read replicas second, raise the database limit last.

use std::time::Duration;
use sqlx::postgres::{PgPool, PgPoolOptions};

pub async fn build_pool(database_url: &str) -> anyhow::Result<PgPool> {
    let pool = PgPoolOptions::new()
        .max_connections(8)    // per-pod cap: pods * 8 + headroom < pg max_connections
        .min_connections(2)    // warm standbys so first requests skip connect latency
        .acquire_timeout(Duration::from_secs(3)) // shed load fast, don't queue for 30s
        .idle_timeout(Duration::from_secs(10 * 60)) // reclaim idle sockets after 10 min
        .max_lifetime(Duration::from_secs(30 * 60)) // recycle before LB/NAT kills them
        .test_before_acquire(true) // cheap liveness check before handing out
        .connect(database_url)
        .await?;
    // Prove the pool is real before serving traffic.
    sqlx::query("SELECT 1").execute(&pool).await?;
    Ok(pool)
}

// Observe in production:
// pool.size() -> total connections, pool.num_idle() -> parked and ready
// SQL: select count(*) from pg_stat_activity where datname = current_database();

query! Compile-Time Checking and the Build-Time Contract

The query! macro is SQLx's headline trick: it sends your SQL to Postgres during compilation and refuses to build if the database objects. Column names, table names, parameter counts, and result types all get verified against the real schema, and the macro generates an anonymous record type with correctly typed fields. A typo in a column name becomes a compiler error with a caret, not a 500 in production. That shift — catching data-layer mistakes with cargo build instead of integration tests — is the whole economic argument for SQLx.

The mechanism is refreshingly literal. With the macros feature enabled, query! reads DATABASE_URL from the environment or .env at compile time, opens a connection, runs prepare against your statement, and records the described parameter and column types into the expansion. Postgres binds use $1, $2 numbering in SQL-text order, and each .bind() or inline argument must match positionally. Nullable columns surface as Option fields; adding a ! override like email! asserts non-null when you know more than the schema implies, and a : Type override forces custom decoding for newtypes and domain types.

The build-time database must mirror production schema, which makes migration discipline part of the build story. The standard loop is: edit a migration, run sqlx migrate run against the local dev database, then cargo build so macros check against the fresh schema. When the macro complains about a missing column right after you added it, the fix is almost always running migrations locally first — the compiler is correctly reporting that your dev database lags your SQL.

There are sharp edges worth learning once. Aggregates and certain expressions infer as nullable even when you know better, so count(*) often needs a ! or coalesce in SQL to keep the Rust type clean. Bool columns decode with quirks in some macro paths, so verify the inferred type the first time you select a flag. And query! returns an anonymous record, which is perfect for single-use projections but awkward when three handlers need the same shape — that duplication smell is the signal to graduate to query_as! with a named struct.

Treat macro errors as schema review, not noise. When CI fails on a query! expansion, read the message as Postgres speaking through the compiler: the table changed, the migration did not run, or the offline cache is stale. Keep the dev database rebuildable from migrations in one command, and macro failures stay five-minute fixes instead of day-long mysteries.

use sqlx::PgPool;

pub struct UserRow {
    pub id: i64,
    pub email: String,
}

// query! — anonymous record, types inferred from the live schema.
// Requires DATABASE_URL at build time OR a committed .sqlx cache.
// Postgres binds are $1, $2 in SQL-text order.
 pub async fn find_user(pool: &PgPool, user_id: i64) -> Result<Option<UserRow>, sqlx::Error> {
    let rec = sqlx::query!(
        r#"SELECT id, email FROM users WHERE id = $1"#,
        user_id
    )
    .fetch_optional(pool)
    .await?;
    Ok(rec.map(|r| UserRow { id: r.id, email: r.email }))
}

// Nullability override: tell the macro a column is never null.
// let rec = sqlx::query!(r#"SELECT id, email AS "email!" FROM users"#)
// Type override: force a custom decode.
// let rec = sqlx::query!(r#"SELECT id AS "id: uuid::Uuid" FROM users"#)

query_as! Typed Mappings Without an ORM Layer

query_as! takes everything query! proves and aims it at a struct you own. The macro still validates SQL against the live schema at compile time, but instead of an anonymous record it maps each column into the named field with the same name. That mapping is positional-forgiving but name-strict: column aliases must equal field names, unused columns are rejected, and missing fields fail the build. The result is a domain type that every handler can share with compiler-enforced consistency between SQL and Rust.

Nullability rules carry over with one addition that bites teams exactly once. If a column can be null, the corresponding field must be Option-wrapped or the macro refuses to expand. Left joins are the classic trigger: columns from the nullable side arrive as Option even when the underlying table marks them not null, because the join can produce nulls. The fix is modeling honestly — Option fields for maybe-absent data — or restructuring with inner joins and coalesce where the business logic guarantees presence.

The underscore override is the escape hatch for custom types. Selecting id as "id: _" tells the macro to infer the column type from the struct field instead of the schema description, which lets newtypes, custom enums, and domain wrappers decode through their own Type plus Decode impls. Runtime checking still verifies compatibility, so a genuine mismatch surfaces as a decode error rather than silent corruption. Use this sparingly and comment each use, because every override is a spot where you overruled the database's own description.

Choosing between the two macros is a duplication question. One-off admin projections and single-use aggregates belong in query! where the anonymous record keeps the shape local to the call site. Anything returned from more than one function, passed across module boundaries, or asserted in tests belongs in a named struct behind query_as!. When you catch yourself destructuring the same anonymous record in two places, promote it — the refactor is mechanical and the compiler guides it.

Keep the sibling runtime APIs in mind for dynamic SQL. sqlx::query and sqlx::query_as build statements at runtime with no compile-time validation, which is exactly what search filters with optional predicates need. The discipline is layering: checked macros for every static statement you can write literally, runtime builders only where the SQL shape genuinely varies, and tests covering each dynamic branch since the compiler cannot.

use sqlx::{FromRow, PgPool};
use uuid::Uuid;
use chrono::{DateTime, Utc};

#[derive(Debug, Clone, FromRow)]
pub struct Account {
    pub id: Uuid,
    pub email: String,
    pub display_name: Option<String>,
    pub created_at: DateTime<Utc>,
}

// query_as! — same compile-time checks, output mapped into YOUR struct.
// Column names must match field names; use AS aliases to align them.
 pub async fn get_account(pool: &PgPool, id: Uuid) -> Result<Account, sqlx::Error> {
    sqlx::query_as!(
        Account,
        r#"SELECT id, email, display_name, created_at FROM accounts WHERE id = $1"#,
        id
    )
    .fetch_one(pool)
    .await
}

// Runtime-checked sibling (no macros, no build-time DB):
// sqlx::query_as::<_, Account>("SELECT ...").bind(id).fetch_one(pool).await

Migrations with sqlx-cli: Run, Revert, and Recovery

Migrations are versioned SQL files that turn schema evolution into reviewable, replayable history, and sqlx-cli is the runner that applies them in order. Each migration gets a timestamped filename with up and down halves when created with -r, and the _sqlx_migrations table records which versions applied cleanly. sqlx migrate run compares that table against the migrations directory and applies only the pending set inside transactions where the database allows it. Re-running is safe by construction: applied versions are skipped, so ten restarts apply nothing new.

The file format rewards small, explicit steps. One migration does one thing — create a table, add a column with its index, backfill then add a constraint — so review can reason about locking and runtime. Postgres runs most DDL transactionally, which means a failed migration rolls back cleanly, but concurrent-index builds and a few lock-taking operations refuse to run inside a transaction block. Mark those correctly, run them during low-traffic windows, and never bundle a heavy backfill with a deploy that also ships code depending on the new column.

Reversible migrations earn their keep the first time a deploy goes sideways. The down file must genuinely undo the up file: drop what was added, restore what was renamed, and handle data written in between. Test the round trip locally with run then revert then run, because an untested down migration is fiction. For destructive changes like dropping a column, the safer production pattern is expand-migrate-contract across two deploys rather than trusting a down file to resurrect data.

Dirty state is the failure mode to rehearse before it happens. If a migration crashes mid-apply, its row stays marked unsuccessful and every later run refuses to proceed until a human resolves it. Diagnose with select version, success from _sqlx_migrations, fix or complete the half-applied change by hand, then continue from a single controlled host. The rule that prevents most dirty states is single-writer DDL: a dedicated migration job in your deploy pipeline, not every pod racing migrate-on-boot at scale.

Wire migrations into boot and CI with intent. Embedding via sqlx::migrate! and running on startup keeps single-binary deploys simple and works beautifully up to a handful of replicas. Past that, move to a one-shot migration job that completes before app pods roll, and keep the embedded runner as a no-op safety net. In CI, run migrations against a fresh database then build with the macros, so checked queries always validate against the schema the deploy will actually create.

# sqlx-cli — install once per machine or CI image
cargo install sqlx-cli --no-default-features --features native-tls,postgres

# Create versioned migrations (reversible with -r)
sqlx migrate add -r create_accounts
# -> migrations/20260926090000_create_accounts.up.sql
# -> migrations/20260926090000_create_accounts.down.sql

# Run / revert against DATABASE_URL
sqlx migrate run
sqlx migrate revert      # rolls back the latest reversible batch
sqlx migrate info        # shows applied vs pending versions

// Embed and run at boot (idempotent — safe across restarts)
use sqlx::PgPool;
 pub async fn run_migrations(pool: &PgPool) -> Result<(), sqlx::migrate::MigrateError> {
    sqlx::migrate!("./migrations").run(pool).await
}

-- migrations/20260926090000_create_accounts.up.sql
CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email CITEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Transactions with pool.begin, Commit, and Rollback Discipline

Transactions turn multi-statement business operations into atomic units, and SQLx models them as a borrowed handle with a single commit point. pool.begin checks out a connection and opens a transaction; every statement in the unit of work executes through &mut *tx so the database sees them as one scope; commit persists everything or, if any step fails, early return drops the handle and the database rolls everything back. The shape is deliberately narrow because narrow is auditable: begin at the top, one commit at the bottom, no branching around the commit.

Row locking inside the transaction is what makes check-then-act safe under concurrency. Selecting the inventory row with for update takes a row-level lock that serializes competing checkouts for the same SKU until the first transaction commits or rolls back. Without that clause, two requests can both read stock of one, both decide it suffices, and both insert — the classic oversell that no retry logic fixes after the fact. Keep the locked section short: read, validate, write, commit, with no network calls or file I/O inside the lock window.

Isolation defaults do most of what application teams need. Postgres read committed shows each statement the latest committed data, which pairs well with explicit row locks for inventory-style flows. When the operation spans predicates rather than single rows — seat maps, unique slot assignment — escalate deliberately with higher isolation or advisory locks, and document why. The mistake is reaching for serializable everywhere: it trades clear lock errors for surprising serialization failures that clients must retry.

Error paths deserve the same design attention as happy paths. Returning an error before commit must leave no partial writes, which drop-on-rollback guarantees — but only if every write actually went through the transaction handle. Audit for the silent splitter: a logging insert or audit write accidentally executed against the pool instead of the tx, which commits even when the business operation rolls back. Grep for execute(&pool inside transactional functions during review; each hit is a suspect.

Scope transactions to writes plus the reads those writes depend on. Moving a slow external API call inside the transaction holds locks and a pooled connection for the duration of someone else's latency budget, which is how one sluggish dependency exhausts your pool. Fetch external data first, then open the transaction, do the guarded writes, and commit. Short transactions are fast transactions, and fast transactions are the ones that survive Black Friday.

use sqlx::PgPool;

pub async fn place_order(pool: &PgPool, user_id: i64, sku: &str, qty: i32) -> anyhow::Result<i64> {
    // One transaction for the whole business operation.
    let mut tx = pool.begin().await?;
    // Optional: bound the whole unit of work server-side.
    sqlx::query("SET LOCAL statement_timeout = '5s'").execute(&mut *tx).await?;

    let stock: i32 = sqlx::query_scalar(
        "SELECT stock FROM inventory WHERE sku = $1 FOR UPDATE",
    )
    .bind(sku)
    .fetch_one(&mut *tx)
    .await?;
    if stock < qty {
        anyhow::bail!("insufficient stock for {sku}"); // drop -> rollback
    }
    let order_id: i64 = sqlx::query_scalar(
        "INSERT INTO orders (user_id, sku, qty) VALUES ($1, $2, $3) RETURNING id",
    )
    .bind(user_id)
    .bind(sku)
    .bind(qty)
    .fetch_one(&mut *tx)
    .await?;
    sqlx::query("UPDATE inventory SET stock = stock - $1 WHERE sku = $2")
        .bind(qty)
        .bind(sku)
        .execute(&mut *tx)
        .await?;
    tx.commit().await?; // single commit point; drop without this rolls back
    Ok(order_id)
}

FromRow Derives and Column Mapping Rules

FromRow is the bridge between relational rows and Rust structs, and its derive covers the vast majority of mappings with two rules that never bend. First, every selected column must correspond to a field by name, using AS aliases wherever the natural SQL name differs from the domain name. Second, nullability must be modeled honestly: a column that can arrive null needs an Option field, including not-null columns on the nullable side of a left join. Order is irrelevant; names and types are everything.

Type mapping follows the Postgres type table that SQLx documents per database module. Integers land in i16, i32, or i64 by width, numerics map to decimal or bigdecimal types, timestamptz becomes DateTime<Utc>, uuid columns become Uuid, and jsonb arrives as serde_json::Value or a deserialized struct through the json feature. Custom domain types work by implementing Type plus Decode and Encode, which sounds heavy until you do it once for a Money or Email newtype and then reuse it across every query for free.

The derive generates try_get calls per field, and reading one expansion with cargo expand pays off permanently. You see exactly why a field needs its bounds, how column-index lookup works, and where the error originates when a name drifts. Manual FromRow impls are rarely needed, but knowing the shape helps when you implement for PgRow directly to add validation — trimming strings, rejecting negative totals — at the decode boundary instead of scattering checks through handlers.

Joins are where naming discipline gets tested. Select o.id, u.email without aliases and two tables contributing id columns collide; qualify every column and alias overlaps explicitly like o.id AS order_id. Prefix conventions — order_ for order columns, customer_ for joined customer columns — keep wide reporting structs readable and make struct updates mechanical when the query gains a column. Review checklists should flag unaliased select * in joined queries as a defect, because schema additions silently change the decoded shape.

Evolve structs and queries as a pair. Adding a non-null column to a table means updating every FromRow struct that selects from it or scoping those queries to explicit column lists that exclude the newcomer. Explicit lists beat select * in application code precisely because they fail loudly at the macro layer when the contract changes. The five minutes spent listing columns saves the midnight debug of a shape that widened underneath a shared struct.

use sqlx::FromRow;
use chrono::{DateTime, Utc};

// Field names MUST match selected column names (or their AS aliases).
// Nullable columns MUST be Option<_>. Order does not matter.
#[derive(Debug, Clone, FromRow)]
pub struct OrderView {
    pub id: i64,
    pub email: String,               // NOT NULL column -> plain String
    pub gift_message: Option<String>, // nullable column -> Option
    pub total_cents: i64,
    pub placed_at: DateTime<Utc>,    // timestamptz maps to DateTime<Utc>
}

// Align names with aliases when joining:
// SELECT o.id, u.email, o.gift_message, o.total_cents, o.placed_at
// FROM orders o JOIN users u ON u.id = o.user_id

// Rename without touching Rust: SELECT email AS customer_email ...
// requires field `customer_email`. Prefer aliasing SQL to fit the struct
// so domain names stay stable while queries evolve.

// Custom decode via FromRow still uses try_get under the hood:
// let email: String = row.try_get("email")?;

sqlx::Error Variants and Honest HTTP Mappings

sqlx::Error is a non-exhaustive enum whose variants tell you exactly which layer failed, and mapping them deliberately is what separates a debuggable API from a pager factory. RowNotFound is the friendliest: a fetch_one or fetch_optional path found nothing, which for REST reads is a 404, not a 500. ColumnNotFound and ColumnIndexOutOfBounds are always query-to-struct bugs — the SQL and the target shape disagree — and should page a developer with the query attached, never confuse a caller with a retryable status.

Database errors wrap Postgres itself and carry machine-readable codes you should match on. Unique violations report 23505 and map naturally to 409 Conflict, telling the client its retry will never succeed without changing the payload. Foreign-key violations report 23503 and mean a referenced row is missing, which is a 422 with a message naming the reference. Check violations, not-null violations, and serialization failures each have codes worth matching once your domain leans on those constraints. Log the full constraint name and table server-side; send callers a stable public message.

Pool errors describe capacity, not data. PoolTimedOut means no connection became available within acquire_timeout — the service is saturated or the database is unreachable — and the correct response is 503 with Retry-After so callers back off instead of piling on. PoolClosed means the pool is shutting down, which callers see during deploys and should also retry. WorkerCrashed points at a background task failure inside the pool machinery and deserves an immediate error-budget look, because it recurs until the underlying cause is fixed.

Decode and I/O errors sit at the boundary between code bugs and infrastructure. Decode failures mean the database returned something the Rust type could not represent — a timestamp out of range, an enum variant the code does not know — and usually follow a schema or data migration that widened reality past the model. Io and Tls errors mean the network or certificate path broke between app and database; these correlate with platform events and should join your TLS-expiry and security-group monitors, not your query dashboards.

The mapping pattern that scales is a single ApiError enum with a From<sqlx::Error> impl owned by the HTTP layer. Every handler returns Result<_, ApiError>, the question-mark operator converts automatically, and IntoResponse renders stable JSON with the right status. Business logic never sees HTTP codes; the edge never sees database internals. Add a catch-all Internal arm that logs the full error with tracing fields — query name, user id, pool stats — so the 500s you do emit arrive with everything needed to fix them.

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

pub enum ApiError {
    NotFound(String),
    Conflict(String),
    Unprocessable(String),
    Unavailable(String),
    Internal(String),
}

impl From<sqlx::Error> for ApiError {
    fn from(e: sqlx::Error) -> Self {
        use sqlx::Error::*;
        match e {
            RowNotFound => Self::NotFound("record not found".into()),
            Database(db) if db.code().as_deref() == Some("23505") => {
                Self::Conflict("duplicate value violates a unique constraint".into())
            }
            Database(db) if db.code().as_deref() == Some("23503") => {
                Self::Unprocessable("referenced record does not exist".into())
            }
            PoolTimedOut => Self::Unavailable("database overloaded, retry shortly".into()),
            PoolClosed => Self::Unavailable("server shutting down".into()),
            ColumnNotFound(c) => Self::Internal(format!("query mapping bug: {c}")),
            other => Self::Internal(format!("unexpected database error: {other}")),
        }
    }
}

impl IntoResponse for ApiError {
    fn into_response(self) -> Response {
        let (status, msg) = match &self {
            Self::NotFound(m) => (StatusCode::NOT_FOUND, m.clone()),
            Self::Conflict(m) => (StatusCode::CONFLICT, m.clone()),
            Self::Unprocessable(m) => (StatusCode::UNPROCESSABLE_ENTITY, m.clone()),
            Self::Unavailable(m) => (StatusCode::SERVICE_UNAVAILABLE, m.clone()),
            Self::Internal(m) => (StatusCode::INTERNAL_SERVER_ERROR, m.clone()),
        };
        (status, Json(json!({ "error": msg }))).into_response()
    }
}

Pooling in Axum State with Clean Shutdown

Sharing a PgPool through Axum state is the standard shape because the pool is cheap to clone and designed for it. PgPool wraps an Arc over the connection set, so cloning duplicates a pointer, not connections. Build exactly one pool at startup, move a clone into an AppState struct alongside config and clients, and hand that state to the router with with_state. Every handler then extracts State<AppState> and borrows &state.pool for queries. No handler creates pools, no middleware opens connections, and pool metrics describe the whole process in one place.

Construction order matters more than it looks. Validate config, build the pool, run the select 1 probe plus migrations, and only then bind the listening socket. This sequence guarantees the orchestrator never routes traffic to a pod whose database is unreachable — readiness follows database proof, not process liveness. Health endpoints should preserve that honesty: a readiness probe that acquires a connection and runs a trivial query fails closed during database incidents, letting the load balancer shed the pod instead of queueing timeouts behind it.

Concurrency behavior falls out of the runtime plus the pool cap. Tokio multiplexes thousands of in-flight requests onto its worker threads, and each database query borrows one pooled connection only for its duration. The pool is the throttle: with max_connections at 8, the ninth concurrent query waits up to acquire_timeout. That backpressure is healthy and observable — graph pool.size, num_idle, and acquire wait time, and alert on sustained saturation before callers notice. What you must never do is spawn_blocking around queries or open transactions across await points that call other services.

Shutdown is the half most teams skip until a deploy drops requests. Wire graceful shutdown so the server stops accepting new connections, finishes in-flight handlers up to a deadline, then closes the pool — which waits for checked-out connections to return. Ten to thirty seconds covers typical queries; longer means a transaction is stuck and needs its own statement_timeout. Verify by deploying under load and counting 5xx during the roll: clean shutdowns show zero, abrupt ones show a spike exactly matching old-pod kills.

Testing with state follows the same single-pool shape. Spin one pool per test binary against a template database, run migrations once, and wrap each test in a transaction rolled back at the end for isolation without per-test database creation. For handler tests, build the router with test state and drive it with axum-test or tower ServiceExt instead of binding ports. The pool design that makes production calm — one shared handle, explicit lifecycle — is the same design that makes tests fast.

use axum::{extract::State, routing::get, Router};
use sqlx::PgPool;
use std::{net::SocketAddr, time::Duration};

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

async fn healthz(State(s): State<AppState>) -> &'static str {
    // Liveness that actually proves DB reachability:
    // run SELECT 1 here in real services, with a timeout.
    let _ = &s.pool;
    "ok"
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let cfg = crate::config::Config::from_env()?;
    let pool = crate::db::build_pool(&cfg.database_url).await?;
    let state = AppState { pool: pool.clone() };
    let app = Router::new().route("/healthz", get(healthz)).with_state(state);
    let addr = SocketAddr::from(([0, 0, 0, 0], 3000));
    let listener = tokio::net::TcpListener::bind(addr).await?;
    // Graceful shutdown: stop accepting, drain handlers, then close pool.
    axum::serve(listener, app)
        .with_graceful_shutdown(shutdown_signal(pool))
        .await?;
    Ok(())
}

async fn shutdown_signal(pool: PgPool) {
    tokio::signal::ctrl_c().await.ok();
    tokio::time::timeout(Duration::from_secs(10), pool.close()).await.ok();
}