如何让sqlx适配自定义的OffsetDateTime类型？

问题背景 #

由于time库的OffsetDateTime未实现Default trait，我自定义了一个包装结构体：

#[derive(Debug, Clone, Copy)]
pub struct OffsetDateTime(pub time::OffsetDateTime);

impl OffsetDateTime {
    pub fn now_utc() -> Self {
        Self(time::OffsetDateTime::now_utc())
    }
}

impl Default for OffsetDateTime {
    fn default() -> Self {
        Self(time::OffsetDateTime::UNIX_EPOCH)
    }
}

impl From<time::OffsetDateTime> for OffsetDateTime {
    fn from(o: time::OffsetDateTime) -> Self {
        Self(o)
    }
}

impl From<OffsetDateTime> for time::OffsetDateTime {
    fn from(o: OffsetDateTime) -> Self {
        o.0
    }
}

使用sqlx的query_as!宏查询时，出现类型不匹配错误：

error[E0308]: mismatched types
   |
66 |           let pla = sqlx::query_as!(
   |  ___________________^
67 | |             Player,
68 | |             r#"SELECT * from player where id = $1"#,
70 | |             id
71 | |         )
   | |_________^ expected struct `custom::types::OffsetDateTime`, found struct `time::OffsetDateTime`
   |
   = note: struct `time::OffsetDateTime` and struct `custom::types::OffsetDateTime` have similar names, but are actually distinct types
note: struct `time::OffsetDateTime` is defined in crate `time`
  --> C:\Users\Fred\.cargo\registry\src\github.com-1ecc6299db9ec823\time-0.3.17\src\offset_date_time.rs:31:1
   |
31 | pub struct OffsetDateTime {
   | ^^^^^^^^^^^^^^^^^^^^^^^^^
note: struct `custom::types::OffsetDateTime` is defined in crate `custom`
   |
4  | pub struct OffsetDateTime(pub time::OffsetDateTime);
   | ^^^^^^^^^^^^^^^^^^^^^^^^^
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)
help: try wrapping the expression in `custom::types::OffsetDateTime`
  --> C:\Users\Fred\.cargo\registry\src\github.com-1ecc6299db9ec823\sqlx-0.6.2\src\macros\mod.rs:561:9
   |
561|         custom::types::OffsetDateTime($crate::sqlx_macros::expand_query!(record = $out_struct, source = $query, args = [$($args)*]))
   |         ++++++++++++++++++++++++++++++                                                                                             +

解决方案 #

要让sqlx正确处理自定义的OffsetDateTime，需要为其实现sqlx提供的数据库类型转换相关trait，以下是针对PostgreSQL的完整实现（其他数据库可替换对应类型）：

1. 确保依赖配置 #

在Cargo.toml中启用sqlx的对应数据库和time支持：

[dependencies]
sqlx = { version = "0.6", features = ["postgres", "time", "runtime-tokio-native-tls"] }
time = "0.3"

2. 完善自定义结构体的实现 #

为自定义OffsetDateTime添加sqlx所需的trait实现：

use sqlx::{Decode, Encode, Postgres, Type};
// 重命名避免类型冲突
use time::OffsetDateTime as TimeOffsetDateTime;

#[derive(Debug, Clone, Copy, Default)]
pub struct OffsetDateTime(pub TimeOffsetDateTime);

impl OffsetDateTime {
    pub fn now_utc() -> Self {
        Self(TimeOffsetDateTime::now_utc())
    }
}

// 保留原有From转换逻辑
impl From<TimeOffsetDateTime> for OffsetDateTime {
    fn from(o: TimeOffsetDateTime) -> Self {
        Self(o)
    }
}

impl From<OffsetDateTime> for TimeOffsetDateTime {
    fn from(o: OffsetDateTime) -> Self {
        o.0
    }
}

// 实现sqlx::Type，指定数据库对应类型
#[cfg(feature = "postgres")]
impl Type<Postgres> for OffsetDateTime {
    fn type_info() -> sqlx::postgres::PgTypeInfo {
        <TimeOffsetDateTime as Type<Postgres>>::type_info()
    }

    fn compatible(ty: &sqlx::postgres::PgTypeInfo) -> bool {
        <TimeOffsetDateTime as Type<Postgres>>::compatible(ty)
    }
}

// 实现Decode，从数据库读取值转换为自定义类型
#[cfg(feature = "postgres")]
impl<'r> Decode<'r, Postgres> for OffsetDateTime {
    fn decode(value: sqlx::postgres::PgValueRef<'r>) -> Result<Self, sqlx::error::BoxDynError> {
        let inner = TimeOffsetDateTime::decode(value)?;
        Ok(Self(inner))
    }
}

// 实现Encode，将自定义类型写入数据库
#[cfg(feature = "postgres")]
impl Encode<'_, Postgres> for OffsetDateTime {
    fn encode_by_ref(&self, buf: &mut sqlx::postgres::PgArgumentBuffer) -> Result<(), sqlx::error::BoxDynError> {
        self.0.encode_by_ref(buf)?;
        Ok(())
    }
}

3. 验证使用 #

修改后的Player结构体和查询代码可以正常运行：

#[derive(Debug, Default, sqlx::FromRow)]
pub struct Player {
    pub id: String,
    pub created_at: OffsetDateTime,
    pub name: String,
}

let pla = sqlx::query_as!(
    Player,
    r#"SELECT * from player where id = $1"#,
    id
)
.fetch_one(&*self.pool)
.await?;

说明 #

• Type trait用于告知sqlx自定义类型对应的数据库原生类型，这里直接复用time::OffsetDateTime的实现即可
• Decode和Encode负责数据库值与自定义类型之间的双向转换，同样复用内部包装的TimeOffsetDateTime的实现
• 如果使用MySQL等其他数据库，只需将代码中的Postgres替换为MySql，并调整Cargo.toml中sqlx的features即可

内容的提问来源于stack exchange，提问作者Fred Hors