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:

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:

Design a site like this with WordPress.com
Get started