Working with Databases using SQLx

SQLx is an async SQL toolkit: you write real SQL, and it handles connection pooling, parameter binding, mapping rows to structs and migrations. It is not an ORM. This lesson uses PostgreSQL; MySQL and SQLite work the same way with their own pool types.

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

Add "tls-rustls" when connecting to a managed database that requires TLS. The default features include macros and migrate, used below. The code in this lesson also compiles unchanged against SQLx 0.9, which needs a newer compiler (Rust 1.94 or later).

Migrations

Schema changes live in versioned SQL files under migrations/. The sqlx-cli tool creates and applies them:

cargo install sqlx-cli --no-default-features --features rustls,postgres
export DATABASE_URL=postgres://app:secret@localhost:5432/app
sqlx database create
sqlx migrate add create_users

That creates migrations/<timestamp>_create_users.sql, which you fill in:

CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);

sqlx migrate run applies pending migrations from the command line. The sqlx::migrate!() macro embeds the directory into your binary so the app can apply them itself at startup, as below. Applied migrations are recorded in a _sqlx_migrations table; never edit a migration that has already run — add a new one.

Pools and queries

use sqlx::postgres::PgPoolOptions;
use sqlx::{FromRow, PgPool};

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

async fn create_user(pool: &PgPool, email: &str, name: &str) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar("INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id")
        .bind(email)
        .bind(name)
        .fetch_one(pool)
        .await
}

async fn find_user(pool: &PgPool, id: i64) -> Result<Option<User>, sqlx::Error> {
    sqlx::query_as::<_, User>("SELECT id, email, name FROM users WHERE id = $1")
        .bind(id)
        .fetch_optional(pool)
        .await
}

async fn rename_user(pool: &PgPool, id: i64, name: &str) -> Result<bool, sqlx::Error> {
    let result = sqlx::query("UPDATE users SET name = $1 WHERE id = $2")
        .bind(name)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(result.rows_affected() == 1)
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let url = std::env::var("DATABASE_URL")?;
    let pool = PgPoolOptions::new().max_connections(5).connect(&url).await?;
    sqlx::migrate!("./migrations").run(&pool).await?;

    let id = create_user(&pool, "ada@example.com", "Ada").await?;
    rename_user(&pool, id, "Ada Lovelace").await?;
    let user = find_user(&pool, id).await?;
    println!("{user:?}");
    Ok(())
}

Against an empty database, this prints Some(User { id: 1, email: "ada@example.com", name: "Ada Lovelace" }); run it twice and the second run fails on the UNIQUE email constraint.

Key points:

• PgPool is a pool of connections. It is cheap to clone (an Arc inside), so create one at startup and share it — in Axum, as State<PgPool>.
• .bind(value) sends values separately from the SQL text ($1, $2 in Postgres, ? in MySQL/SQLite). Never build SQL with format! from user input; binding is what prevents SQL injection.
• Fetch methods say how many rows you expect: fetch_one (the first row; Error::RowNotFound if there is none), fetch_optional (zero or one), fetch_all (a Vec), fetch (a stream), and execute for statements that return no rows.
• #[derive(FromRow)] maps columns to fields by name. Rust and SQL types must line up: BIGINT/BIGSERIAL ↔ i64, INTEGER ↔ i32, TEXT ↔ String, nullable columns ↔ Option<T>.

Transactions

pool.begin() starts a transaction. Run queries against &mut *tx, then commit(). If the transaction is dropped without committing — for example, because ? returned early — it is rolled back.

use sqlx::PgPool;

/// Moves `cents` between accounts. Returns false (and changes nothing)
/// if the source account has insufficient funds.
async fn transfer(pool: &PgPool, from: i64, to: i64, cents: i64) -> Result<bool, sqlx::Error> {
    let mut tx = pool.begin().await?;

    let debited = sqlx::query(
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1",
    )
    .bind(cents)
    .bind(from)
    .execute(&mut *tx)
    .await?;

    if debited.rows_affected() == 0 {
        return Ok(false); // `tx` dropped here: rolled back
    }

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

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

Compile-time checked queries

The query! and query_as! macros connect to your database while compiling and check the SQL, the parameter types and the result columns:

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

A typo in a column name becomes a compile error instead of a runtime one. The cost is that building needs DATABASE_URL pointing at a migrated database. For CI and Docker builds, run cargo sqlx prepare to save query metadata into a .sqlx/ directory, commit it, and build with SQLX_OFFLINE=true.

Common mistakes

• Building SQL with format!("... WHERE name = '{name}'"). Use .bind.
• Opening a new connection per request instead of sharing one pool.
• Mismatched types, such as reading a BIGINT into i32. SQLx reports a decode error at runtime (or a compile error with the macros).
• Forgetting commit(). The transaction is silently rolled back on drop.
• Running queries on &pool inside a transaction by accident; they run outside it. Use &mut *tx.
• Using the query! macros without DATABASE_URL or a .sqlx/ directory, so the build fails on another machine or in CI.

Practice

Write async fn users_by_domain(pool: &PgPool, domain: &str) -> Result<Vec<User>, sqlx::Error> that returns users whose email ends with @domain, ordered by name. Bind the pattern rather than formatting it into the SQL.

use sqlx::{FromRow, PgPool};

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

async fn users_by_domain(pool: &PgPool, domain: &str) -> Result<Vec<User>, sqlx::Error> {
    // Escape LIKE wildcards in the input, then bind the whole pattern.
    let escaped = domain.replace('\\', "\\\\").replace('%', "\\%").replace('_', "\\_");
    let pattern = format!("%@{escaped}");
    sqlx::query_as::<_, User>(
        "SELECT id, email, name FROM users WHERE email LIKE $1 ESCAPE '\\' ORDER BY name",
    )
    .bind(pattern)
    .fetch_all(pool)
    .await
}