How to use MySQL DML commands in Rust

Introduction

In this article, we will explore how to access MySQL from Rust. We will learn how to use all MySQL DML commands like select, insert, update, and delete in Rust.

Create an application package.

Create a new application.

cargo new temp-project

[package]
name = "temp_project"
version = "0.1.0"
edition = "2021"
# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

Build your project.

cargo build

Add these dependencies to the Cargo.toml file.

[dependencies]
mysql = "*"
chrono = "0.4"

We require the chrono crate to work with date and time columns.

Get Started

In the main.rs file, import the namespaces.

use mysql::prelude::*;
use mysql::*;
use chrono::prelude::*; //For date and time

MySQL connection in Rust programming language

Insert this code into the main() function. The URL of your connection may differ.

fn main() {
    let url = "mysql://root:root@localhost:3306/mcn"; //"mysql://UserName:Password@localhost:3306/DatabaseName"
    let pool = Pool::new(url).unwrap();
    let mut conn = pool.get_conn().unwrap();
}

The program starts by defining a url variable containing the database's connection information. In this case, it's a MySQL database located at localhost one port 3306, with the username and password set to, and the database name set to mcn.
Next, a new Pool instance is created by calling the new function on the Pool struct, passing in the url variable as an argument. The unwrap method is called on the result to extract the Pool value from the Result. If there is an error creating the pool, the program will panic.
Finally, a mutable reference to a database connection is obtained by calling the get_conn method on the Pool instance. The unwrap the method is called on the result to extract the connection value. The program will panic if there was an error in obtaining a connection.

Run the code at this point to make sure you can open a connection.

cargo run

Create a table

In this article, we will use a Stock table with the schema shown below

The Structure

