Rust & MySQL: connect, execute SQL statements and stored procs using crate sqlx.

#rust #mysql #sqlx

We'll discuss: ⓵ how to connect to a MySQL server, ⓶ run queries to select some data and display returned data, ⓷ finally, execute stored procedures which return a single dataset.

First, please let me state that I'm aware of at least three (3) different crates for MySQL: ⓵ mysql, ⓶ sqlx, and ⓷ Diesel.

I did look at mysql initially. Then I started checking other crates. Diesel is an Object Relation Model (ORM), I'm not yet keen on taking on the complication of learning ORM, I give this crate a pass in the meantime.

According to the documentation, crate sqlx is implemented in Rust, and it's database agnostic: it supports PostgreSQL, MySQL, SQLite, and MSSQL.

-- It sounds enticing 😂... We learn one crate for several database servers. The learning process is tough for me. The Rust standard library gives examples. This crate lacks that... It takes a long time for me to be able to write the example code in this post, with help from the Rust Users Community Forum.

The database used in this post is the Oracle Corporation MySQL test data; it's also been used in the following posts:

In this post, we use the employees table, whose structure is:

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;

Cargo.toml is common for all examples. Its dependencies section is as follow:

...
[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"]}

Crate async-std is required as stated in crate sqlx's documentation.

On sqlx crate features macros and time. Crate feature macros is required to make the constraint FromRow available for the derive attribute. It took me a while to figure this one out, the documentation does not seem to mention it. Crate feature time must be enabled, otherwise sqlx::types::time::Date would not work: I think it's crate time that sqlx uses. Although I could not find any documentation to back this up.

We've discussed crate time in this post Rust: baby step -- some preliminary look at date.

-- Without explicitly including crate time, and enable crate features, formatting and macros, I can't use date formatting function. I'm unsure of how this relates to sqlx::types::time::Date. So please keep this point in mind, there might be a better alternative.

Please note: I only tested these examples on Windows 10.

❶ Establishing a MySQL server database connection. Based on Struct sqlx::MySqlConnection, and examples given in async-std and launchbadge/sqlx, I came up with the following example:

Content of src/main.rs:

use sqlx::{Pool, MySql, Error, MySqlPool};

use async_std::task;

async fn connect() -> Result<Pool<MySql>, Error> {
    return MySqlPool::connect("mysql://root:pcb.2176310315865259@localhost:3306/employees").await;
}

async fn do_test_connection() {
    let result = task::block_on(connect());

    match result {
        Err(err) => {
            println!("Cannot connect to database [{}]", err.to_string());
        }        

        Ok(_) => {
            println!("Connected to database successfully.");
        }
    }
}

fn main() {
    task::block_on(do_test_connection());
}

❷ Select data using a SQL statement. In addition to the references quoted in Establishing a MySQL server database connection above, the following posts help me write this example:

Content of src/main.rs:

use sqlx::{FromRow, Pool, MySql, Error, MySqlPool};
use sqlx::types::time::Date;
use time::macros::format_description;

use async_std::task;

#[derive(FromRow)]
struct Employee {
    emp_no: i32,
    birth_date: Date,
    first_name: String,
    last_name: String,    
    gender: String,
    hire_date: Date,
}

async fn connect() -> Result<Pool<MySql>, Error> {
    return MySqlPool::connect("mysql://root:pcb.2176310315865259@localhost:3306/employees").await;
}

async fn do_run_query() {
    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_as::<_, Employee>("select * from employees where emp_no <= 10010")
                .fetch_all(&pool).await.unwrap();

            println!("Number of Employees selected: {}", query_result.len());

            let format = format_description!("[day]/[month]/[year]");

            for (rindex, employee) in query_result.iter().enumerate() {
                println!("{}. No.: {}, Birth Date: {}, First Name: {}, Last Name: {}, Gender: {}, Hire Date: {}", 
                    rindex+1,
                    &employee.emp_no,
                    &employee.birth_date.format(&format).unwrap(),
                    &employee.first_name,
                    &employee.last_name,
                    &employee.gender,
                    &employee.hire_date.format(&format).unwrap());
            }
        }
    }
}

