Rust & MySQL: JSON serialisation of result sets retrieved using crate sqlx.
We run a MySQL stored procedure which returns a result set which has date columns. Using crates serde and serde_json, we serialise this result set into a JSON array of objects, whereby date columns are in Australian date format of dd/mm/yyyy.
 |
|---|
| Rust & MySQL: JSON serialisation of result sets retrieved using crate sqlx. |
This post is an extension of Rust & MySQL: connect, execute SQL statements and stored procs using crate sqlx. We’ll use the same Oracle Corporation MySQL test database, the same employees table and the same get_employees stored procedure.
To recap, the employees table has the following structure:
CREATE TABLE `employees` (
`emp_no` int NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL,
`last_name` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
`gender` enum('M','F') COLLATE utf8mb4_unicode_ci NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
And the get_employees stored procedure is:
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `get_employees`( pmLastName varchar(16), pmFirstName varchar(14) )
READS SQL DATA
begin
select * from employees e where (e.last_name like pmLastName)
and (e.first_name like pmFirstName) order by e.emp_no;
end$$
DELIMITER ;
The code is an extension of the final version of the code in Select data via running a stored procedure section of the previously mentioned post:
🚀 In this post, instead of manually formatting and printing each row of data, we serialise the entire result set into JSON and printing the JSON data out in a single operation.
The updated dependencies of the Cargo.toml file used in this post:
...
[dependencies]
async-std = "1.12.0"
sqlx = {version = "0.7", default-features = false, features = ["runtime-async-std", "macros", "mysql", "time"]}
time = {version = "0.3.22", default-features = false, features = ["formatting", "macros", "serde"]}
serde = {version = "1.0.188", features = ["derive"]}
serde_json = "1.0.107"
● For time crate, we add serde crate feature, so that we can do date serialising.
● We add two more crates for serialising and deserialising: serde and serde_json.
The complete working example is presented below.
Content of src/main.rs:
use sqlx::{FromRow, Pool, MySql, Error, MySqlPool, Row};
use sqlx::types::time::Date;
use async_std::task;
use serde::Serialize;
#[derive(FromRow, Serialize)]
struct Employee {
emp_no: i32,
#[serde(with = "my_date_format")]
birth_date: Date,
first_name: String,
last_name: String,
gender: String,
#[serde(with = "my_date_format")]
hire_date: Date,
}
mod my_date_format {
use sqlx::types::time::Date;
use time::macros::format_description;
use serde::{self, Serializer};
pub fn serialize<S>(
date: &Date,
serializer: S,
) -> Result<S::Ok, S::Error>
where
S: Serializer,
{
let format = format_description!("[day]/[month]/[year]");
let s = &date.format(&format).unwrap();
serializer.serialize_str(&s)
}
}
async fn connect() -> Result<Pool<MySql>, Error> {
return MySqlPool::connect("mysql://root:pcb.2176310315865259@localhost:3306/employees").await;
}
async fn do_run_stored_proc() {
let result = task::block_on(connect());
match result {
Err(err) => {
println!("Cannot connect to database [{}]", err.to_string());
}
Ok(pool) => {
let query_result = sqlx::query("call get_employees(?, ?)")
.bind("%chi").bind("%ak")
.map(|row: sqlx::mysql::MySqlRow| {
Employee {
emp_no: row.get(0),
birth_date: row.get(1),
first_name: row.get(2),
last_name: row.get(3),
gender: row.get(4),
hire_date: row.get(5)
}
})
.fetch_all(&pool).await.unwrap();
let json = serde_json::to_string_pretty(&query_result).unwrap();
println!("{}", json);
}
}
}
fn main() {
task::block_on(do_run_stored_proc());
}
Let’s walk through the code:
● Trait serde::Serialize – the struct Employee needs to implement this trait, so that it can be serialised.
● This documentation page Date in a custom format from crate serde provides an example for date time serialisation and deserialisation. The mod my_date_format above comes from this example, but I implement only the serialisation part.
The date format in pub fn serialize<S> has been discussed in the previous mentioned post, and in detail in Rust: baby step – some preliminary look at date.
● The derive macro helper attribute #[serde(with = "my_date_format")] also comes from Date in a custom format. Through trial and error, I’ve found out that it needs to be added to above all fields which need to be serialised.
● let json = serde_json::to_string_pretty(&query_result).unwrap(); is also from Date in a custom format. Although the use of the variable query_result is trial and error… and by sheer luck, I have it working the first time round. Originally I thought of extracting each row into a vector, then serialise the vector: but that is too much additional work. If you asked me why query_result works in this case, I would not be able to explain! Hopefully, I will come to understand this in the future.
Please note: the example code has been tested on both Windows 10 and Ubuntu 22.10.
The following screenshot shows the output of the above example:
One final point, we look at deleting and inserting data in Rust & MySQL: delete, insert data using crate sqlx, also using the employees table. The updated struct Employee in this post would still work in the just mentioned post. This is because when we manually create an instance of struct Employee, we have the two date columns in the correct format, hence no deserialisation is required:
...
birth_date: date!(1999-11-24),
hire_date: date!(2022-04-29)
...
Thank you for reading. I hope you find this post useful and stay safe as always.
✿✿✿
Feature image source: