Postgres in Rust with sqlx, A Practical Setup

TL;DR — sqlx is a pure-Rust SQL crate with compile-time query checking against a real database. Use sqlx::query_as! for typed results, sqlx::query! for actions, and the built-in migrator. Connect via PgPool. No ORM, no macros that hide what’s running. The most pleasant database layer I’ve used in any language.

After Axum for the HTTP layer, the database layer for our Rust services is sqlx 0.5. There’s no ORM I’d recommend in Rust as of 2022 — diesel exists but feels heavy, seaorm is young. sqlx is the right middle: write SQL, get typed results, have the compiler check the queries against a real database at build time.

This post walks through the sqlx setup we use, with the gotchas that took me a day to learn.

What sqlx does that’s special

The standout feature: compile-time SQL checking. When you write:

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

…sqlx connects to a real Postgres at compile time, prepares the query, infers the result types from Postgres’s response, and bakes those types into the macro output. If you misspell a column, refer to a table that doesn’t exist, or pass a type the query doesn’t accept, the build fails with a clear error.

You don’t get this from ORMs. You don’t get it from most query builders. It’s sqlx’s killer feature.

There’s also sqlx::query_as (no !) which compiles without a live database connection, by using runtime types. Same API, less safety. Use the macros when you can; fall back to the runtime version for dynamic SQL.

Setup

# Cargo.toml
[dependencies]
sqlx = { version = "0.5", features = [
    "runtime-tokio-native-tls",
    "postgres",
    "uuid",
    "chrono",
    "macros",
    "migrate",
] }
chrono = { version = "0.4", features = ["serde"] }
uuid = { version = "0.8", features = ["v4", "serde"] }

Feature flags matter:

• runtime-tokio-native-tls — runtime + TLS implementation. Use runtime-tokio-rustls if you prefer rustls.
• postgres — the actual driver. There’s also mysql and sqlite.
• uuid, chrono — type bridges for Postgres’s uuid and timestamp columns.
• macros — enables query! etc.
• migrate — enables built-in migrator.

Connecting

use sqlx::postgres::PgPoolOptions;

let pool = PgPoolOptions::new()
    .max_connections(10)
    .min_connections(2)
    .acquire_timeout(std::time::Duration::from_secs(3))
    .connect(&std::env::var("DATABASE_URL")?)
    .await?;

PgPool is a connection pool, cheaply cloneable. Pass &pool (or clone it) into handlers. With the Axum extension pattern, store it in app state.

Pool size: same logic as everywhere else. Match Postgres’s max_connections minus reserved overhead, divided by number of service instances. With a PgBouncer in front, you can set per-service max_connections higher.

Migrations

sqlx ships a built-in migrator. Migrations are SQL files in migrations/:

migrations/
├── 20220318000000_create_users.up.sql
├── 20220318000000_create_users.down.sql
├── 20220319000000_add_subscriptions.up.sql
└── 20220319000000_add_subscriptions.down.sql

Create migrations via the sqlx CLI:

cargo install sqlx-cli --no-default-features --features postgres
sqlx migrate add create_users

Run them from your binary at startup, or via CLI:

sqlx::migrate!("./migrations").run(&pool).await?;

The migrate! macro embeds the SQL files at compile time. The deployed binary contains its own migrations; no need to ship the migrations/ directory separately.

Compile-time embedding means: change a SQL file, rebuild. Run the binary, migrations are applied. Simple.

Querying — the four shapes

Four common query patterns:

1. Action — no return data:

sqlx::query!(
    "DELETE FROM users WHERE id = $1",
    user_id
).execute(&pool).await?;

2. Fetch one row, mapped to struct:

#[derive(Debug)]
struct User {
    id: i64,
    email: String,
    created_at: chrono::DateTime<chrono::Utc>,
}

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

fetch_one returns RowNotFound error if nothing matches. Use fetch_optional to get Option<User> instead.

3. Fetch many rows:

