如何使用sqlx QueryBuilder正确查询PostgreSQL数据库并添加类型注解？

哈哈，这个坑我之前也踩过！用sqlx的QueryBuilder的时候，确实不能直接照搬query_as!那种带冒号的类型注解语法，因为两者的工作方式完全不一样。

问题根源 #

query_as!是编译期宏，它会悄悄把你写的"列名: 类型"这种语法转换成PostgreSQL能理解的类型转换和列名映射；但QueryBuilder是动态拼接SQL字符串的，PostgreSQL本身根本不认识这种带冒号的注解，所以会把整个字符串当成列名，而且不会自动帮你做类型转换，自然就返回RECORD类型了。

解决思路 #

我们需要直接在SQL里处理类型转换，同时确保结构体字段和返回的列名匹配，具体步骤如下：

1. 显式处理时间戳类型
   DateTime<Utc>对应PostgreSQL的timestamptz类型，所以把原来带注解的时间戳查询改成明确的类型转换，同时保持列名和结构体字段一致：

   // 替换前
   CURRENT_TIMESTAMP AS "timestamp: DateTime<Utc>",
   // 替换后
   CURRENT_TIMESTAMP::timestamptz AS "timestamp",
   

2. 处理数组聚合的类型映射
   只要你的Subscription结构体实现了sqlx::FromRow，且subset_results返回的行结构和Subscription完全匹配，sqlx就能自动把聚合后的数组转换成Vec<Subscription>。所以去掉注解，只保留列名：

   // 替换前
   array_agg(subset_results) AS "data: Vec<Subscription>"
   // 替换后
   array_agg(subset_results) AS "data"
   

修改后的完整代码片段 #

builder.push(r#"
)
SELECT
    (SELECT COUNT(*) FROM full_results) AS total_count,
    (SELECT COUNT(*) FROM subset_results) AS returned_count,
    CURRENT_TIMESTAMP::timestamptz AS "timestamp",
    array_agg(subset_results) AS "data"
FROM subset_results
"#);

let query = builder.build_query_as::<ResponseEnvelope<Vec<Subscription>>>();

// log::info!(&quot;{:?}&quot;, &amp;query.sql());

let subscriptions = query.fetch_one(pool.get_ref()).await;

结构体配置注意事项 #

确保你的ResponseEnvelope和Subscription结构体正确实现FromRow，字段名要和SQL返回的列名对应（PostgreSQL默认列名是小写，可通过#[sqlx(rename)]注解适配驼峰命名）：

use chrono::{DateTime, Utc};
use sqlx::FromRow;

#[derive(Debug, FromRow)]
struct ResponseEnvelope<T> {
    total_count: i64,
    returned_count: i64,
    #[sqlx(rename = "timestamp")] // 若结构体字段是驼峰则需要此注解
    timestamp: DateTime<Utc>,
    data: T,
}

#[derive(Debug, FromRow)]
struct Subscription {
    // 字段需与subscriptions表列一一对应，可加rename注解适配列名
    id: i32,
    subscriber_id: i32,
    start_date: DateTime<Utc>,
    end_date: DateTime<Utc>,
    plan_id: i32,
    postage_id: i32,
    amount: f64,
    copies: i32,
    delivery_type: String,
    payment_received_date: Option<DateTime<Utc>>,
    payment_instrument_date: Option<DateTime<Utc>>,
    payment_instrument: String,
    payment_instrument_details: String,
    payment_details: String,
    receipt_number: String,
}

特殊情况处理 #

如果subset_results的行结构和Subscription不完全匹配，你可以在subset_results里明确指定列，然后聚合时构造对应行：

builder.push(r#"
),
subset_results AS (
    SELECT id, subscriber_id, start_date, end_date, plan_id, postage_id, amount, copies, delivery_type, payment_received_date, payment_instrument_date, payment_instrument, payment_instrument_details, payment_details, receipt_number FROM full_results
    ORDER BY end_date DESC
    LIMIT "#);
builder.push_bind(limit);
builder.push(" OFFSET ");
builder.push_bind(offset);
builder.push(r#"
)
SELECT
    (SELECT COUNT(*) FROM full_results) AS total_count,
    (SELECT COUNT(*) FROM subset_results) AS returned_count,
    CURRENT_TIMESTAMP::timestamptz AS "timestamp",
    array_agg(row(id, subscriber_id, start_date, end_date, plan_id, postage_id, amount, copies, delivery_type, payment_received_date, payment_instrument_date, payment_instrument, payment_instrument_details, payment_details, receipt_number)) AS "data"
FROM subset_results
"#);

总的来说，QueryBuilder是纯动态构建SQL，没有宏的编译期处理能力，所以所有的类型转换和列名映射都要在SQL里显式处理，再靠FromRow完成结构体的映射，这样就能解决你遇到的RECORD类型问题了。

备注：内容来源于stack exchange，提问作者Gaurav Joseph