SQLx is one of Rust’s killer apps for backend work. Type-safe SQL at compile time without an ORM. This post is the working set.

Setup

[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "tls-rustls", "postgres", "macros", "uuid", "chrono"] }

use sqlx::postgres::PgPoolOptions;

let pool = PgPoolOptions::new()
    .max_connections(20)
    .acquire_timeout(Duration::from_secs(3))
    .connect(&env::var("DATABASE_URL")?).await?;

Pool is Clone-cheap (Arc inside); pass it everywhere.

query! macro

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

println!("{} - {}", row.id, row.email);

At compile time, SQLx connects to your dev DB, validates the SQL, and gives you a typed struct. Mismatches fail to compile.

query_as! for explicit types

#[derive(sqlx::FromRow)]
struct User { id: i64, email: String, name: Option<String> }

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

Returns User; fields must match SELECT.

Offline mode

For CI without DB access:

cargo install sqlx-cli
sqlx prepare   # writes .sqlx/ with cached query metadata
git add .sqlx/

CI builds use the cached metadata; no live DB needed.

fetch_one / fetch_optional / fetch_all

sqlx::query!("...").fetch_one(&pool).await?;        // exactly one row; error if 0
sqlx::query!("...").fetch_optional(&pool).await?;   // Option<Row>; None if 0
sqlx::query!("...").fetch_all(&pool).await?;        // Vec<Row>
sqlx::query!("...").execute(&pool).await?;          // INSERT/UPDATE/DELETE

Pick by intent.

Transactions

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

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

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

tx.commit().await?;  // explicit; drops without commit = rollback

&mut *tx — tx derefs to a connection. Quirky but consistent across SQLx APIs.

Streaming large result sets

use futures::StreamExt;

let mut rows = sqlx::query!("SELECT id, email FROM users").fetch(&pool);
while let Some(row) = rows.try_next().await? {
    process(row.id, &row.email);
}

Doesn’t load everything in memory. Critical for big tables.

Migrations

sqlx migrate add create_users
# edits migrations/<timestamp>_create_users.sql

sqlx migrate run
sqlx migrate revert

Embed in app:

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

App runs migrations on startup. Fail-fast if migration fails.

Custom types

#[derive(sqlx::Type)]
#[sqlx(type_name = "user_status", rename_all = "lowercase")]
enum UserStatus { Active, Suspended, Deleted }

#[derive(sqlx::FromRow)]
struct User { id: i64, status: UserStatus }

Custom enum mapped to Postgres enum.

JSON

use sqlx::types::Json;
use serde::{Serialize, Deserialize};

#[derive(Serialize, Deserialize, sqlx::Type)]
struct Prefs { theme: String, notifications: bool }

let row = sqlx::query!(
    r#"SELECT id, prefs as "prefs: Json<Prefs>" FROM users WHERE id = $1"#,
    user_id
).fetch_one(&pool).await?;

Postgres JSON/JSONB ↔ Rust struct via serde.

Dynamic SQL

For queries built at runtime:

use sqlx::QueryBuilder;

let mut qb = QueryBuilder::new("SELECT id, email FROM users WHERE 1=1 ");
if let Some(name) = filter.name {
    qb.push(" AND name = ").push_bind(name);
}

let rows = qb.build().fetch_all(&pool).await?;

Parameter binding still escapes. No SQL injection.

Error handling

match sqlx::query!("INSERT INTO users (email) VALUES ($1)", email)
    .execute(&pool).await
{
    Ok(_) => Ok(()),
    Err(sqlx::Error::Database(e)) if e.is_unique_violation() => {
        Err(AppError::EmailTaken)
    }
    Err(e) => Err(e.into()),
}

Distinguish constraint violations from genuine DB errors. See Rust Error Handling .

Patterns from production

Repository pattern

pub struct UserRepo { pool: PgPool }

impl UserRepo {
    pub async fn create(&self, email: &str) -> Result<User, AppError> {
        let user = sqlx::query_as!(User,
            "INSERT INTO users (email) VALUES ($1) RETURNING id, email, created_at",
            email
        ).fetch_one(&self.pool).await?;
        Ok(user)
    }
}

Centralized SQL; testable; easy to mock at the repo trait level.

Pagination

let users = sqlx::query_as!(User,
    "SELECT id, email FROM users WHERE id > $1 ORDER BY id LIMIT $2",
    cursor, page_size as i64
).fetch_all(&pool).await?;

Cursor-based; stable under inserts.

Bulk insert

sqlx::query!(
    "INSERT INTO users (email) SELECT * FROM UNNEST($1::text[])",
    &emails[..]
).execute(&pool).await?;

Single round-trip for thousands of inserts. Much faster than a loop.

Common mistakes

1. Connections per request

let conn = PgConnection::connect(...).await?;  // BAD

Use the pool. Cloning the pool is cheap; opening connections is expensive.

2. No timeout

A bad query hangs forever. Set acquire_timeout and per-query timeouts (via tokio::time::timeout).

3. SELECT *

query! will type all columns including ones you don’t need. Wide tables → wasted bytes. Specify columns.

4. Skipping transactions

Multi-step writes without a transaction → partial state on errors. Wrap in pool.begin().

5. Ignoring offline mode in CI

CI builds need DB or .sqlx/ cache. Decide which; set up accordingly.

What I’d ship today

For Rust + Postgres backend:

  • SQLx with query!/query_as! macros.
  • Pool sized appropriately (test with pgbench).
  • Repository pattern for testability.
  • Migrations embedded; run at startup.
  • OTEL instrumentation for query traces.
  • pgcat / PgBouncer in front for transaction-pooling.

Read this next

If you want my Rust + SQLx + Axum production starter, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .