Rust & MySQL: delete, insert data using crate sqlx.

We'll look at: how to delete data from and insert data into MySQL tables using crate sqlx.

This post is a continuation of Rust & MySQL: connect, execute SQL statements and stored procs using crate sqlx. We'll use the same Oracle Corporation MySQL test data database. We'll also use the employees table. To recap, its 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;

And also the stored procedure:

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 ;

Please note: the example code has been tested on both Windows 10 and Ubuntu 22.10.

❀❀❀

The followings are the references used to develop the code for this post:

• Module sqlx::query
• Struct sqlx::query::Query
• Inserting a struct into an sqlite db using sqlx and rust

Cargo.toml is also identical to the one used in the previous quoted post. 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"]}

The example code is simple. We delete the employee whose number is 600000 from the database. If the deletion was successful, we would insert a new employee whose number is 600000. Finally, if the addition was successful, we would retrieve the just inserted employee by calling the stored procedure get_employees(...), with partial last name and partial first name of the just inserted employee.

The complete working example is presented below.

Content of src/main.rs:

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

use async_std::task;

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

const TEST_EMP_NO: i32 = 600000; // Last emp_no in database is 500113.

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

async fn do_delete(pool: &sqlx::Pool<MySql>, emp_no: i32) -> bool {
    let result = sqlx::query("delete from employees where emp_no = ?")
        .bind(emp_no)
        .execute(pool).await;

    match result {
        Err(e) => {
            println!("Error deleting employee: {}\n", e.to_string());
            return false;
        }

        Ok(res) => {
            println!("Employee number: {} has been deleted.", emp_no);
            println!("Number of Employees deleted: {}", res.rows_affected());            
        }
    }

    true
}

async fn do_insert(pool: &sqlx::Pool<MySql>, emp: &Employee) -> bool {
    let result = sqlx::query(
        "insert into employees (
            emp_no, 
            birth_date, 
            first_name, 
            last_name, 
            gender, 
            hire_date) 
        values (?, ?, ?, ?, ?, ?)")
        .bind(&emp.emp_no)
        .bind(&emp.birth_date)
        .bind(&emp.first_name)
        .bind(&emp.last_name)
        .bind(&emp.gender)
        .bind(&emp.hire_date)
        .execute(pool).await;

    match result {
        Err(e) => {
            println!("Error inserting employee: {:#?}", emp);
            println!("Error message: [{}].\n", e.to_string());
            return false;
        }

        Ok(res) => {
            println!("Employee has been inserted.");
            println!("Number of employees inserted: {}", res.rows_affected());
        }
    }

    true
}

async fn do_query(pool: &sqlx::Pool<MySql>, last_name: &str, first_name: &str) {
    let result = sqlx::query("call get_employees(?, ?)")
    .bind(last_name)
    .bind(first_name)
    .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;

    match result {
        Err(e) => {
            println!("Error select employee with last name: {}, first name: {}", last_name, first_name);
            println!("Error message: [{}].\n", e.to_string());
        }

        Ok(query_result) => {
            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());
            }
        }
    }
}

async fn do_delete_insert_data() {
    let result: Result<sqlx::Pool<sqlx::MySql>, sqlx::Error> = task::block_on(connect());

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

        Ok(pool) => {
            if !task::block_on(do_delete(&pool, TEST_EMP_NO)) {
                panic!("Failed to delete test employee.");
            }

            if !task::block_on(do_insert(&pool, &Employee {
                emp_no: TEST_EMP_NO,
                birth_date: date!(1999-11-24),
                first_name: String::from("Bé Hai"),
                last_name: String::from("Nguyễn"),
                gender: String::from("M"),
                hire_date: date!(2022-04-29)
            })) {
                panic!("Failed to insert test employee.");
            }

            task::block_on(do_query(&pool, "%uyễn", "%é H%"));
        }
    }
}

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

Some of the code should be familiar, based on the last mentioned post above. We'll go over the new code.

• Method do_delete(pool: &sqlx::Pool<MySql>, emp_no: i32) -> bool is where test record deletion takes place. We call sqlx::query with a parameterised delete SQL statement, this call returns struct sqlx::query::Query. We then call its bind(...) method to pass the value of do_delete(...)'s parameter emp_no to SQL statement parameter. We then chained-call to the execute(...) method to run the delete SQL statement. If the deletion fails, we return false otherwise true.
• Method do_insert(pool: &sqlx::Pool<MySql>, emp: &Employee) -> bool is where test record insertion takes place. Its internal working is pretty much identical to do_delete(...).
• Method do_query(pool: &sqlx::Pool<MySql>, last_name: &str, first_name: &str) is a just a refactored version of the last example in the above mentioned post.
• Method do_delete_insert_data() should be self-explanatory.

I write this example code for my own understanding, and this post so that I will have something to go back to if I forgot how to do this 😂. It has been easier than the last one. I do hope it's useful for somebody. Thank you for reading and stay safe as always.

✿✿✿

Feature image source:

• https://www.omgubuntu.co.uk/2022/09/ubuntu-2210-kinetic-kudu-default-wallpaper
• https://in.pinterest.com/pin/337277459600111737/
• https://www.rust-lang.org/
• https://www.pngitem.com/download/ibmJoR_rust-language-hd-png-download/