Actix Web With SQLx and Connection Pools in One Repository

Actix Web and SQLx are a very common pairing, and the one mistake that almost everybody makes with it is invisible in development. The pool gets built inside the closure passed to HttpServer::new, the service works perfectly on a laptop, and then in production Postgres reports "too many connections" under load. This guide builds the service correctly, then builds the wrong version on purpose and measures the damage against a real PostgreSQL 17.

Everything compiled and ran on Rust 1.99, edition 2024, with actix-web 4.18, sqlx 0.9.0 and tokio 1.54. The Axum equivalent of the same wiring is in Axum and SQLx together; the broader framework comparison is in Axum versus Actix Web.

What the factory closure really does

HttpServer::new(|| App::new()...) takes a closure that builds the application. It looks like it is called once. It is not: Actix starts a number of worker threads (by default one per physical core), and each worker calls the closure to build its own copy of the App. Ten workers means ten App values, ten route tables, ten sets of middleware.

For routing and middleware that is the design, and it is why Actix needs no locking in its hot path. For shared resources it is a trap, because whatever you construct inside the closure is constructed once per worker. A database pool, a Redis client, an HTTP client with its own connection cache or a loaded configuration file all belong outside the closure and get cloned in.

The correct wiring

The dependency list:

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

[dependencies]
actix-web = "4.18.2"
serde = { version = "1.0.229", features = ["derive"] }
serde_json = "1.0.154"
sqlx = { version = "0.9.0", features = ["runtime-tokio", "postgres", "migrate"] }
tokio = { version = "1.54.1", features = ["rt-multi-thread", "macros", "time"] }

The schema is a single table, kept in a migration so tests and production build it the same way:

CREATE TABLE notes (
    id    BIGSERIAL PRIMARY KEY,
    body  TEXT NOT NULL
);

The library holds the handlers and a configure function that registers routes on any ServiceConfig. Using configure rather than building the App inline is what lets the tests and main share the exact same routes:

use actix_web::{HttpResponse, Responder, ResponseError, http::StatusCode, web};
use serde::{Deserialize, Serialize};
use sqlx::PgPool;

#[derive(Serialize, sqlx::FromRow)]
pub struct Note {
    id: i64,
    body: String,
}

#[derive(Deserialize)]
pub struct NewNote {
    body: String,
}

#[derive(Debug)]
pub struct ApiError(sqlx::Error);

impl std::fmt::Display for ApiError {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        match self.0 {
            sqlx::Error::RowNotFound => write!(f, "not found"),
            _ => write!(f, "internal error"),
        }
    }
}

impl ResponseError for ApiError {
    fn status_code(&self) -> StatusCode {
        match self.0 {
            sqlx::Error::RowNotFound => StatusCode::NOT_FOUND,
            _ => StatusCode::INTERNAL_SERVER_ERROR,
        }
    }

    // The default body is the `Display` text as plain text; clients of a JSON API want JSON.
    fn error_response(&self) -> HttpResponse {
        HttpResponse::build(self.status_code())
            .json(serde_json::json!({ "error": self.to_string() }))
    }
}

impl From<sqlx::Error> for ApiError {
    fn from(e: sqlx::Error) -> Self {
        ApiError(e)
    }
}

async fn create(
    pool: web::Data<PgPool>,
    new: web::Json<NewNote>,
) -> Result<impl Responder, ApiError> {
    let note = sqlx::query_as::<_, Note>("INSERT INTO notes (body) VALUES ($1) RETURNING id, body")
        .bind(&new.body)
        .fetch_one(pool.get_ref())
        .await?;
    Ok(HttpResponse::Created().json(note))
}

async fn show(pool: web::Data<PgPool>, id: web::Path<i64>) -> Result<impl Responder, ApiError> {
    let note = sqlx::query_as::<_, Note>("SELECT id, body FROM notes WHERE id = $1")
        .bind(id.into_inner())
        .fetch_one(pool.get_ref())
        .await?;
    Ok(web::Json(note))
}

