How To Connect MySQL To Rust?

Introduction

The current and powerful programming language Rust is renowned for its performance, security, and speed. In contrast, the widely used open-source relational database management system MySQL is utilized to store and retrieve data. If you're developing applications that need a database backend, knowing how to connect MySQL to Rust can be helpful. In this article, we'll look at utilizing the MySQL crate to connect MySQL to Rust.

Prerequisites

You must have Rust and MySQL installed on your computer before we can begin. If you are new to Rust, then I will recommend you first check out the official Doc. To test your connection, you must also build a MySQL database. Before using Rust to connect to a MySQL database, make sure you have the following.

Your system has installed Rust, if not, download it from the official Rust website and go to https://www.rust-lang.org/tools/install.
A MySQL server is installed on your computer or a remote server, if not, then download it from the official MySQL website, and go to https://www.mysql.com/.
A Rust-based MySQL client library. We'll be utilizing the MySQL crate in this tutorial, which you can include in your project using Cargo.

Create a new project with Cargo

Let's create a new project using cargo named rust_to_mysql and navigate to the project by using the following commands.

$ cargo new rust_to_mysql
$ cd rust_to_mysql

And now your project is created. Now navigate to Cargo.toml file and add the dependencies as described below.

Adding the MySQL crate

To connect to MySQL with Rust, we'll use the MySQL crate, which is a MySQL client library for Rust. To install the MySQL crate, you can add it to your project's Cargo.toml file as follows.

[dependencies]
mysql = "20.0.2"

After including the MySQL crate as a dependency for your project, you can use cargo build to download and install the crate.

Connecting to MySQL

We'll utilize a connection pool to establish a connection to MySQL using the MySQL crate. A connection pool is a group of database connections that several clients can use at once. An illustration of how to build a connection pool and obtain a connection is given below.

use mysql::*;

fn main(){
    let url = "mysql://username:password@localhost:3306/database_name";
    let pool = Pool::new(url).unwrap();
    let mut conn = pool.get_conn().unwrap();
    
}

Change username, password, and database name with your own MySQL login information, correspondingly. A new connection pool and the pool are both created using the Pool::new() function. The function gets conn() retrieves a connection from the pool.

Taking input from the user

To execute the queries, first, we take the input from the user, like username and password then we will create a login function that takes these inputs as parameters.

fn main(){
    let url = "mysql://username:password@localhost:3306/database_name";
    let pool = Pool::new(url)?;
    let mut conn = pool.get_conn()?;
    let url = "mysql://your_username:password@localhost:3306/db_name";
    let pool = Pool::new(url).unwrap();
    //creating a connection
    let mut conn = pool.get_conn().unwrap();

    let mut username = String::new();
    println!("Enter your employee id :");
    std::io::stdin().read_line(&mut username).unwrap();
    let empid: i64 = username.trim().parse().expect("username");
    println!("Employee ID:{}", empid);

    let mut pswrd = String::new();
    println!("Enter your password :");
    std::io::stdin().read_line(&mut pswrd).unwrap();
}

Create a structure

We will create a structure named Employee as required the data column we will retrieve from the employee table, you can create according to your DB table.

#[derive(Debug, PartialEq, Eq)]
struct Employee {
    employee_id: i64,
    employee_fname: String,
    employee_lname: String,
    employee_mail: String,
    password: String,
    user_type: i64,
}

Creating login function

We will create a login function that will take three arguments as cn for connection and username, and password, and we will check the validity of the user using if-else.

fn login(cn: &mut PooledConn, pasw: String, empid: i64) {
        let y=format!("select EmployeeID, EmployeeFirstName, EmployeeLastName, EmployeeEmail, password, UserTypeId from employee where EmployeeID= {}",empid);
        let res = cn
            .query_map(
                y,
                |(
                    employee_id,
                    employee_fname,
                    employee_lname,
                    employee_mail,
                    password,
                    user_type,
                )| Employee {
                    employee_id: employee_id,
                    employee_fname: employee_fname,
                    employee_lname: employee_lname,
                    employee_mail: employee_mail,
                    password: password,
                    user_type: user_type,
                },
            )
            .expect("Query failed.");
        let mut pass: String = String::new();
        let mut mail: String = String::new();
        let mut esid = 0;
        let mut name: String = String::new();
        let mut utid: i64 = 0;
        for r in res {
            pass = r.password;
            mail = r.employee_mail;
            esid = r.employee_id;
            name = r.employee_fname;
            utid = r.user_type;
        }


        if pasw.trim() == pass {
            println!("Welcome {}", name);
        } else {
            println!("Login failed");
        }
    }

Calling the main function

Now call the login() function in our main() function. Now our code will look as follows.

use mysql::prelude::*;
use mysql::*;

use std::time::SystemTime;

#[derive(Debug, PartialEq, Eq)]
struct Employee {
    employee_id: i64,
    employee_fname: String,
    employee_lname: String,
    employee_mail: String,
    password: String,
    user_type: i64,
}

fn main() {
    let url = "mysql://your_username:password@localhost:3306/db_name";
    let pool = Pool::new(url).unwrap();
    //creating a connection
    let mut conn = pool.get_conn().unwrap();

    let mut username = String::new();
    println!("Enter your employee id :");
    std::io::stdin().read_line(&mut username).unwrap();
    let empid: i64 = username.trim().parse().expect("username");
    println!("Employee ID:{}", empid);

    let mut pswrd = String::new();
    println!("Enter your password :");
    std::io::stdin().read_line(&mut pswrd).unwrap();

    login(&mut conn, pswrd, empid);
}

//retrieving data for login
 fn login(cn: &mut PooledConn, pasw: String, empid: i64) {
        let y=format!("select EmployeeID, EmployeeFirstName, EmployeeLastName, EmployeeEmail, password, UserTypeId from employee where EmployeeID= {}",empid);
        let res = cn
            .query_map(
                y,
                |(
                    employee_id,
                    employee_fname,
                    employee_lname,
                    employee_mail,
                    password,
                    user_type,
                )| Employee {
                    employee_id: employee_id,
                    employee_fname: employee_fname,
                    employee_lname: employee_lname,
                    employee_mail: employee_mail,
                    password: password,
                    user_type: user_type,
                },
            )
            .expect("Query failed.");
        let mut pass: String = String::new();
        let mut mail: String = String::new();
        let mut esid = 0;
        let mut name: String = String::new();
        let mut utid: i64 = 0;
        for r in res {
            pass = r.password;
            mail = r.employee_mail;
            esid = r.employee_id;
            name = r.employee_fname;
            utid = r.user_type;
        }


        if pasw.trim() == pass {
            println!("Welcome {}", name);
        } else {
            println!("Login failed");
        }
    }

Running our project

To run our project, navigate to our project directory and run the command cargo run.

Additional Points to Consider

Here are some more factors to keep in mind while using Rust to connect to a MySQL database:

Ensure that your MySQL server is operational and reachable. Make sure your firewall permits connections to port 3306 if you're connecting to a remote server.
To control connections to the database, use a connection pool. The performance will be enhanced, and connections will be correctly closed when they are no longer required.
Use prepared statements when running queries.
Handle errors correctly. You should use Rust's powerful error-handling system to deal with any errors that may arise when connecting to the database.

Conclusion

Because of the MySQL crate, connecting MySQL to Rust is a simple operation. You should be able to connect to a MySQL database and run queries with ease by following the instructions provided in this article. Do not forget that the MySQL crate has many more features than what we have discussed here, like transactions, prepared statements, and more.