I was using sqlx
with MySQL through RSPC. The database is connected successfully, however there are some types that are not currently supported for example chrono::NaiveDate
, bigdecimal::BigDecimal
. A list of what sqlx
maps SQL types into rust types can be found here.
As a workaround, I could get it working by creating another struct and manually mapping the values into the values I need, see following:
// this struct contains the results fetched from db
#[derive(Debug, Serialize)]
struct ExpenseQueryResult {
id: i32,
name: String,
amount: BigDecimal,
date: chrono::NaiveDate,
category: String,
comment: Option<String>,
tags: Option<String>,
}
// this is an additional struct for mapping unsupported values into supported values
#[derive(Debug)]
struct ListExpensesResponse {
id: i32,
name: String,
amount: f64,
date: String,
category: String,
comment: Option<String>,
tags: Option<String>,
}
// my router definition
let router = rspc::Router::<Ctx>::new()
.config(Config::new().export_ts_bindings("../generated/bindings.ts"))
.query("listExpenses", |ctx: Ctx, _: ()| async move {
let expenses: Vec<ExpenseQueryResult> = sqlx::query_as!(
ExpenseQueryResult,
r#"
select
e. `id`,
e. `name`,
e. `amount`,
e. `date`,
ec. `name` as `category`,
e. `comment`,
group_concat(t. `name` separator ', ') as `tags`
from
`expenses` e
inner join `expenses_categories` ec on e. `expenses_categories_id` = ec. `id`
left join `tags_expenses` te on e. `id` = te. `expenses_id`
left join `tags` t on t. `id` = te. `tags_id`
group by
e. `id`,
e. `name`,
e. `amount`,
e. `date`,
ec. `name`,
e. `comment`
order by
e. `date` desc;
"#,
)
.fetch_all(&ctx.pool)
.await
.unwrap();
selection!(
expenses
.into_iter()
.map(|e| {
ListExpensesResponse {
id: e.id,
name: e.name,
amount: e.amount.to_f64().unwrap_or(-1.0),
date: e.date.to_string(),
category: e.category,
comment: e.comment,
tags: e.tags,
}
})
.collect::<Vec<ListExpensesResponse>>(),
[{ id, name, amount, date, category, comment, tags }]
)
})
.build()
.arced();
So this code above is working well and fine and produce the following JSON response:
[
{
"type": "response",
"id": null,
"result": [
{
"amount": 118.00,
"category": "Tithe",
"comment": "Any comments",
"date": "2022-08-06",
"id": 4,
"name": "August Tithe",
"tags": "Touch n' Go"
}
]
}
]
However, since I'm learning Rust and wanted to try making chrono::NaiveDate
and bigdecimal::BigDecimal
works by implementing the rspc::internal::specta::Type
trait for it.
The following shows my implementation
#[derive(Debug, Serialize)]
struct SpectaCompatibleNaiveDate(chrono::NaiveDate);
impl From<chrono::NaiveDate> for SpectaCompatibleNaiveDate {
fn from(date: chrono::NaiveDate) -> Self {
Self(date)
}
}
impl rspc::internal::specta::Type for SpectaCompatibleNaiveDate {
const NAME: &'static str = "NaiveDate";
fn inline(
_: rspc::internal::specta::DefOpts,
_: &[rspc::internal::specta::DataType],
) -> rspc::internal::specta::DataType {
rspc::internal::specta::DataType::Primitive(rspc::internal::specta::PrimitiveType::String)
}
fn reference(
_: rspc::internal::specta::DefOpts,
_: &[rspc::internal::specta::DataType],
) -> rspc::internal::specta::DataType {
rspc::internal::specta::DataType::Primitive(rspc::internal::specta::PrimitiveType::String)
}
fn definition(_: rspc::internal::specta::DefOpts) -> rspc::internal::specta::DataType {
panic!()
}
}
#[derive(Debug, Serialize)]
struct SpectaCompatibleBigDecimal(bigdecimal::BigDecimal);
impl From<bigdecimal::BigDecimal> for SpectaCompatibleBigDecimal {
fn from(decimal: bigdecimal::BigDecimal) -> Self {
Self(decimal)
}
}
impl rspc::internal::specta::Type for SpectaCompatibleBigDecimal {
const NAME: &'static str = "BigDecimal";
fn inline(
_: rspc::internal::specta::DefOpts,
_: &[rspc::internal::specta::DataType],
) -> rspc::internal::specta::DataType {
rspc::internal::specta::DataType::Primitive(rspc::internal::specta::PrimitiveType::f64)
}
fn reference(
_: rspc::internal::specta::DefOpts,
_: &[rspc::internal::specta::DataType],
) -> rspc::internal::specta::DataType {
rspc::internal::specta::DataType::Primitive(rspc::internal::specta::PrimitiveType::f64)
}
fn definition(_: rspc::internal::specta::DefOpts) -> rspc::internal::specta::DataType {
panic!()
}
}
For SpectaCompatibleNaiveDate
, it's working flawlessly. However for SpectaCompatibleBigDecimal
, it returned String to the client instead of a number value. Here's the JSON response being returned.
[
{
"type": "response",
"id": null,
"result": [
{
"amount": "118.00",
"category": "Tithe",
"comment": "Any comments",
"date": "2022-08-06",
"id": 4,
"name": "August Tithe",
"tags": "Touch n' Go"
}
]
}
]