DEV Community

Cover image for Rust & MySQL: delete, insert data using crate sqlx.
Be Hai Nguyen
Be Hai Nguyen

Posted on

4

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;
Enter fullscreen mode Exit fullscreen mode

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 ;
Enter fullscreen mode Exit fullscreen mode

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"]}
Enter fullscreen mode Exit fullscreen mode

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:
Enter fullscreen mode Exit fullscreen mode
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());
}
Enter fullscreen mode Exit fullscreen mode

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:

Reinvent your career. Join DEV.

It takes one minute and is worth it for your career.

Get started

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay