SQLX 编译期校验查询

示例说明

SQL 拼写错误通常要等运行时才炸——query! 宏在编译期就连接数据库（或离线缓存）核对语句语法、列存在性与类型映射，构建失败即问题暴露。运行时等价物 query_as 配合 FromRow 派生把行直接映射为强类型结构体；参数全部走 bind 的占位符通道，注入在结构上不可能。

技术要点

• 编译期连库校验语句与类型
• FromRow 派生行到结构体映射
• 事务内成对更新保证原子性

代码解读

fetch_open_invoices 按客户查开放发票并限定条数，两个占位符各自 bind 类型化参数；settle 在事务里先改状态再记台账，两步同进同退。连接池配置最大十最小二、获取超时三秒——池耗尽时调用方快速失败而不是无限等待。coalesce 兜住空表的求和。

使用建议

CI 里用 sqlx prepare 生成离线校验缓存免构建期连库。慢查询配数据库侧 statement_timeout。大结果集用 fetch 流式拉取替代 fetch_all。

代码示例

use sqlx::postgres::PgPoolOptions;
use sqlx::{query_as, PgPool, Row};
use std::time::Duration;

#[derive(Debug, sqlx::FromRow)]
struct Invoice {
    id: i64,
    customer: String,
    amount_cents: i64,
    status: String,
}

async fn fetch_open_invoices(pool: &PgPool, customer: &str) -> Result<Vec<Invoice>, sqlx::Error> {
    // query! 宏在编译期连库校验 SQL 与列类型（此处用 query_as 演示运行时等价物）
    query_as::<_, Invoice>(
        "SELECT id, customer, amount_cents, status \
         FROM invoices \
         WHERE customer = $1 AND status = 'open' \
         ORDER BY amount_cents DESC LIMIT $2",
    )
    .bind(customer)
    .bind(20i32)
    .fetch_all(pool)
    .await
}

async fn settle(pool: &PgPool, invoice_id: i64) -> Result<u64, sqlx::Error> {
    let mut tx = pool.begin().await?;

    let updated = sqlx::query("UPDATE invoices SET status = 'paid' WHERE id = $1")
        .bind(invoice_id)
        .execute(&mut *tx)
        .await?
        .rows_affected();

    sqlx::query("INSERT INTO ledger (invoice_id, kind) VALUES ($1, 'settle')")
        .bind(invoice_id)
        .execute(&mut *tx)
        .await?;

    tx.commit().await?;
    Ok(updated)
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPoolOptions::new()
        .max_connections(10)
        .min_connections(2)
        .acquire_timeout(Duration::from_secs(3))
        .connect("postgres://user:pass@localhost/billing")
        .await?;

    let invoices = fetch_open_invoices(&pool, "acme").await?;
    println!("open invoices: {}", invoices.len());

    for invoice in &invoices {
        let affected = settle(&pool, invoice.id).await?;
        println!("invoice {} settled ({} row)", invoice.id, affected);
    }

    let total: i64 = sqlx::query("SELECT coalesce(sum(amount_cents), 0) FROM invoices")
        .fetch_one(&pool)
        .await?
        .get(0);
    println!("ledger total cents: {total}");

    pool.close().await;
    Ok(())
}