sqlx::types::Json for PostgreSQL jsonb.

Vec binds to PostgreSQL arrays with compatible types.

sqlx

Q: Transactions?

pool.begin().await? then commit or automatic rollback on drop.

Q: Testing?

#[sqlx::test] spins up DB or use testcontainers.

Q: Logging queries?

Enable sqlx tracing feature; filter SQL in prod logs for PII.

Q: Pool timeouts?

acquire_timeout on PgPoolOptions prevents hung requests.

sqlx 0.8 delivers async, driver-native SQL with optional compile-time query verification against a live database.

Recipe

Quick-reference recipe card - copy-paste ready.

use sqlx::FromRow;
 
#[derive(FromRow)]
struct User { id: i64, email: String }
 
let users = sqlx::query_as::<_, User>("SELECT id, email FROM users WHERE active = $1")
    .bind(true)
    .fetch_all(&pool)
    .await?;

When to reach for this: You want raw SQL control, async Tokio integration, and PostgreSQL/MySQL/SQLite support without a heavy ORM.

Working Example

use sqlx::{postgres::PgPoolOptions, FromRow};
 
#[derive(Debug, FromRow)]
struct Item { id: i64, name: String }
 
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(&std::env::var("DATABASE_URL").expect("DATABASE_URL"))
        .await?;
 
    sqlx::query("CREATE TABLE IF NOT EXISTS items (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)")
        .execute(&pool)
        .await?;
 
    let inserted = sqlx::query_as::<_, Item>(
        "INSERT INTO items (name) VALUES ($1) RETURNING id, name"
    )
    .bind("widget")
    .fetch_one(&pool)
    .await?;
 
    let all = sqlx::query_as::<_, Item>("SELECT id, name FROM items ORDER BY id")
        .fetch_all(&pool)
        .await?;
 
    println!("{:?} {:?}", inserted, all);
    Ok(())
}

What this demonstrates:

PgPoolOptions for pool configuration.
FromRow mapping rows to structs.
INSERT ... RETURNING with typed result.
fetch_all for multi-row queries.

Deep Dive

How It Works

sqlx speaks the native protocol (e.g., PostgreSQL wire) without blocking the runtime.
query! macro checks SQL at compile time when DATABASE_URL is set for cargo sqlx prepare.
Offline mode stores query metadata in .sqlx/ for CI without DB.

Query Styles

API	Checking	Use
`query`	Runtime	Dynamic SQL
`query_as`	Runtime mapping	Struct rows
`query!`	Compile-time	Known SQL
`query_as!`	Compile-time	Typed rows

Rust Notes

// Offline compile-time checks
// cargo sqlx prepare --workspace
let row = sqlx::query!("SELECT id FROM users WHERE id = $1", id).fetch_one(&pool).await?;

Gotchas

SQL in format strings - Injection risk. Fix: Only use query with bind parameters.
Compile-time DB required - query! needs DB at build unless prepared. Fix: Run cargo sqlx prepare in CI.
N+1 queries in loops - One query per item kills latency. Fix: Batch WHERE id = ANY($1) or joins.
Pool per request - Creating pools repeatedly exhausts DB. Fix: One pool in AppState.
Migration drift - Hand-written SQL out of sync with schema. Fix: Migrations as source of truth.

Alternatives

Alternative	Use When	Don't Use When
Diesel	Type-safe query builder, sync	Pure async Tokio stack preferred
SeaORM	Async ORM with relations	Maximum SQL control required
tokio-postgres alone	Minimal deps	You want pooling and macros

FAQs

Which databases?

PostgreSQL, MySQL, SQLite, MSSQL via feature flags.

Transactions?

pool.begin().await? then commit or automatic rollback on drop.

JSON columns?

sqlx::types::Json<T> for PostgreSQL jsonb.

Arrays?

Vec<T> binds to PostgreSQL arrays with compatible types.

Testing?

#[sqlx::test] spins up DB or use testcontainers.

MSRV?

Match workspace Rust toolchain - sqlx tracks recent stable.

Logging queries?

Enable sqlx tracing feature; filter SQL in prod logs for PII.

Connection string secrets?

Env vars only; never commit URLs with passwords.

Pool timeouts?

acquire_timeout on PgPoolOptions prevents hung requests.

Read replicas?

Separate pools for read vs write URLs; route in repository layer.

Databases Basics - Pool and query intro
Migrations - Schema management
Connection Pooling - Pool sizing
Transactions & Isolation - ACID usage
Query Patterns & N+1 - Efficient access