pub fn configure(cfg: &mut web::ServiceConfig) {
    cfg.service(
        web::scope("/notes")
            .route("", web::post().to(create))
            .route("/{id}", web::get().to(show)),
    );
}

Start with ApiError. create and show return Result<impl Responder, ApiError>, which lets the ? after .fetch_one(...) convert a sqlx::Error automatically through the From impl. Actix does not need a conversion into a response by hand: the ResponseError trait is the hook, and its status_code method is the single place that decides that RowNotFound is 404 and everything else is 500. Display supplies the body text, and it deliberately prints internal error for the 500 case; Actix sends the Display output as the response body, so anything you print there reaches the client. The real database error stays inside ApiError.0, where logging middleware can read it.

ApiError also overrides error_response. Without that override Actix sends the Display text as the body, so a missing note answers with the bare words not found and no content type that a client can parse. A JSON API should fail in JSON, and the override is four lines. The test further down pins the exact body: {"error":"not found"}.

Handlers take web::Data<PgPool>. web::Data<T> is a thin wrapper around Arc<T>, and PgPool is itself reference counted internally, so there are two layers of sharing and both are cheap. pool.get_ref() gives the &PgPool that fetch_one wants. Do not write web::Data<Arc<PgPool>>; that is an Arc of an Arc and compiles without complaint, which is exactly why people do it.

Now the binary:

use std::time::Duration;

use actix_web::{App, HttpServer, web};
use notes_actix::configure;
use sqlx::postgres::PgPoolOptions;

fn env_secs(name: &str, default: u64) -> Duration {
    let value = std::env::var(name).ok().and_then(|v| v.parse().ok());
    Duration::from_secs(value.unwrap_or(default))
}

#[actix_web::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let url = std::env::var("DATABASE_URL")?;
    let workers: usize = std::env::var("WORKERS")
        .ok()
        .and_then(|v| v.parse().ok())
        .unwrap_or(4);

    // Built once, outside the factory closure, and shared by every worker.
    let pool = PgPoolOptions::new()
        .max_connections(8)
        .min_connections(1)
        .idle_timeout(env_secs("IDLE_SECS", 600))
        .connect(&url)
        .await?;
    sqlx::migrate!().run(&pool).await?;
    let data = web::Data::new(pool.clone());

    HttpServer::new(move || App::new().app_data(data.clone()).configure(configure))
        .workers(workers)
        .bind(("127.0.0.1", 38081))?
        .run()
        .await?;

    // The server has drained its requests; hand the connections back politely.
    pool.close().await;
    Ok(())
}

The pool is built with PgPoolOptions before HttpServer::new, migrations run on it, and the result is wrapped in web::Data::new. The closure is move and calls data.clone() on every invocation, so each worker gets a handle to the same pool. WORKERS and IDLE_SECS are read from the environment only to make the experiments below reproducible; in a real deployment leave Actix's default worker count or set it to what you measured. After .run().await returns, the server has stopped accepting requests and finished the ones in flight, which is the moment to call pool.close().await. The pool handle in data and the clone used here point at the same pool, so closing one closes it for all.

Build the pool in the wrong place and count connections

Here is the version that bites. It creates the pool inside the closure, with connect_lazy so it needs no await there:

use actix_web::{App, HttpServer, web};
use notes_actix::configure;
use sqlx::postgres::PgPoolOptions;

// The mistake: this closure runs once per worker, so every worker builds its own pool.
#[actix_web::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let url = std::env::var("DATABASE_URL")?;
    HttpServer::new(move || {
        let pool = PgPoolOptions::new()
            .max_connections(8)
            .connect_lazy(&url)
            .unwrap();
        App::new()
            .app_data(web::Data::new(pool))
            .configure(configure)
    })
    .workers(4)
    .bind(("127.0.0.1", 38082))?
    .run()
    .await?;
    Ok(())
}

connect_lazy is the only way to build a pool inside a synchronous closure without blocking, which is part of why the mistake is easy to make: it compiles cleanly, passes every test that builds a single App, and looks tidy. Run both binaries, four workers each, and send 400 requests with 64 in flight at once to /notes/1. Then ask Postgres how many connections each one holds:

