Connection Pooling: Sizing and Lifecycle

23 min read

Opening a fresh database connection for every query is one of the most expensive mistakes a service can make — a TCP handshake, TLS negotiation, and an authentication round-trip on the hot path of every request. A connection pool keeps a small, reusable set of live connections so each query borrows one, uses it, and returns it. This page is about managing that pool in Rust: sqlx::Pool, the deadpool and bb8 generic poolers, and the sizing and lifecycle knobs that decide whether your service stays up under load.

Quick Overview

A connection pool is a fixed-capacity set of open database connections that requests check out and return, instead of dialing the database anew each time. In Node you rarely touch this directly — pg.Pool, Knex’s pool, or Prisma’s internal pool hide it behind a config object. In Rust you build the pool explicitly and own its full configuration: maximum size, minimum warm connections, acquire timeout, idle timeout, and maximum connection lifetime. SQLx ships its own Pool<DB>; for drivers that do not (the raw tokio-postgres, redis, MongoDB drivers), the ecosystem standardizes on two generic poolers, deadpool and bb8. The pool handle is a cheap clonable Arc you build once at startup and share across every handler.

Note: Every Rust snippet here was compiled and run with cargo 1.96.0 (current stable; 2024 edition). The SQLx examples were verified against SQLx 0.8.6; the Pool/PoolOptions API shown is identical in 0.8 and the newer 0.9 line that cargo add sqlx now resolves to on Rust ≥ 1.94. The generic-pooler examples use deadpool 0.14 and bb8 0.9. SQLx examples run against in-memory SQLite, so they reproduce with no server installed.

TypeScript/JavaScript Example

Here is a realistic node-postgres (pg) pool — the kind you would build once in a module and import everywhere. The pool config is the heart of it: how many connections, how long to wait, when to recycle.

1
import { Pool, type PoolConfig } from "pg";
2

3
const config: PoolConfig = {
4
  connectionString: process.env.DATABASE_URL,
5
  max: 20,                       // hard cap on connections
6
  min: 2,                        // keep a couple warm (pg keeps them, doesn't pre-open)
7
  connectionTimeoutMillis: 5_000, // wait up to 5s for a free connection
8
  idleTimeoutMillis: 30_000,      // close a connection idle for 30s
9
  maxLifetimeSeconds: 1_800,      // recycle a connection after 30 min
10
};
11

12
// ONE shared pool for the whole process. Importing this module everywhere
13
// gives every route the same pool — never `new Pool()` per request.
14
export const pool = new Pool(config);
15

16
// Surface background errors (an idle connection dropped by the server, etc.).
17
pool.on("error", (err) => {
18
  console.error("idle client error", err.message);
19
});
20

21
export async function countUsers(): Promise<number> {
22
  // `pool.query` checks a connection out and returns it automatically.
23
  const { rows } = await pool.query<{ count: string }>(
24
    "SELECT COUNT(*) AS count FROM users",
25
  );
26
  return Number(rows[0].count);
27
}
28

29
// For a multi-statement unit of work you check a client out explicitly
30
// and MUST release it in a finally — forgetting `release()` leaks a
31
// connection and eventually exhausts the pool.
32
export async function transfer(from: number, to: number, amount: number) {
33
  const client = await pool.connect();
34
  try {
35
    await client.query("BEGIN");
36
    await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amount, from]);
37
    await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, to]);
38
    await client.query("COMMIT");
39
  } catch (e) {
40
    await client.query("ROLLBACK");
41
    throw e;
42
  } finally {
43
    client.release(); // <- the classic bug is forgetting this
44
  }
45
}
46

47
// On shutdown, drain the pool so in-flight queries finish.
48
export async function shutdown() {
49
  await pool.end();
50
}

Three things to keep in mind, because Rust changes the ergonomics of all three:

The config is a plain object whose fields are easy to forget or mistype (connectionTimeoutMillis vs connectTimeout).
client.release() is a manual obligation — the leak when you forget it is a real, common production incident.
The pool is a module-level singleton you import; sharing is implicit.

