I use newtype structs to wrap strings when dealing with sqlx fairly regularly, and have not had the kinds of problems you're encountering. However, I use sqlx's features for this, rather than implementing Encode and Decode myself (where possible). For example, I have:
#[derive(PartialEq, Eq, sqlx::Type)]
#[sqlx(transparent)]
pub struct Secret(String);
pub struct Tokens<'t>(&'t mut SqliteConnection);
impl Tokens<'_> {
// Issue a new token for an existing login. The issued_at timestamp will
// be used to control expiry, until the token is actually used.
pub async fn issue(
&mut self,
user: &User,
issued_at: &DateTime,
) -> Result<Secret, sqlx::Error> {
let id = Id::generate();
let secret = Uuid::new_v4().to_string();
let user = user.id();
let secret = sqlx::query_scalar!(
r#"
insert
into token (id, secret, user, issued_at, last_used_at)
values ($1, $2, $3, $4, $4)
returning secret as "secret!: Secret"
"#,
id,
secret,
user,
issued_at,
)
.fetch_one(&mut *self.0)
.await?;
Ok(secret)
}
/* ... */
}
Where a Secret is used as a query parameter, sqlx unwraps it to a String for me automatically. The underlying field is a varchar field - analogous in most ways to pg's text type, though I'm using sqlite for this project specifically.
Going the other way, and specifically addressing your nullability point, I have:
#[derive(
Clone,
Copy,
Debug,
Eq,
Ord,
PartialEq,
PartialOrd,
serde::Deserialize,
serde::Serialize,
sqlx::Type,
)]
#[serde(transparent)]
#[sqlx(transparent)]
pub struct Sequence(i64);
pub struct Messages<'t>(&'t mut SqliteConnection);
impl Messages<'_> {
// ...
pub async fn all(&mut self, resume_at: Sequence) -> Result<Vec<History>, sqlx::Error> {
let messages = sqlx::query!(
r#"
select
message.channel as "channel: channel::Id",
message.sender as "sender: user::Id",
message.id as "id: Id",
message.body as "body: Body",
message.sent_at as "sent_at: DateTime",
message.sent_sequence as "sent_sequence: Sequence",
deleted.deleted_at as "deleted_at?: DateTime",
deleted.deleted_sequence as "deleted_sequence?: Sequence"
from message
left join message_deleted as deleted
using (id)
where message.sent_sequence <= $1
order by message.sent_sequence
"#,
resume_at,
)
.map(|row| History {
message: Message {
sent: Instant::new(row.sent_at, row.sent_sequence),
channel: row.channel,
sender: row.sender,
id: row.id,
body: row.body.unwrap_or_default(),
deleted_at: row.deleted_at,
},
deleted: Instant::optional(row.deleted_at, row.deleted_sequence),
})
.fetch_all(&mut *self.0)
.await?;
Ok(messages)
}
// ...
}
Note the annotations on deleted.deleted_sequence, in particular. sqlx takes the stored integer, or null, and wraps it in a Some(Sequence(value)) if non-null, or in None if null. (You can ignore the map afterwards; the row structure doesn't perfectly match the struct I'm trying to construct, but that has no bearing on your question, I think.)
this is more of a what-is-your-experience-with-this type of question
It took a bit of doing to pull this understanding out of the sqlx documentation. I would not trust an LLM to provide any of this insight; go to the source, instead.
The relevant bits that I can recall are:
The big surprise for me was that sqlx will not infer the type a field "should have" based on what it gets assigned to, the way Rust itself sometimes will. Your select * will probably have to be select *, refresh_token as "refresh_token: RefreshToken" or similar so that sqlx has the type information it needs to wrap your token.
For completeness' sake, the querying patterns above also work fine with manual implementations of Type and friends. I have
use std::{fmt, string::String as StdString};
#[derive(Clone, Debug, Default, Eq, PartialEq, serde::Serialize, serde::Deserialize)]
#[serde(into = "StdString", from = "StdString")]
#[serde(bound = "N: Normalize")]
pub struct String<N>(StdString, N);
// Type is manually implemented so that we can implement Decode to do
// normalization on read. Implementation is otherwise based on
// `#[derive(sqlx::Type)]` with the `#[sqlx(transparent)]` attribute.
impl<DB, N> Type<DB> for String<N>
where
DB: Database,
StdString: Type<DB>,
{
fn type_info() -> <DB as Database>::TypeInfo {
<StdString as Type<DB>>::type_info()
}
fn compatible(ty: &<DB as Database>::TypeInfo) -> bool {
<StdString as Type<DB>>::compatible(ty)
}
}
impl<'r, DB, N> Decode<'r, DB> for String<N>
where
DB: Database,
StdString: Decode<'r, DB>,
N: Normalize,
{
fn decode(value: <DB as Database>::ValueRef<'r>) -> Result<Self, sqlx::error::BoxDynError> {
let value = StdString::decode(value)?;
Ok(Self::from(value))
}
}
impl<'q, DB, N> Encode<'q, DB> for String<N>
where
DB: Database,
StdString: Encode<'q, DB>,
{
fn encode(
self,
buf: &mut <DB as Database>::ArgumentBuffer<'q>,
) -> Result<IsNull, sqlx::error::BoxDynError> {
let Self(value, _) = self;
value.encode(buf)
}
fn encode_by_ref(
&self,
buf: &mut <DB as Database>::ArgumentBuffer<'q>,
) -> Result<IsNull, sqlx::error::BoxDynError> {
let Self(value, _) = self;
value.encode_by_ref(buf)
}
fn produces(&self) -> Option<<DB as Database>::TypeInfo> {
let Self(value, _) = self;
value.produces()
}
fn size_hint(&self) -> usize {
let Self(value, _) = self;
value.size_hint()
}
}
for dealing with strings that must be in a specific normalization form, and I use it in very much the same way as the above #[derive(sqlx::Type)]-annotated structs.