Connection Pools & Queries

SQLx is Rust's async database toolkit. It is not an ORM — there is no schema definition language, no migration DSL baked in, no object mapping magic. You write SQL. SQLx compiles it, checks it, and maps the results to Rust types. If your SQL is wrong, you find out at compile time, not in production at 3 AM.

Setting Up a Connection Pool

Every production application uses a connection pool. Creating a new database connection per request is slow — pools maintain a set of open connections and hand them out as needed.

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPoolOptions::new()
        .max_connections(20)
        .min_connections(5)
        .acquire_timeout(std::time::Duration::from_secs(3))
        .idle_timeout(std::time::Duration::from_secs(600))
        .connect("postgres://user:pass@localhost/mydb")
        .await?;

    // Use the pool throughout your application
    let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM users")
        .fetch_one(&pool)
        .await?;

    println!("User count: {}", row.0);
    Ok(())
}

Key pool settings:

• max_connections — upper bound on open connections. Start with 20, tune based on your database's connection limit.
• min_connections — keep this many connections warm. Avoids cold-start latency.
• acquire_timeout — how long to wait for a connection before giving up. Three seconds is a reasonable default.
• idle_timeout — close connections that sit idle too long.

PgPool is internally reference-counted. Cloning it is cheap — it shares the same underlying pool. You can pass it directly to Axum's state without wrapping in Arc.

Basic Queries with sqlx::query

The simplest way to run a query:

// Execute a query, get the raw rows
let rows = sqlx::query("SELECT id, name, email FROM users WHERE active = $1")
    .bind(true)
    .fetch_all(&pool)
    .await?;

for row in rows {
    let id: i64 = row.get("id");
    let name: String = row.get("name");
    println!("{}: {}", id, name);
}

bind attaches parameters. PostgreSQL uses $1, $2, etc. for placeholders. MySQL uses ?. Parameters are always escaped — SQL injection is not possible with parameterized queries.

Typed Queries with query_as

Manually calling .get() on rows is tedious and error-prone. Use query_as to map rows directly to a struct:

use sqlx::FromRow;

#[derive(Debug, FromRow)]
struct User {
    id: i64,
    name: String,
    email: String,
    active: bool,
}

let users: Vec<User> = sqlx::query_as::<_, User>(
    "SELECT id, name, email, active FROM users WHERE active = $1"
)
.bind(true)
.fetch_all(&pool)
.await?;

for user in &users {
    println!("{}: {} ({})", user.id, user.name, user.email);
}

FromRow derives the mapping from column names to struct fields. Column names must match field names, or you can use #[sqlx(rename = "column_name")] on individual fields.

Compile-Time Checked Queries with query!

This is SQLx's killer feature. The query! macro connects to your database at compile time and verifies your SQL:

let user = sqlx::query!(
    "SELECT id, name, email FROM users WHERE id = $1",
    user_id
)
.fetch_one(&pool)
.await?;

// Fields are strongly typed based on the database schema
println!("{}: {}", user.id, user.name);

If the table does not exist, the column names are wrong, or the types do not match — compilation fails. This catches an enormous class of bugs before your code ever runs.

For typed struct mapping, use query_as!:

struct User {
    id: i64,
    name: String,
    email: String,
}

let user = sqlx::query_as!(
    User,
    "SELECT id, name, email FROM users WHERE id = $1",
    user_id
)
.fetch_one(&pool)
.await?;

To use compile-time checking, set the DATABASE_URL environment variable or create a .env file. SQLx connects to the database during cargo build.

Fetch Methods

SQLx provides several fetch methods for different use cases:

// Exactly one row. Errors if zero or more than one.
let user = sqlx::query_as::<_, User>("SELECT * FROM users WHERE id = $1")
    .bind(id)
    .fetch_one(&pool)
    .await?;

// Zero or one row. Returns Option<User>.
let maybe_user = sqlx::query_as::<_, User>("SELECT * FROM users WHERE id = $1")
    .bind(id)
    .fetch_optional(&pool)
    .await?;