Rust Equivalent

SQLx builds the same pool through PoolOptions. Every setting is a typed method, the connection is returned to the pool automatically when the guard is dropped (no finally, no leak), and the pool handle is a value you clone into your application state.

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite", "postgres"] }
3
tokio = { version = "1", features = ["full"] }

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::SqlitePool;
3
use std::time::Duration;
4

5
// Shared application state — the SQLx analogue of importing the `pool` singleton.
6
#[derive(Debug, Clone)]
7
struct AppState {
8
    pool: SqlitePool,
9
}
10

11
// Build ONE pool at startup. Every knob is a typed builder method, so a typo
12
// is a compile error, not a silently-ignored object key.
13
async fn build_pool(url: &str) -> Result<SqlitePool, sqlx::Error> {
14
    SqlitePoolOptions::new()
15
        .max_connections(10)              // hard cap
16
        .min_connections(2)               // keep this many warm
17
        .acquire_timeout(Duration::from_secs(5))  // wait up to 5s for a free conn
18
        .idle_timeout(Duration::from_secs(600))   // close a conn idle for 10 min
19
        .max_lifetime(Duration::from_secs(1800))  // recycle after 30 min
20
        .connect(url)
21
        .await
22
}
23

24
#[tokio::main]
25
async fn main() -> Result<(), sqlx::Error> {
26
    // `sqlite::memory:` runs with nothing installed.
27
    let pool = build_pool("sqlite::memory:").await?;
28

29
    sqlx::query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
30
        .execute(&pool)
31
        .await?;
32
    sqlx::query("INSERT INTO users (name) VALUES (?)")
33
        .bind("Alice")
34
        .execute(&pool)
35
        .await?;
36

37
    // Cloning is a cheap Arc bump; both handles share the SAME connections.
38
    let state = AppState { pool: pool.clone() };
39
    let state2 = state.clone();
40

41
    let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM users")
42
        .fetch_one(&state2.pool)
43
        .await?;
44
    println!("user count = {}", row.0);
45

46
    // Pool statistics, the way a /metrics or /health endpoint would read them.
47
    println!("pool size = {}", pool.size());
48
    println!("idle connections = {}", pool.num_idle());
49

50
    // Graceful shutdown: stop handing out connections and drain in-flight ones.
51
    pool.close().await;
52
    println!("pool closed = {}", pool.is_closed());
53

54
    Ok(())
55
}

Real output:

1
user count = 1
2
pool size = 2
3
idle connections = 1
4
pool closed = true

Note: pool size = 2 because min_connections(2) warmed two connections at startup, even though only one query ran. idle connections = 1 because the COUNT(*) query had checked one out at the moment we measured (the measurement races the connection’s return; the point is that size ≥ num_idle).

For PostgreSQL the only change is the builder type and the connection string:

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "postgres"] }
3
tokio = { version = "1", features = ["full"] }

1
use sqlx::postgres::PgPoolOptions;
2
use sqlx::PgPool;
3
use std::time::Duration;
4

5
async fn build_pg_pool() -> Result<PgPool, sqlx::Error> {
6
    PgPoolOptions::new()
7
        .max_connections(20)
8
        .min_connections(5)
9
        .acquire_timeout(Duration::from_secs(5))
10
        .idle_timeout(Duration::from_secs(300))
11
        .max_lifetime(Duration::from_secs(1800))
12
        // Reads `DATABASE_URL` from the environment / .env in real apps.
13
        .connect("postgres://postgres:secret@localhost/app")
14
        .await
15
}

Note: This PostgreSQL builder type-checks and compiles, but .connect().await only dials a TCP socket at runtime, so it needs a live server to actually run. The SQLite examples need no server, which is why this page shows live output with SQLite.

Detailed Explanation

The pool is an `Arc` — clone it, do not rebuild it