Stack versions: This page was written for Rust 1.97.0 (edition 2024), Tokio 1.x, Axum 0.8, serde 1.0, sqlx 0.8, clap 4, and Polars 0.46+.

Recipe

Quick-reference recipe card - copy-paste ready.

use sqlx::FromRow;
 
#[derive(FromRow)]
struct User { id: i64, email: String }
 
let users = sqlx::query_as::<_, User>("SELECT id, email FROM users WHERE active = $1")
    .bind(true)
    .fetch_all(&pool)
    .await?;

When to reach for this: You want raw SQL control, async Tokio integration, and PostgreSQL/MySQL/SQLite support without a heavy ORM.

Working Example

use sqlx::{postgres::PgPoolOptions, FromRow};
 
#[derive(Debug, FromRow)]
struct Item { id: i64, name: String }
 
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(&std::env::var("DATABASE_URL").expect("DATABASE_URL"))
        .await?;
 
    sqlx::query("CREATE TABLE IF NOT EXISTS items (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)")
        .execute(&pool)
        .await?;
 
    let inserted = sqlx::query_as::<_, Item>(
        "INSERT INTO items (name) VALUES ($1) RETURNING id, name"
    )
    .bind("widget")
    .fetch_one(&pool)
    .await?;
 
    let all = sqlx::query_as::<_, Item>("SELECT id, name FROM items ORDER BY id")
        .fetch_all(&pool)
        .await?;
 
    println!("{:?} {:?}", inserted, all);
    Ok(())
}

What this demonstrates:

PgPoolOptions for pool configuration.
FromRow mapping rows to structs.
INSERT ... RETURNING with typed result.
fetch_all for multi-row queries.

Deep Dive

How It Works

sqlx speaks the native protocol (e.g., PostgreSQL wire) without blocking the runtime.
query! macro checks SQL at compile time when DATABASE_URL is set for cargo sqlx prepare.
Offline mode stores query metadata in .sqlx/ for CI without DB.

Query Styles

API	Checking	Use
`query`	Runtime	Dynamic SQL
`query_as`	Runtime mapping	Struct rows
`query!`	Compile-time	Known SQL
`query_as!`	Compile-time	Typed rows

Rust Notes

// Offline compile-time checks
// cargo sqlx prepare --workspace
let row = sqlx::query!("SELECT id FROM users WHERE id = $1", id).fetch_one(&pool).await?;

Gotchas

SQL in format strings - Injection risk. Fix: Only use query with bind parameters.
Compile-time DB required - query! needs DB at build unless prepared. Fix: Run cargo sqlx prepare in CI.
N+1 queries in loops - One query per item kills latency. Fix: Batch WHERE id = ANY($1) or joins.
Pool per request - Creating pools repeatedly exhausts DB. Fix: One pool in AppState.
Migration drift - Hand-written SQL out of sync with schema. Fix: Migrations as source of truth.

Alternatives

Alternative	Use When	Don't Use When
Diesel	Type-safe query builder, sync	Pure async Tokio stack preferred
SeaORM	Async ORM with relations	Maximum SQL control required
tokio-postgres alone	Minimal deps	You want pooling and macros

FAQs

Which databases?

PostgreSQL, MySQL, SQLite, MSSQL via feature flags.

Transactions?

pool.begin().await? then commit or automatic rollback on drop.

JSON columns?

sqlx::types::Json<T> for PostgreSQL jsonb.

Arrays?

Vec<T> binds to PostgreSQL arrays with compatible types.

Testing?

#[sqlx::test] spins up DB or use testcontainers.

MSRV?

Match workspace Rust toolchain - sqlx tracks recent stable.

Logging queries?

Enable sqlx tracing feature; filter SQL in prod logs for PII.

Connection string secrets?

Env vars only; never commit URLs with passwords.

Pool timeouts?

acquire_timeout on PgPoolOptions prevents hung requests.

Read replicas?

Separate pools for read vs write URLs; route in repository layer.

Databases Basics - Pool and query intro
Migrations - Schema management
Connection Pooling - Pool sizing
Transactions & Isolation - ACID usage
Query Patterns & N+1 - Efficient access

Stack versions: This page was written for Rust 1.97.0 (edition 2024), Tokio 1.x, Axum 0.8, serde 1.0, sqlx 0.8, clap 4, and Polars 0.46+.

sqlx

Search Documentation

sqlx