Sqlx Postgres

SQLx + PostgreSQL v17 Development Guide

Compile-time Checked Queries

// query_as! (type-safe)
let user = sqlx::query_as!(
    User,
    r#"SELECT id, name, email, role as "role: UserRole" FROM users WHERE id = $1"#,
    id
).fetch_one(&pool).await?;

// Single column
let count = sqlx::query_scalar!("SELECT COUNT(*) FROM users WHERE active = true")
    .fetch_one(&pool).await?;

// INSERT/UPDATE/DELETE
sqlx::query!("INSERT INTO users (name, email) VALUES ($1, $2)", name, email)
    .execute(&pool).await?;

Fetch Methods

.fetch_one(&pool)       // 1 row (error if 0 or 2+)
.fetch_optional(&pool)  // 0-1 row (Option<T>)
.fetch_all(&pool)       // All rows (Vec<T>)
.fetch(&pool)           // Stream (Stream<T>)
.execute(&pool)         // Execute only (PgQueryResult)

PostgreSQL ENUM

-- Migration
CREATE TYPE user_role AS ENUM ('Admin', 'AreaManager', 'ServiceStation');

#[derive(Debug, Clone, sqlx::Type, Serialize, Deserialize)]
#[sqlx(type_name = "user_role", rename_all = "PascalCase")]
pub enum UserRole { Admin, AreaManager, ServiceStation }

// Query (cast required)
sqlx::query_as!(User, r#"SELECT role as "role: UserRole" FROM users"#)

JSONB

#[derive(Debug, Serialize, Deserialize)]
pub struct Metadata { pub tags: Vec<String> }

// INSERT
sqlx::query!("INSERT INTO items (metadata) VALUES ($1)", sqlx::types::Json(metadata) as _)
    .execute(&pool).await?;

// SELECT
sqlx::query_as!(Item, r#"SELECT metadata as "metadata: Json<Metadata>" FROM items"#)

Transactions

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

sqlx::query!("INSERT INTO users (name) VALUES ($1)", name)
    .execute(&mut *tx).await?;

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

tx.commit().await?;
// Error → tx drops → auto rollback

Pagination

#[derive(Deserialize)]
pub struct Pagination { page: Option<i64>, per_page: Option<i64> }

impl Pagination {
    pub fn offset(&self) -> i64 { (self.page.unwrap_or(1) - 1) * self.per_page() }
    pub fn per_page(&self) -> i64 { self.per_page.unwrap_or(20).min(100) }
}

sqlx::query_as!(User, "SELECT * FROM users LIMIT $1 OFFSET $2",
    pagination.per_page(), pagination.offset())

Bulk INSERT (UNNEST)

let names: Vec<String> = items.iter().map(|i| i.name.clone()).collect();
let values: Vec<i32> = items.iter().map(|i| i.value).collect();

sqlx::query!(
    "INSERT INTO items (name, value) SELECT * FROM UNNEST($1::text[], $2::int[])",
    &names, &values
).execute(&pool).await?;

UPSERT

sqlx::query!(
    r#"INSERT INTO prices (station_id, fuel_type, price)
       VALUES ($1, $2, $3)
       ON CONFLICT (station_id, fuel_type)
       DO UPDATE SET price = EXCLUDED.price, recorded_at = NOW()"#,
    station_id, fuel_type as _, price
).execute(&pool).await?;

Dynamic Query (QueryBuilder)

let mut builder = QueryBuilder::new("SELECT * FROM users WHERE 1=1");

if let Some(name) = &filter.name {
    builder.push(" AND name ILIKE ").push_bind(format!("%{}%", name));
}
builder.push(" ORDER BY id LIMIT ").push_bind(limit);

builder.build_query_as::<User>().fetch_all(&pool).await?

Migrations

sqlx migrate add create_users_table
sqlx migrate run
sqlx migrate revert

Notes

1. ENUM cast: role as "role: UserRole" format required
2. NULL: Use Option<T> for nullable columns
3. Compile-time check: Requires DATABASE_URL env
4. Offline mode: cargo sqlx prepare generates .sqlx dir
5. Connection: &pool for normal, &mut *tx for transactions