SqlitePool (an alias for Pool<Sqlite>) is internally an Arc around the shared connection set, so pool.clone() just bumps a reference count — it does not open new connections or copy the configuration. This is the whole sharing strategy: build the pool once in main, store it in your application state, and .clone() it into every request handler and background task. The earlier example spawns eight tasks that all share a four-connection pool:

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::SqlitePool;
3

4
async fn worker(id: u32, pool: SqlitePool) -> Result<i64, sqlx::Error> {
5
    let row: (i64,) = sqlx::query_as("SELECT ?1 * 10")
6
        .bind(id as i64)
7
        .fetch_one(&pool)
8
        .await?;
9
    Ok(row.0)
10
}
11

12
#[tokio::main]
13
async fn main() -> Result<(), sqlx::Error> {
14
    let pool = SqlitePoolOptions::new()
15
        .max_connections(4)
16
        .connect("sqlite::memory:")
17
        .await?;
18

19
    // Eight tasks, four connections: the pool queues the extra demand.
20
    let mut handles = Vec::new();
21
    for id in 1..=8u32 {
22
        let pool = pool.clone(); // cheap Arc clone
23
        handles.push(tokio::spawn(async move { worker(id, pool).await }));
24
    }
25

26
    let mut total = 0;
27
    for h in handles {
28
        total += h.await.unwrap()?;
29
    }
30
    println!("sum of results = {total}");
31
    println!("pool max connections = {}", pool.options().get_max_connections());
32
    Ok(())
33
}

Real output:

1
sum of results = 360
2
pool max connections = 4

Eight tasks needed connections but only four existed; SQLx queued the surplus and serviced them as connections freed up. This is exactly what you want — the pool is the back-pressure mechanism that protects your database from an unbounded fan-out. The shared-ownership machinery behind Arc is covered in Rc and Arc and the Arc/Mutex async pattern.

The lifecycle knobs, and what each one protects against

Setting (SQLx)	Node `pg` equivalent	What it controls	What it protects against
`max_connections(n)`	`max`	Hard ceiling on open connections	Overwhelming the database’s own `max_connections` limit
`min_connections(n)`	`min`	Warm connections kept open	Cold-start latency on the first requests
`acquire_timeout(d)`	`connectionTimeoutMillis`	How long a checkout waits	A stuck request hanging forever when the pool is saturated
`idle_timeout(d)`	`idleTimeoutMillis`	Close a connection idle this long	Holding connections you no longer need
`max_lifetime(d)`	`maxLifetimeSeconds`	Recycle a connection after this age	Stale connections, server-side timeouts, load-balancer drift
`test_before_acquire(bool)`	(manual)	Ping a connection before lending it	Handing out a connection the server silently closed

Tip: min_connections in SQLx actually pre-opens connections in the background, unlike node-postgres where min only means “don’t close below this many once they exist.” If cold-start latency matters, SQLx warms the pool for you.

A small program shows the warm-up and a health-check query in action:

1
use sqlx::sqlite::SqlitePoolOptions;
2
use std::time::Duration;
3

4
#[tokio::main]
5
async fn main() -> Result<(), sqlx::Error> {
6
    let pool = SqlitePoolOptions::new()
7
        .max_connections(10)
8
        .min_connections(3)                // eagerly open 3 at startup
9
        .acquire_timeout(Duration::from_secs(3))
10
        .test_before_acquire(true)         // validate before lending
11
        .connect("sqlite::memory:")
12
        .await?;
13

14
    // Let the background warm-up open the minimum connections.
15
    tokio::time::sleep(Duration::from_millis(50)).await;
16
    println!("after warm-up: size = {}, idle = {}", pool.size(), pool.num_idle());
17

18
    // The query a /health endpoint runs to prove the DB is reachable.
19
    sqlx::query("SELECT 1").execute(&pool).await?;
20
    println!("health check ok");
21

22
    Ok(())
23
}

Real output:

1
after warm-up: size = 3, idle = 3
2
health check ok

Connections return themselves — no `finally`, no leak

