Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 38 ยท Databases with SQLx

Setting Up SQLx and Connection Pools

Add SQLx to an Axum app, create a PgPool with PgPoolOptions, share it through State, size the pool sensibly and understand acquire timeouts, with a runnable pool stand-in.

Most web services need a database, and in the Axum world the most popular choice is SQLx: an async, pure-Rust SQL toolkit for PostgreSQL, MySQL and SQLite. It is not an ORM. You write real SQL, and SQLx gives you typed results, async execution and, optionally, compile-time checking of your queries against a live schema.

The first thing every SQLx app does is build a connection pool. This lesson explains why, shows how to create and share one in Axum, and models pool behaviour with a runnable stand-in. SQLx and a database server are not available on this site's runner, so the real code is read-only.

Why a pool

Opening a database connection is slow (TCP, TLS, authentication) and databases cap how many they accept, often around a hundred for PostgreSQL. Opening one per request would be both slow and dangerous. A pool keeps a handful of connections open, lends one to each query and takes it back afterwards. If all connections are busy, the next caller waits, which acts as natural back pressure.

Dependencies

TOML
# Cargo.toml (read-only)
[dependencies]
axum = "0.8"
tokio = { version = "1", features = ["full"] }
serde = { version = "1", features = ["derive"] }
sqlx = { version = "0.8", features = ["runtime-tokio", "tls-rustls", "postgres", "macros", "migrate", "chrono", "uuid"] }
dotenvy = "0.15"

Features are opt-in: choose one runtime (runtime-tokio), one TLS backend, and the database driver you use. macros enables query! and FromRow derive, migrate enables the migration runner.

Creating the pool

Rust
// Real code: read-only.
use sqlx::postgres::PgPoolOptions;
use std::time::Duration;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    dotenvy::dotenv().ok();
    let url = std::env::var("DATABASE_URL")?; // postgres://user:pass@localhost/app

    let pool = PgPoolOptions::new()
        .max_connections(10)
        .min_connections(1)
        .acquire_timeout(Duration::from_secs(3))
        .idle_timeout(Duration::from_secs(300))
        .connect(&url)
        .await?;

    sqlx::query("SELECT 1").execute(&pool).await?; // smoke test
    Ok(())
}

PgPool is cheap to clone: it is an Arc inside, so every clone refers to the same pool. That makes it ideal for Axum state.

Rust
use axum::{extract::State, routing::get, Router};
use sqlx::PgPool;

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

async fn db_health(State(state): State<AppState>) -> Result<&'static str, StatusCode> {
    sqlx::query("SELECT 1")
        .execute(&state.db)
        .await
        .map(|_| "database ok")
        .map_err(|_| StatusCode::SERVICE_UNAVAILABLE)
}

let app = Router::new()
    .route("/health/db", get(db_health))
    .with_state(AppState { db: pool });

You can also use State<PgPool> directly when the pool is the only state. Handlers pass &state.db (anything that implements Executor) to queries; the pool lends a connection for the duration of that query only.

Keep DATABASE_URL in a .env file for local development (and add .env to .gitignore). In production it comes from the environment. Never hard-code credentials in source.

Sizing the pool

There is no magic number, but a few rules help:

  • max_connections must fit under the database's own limit across every instance of your app. Ten instances times twenty connections is two hundred.
  • A pool bigger than the database can usefully run in parallel only creates queueing inside PostgreSQL. A common start is the database's CPU core count times two, plus a margin.
  • Set acquire_timeout so a request fails fast (with a clear error) rather than hanging when the pool is exhausted.
  • Short queries release connections quickly; holding a connection across a slow HTTP call to another service starves the pool.

Model: a pool with back pressure

Here is a pool built from Mutex and Condvar. Two connections, four workers: the workers that cannot get a connection wait until one is returned. The guard gives the connection back when it is dropped, the same RAII trick SQLx uses.

Rust A connection pool in miniature (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with io::stdin().read_line()), it gets the next line from this box.

Only connections 1 and 2 ever exist, yet four workers got served: each waited for a returned connection. A pool of size two never serves more than two simultaneous queries.

Rust What an acquire timeout looks like (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with io::stdin().read_line()), it gets the next line from this box.

In SQLx the equivalent error is sqlx::Error::PoolTimedOut. Map it to 503 Service Unavailable in your error type; we do that in error handling with IntoResponse.

Other databases

Swap the types: SqlitePoolOptions/SqlitePool for SQLite (great for tests and small apps, URL sqlite://data.db?mode=rwc), MySqlPoolOptions for MySQL. Or use sqlx::AnyPool to choose at runtime, at the cost of losing database-specific types.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is a connection pool used instead of one connection per request?

    Opening connections costs time and server resources. A pool reuses a limited set and makes extra callers wait.
  2. 2. Why can PgPool be put straight into Axum state?

    PgPool wraps an Arc. Each clone points to the same pool, so every handler shares the same connections.
  3. 3. What should you set so a request fails fast when all connections are busy?

    acquire_timeout bounds the wait for a free connection; after it elapses you get PoolTimedOut.
  4. 4. Which is a risk of holding a pooled connection while calling a slow external API?

    Held connections are unavailable to other requests. Release them before slow non-database work.

Summary

  • SQLx is a pure-Rust async SQL toolkit; enable only the features you need.
  • Build a PgPool with PgPoolOptions and put it in Axum state; clones share one pool.
  • Size max_connections against the database limit across all app instances.
  • Set acquire_timeout and map PoolTimedOut to a 503.
  • The pool is RAII: a connection returns when its guard is dropped.

Next: Queries and Row Mapping