let users = sqlx::query_as!(
    User,
    "SELECT id, email, created_at FROM users WHERE created_at > $1",
    since
).fetch_all(&pool).await?;

For large result sets, use .fetch(&pool) which returns a Stream — process row-by-row without buffering everything.

4. Insert with returning:

let id: i64 = sqlx::query_scalar!(
    "INSERT INTO users (email) VALUES ($1) RETURNING id",
    email
).fetch_one(&pool).await?;

query_scalar! is a shortcut for “I want one column from one row.” Returns the column type directly.

Transactions

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

let user_id: i64 = sqlx::query_scalar!(
    "INSERT INTO users (email) VALUES ($1) RETURNING id",
    email
).fetch_one(&mut tx).await?;

sqlx::query!(
    "INSERT INTO subscriptions (user_id, plan) VALUES ($1, $2)",
    user_id, plan
).execute(&mut tx).await?;

tx.commit().await?;

Pass &mut tx instead of &pool to each query. The transaction commits on .commit() or rolls back on drop (or explicit .rollback()). If you forget to commit, the transaction is rolled back automatically.

For functions that need to work both in and out of a transaction, accept impl sqlx::Executor<'_, Database = sqlx::Postgres>:

async fn create_user<'e, E>(executor: E, email: &str) -> Result<i64, sqlx::Error>
where
    E: sqlx::Executor<'e, Database = sqlx::Postgres>,
{
    let id = sqlx::query_scalar!(
        "INSERT INTO users (email) VALUES ($1) RETURNING id", email
    ).fetch_one(executor).await?;
    Ok(id)
}

// Call site can pass either a pool or a transaction:
create_user(&pool, "a@b.com").await?;
create_user(&mut tx, "a@b.com").await?;

Offline mode for CI

query! macros need a database at compile time. That’s awkward for CI runners that don’t have Postgres handy. sqlx solves it with offline mode:

# Generate the metadata file from a live DB during dev
cargo sqlx prepare --database-url postgres://...

# Commit the resulting sqlx-data.json
git add sqlx-data.json

In CI, set SQLX_OFFLINE=true. sqlx uses the committed sqlx-data.json instead of connecting. Build succeeds without a live database.

In Cargo.toml:

[dependencies]
sqlx = { version = "0.5", features = [..., "offline"] }

This is non-negotiable for any CI pipeline. Set it up once and forget.

Common Pitfalls

Forgetting Nullable columns in struct fields. A nullable Postgres column needs Option<T> in Rust. query_as! infers this from schema; mismatches are compile errors. Don’t fight them — fix the type.

Using query! with dynamic SQL (string concatenation). The macro needs the SQL literal at compile time. If you need dynamic queries (rare in OLTP), use query (no macro) with proper parameter binding. Never concatenate user input.

Forgetting to bind parameters. sqlx uses $1, $2, etc. Inline values are not interpolated; they’re passed separately. If you query!("SELECT * FROM users WHERE id = {user_id}", user_id), you get a compile error (good — caught early).

Long-held transactions blocking vacuum. Same as in the autovacuum post. Keep transactions short. Don’t hold one across .awaits that do non-DB work.

Not running migrations at boot. Forgetting sqlx::migrate!("./migrations").run(&pool).await? in main means deploys ship with stale schema. Always run migrations on boot.

SQLX_OFFLINE not set in CI. Builds fail with “DATABASE_URL must be set.” Either set it true (with committed sqlx-data.json) or provide a real connection.

Connecting with localhost from inside Docker. Container networking: use the service name (postgres) not localhost. Easy mistake; reads as a runtime error.

Wrapping Up

sqlx + Postgres is the closest backend-Rust comes to “feels obvious.” Write SQL, get types, run migrations, transact when needed. The compile-time verification catches a class of bugs I used to find at runtime in every other language. Monday: Serde JSON patterns — handling the wire format that ties Axum and sqlx together.