The most important ergonomic difference from node-postgres: when you call pool.acquire().await? in SQLx you get a PoolConnection guard, and the connection is returned to the pool automatically when that guard goes out of scope (its Drop implementation runs). There is no client.release() to forget, because Rust’s ownership model runs the cleanup for you. The classic node-postgres leak — an early return or a thrown error that skips release() — simply cannot happen, since Drop runs on every exit path including a panic or a ? early-return. This is the same RAII discipline that makes the SQLx Transaction guard roll back automatically if you drop it without committing.

When SQLx is not enough: `deadpool` and `bb8`

SQLx’s Pool only pools SQLx connections. The moment you use a driver that has no built-in pool — raw tokio-postgres, the redis crate, a custom client — you reach for a generic async pooler. The two standards are:

deadpool — a lightweight pooler built around a Manager trait, with ready-made adapters like deadpool-postgres and deadpool-redis. It is the more popular choice today: simple, fast, and async/await-native.
bb8 — an older but still maintained generic pooler with the same ManageConnection concept and adapters like bb8-postgres and bb8-redis.

Here is a deadpool-postgres pool with the same sizing knobs, plus a handler-style checkout:

1
[dependencies]
2
deadpool-postgres = "0.14"
3
tokio-postgres = "0.7"
4
tokio = { version = "1", features = ["full"] }

1
use deadpool_postgres::{Config, ManagerConfig, Pool, RecyclingMethod, Runtime};
2
use tokio_postgres::NoTls;
3

4
fn build_pool() -> Pool {
5
    let mut cfg = Config::new();
6
    cfg.host = Some("localhost".to_string());
7
    cfg.dbname = Some("app".to_string());
8
    cfg.user = Some("postgres".to_string());
9
    cfg.password = Some("secret".to_string());
10
    // RecyclingMethod::Fast returns a connection without a round-trip check;
11
    // RecyclingMethod::Verified pings it first (slower, safer).
12
    cfg.manager = Some(ManagerConfig {
13
        recycling_method: RecyclingMethod::Fast,
14
    });
15
    // Pool sizing lives in `cfg.pool`.
16
    cfg.pool = Some(deadpool_postgres::PoolConfig::new(16));
17
    cfg.create_pool(Some(Runtime::Tokio1), NoTls).unwrap()
18
}
19

20
// A handler: check a client out, run a query, return it automatically on drop.
21
async fn count_users(pool: &Pool) -> Result<i64, Box<dyn std::error::Error>> {
22
    let client = pool.get().await?; // checkout; returned to the pool when dropped
23
    let row = client.query_one("SELECT COUNT(*) FROM users", &[]).await?;
24
    Ok(row.get(0))
25
}
26

27
#[tokio::main]
28
async fn main() {
29
    let pool = build_pool();
30
    println!("max size = {}", pool.status().max_size);
31
    // count_users(&pool) would need a live Postgres server to run.
32
    let _f = count_users;
33
}

Real output:

1
max size = 16

The bb8 equivalent uses a fluent builder that should feel familiar after PgPoolOptions:

1
[dependencies]
2
bb8 = "0.9"
3
bb8-postgres = "0.9"
4
tokio-postgres = "0.7"
5
tokio = { version = "1", features = ["full"] }

1
use bb8::Pool;
2
use bb8_postgres::PostgresConnectionManager;
3
use std::time::Duration;
4
use tokio_postgres::NoTls;
5

6
async fn build_pool() -> Pool<PostgresConnectionManager<NoTls>> {
7
    let manager = PostgresConnectionManager::new_from_stringlike(
8
        "host=localhost user=postgres dbname=app",
9
        NoTls,
10
    )
11
    .unwrap();
12

13
    Pool::builder()
14
        .max_size(16)
15
        .min_idle(Some(2))
16
        .connection_timeout(Duration::from_secs(5))
17
        .idle_timeout(Some(Duration::from_secs(600)))
18
        .max_lifetime(Some(Duration::from_secs(1800)))
19
        // `build_unchecked` returns immediately without opening connections;
20
        // `build` would eagerly verify one connection and is fallible.
21
        .build_unchecked(manager)
22
}
23