fn main() {
    task::block_on(do_run_query());
}

The fields in struct Employee match table employees's exactly. I think the code is self-explanatory, please see the relevant documentation for detail, I can't explain better than the official crates' documentation.

❸ Select data via running a stored procedure. The stored procedure is simple:

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 ;

Within MySQL Workbench, it can be called with:

call get_employees('%chi', '%ak'); 
call get_employees('%CHI', '%AK');

I could not find any example or documentation on how to call stored procedures. So willy-nilly, instead of a query, as seen in Select data using a SQL statement above, I pass in the stored procedure call:

            ...
            let query_result = sqlx::query_as::<_, Employee>("call get_employees('%chi', '%ak')")
                //.bind("%chi").bind("%ak")
                .fetch_all(&pool).await.unwrap();

            println!("Number of Employees selected: {}", query_result.len());
            ...

It panics with:

F:\rust\sqlx>set RUST_BACKTRACE=1

F:\rust\sqlx>target\debug\learn_sqlx.exe
thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: ColumnNotFound("emp_no")', src\main.rs:32:41
stack backtrace:
...

I asked for help: How to call a MySQL stored proc using crate sqlx? It seems that crate sqlx has some unresolved bug -- accessing resultant dataset column by name would cause a panic.

My first attempt based on the suggested solution: access the resultant dataset column by index, and copy column values into individual variables, then (process and) display these variables:

Content of src/main.rs:

use sqlx::{Pool, MySql, Error, MySqlPool, Row};
use sqlx::types::time::Date;
use time::macros::format_description;

use async_std::task;

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")
                .fetch_all(&pool).await.unwrap();

            println!("Number of Employees selected: {}", query_result.len());

            let format = format_description!("[day]/[month]/[year]");

            for (rindex, row) in query_result.iter().enumerate() {
                let emp_no: i32 = row.get(0);
                let birth_date: Date = row.get(1);
                let first_name: String = row.get(2);
                let last_name: String = row.get(3);
                let gender: String = row.get(4);
                let hire_date: Date = row.get(5);

                println!("{}. No.: {}, Birth Date: {}, First Name: {}, Last Name: {}, Gender: {}, Hire Date: {}", 
                    rindex+1,
                    emp_no,
                    birth_date.format(&format).unwrap(),
                    first_name,
                    last_name,
                    gender,
                    hire_date.format(&format).unwrap());
            }
        }
    }
}

fn main() {
    task::block_on(do_run_stored_proc());
}

Based on the following posts:

My second attempt at calling the stored procedure and manually map to struct Employee:

Content of src/main.rs:

use sqlx::{FromRow, Pool, MySql, Error, MySqlPool, Row};
use sqlx::types::time::Date;
use time::macros::format_description;

use async_std::task;

#[derive(FromRow)]
struct Employee {
    emp_no: i32,
    birth_date: Date,
    first_name: String,
    last_name: String,    
    gender: String,
    hire_date: Date,
}

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();

            println!("Number of Employees selected: {}", query_result.len());

            let format = format_description!("[day]/[month]/[year]");

            for (rindex, employee) in query_result.iter().enumerate() {
                println!("{}. No.: {}, Birth Date: {}, First Name: {}, Last Name: {}, Gender: {}, Hire Date: {}", 
                    rindex+1,
                    &employee.emp_no,
                    &employee.birth_date.format(&format).unwrap(),
                    &employee.first_name,
                    &employee.last_name,
                    &employee.gender,
                    &employee.hire_date.format(&format).unwrap());
            }
        }
    }
}

fn main() {
    task::block_on(do_run_stored_proc());
}

Crate sqlx is very large, it'd take a lot of time to be fluent in this crate. This is my first step. It takes a lot of time to write these simple examples: Rust is certainly tougher to learn than Python!

I'm writing this post to have a record of my progress. But I do hope some newcomers find it helpful and useful. Thank you for reading and stay safe as always.

✿✿✿

Feature image source: