Using the customized struct to map the SQL query result with Rust Diesel
Sometimes we need to use Diesel1 to do raw SQL query and map the result to a customized struct. To show the usage of this scenario, I have provided an example here:
As the above PR shown, the query is like this:
let q = format!("SELECT UPPER(username) as upper_username, meta, LENGTH(username) as len_username FROM users");
As we can see, the sql query returns some fields that are not corresponding to the table structure, but we want to map the result to a Rust struct. To do so, we can define a customized rust to do the mapping:
#[derive(QueryableByName, Default, Serialize, Deserialize, Debug, Clone, PartialEq)]
pub struct SqlUser {
#[diesel(sql_type = diesel::sql_types::Varchar)]
upper_username: String,
#[diesel(sql_type = diesel::sql_types::Nullable < diesel::sql_types::Jsonb >)]
meta: Option<Meta>,
#[diesel(sql_type = diesel::sql_types::Int4)]
len_username: i32,
}
As the above code shown, we need to derive the structure with the QueryableByName macro. Here is part of the doc of QueryableByName:
Implements QueryableByName for untyped sql queries, such as that one generated by sql_query To derive this trait, Diesel needs to know the SQL type of each field. It can get the data from the corresponding table type. It uses the snake_case type name with an added s. It is possible to change this default by using #[diesel(table_name = something)]. If you define use the table type, the SQL type will be diesel::dsl::SqlTypeOf<table_name::column_name>. In cases which there are no table type, you can do the same by annotating each field with #[diesel(sql_type = SomeType)].
As the doc says, we need to define the sql_type of each field manually for the Diesel to understand how to map the result to the Rust type, and by default we need to keep the field name same within the returned column names in SQL query.
To run the example, start the project firstly:
➤ cargo run 01:04:45
Finished dev [unoptimized + debuginfo] target(s) in 0.11s
Running `target/debug/axum-playground`
2024-02-24 01:04:48.286 DEBUG main src/main.rs:821: listening on 127.0.0.1:3000
And then access the service like this:
➤ curl http://localhost:3000/find_all_sql_users 00:54:46
Here is the reformatted response of the above request:
{
"r": true,
"d": [
{
"upper_username": "LIWEINAN42",
"meta": {
"meta": null,
"data": {
"foo": "1",
"bar": "1"
}
},
"len_username": 10
},
{
"upper_username": "LIWEINAN67",
"meta": {
"meta": null,
"data": {
"bar": "1",
"foo": "1"
}
},
"len_username": 10
}
],
"e": null
}
Above is the description of this feature usage of Diesel.