24
#[tokio::main]
25
async fn main() {
26
    let pool = build_pool().await;
27
    let state = pool.state();
28
    println!("connections = {}", state.connections);
29
    println!("idle = {}", state.idle_connections);
30
    // pool.get().await would need a live Postgres server to run.
31
    let _ = pool;
32
}

Real output (the pool was built without contacting a server, so both counts start at zero):

1
connections = 0
2
idle = 0

Tip: If you are using SQLx, you do not need deadpool or bb8 — SQLx’s own Pool covers PostgreSQL, MySQL, and SQLite. Reach for the generic poolers only for drivers without a built-in pool, such as redis (Redis) or a raw tokio-postgres client.

Key Differences

Aspect	TypeScript (`pg.Pool` / Knex / Prisma)	Rust (SQLx / deadpool / bb8)
Pool construction	a config object, fields easy to mistype	typed builder methods; a typo is a compile error
Sharing	module-level singleton you import	a clonable `Arc` handle you pass into state
Returning a connection	manual `client.release()` in a `finally`	automatic on `Drop` — cannot be leaked
”No free connection”	rejected Promise after `connectionTimeoutMillis`	`Err(PoolTimedOut)` after `acquire_timeout`
Connection recycling	`maxLifetimeSeconds`, `idleTimeoutMillis`	`max_lifetime`, `idle_timeout`
Pre-warming	`min` keeps existing ones; does not pre-open	`min_connections` actively pre-opens in the background
Generic pooling	one pool type fits all `pg` clients	SQLx pool for SQLx; `deadpool`/`bb8` for other drivers
Sizing guidance	same rule of thumb	same rule of thumb (see below)

Sizing: the rule of thumb is the same as everywhere else

Pool size is a database-side concern, not a Rust one. The widely cited HikariCP guidance applies regardless of language: a small, fixed pool almost always beats a large one. A useful starting point for a CPU-bound workload is connections = ((core_count * 2) + effective_spindle_count), then load-test from there. The key insight that surprises people: a pool of, say, 10 connections frequently out-throughputs a pool of 100, because the database spends less time context-switching and contending on locks. Critically, the sum of max_connections across all your service instances must stay under the database server’s own connection limit (PostgreSQL’s max_connections, default 100) — otherwise new instances fail to connect. If you run many instances or serverless functions, put a server-side pooler like PgBouncer in front of PostgreSQL and keep each app pool small.

Warning: The default SQLx max_connections is 10. That is fine for a single instance but will exhaust a default PostgreSQL server (max_connections = 100) at just ten app instances. Always set max_connections deliberately and divide your database’s limit across your fleet.

Common Pitfalls

Building a new pool per request

The cardinal sin. A pool that you build inside a handler opens fresh connections, serves one request, and is dropped — throwing away every benefit of pooling and hammering the database with connection churn. Build the pool once in main, store it in shared state, and clone the handle into handlers. This is the Rust equivalent of new Pool() on every Express request, and it is just as wrong.

Forgetting that the connection is held until the guard drops

pool.acquire().await? hands you a guard that holds a connection for as long as it is alive. If you bind it to a variable that stays in scope across a long .await (a slow HTTP call, a sleep), you keep a pooled connection checked out the whole time, starving everyone else. Keep checkouts short: acquire, query, drop. Prefer passing &pool directly to query.execute(&pool) (which checks out and returns within the single call) over holding a PoolConnection across unrelated awaits.

Pool exhaustion surfaces as a timeout, not a hang

When every connection is busy and a new acquire waits longer than acquire_timeout, SQLx returns an error rather than blocking forever. This program forces it by capping the pool at one connection, holding that connection, and asking for a second:

1
use sqlx::sqlite::SqlitePoolOptions;
2
use std::time::Duration;
3