fn show_stock(cn:&mut PooledConn)
    {
        let qr = format!("create table Stock
        ItemId INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
        ItemName VARCHAR(128) NOT NULL,categoryId int,
        PRIMARY KEY(ItemId),FOREIGN KEY (categoryId) REFERENCES category
        (categoryId)");
        }

This code snippet defines the show_stock Rust method, which accepts a mutable reference to a PooledConn struct as an input. The PooledConn struct represents a pooled database connection capable of running SQL queries.
The format! Macro is used to generate a SQL query within the show_stock function. The query generates a new Stock table with three columns: ItemId, ItemName, and categoryId. The ItemName column has the data type VARCHAR(128) and is designated as NOT NULL. The ItemId column is an INTEGER data type and serves as the table's primary key. It also has the AUTO_INCREMENT attribute, which indicates that its value is automatically incremented when a new row is added to the database.
The categoryId column is an int data type indicated as a foreign key referencing the category table's categoryId column.
However, the SQL query is merely created as a string in this code snippet. It does not run on the database. To run the query, invoke the execute method on the PooledConn struct, handing in the SQL query text as input.

How to insert data in the stock table using Rust?

The params macro simplifies the supply of named parameter values. Drop in exec drop() indicates that no result data is returned. This is sufficient for inserting, updating, and deleting SQL.
If you have a lot of inserts, compiling the SQL as a prepared statement will save you time.

fn insert_stock(cn:&mut PooledConn)
{
    let mut itemname = String::new();
    println!("Enter the name of the item:");
    std::io::stdin().read_line(&mut itemname).unwrap();

    let mut qt = String::new();
    println!("Enter the quantity:");
    std::io::stdin().read_line(&mut qt).unwrap();
    let quantity:i64 = qt.trim().parse().expect("enter valid quantity");

    let mut cid = String::new();
    println!("Enter the category id:");
    std::io::stdin().read_line(&mut cid).unwrap();
    let categoryid:i64 = cid.trim().parse().expect("enter valid category id");

    let query="INSERT INTO stock (ItemName,quantity,categoryId) values (:itemname, :quantity, :categoryid)";
    let params=params!{"itemname"=>itemname, "quantity"=>quantity,"categoryid"=>categoryid,};
    cn.exec_drop(query,params).unwrap();
}

This code introduces a Rust function called insert_stock, which accepts a mutable reference to a PooledConn struct as input. The PooledConn struct represents a pooled database connection capable of running SQL queries.
Using println! and std::io::stdin(), the user is invited to provide the name of an item, its quantity, and its category ID within the insert_stock method. The read_line() and write_line() functions. The input values are then read as strings into the item name, qt, and cid variables.
The qt and cid strings are then parsed into i64 integers by removing leading and trailing whitespace with the trim method and converting the string to an integer with the parse method. If the conversion fails, the program will report an error.
Then comes a SQL query used to define the SQL query string. The query inserts a new row into the stock table using named parameters, with values for the ItemName, quantity, and categoryId columns. The params! The macro generates a Params struct with the values of the named parameters. The Params struct is then supplied as a parameter to the PooledConn struct's exec_drop method, which performs the SQL query and drops the result. The unwrap technique is used to extract the result, and if the query was executed incorrectly, the program would panic.

How to select data from the MySQL database?

Here I am applying an inner join between two tables to fetch the data from the database. Append this code to the main() function.

fn show_stock(cn:&mut PooledConn)
    {
    let qr = format!("select ItemId, ItemName, stock.categoryId, categoryName, quantity from stock inner join category on stock.categoryId = category.categoryId");
        let res:Vec<(i64,String,i64, String, i64)> = cn.query(qr).unwrap();
    for r in res {
        println!("Item ID:{} | Item Name:{} | Category Id:{} | Category Name:{} | Quantity:{}", r.0, r.1, r.2,r.3, r.4);
    }
    }

This code sample defines the show_stock Rust method, which accepts a mutable reference to a PooledConn struct as an argument. The PooledConn struct represents a pooled database connection capable of running SQL queries.
The format! macro is used to generate a SQL query within the show_stock function. The query uses an inner join to select five columns from two tables: ItemId, ItemName, categoryId, categoryName, and quantity. The stock table is used to pick the ItemId, ItemName, and categoryId columns, while the category table is used to select the categoryName field. To connect the two tables, the categoryId column is used as a join condition.
The query method on the PooledConn struct is called, with the SQL query text as an input. The procedure yields

Perform a Streamed Query

Streamed queries read the result data row by row. This is useful if you need to read large amounts of data for exporting or other purposes. The entire data set is never kept in memory.
Append this code to the main() function.

conn.query_iter("select ItemId, ItemName from stock")
.unwrap()
.for_each(|row| {
let r:(i32, String) = from_row(row.unwrap());
println!("{}, {:?}", r.0, r.1);
});

The row is of the type MySQL common::row::Row in this case. This type transports data in the form of bytes at the most basic level. The form row() function converts bytes into more user-friendly formats such as i32 and String. The converted data is returned as a tuple with the items in the same order as the query columns.

Gather Query Results

The query result can be collected in a vector. The vector's items are all tuples.

let res:Vec<(i64,String,i64, String, i64)> = cn.query(qr).unwrap();
    
    for r in res {
        println!("Item ID:{} | Item Name:{} | Category Id:{} | Category Name:{} | Quantity:{}", r.0, r.1, r.2,r.3, r.4);
    }

We don't have to do anything because the query() function converts low-level bytes to our desired data types. We had to specify the tuple's data type explicitly. Otherwise, the compiler has no way of knowing.

Convert the outcome to structured data

Working with tuples is not a bad thing. However, for a real-world application, you might want to define a struct to model each row in the result. We'll do it right now.
Add a struct.

struct Employee{
    employee_id:i64,
    employee_fname:String,
    employee_lname:String,
    employee_mail:String,
    password:String,
    user_type:i64
 }

 fn show_all(cn:&mut PooledConn)
   {
    let res:Vec<(i64, String, String,String,String,String,i64,String,i64,i64)> = cn.query("select * from employee")
 .unwrap();
 for r in res {
 println!("Employee ID:{} |First Name:{} |Last Name:{} |Email:{} |Mobile No:{} |Address:{} |Salary:{} |Password:{} |Department ID:{} |User Type ID:{}", r.0, r.1, r.2, r.3, r.4,r.5,r.6,r.7,r.8,r.9);
    }
   }

The cool thing is that we didn't have to specify the data type of the tuple. The compiler deduced it from the data types of the fields of the Product.

Delete and Update

These are comparable to insert.

let stmt = conn.prep("update stock set ItemName=:itemname,quantity=:quantity  where ItemId=:ItemId")
.unwrap();
conn.exec_drop(&stmt, params! {
"ItemId" => 6,
"itemname" => "P1111",
"quantity" => 5,
}).unwrap();

Using the conn database connection object, this code snippet updates a record in the stock table. First, a prepared statement is constructed by calling the conn object's prep method, which accepts a string argument containing a SQL query with named arguments. The query in this scenario is an UPDATE statement that updates the ItemName and quantity columns for a row with a certain ItemId value.
The prepared statement is stored in the variable stmt. If the statement preparation is successful, the statement object is extracted from the Result object returned by prep using unwrap().
The prepared statement object and params! macro calls are then passed to the exec_drop method on the conn object.
macro parameters create a Params object with named parameter values for the named parameters used in the SQL query string.
In this case, the ItemId parameter is set to 6, the itemname parameter is set to "P1111", and the quantity parameter is set to 5. The exec_drop method executes the SQL query with the provided parameter values and returns a Result object that can be unwrapped to extract the number of affected rows.
This code snippet updates the ItemName and quantity columns of a row in the stock table with an ItemId value of 6 to new values of "P1111" and 5, respectively.

Delete the data on the stock table

let stmt = conn.prep("delete from stock where ItemId=:ItemId").unwrap();
conn.exec_drop(&stmt, params! {
"ItemId" => 6,
}).unwrap();

This code defines deletes a row from the stock table using the conn database connection object. First, a prepared statement is created using the prep method of the conn object, which takes a string argument containing a SQL query with named parameters. In this case, the query is a DELETE statement that removes a row with a specific ItemId value.
The prepared statement is assigned to a variable called stmt. If the statement preparation is successful, unwrap() is called to extract the statement object from the Result object returned by prep.
The exec_drop method is then called on the conn object with the prepared statement object and params! macro call as arguments. The params! The macro creates a Params object with named parameter values for the named parameters used in the SQL query string.
In this case, the ItemId parameter is set to 6. The exec_drop method executes the SQL query with the provided parameter value and returns a Result object that can be unwrapped to extract the number of affected rows.
This code snippet deletes a row from the stock table with an ItemId value of 6.

Summary

We learned how to insert, update, and delete in this section. This should cover the most common data types used in database programming. I love how it converts data to any type I am working with Rust. Here we used i32, String, and NaieveDate.