seq 1 400 | xargs -P 64 -I{} curl -s -o /dev/null http://127.0.0.1:38081/notes/1
docker exec pg psql -U postgres -d actix_lab -Atc \
  "select count(*) from pg_stat_activity where datname = 'actix_lab' and pid <> pg_backend_pid()"

shared pool, 4 workers     : 8 server connections
pool per worker, 4 workers : 28 server connections

The shared pool stopped at its configured maximum of 8. The per-worker version opened 28: four independent pools, each allowed to grow to 8, each growing as far as its own worker's traffic demanded. It did not reach the theoretical 32 only because the load was uneven; a heavier or longer run would. The max_connections(8) you wrote was a per-pool limit, and there were four pools.

Put numbers on what that means in production. A modest server has 16 cores and so 16 workers. A pool of 10 built per worker means up to 160 connections from one process. Postgres' default max_connections is 100, so a single instance can exhaust the server alone, and three replicas make it certain. The symptom is FATAL: sorry, too many clients already in a service that passed all its tests, and it appears only under concurrent load, because connections are opened on demand.

The fix costs nothing: the pool is a value created once and cloned. The right size for that one pool is its own question, covered in SQLx pool sizing; the short version is that a number far smaller than your worker count is usually enough, because each query holds a connection for milliseconds.

The error that only appears at request time

The other thing that goes wrong is forgetting to register the data. web::Data<PgPool> in a handler's argument list is not checked against app_data when the app is built. If you forget .app_data(...), the code compiles, the server starts, and every request that reaches that handler fails. forgetting_app_data_is_a_500 in the test file reproduces it:

thread 'forgetting_app_data_is_a_500' panicked at tests/api.rs:45:5:
assertion `left == right` failed
  left: b"Requested application data is not configured correctly. View/enable debug logs for more details."
 right: ""

The test first asserted an empty body, which is how the real text was found: the left side is what Actix sent. The text the client receives is Requested application data is not configured correctly. View/enable debug logs for more details. with status 500. It does not name the type, which is why it can take a while to trace back. Two protections work. First, test every route with the same configure and app_data that main uses, as the tests below do, so a missing registration fails a test. Second, when you see this message, look for a handler that asks for a type that main never put in app_data. A common cause is registering web::Data<PgPool> and then asking for web::Data<Arc<PgPool>>, or the other way round: the type must match exactly.

How many connections do you actually hold?

The pool options say more than max_connections. The server above sets min_connections(1) and an idle_timeout, and the behaviour is easy to watch. With IDLE_SECS=2, the numbers from pg_stat_activity over one burst of traffic are:

at start                   : 1
right after the burst      : 8
5 seconds later (idle 2 s) : 1
after SIGTERM              : 0

At start the pool holds its one minimum connection. The 400-request burst with 64 requests in flight pushes it to the maximum of 8, because eight queries are running at the same instant and a ninth must wait for a free connection. Five seconds after the burst, every connection above the minimum has been idle longer than two seconds and the pool closed them, leaving 1. Then SIGTERM stopped the server, Actix drained and returned from run(), and pool.close() released the last connection: the count is 0, which means no connection was left for Postgres to time out on its own.

Three consequences are worth writing down. First, a pool does not need to be sized for the average load; it needs to cover the concurrent queries at the peak, and the idle timeout gives the capacity back afterwards. Second, min_connections is a latency setting, not a capacity one: it keeps a connection warm so the first request after a quiet period does not pay for a TCP and TLS handshake plus authentication, which on a managed database across a network can add tens of milliseconds. Third, if your database is behind a connection proxy such as PgBouncer in transaction mode, long idle connections cost almost nothing, and you may prefer a longer idle_timeout and a larger minimum; against a database with a hard connection ceiling, a short timeout is how many services share it.

The default idle_timeout is ten minutes in SQLx, which is why the example passes 600 as its fallback and why the effect is invisible unless you shorten it.