4
#[tokio::main]
5
async fn main() -> Result<(), sqlx::Error> {
6
    let pool = SqlitePoolOptions::new()
7
        .max_connections(1)
8
        .acquire_timeout(Duration::from_millis(500))
9
        .connect("sqlite::memory:")
10
        .await?;
11

12
    // Check out the only connection and keep the guard alive.
13
    let _held = pool.acquire().await?;
14

15
    // Asking for a second one with none free -> times out.
16
    match pool.acquire().await {
17
        Ok(_) => println!("acquired a second connection (unexpected)"),
18
        Err(e) => println!("second acquire failed: {e}"),
19
    }
20

21
    Ok(())
22
}

Real output:

1
second acquire failed: pool timed out while waiting for an open connection

Warning: A flood of these pool timed out errors is the unmistakable signature of pool exhaustion — either your pool is too small for the load, or (more often) some code path is holding connections too long. Setting a sane acquire_timeout turns a silent hang into a fast, loud failure you can alert on, which is exactly what you want.

Leaving `max_lifetime` unset behind a load balancer or PgBouncer

Without a max_lifetime, a pooled connection can live indefinitely. Behind a network load balancer or PgBouncer that silently drops idle TCP connections, your pool will hand out a dead connection and the next query fails. Set a max_lifetime (often 30 minutes) so connections are proactively recycled before any intermediary kills them, and enable test_before_acquire if you have seen this in production.

Assuming `deadpool`/`bb8` settings map one-to-one to SQLx

They cover the same concepts but spell them differently: deadpool puts sizing in a PoolConfig and uses a RecyclingMethod enum; bb8 uses .max_size/.min_idle/.connection_timeout. Do not copy SQLx method names across — check each crate’s builder. The verified examples above show the exact current spelling for deadpool 0.14 and bb8 0.9.

Best Practices

One pool, built once, shared everywhere. Construct it in main, put it in your application state, and clone the Arc handle into handlers and tasks. Never build a pool per request.
Set max_connections deliberately, and keep it small. Start near (cores * 2) and load-test. Make sure the total across all instances fits under the database’s own max_connections; front PostgreSQL with PgBouncer when you scale out.
Always set an acquire_timeout. A bounded wait converts pool exhaustion from a hang into an observable error you can alert on and shed load against.
Set max_lifetime (and consider idle_timeout) so connections are recycled before a load balancer, firewall, or PgBouncer drops them out from under you.
Pre-warm with min_connections if cold-start latency matters; SQLx opens them in the background so the first requests do not pay the dial cost.
Keep checkouts short. Pass &pool to queries so the connection is borrowed and returned inside one call; avoid holding a PoolConnection guard across unrelated .await points.
Expose pool health. pool.size() and pool.num_idle() make great /metrics gauges; a SELECT 1 against the pool is a solid /health readiness check.
Use SQLx’s own pool for SQLx. Reach for deadpool/bb8 only for drivers (Redis, raw tokio-postgres) that lack a built-in pool.

Real-World Example

A production-shaped startup: build one pool with sensible lifecycle settings, share it through an AppState (the same value you would hand to axum’s State), serve concurrent “requests” off the shared pool, then shut down gracefully. This is the skeleton of essentially every Rust web service that talks to a database.

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite"] }
3
tokio = { version = "1", features = ["full"] }

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::{FromRow, SqlitePool};
3
use std::time::Duration;
4

5
#[derive(Debug, FromRow)]
6
struct User {
7
    id: i64,
8
    name: String,
9
}
10

11
// Shared application state — exactly what goes behind axum's `State`.
12
#[derive(Clone)]
13
struct AppState {
14
    pool: SqlitePool,
15
}
16

17
// Build ONE pool at startup with production-shaped lifecycle settings.
18
async fn init_pool() -> Result<SqlitePool, sqlx::Error> {
19
    SqlitePoolOptions::new()
20
        .max_connections(20)
21
        .min_connections(5)
22
        .acquire_timeout(Duration::from_secs(5))
23
        .idle_timeout(Duration::from_secs(300))
24
        .max_lifetime(Duration::from_secs(1800))
25
        .connect("sqlite::memory:") // DATABASE_URL in real apps
26
        .await
27
}
28

