Rust Sqlx

SQLx + PostgreSQL Patterns

Data access for Rust services. Repository implementations live in infrastructure and receive &mut PgConnection from use cases that own the transaction (see rust-clean-architecture).

Query Macro Selection

Macros verify SQL against the schema AT COMPILE TIME — wrong column names are compile errors. Prefer macros; runtime query()/query_as::<_, T>() is acceptable when the template's runtime-query style is already established (detected by codebase-scanning) or for dynamic SQL with bound parameters.

let user = sqlx::query_as!(UserRow,
    "SELECT id, username, email FROM users WHERE id = $1", id)
    .fetch_optional(&mut *conn)
    .await?;

• fetch_optional for lookups that may miss — NEVER fetch_one (turns a missing row into an error you must untangle).
• fetch_one only when absence is a bug (e.g. RETURNING after INSERT).
• SQLx 0.9 note: runtime query() requires &'static str or an AssertSqlSafe wrapper — user input must NEVER be interpolated into SQL text; it goes in $n bind parameters.

Executor-Generic Functions

One signature serves pool, connection, and transaction:

pub async fn find_user<'e, E>(executor: E, id: Uuid) -> sqlx::Result<Option<UserRow>>
where E: Executor<'e, Database = Postgres>,
{
    sqlx::query_as!(UserRow, "SELECT * FROM users WHERE id = $1", id)
        .fetch_optional(executor).await
}
// call with &pool, &mut conn, or &mut *tx

For port traits (which must be object-safe for Arc<dyn ...>), use &mut PgConnection parameters instead — both pool connections and transactions deref to it.

Type Mapping

FromRow attributes: #[sqlx(rename = "...")], #[sqlx(default)], #[sqlx(json)], #[sqlx(flatten)]. Row structs are pub(crate) in infrastructure — never exported, never serialized to the wire.

Transactions

let mut tx = pool.begin().await?;          // use case owns this
repo_a.insert(&mut tx, &a).await?;
repo_b.insert(&mut tx, &b).await?;
tx.commit().await?;                        // explicit; drop = rollback

Savepoints: tx.begin().await? inside a transaction nests one.

Pool Configuration

PgPoolOptions::new()
    .max_connections(config.db_max_conn)   // from config, not hardcoded
    .acquire_timeout(Duration::from_secs(5))
    .connect(&config.database_url).await?

Never hold a connection across an .await on unrelated work; acquire late, release early. Tests use transactional fixtures (#[sqlx::test]) for isolation.

Migrations

• Filename: NNNN_snake_case.sql or YYYYMMDDHHMMSS_snake_case.sql — pick the project's existing convention (codebase-scanning detects it) and stay consistent.
• Idempotent DDL: CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS.
• Destructive statements (DROP/TRUNCATE/column drops) require an explicit -- @destructive marker comment and a stated rollback plan.
• sqlx migrate run at service startup (run_migrations(&pool)), and fresh-DB migration must succeed end-to-end in CI.

Offline Cache (CI without a database)

cargo sqlx prepare --workspace     # writes .sqlx/ — COMMIT IT

CI builds use the cache; no DATABASE_URL needed. Regenerate after ANY schema or query change — cargo sqlx prepare --check in the quality gate catches drift.

Error Mapping (SQLSTATE)

Map at the RepoError boundary; never let sqlx::Error escape infrastructure:

Forbidden Patterns