// All matching rows.
let users = sqlx::query_as::<_, User>("SELECT * FROM users WHERE active = $1")
    .bind(true)
    .fetch_all(&pool)
    .await?;

// Streaming — for large result sets that should not be loaded into memory at once.
use futures::TryStreamExt;

let mut stream = sqlx::query_as::<_, User>("SELECT * FROM users")
    .fetch(&pool);

while let Some(user) = stream.try_next().await? {
    process_user(user);
}

Use fetch_optional for lookups that might return nothing. Use fetch (streaming) for large result sets.

Insert, Update, Delete

Write operations use the same query interface:

// Insert with RETURNING
let new_user = sqlx::query_as::<_, User>(
    "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email, active"
)
.bind("Alice")
.bind("alice@example.com")
.fetch_one(&pool)
.await?;

// Update
let result = sqlx::query("UPDATE users SET active = $1 WHERE id = $2")
    .bind(false)
    .bind(user_id)
    .execute(&pool)
    .await?;

println!("Rows affected: {}", result.rows_affected());

// Delete
let result = sqlx::query("DELETE FROM users WHERE id = $1")
    .bind(user_id)
    .execute(&pool)
    .await?;

execute returns metadata including rows_affected(). Use RETURNING with fetch_one when you need the created or updated row back.

Transactions

Wrap multiple operations in a transaction to ensure atomicity:

let mut tx = pool.begin().await?;

let user = sqlx::query_as::<_, User>(
    "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email, active"
)
.bind("Bob")
.bind("bob@example.com")
.fetch_one(&mut *tx)
.await?;

sqlx::query(
    "INSERT INTO user_settings (user_id, theme) VALUES ($1, $2)"
)
.bind(user.id)
.bind("dark")
.execute(&mut *tx)
.await?;

tx.commit().await?;

If any query fails or if tx is dropped without calling commit(), all changes roll back. Note the &mut *tx syntax — transactions dereference to the connection.

For more complex transaction patterns:

async fn transfer_funds(
    pool: &sqlx::PgPool,
    from_id: i64,
    to_id: i64,
    amount: i64,
) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;

    let from_balance: (i64,) = sqlx::query_as(
        "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE"
    )
    .bind(from_id)
    .fetch_one(&mut *tx)
    .await?;

    if from_balance.0 < amount {
        // Drop tx without committing — automatic rollback
        return Err(sqlx::Error::Protocol("Insufficient funds".to_string()));
    }

    sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
        .bind(amount)
        .bind(from_id)
        .execute(&mut *tx)
        .await?;

    sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
        .bind(amount)
        .bind(to_id)
        .execute(&mut *tx)
        .await?;

    tx.commit().await?;
    Ok(())
}

FOR UPDATE locks the row until the transaction completes, preventing race conditions on the balance check.

Common Pitfalls

• Not setting pool limits. The default max_connections may be too high for your database. PostgreSQL defaults to 100 max connections — if you have 10 instances with 20 connections each, you are at the limit.
• Using fetch_one for lookups that might return nothing. This panics or errors on zero rows. Use fetch_optional and handle the None case.
• String interpolation instead of bind parameters. Never use format!("SELECT * FROM users WHERE name = '{}'", name). Always use .bind(). This is a SQL injection vulnerability.
• Forgetting to commit transactions. If a transaction is dropped without commit(), it rolls back silently. This is a safety feature, but it can be confusing when your writes disappear.
• Not using RETURNING. Without it, you need a second query after INSERT or UPDATE. PostgreSQL gives you the result in one round trip.

Key Takeaways

• Use PgPoolOptions to configure connection pools with appropriate limits and timeouts.
• query_as maps rows to structs using FromRow. Use it for all typed queries.
• query! and query_as! check your SQL at compile time — use them when possible.
• Always use .bind() for parameters. Never interpolate values into SQL strings.
• Choose the right fetch method: fetch_one, fetch_optional, fetch_all, or streaming fetch.
• Wrap related operations in transactions. Dropping a transaction without committing rolls it back.