29
// A handler-like function borrows the SHARED pool through state.
30
async fn list_users(state: &AppState) -> Result<Vec<User>, sqlx::Error> {
31
    sqlx::query_as::<_, User>("SELECT id, name FROM users ORDER BY id")
32
        .fetch_all(&state.pool)
33
        .await
34
}
35

36
#[tokio::main]
37
async fn main() -> Result<(), sqlx::Error> {
38
    let pool = init_pool().await?;
39
    sqlx::query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
40
        .execute(&pool)
41
        .await?;
42
    for name in ["Alice", "Bob"] {
43
        sqlx::query("INSERT INTO users (name) VALUES (?)")
44
            .bind(name)
45
            .execute(&pool)
46
            .await?;
47
    }
48

49
    let state = AppState { pool: pool.clone() };
50

51
    // Two concurrent "requests" sharing the one pool.
52
    let s1 = state.clone();
53
    let s2 = state.clone();
54
    let (a, b) = tokio::join!(
55
        tokio::spawn(async move { list_users(&s1).await }),
56
        tokio::spawn(async move { list_users(&s2).await }),
57
    );
58
    let users1 = a.unwrap()?;
59
    let users2 = b.unwrap()?;
60
    println!("request 1 saw {} users (first: {})", users1.len(), users1[0].name);
61
    println!("request 2 saw {} users (first: {})", users2.len(), users2[0].name);
62

63
    // Graceful shutdown: stop accepting checkouts and drain in-flight ones.
64
    pool.close().await;
65
    println!("pool closed cleanly: {}", pool.is_closed());
66
    Ok(())
67
}

Real output:

1
request 1 saw 2 users (first: Alice)
2
request 2 saw 2 users (first: Alice)
3
pool closed cleanly: true

In a real axum service the AppState goes into Router::with_state, each handler receives State(state): State<AppState>, and you call pool.close().await in your shutdown signal handler so in-flight queries finish before the process exits. The pattern is identical to how a database handle is shared across routes in Section 16: Web APIs.

Exercises

Exercise 1: Build and inspect a pool

Difficulty: Beginner

Objective: Build a SQLite pool with explicit sizing and read back its configuration and live stats.

Instructions: Using an in-memory SQLite database, build a pool with max_connections(8), min_connections(2), and a 2-second acquire_timeout. Run a SELECT 1 to prove it connects, then print the pool’s max and min connection settings via pool.options().

Solution

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite"] }
3
tokio = { version = "1", features = ["full"] }

1
use sqlx::sqlite::SqlitePoolOptions;
2
use std::time::Duration;
3

4
#[tokio::main]
5
async fn main() -> Result<(), sqlx::Error> {
6
    let pool = SqlitePoolOptions::new()
7
        .max_connections(8)
8
        .min_connections(2)
9
        .acquire_timeout(Duration::from_secs(2))
10
        .connect("sqlite::memory:")
11
        .await?;
12

13
    sqlx::query("SELECT 1").execute(&pool).await?;
14
    println!("connected; max = {}", pool.options().get_max_connections());
15
    println!("min = {}", pool.options().get_min_connections());
16
    Ok(())
17
}

Real output:

1
connected; max = 8
2
min = 2

Exercise 2: Trigger pool exhaustion

Difficulty: Intermediate

Objective: See firsthand how a saturated pool turns into a timeout error rather than a hang.

Instructions: Build a pool capped at a single connection with a 500 ms acquire_timeout. Check out the only connection into a variable and keep it alive. Then call pool.acquire().await again and match on the result, printing the error message when the second acquire fails.

Solution

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite"] }
3
tokio = { version = "1", features = ["full"] }

1
use sqlx::sqlite::SqlitePoolOptions;
2
use std::time::Duration;
3