Set acquire_timeout too. SQLx waits 30 seconds by default for a free connection, and Actix has no request timeout of its own, so a saturated pool turns into requests that hang for half a minute while the client's own timeout (often 10 or 15 seconds) fires first and retries. The retries add load to the pool that was already saturated. A timeout of two or three seconds on the acquire makes the failure fast and visible, returns a 500 you can alert on and lets the retry land on a replica that has a free connection. It is one extra line on the same PgPoolOptions builder, and it is the cheapest protection against the cascade where one slow query takes the whole service down.

Why ? works in handlers, and what it hides

When create runs .fetch_one(...).await?, the ? operator calls From<sqlx::Error> for ApiError, producing an ApiError, which the handler returns in the Err arm of Result<impl Responder, ApiError>. Actix then calls ResponseError::error_response on it. That chain is why handlers have no match on database results, and it is the same idea as IntoResponse in Axum.

What it hides is the log line. Nothing in this error path writes anything down: the 500 case returns internal error to the client and drops the sqlx::Error. Add a line to error_response that logs self.0 at error level, or use actix_web::middleware::Logger, which records the status but not the cause. A production service that returns 500s with no record of why is hard to run. Structured logging with tracing shows how to attach a request id so a client's complaint maps to a log entry.

A related decision is what RowNotFound means. For a GET /notes/{id} it is a 404. For fetch_one inside a POST that inserts and returns the row it would mean the insert returned nothing, which is a bug. The status mapping above is a single global rule, and a service with more tables eventually needs the conversion at the call site (.fetch_optional(...).await?.ok_or(ApiError::NotFound)) so each query chooses its own meaning.

When the state is more than a pool

Real services share more than a pool: a page size, a feature flag, an HTTP client for another service. Put them in one struct and register it once, rather than registering four web::Data values that each need a matching extractor:

use actix_web::{App, HttpResponse, HttpServer, web};
use sqlx::PgPool;

// One struct for everything handlers share. Clone is cheap: the pool is reference counted.
struct AppState {
    pool: PgPool,
    page_size: i64,
}

async fn latest(state: web::Data<AppState>) -> Result<HttpResponse, actix_web::Error> {
    let ids: Vec<i64> = sqlx::query_scalar("SELECT id FROM notes ORDER BY id DESC LIMIT $1")
        .bind(state.page_size)
        .fetch_all(&state.pool)
        .await
        .map_err(actix_web::error::ErrorInternalServerError)?;
    Ok(HttpResponse::Ok().json(ids))
}

#[actix_web::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let pool = PgPool::connect(&std::env::var("DATABASE_URL")?).await?;
    let state = web::Data::new(AppState {
        pool,
        page_size: 20,
    });

    HttpServer::new(move || {
        App::new()
            .app_data(state.clone())
            .route("/latest", web::get().to(latest))
    })
    .bind(("127.0.0.1", 38083))?
    .run()
    .await?;
    Ok(())
}

Request it and the handler returns the newest twenty ids from a table that held thirty rows:

[30,29,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,11]

The struct is registered as web::Data::new(AppState { ... }) and extracted as web::Data<AppState>. The compiler cannot tell you if the two disagree, so the same rule applies as before: one type, registered once, used everywhere. The struct needs no Clone derive, because web::Data already supplies the sharing, and state.clone() in the closure clones the Arc handle. Keep values that change at runtime out of it, or wrap them in RwLock or an atomic: web::Data hands out shared references only, so mutation must be explicit, and an Arc<Mutex<_>> held across an .await in a handler is how an Actix service deadlocks under load.

The error mapping in this example uses ErrorInternalServerError, the quick helper in actix_web::error. It is fine for a one-off route, and it is what the ResponseError approach replaces once you have more than a couple of handlers, because the helper cannot distinguish a missing row from a broken connection.

Tests: a real database per test

actix_web::test builds the service in process, with no sockets, and #[sqlx::test] hands each test a new database with the migrations directory already applied:

use actix_web::{App, http::StatusCode, test, web};
use notes_actix::configure;
use sqlx::PgPool;

#[sqlx::test]
async fn create_and_fetch(pool: PgPool) {
    let app = test::init_service(
        App::new()
            .app_data(web::Data::new(pool))
            .configure(configure),
    )
    .await;

    let req = test::TestRequest::post()
        .uri("/notes")
        .set_json(serde_json::json!({"body": "first"}))
        .to_request();
    let res = test::call_service(&app, req).await;
    assert_eq!(res.status(), StatusCode::CREATED);

    let req = test::TestRequest::get().uri("/notes/1").to_request();
    let body = test::call_and_read_body(&app, req).await;
    assert_eq!(body, r#"{"id":1,"body":"first"}"#);
}

#[sqlx::test]
async fn missing_note_is_404(pool: PgPool) {
    let app = test::init_service(
        App::new()
            .app_data(web::Data::new(pool))
            .configure(configure),
    )
    .await;
    let res = test::call_service(&app, test::TestRequest::get().uri("/notes/7").to_request()).await;
    assert_eq!(res.status(), StatusCode::NOT_FOUND);
    assert_eq!(test::read_body(res).await, r#"{"error":"not found"}"#);
}

#[actix_web::test]
async fn forgetting_app_data_is_a_500() {
    // No `.app_data(...)`: the `web::Data<PgPool>` extractor has nothing to hand over.
    let app = test::init_service(App::new().configure(configure)).await;
    let res = test::call_service(&app, test::TestRequest::get().uri("/notes/1").to_request()).await;
    assert_eq!(res.status(), StatusCode::INTERNAL_SERVER_ERROR);
    let body = test::read_body(res).await;
    assert_eq!(
        body,
        "Requested application data is not configured correctly. View/enable debug logs for more details."
    );
}

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 forgetting_app_data_is_a_500 ... ok
test missing_note_is_404 ... ok
test create_and_fetch ... ok
test result: ok. 3 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.32s
test result: ok. 0 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

test::init_service takes the same App shape as main, with the test's pool in place of the production one, and configure(configure) registers the identical routes. call_and_read_body returns the bytes of the response, compared here with the exact JSON. The id is 1 because the test database is empty; with a shared database that assertion would depend on the order tests ran in.

The #[actix_web::test] attribute on the last test is the variant without a database. It builds an app without app_data and asserts the full message above, so the failure mode is pinned and cannot silently change. If a later Actix release rewords it, this test goes red and you update one string.

Blocking work does not belong in a handler

Actix workers run async tasks cooperatively on a single-threaded runtime each. A handler that does something slow and synchronous, hashing a password with Argon2, resizing an image, parsing a very large document, stops its worker from serving any other request until it finishes. SQLx is async and does not block. For the CPU bound part use web::block(|| ...), which runs the closure on a thread pool and gives you a future for the result. This is the other half of keeping the database layer healthy: a worker stuck on a hash cannot return its connection, and the connection sits idle in the pool while requests queue up behind it.

A short checklist

1. The pool is built before HttpServer::new, and the closure only clones a web::Data.
2. max_connections times the number of service replicas is less than Postgres' own limit, with room for migrations and humans.
3. Errors implement ResponseError, and the 500 case does not echo the database message.
4. Every handler's web::Data<T> type matches a registration in main exactly, and a test per route proves it.
5. CPU heavy work goes through web::block.

Where Actix and this pattern are not the right fit

If you are starting a new service with no Actix experience on the team, Axum shares the Tokio and Tower ecosystem and removes the per-worker App subtlety: there is one Router and one state value. If your workload is pure database round trips behind a pool of ten, the framework will not be your bottleneck whichever you choose; the pool will. And if you need SQLx's compile-time checked macros, they work identically here, as in the Axum guide.

Next steps

Add request ids and structured logging as middleware, then wrap the sweep in a load test. Watching pg_stat_activity while load testing a Rust service is the quickest way to learn what your real connection ceiling is, and the transaction patterns from SQLx transactions carry over unchanged.