4
#[tokio::main]
5
async fn main() -> Result<(), sqlx::Error> {
6
    let pool = SqlitePoolOptions::new()
7
        .max_connections(1)
8
        .acquire_timeout(Duration::from_millis(500))
9
        .connect("sqlite::memory:")
10
        .await?;
11

12
    let _held = pool.acquire().await?; // hold the only connection
13

14
    match pool.acquire().await {
15
        Ok(_) => println!("acquired a second connection (unexpected)"),
16
        Err(e) => println!("second acquire failed: {e}"),
17
    }
18
    Ok(())
19
}

Real output:

1
second acquire failed: pool timed out while waiting for an open connection

Tip: Dropping _held (for example by ending its scope before the second acquire) would let the second checkout succeed — proof that the connection returns itself on Drop, with no release() call.

Exercise 3: Build the pool from explicit `ConnectOptions`

Difficulty: Advanced

Objective: Configure connection-level options (not just sizing) by building the pool from a ConnectOptions value, and turn off SQLx’s statement logging.

Instructions: Instead of passing a URL string to .connect(...), parse a SqliteConnectOptions from "sqlite::memory:", disable statement logging with .log_statements(log::LevelFilter::Off), and build the pool with .connect_with(...). Set max_connections(8) and min_connections(2), run a SELECT 1, and print the effective max and min. (Add the log crate for the LevelFilter.)

Solution

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite"] }
3
tokio = { version = "1", features = ["full"] }
4
log = "0.4"

1
use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
2
use sqlx::ConnectOptions;
3
use std::str::FromStr;
4
use std::time::Duration;
5

6
#[tokio::main]
7
async fn main() -> Result<(), sqlx::Error> {
8
    // Connection-LEVEL options (logging, pragmas, etc.) live on ConnectOptions.
9
    let connect_opts = SqliteConnectOptions::from_str("sqlite::memory:")?
10
        .log_statements(log::LevelFilter::Off);
11

12
    // Pool-LEVEL options (sizing, timeouts) live on PoolOptions.
13
    let pool = SqlitePoolOptions::new()
14
        .max_connections(8)
15
        .min_connections(2)
16
        .acquire_timeout(Duration::from_secs(2))
17
        .connect_with(connect_opts)
18
        .await?;
19

20
    sqlx::query("SELECT 1").execute(&pool).await?;
21
    println!("connected; max = {}", pool.options().get_max_connections());
22
    println!("min = {}", pool.options().get_min_connections());
23
    Ok(())
24
}

Real output:

1
connected; max = 8
2
min = 2

Tip: The split is deliberate: ConnectOptions configures each connection (logging, TLS, SQLite pragmas), while PoolOptions configures the pool around them (sizing, timeouts, lifecycle). connect_with is how you combine the two.

Connection Pooling: Sizing and Lifecycle

Quick Overview

TypeScript/JavaScript Example

Rust Equivalent

Detailed Explanation

The pool is an Arc — clone it, do not rebuild it

The lifecycle knobs, and what each one protects against

Connections return themselves — no finally, no leak

When SQLx is not enough: deadpool and bb8

Key Differences

Sizing: the rule of thumb is the same as everywhere else

Common Pitfalls

Building a new pool per request

Forgetting that the connection is held until the guard drops

Pool exhaustion surfaces as a timeout, not a hang

Leaving max_lifetime unset behind a load balancer or PgBouncer

Assuming deadpool/bb8 settings map one-to-one to SQLx

Best Practices

Real-World Example

Further Reading

Exercises

Exercise 1: Build and inspect a pool

Exercise 2: Trigger pool exhaustion

Exercise 3: Build the pool from explicit ConnectOptions

The pool is an `Arc` — clone it, do not rebuild it

Connections return themselves — no `finally`, no leak

When SQLx is not enough: `deadpool` and `bb8`

Leaving `max_lifetime` unset behind a load balancer or PgBouncer

Assuming `deadpool`/`bb8` settings map one-to-one to SQLx

Exercise 3: Build the pool from explicit `ConnectOptions`