Rust - Connect to MySQL

Last Updated on January 25, 2020

This post is about connecting to MySQL from Rust.

Requirements

Cargo
- A Rust package manager akin to Apache Maven
- It also gets installed when we install Rust with rustup-init.exe
A Rust crate called mysql
A running instance of MySQL
- See docker-compose.yml for MySQL

Database Table

We have this table structure for our codes and testing.

create table person
(
	person_id int auto_increment,
	person_name varchar(100) null,
	constraint person_pk
		primary key (person_id)
);

create table person

(

person_id int auto_increment,

person_name varchar(100) null,

constraint person_pk

primary key (person_id)

);

Sample data:

PERSON_ID	PERSON_NAME
1			Gary
2			Steve

PERSON_ID PERSON_NAME

1 Gary

2 Steve

Update Cargo.toml

Under the [dependencies] section, add the mysql = "16.1.0" :

[package]
name = "rust-project1"
version = "0.1.0"
authors = ["Karl San Gabriel"]
edition = "2018"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
mysql = "16.1.0"

[package]

name = "rust-project1"

version = "0.1.0"

authors = ["Karl San Gabriel"]

edition = "2018"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]

mysql = "16.1.0"

Rust Codes

Before we create the codes to connect to MySQL and retrieve data from it, we need to reference to MySQL crate as follows.

extern crate mysql;
...

1 2	extern crate mysql; ...

Then, we need to create a struct that resembles and represents the persons table.

...
#[derive(Debug, PartialEq, Eq)]
struct Person {
    person_id: i32,
    person_name: String
}
...

...

#[derive(Debug, PartialEq, Eq)]

struct Person {

person_id: i32,

person_name: String

}

...

The main function

In the main function, we first get a connection:

...
    let pool = mysql::Pool::new("mysql://root:a1128f69-e6f7-4e93-a2df-3d4db6030abc@localhost:3306/turretadb").unwrap();
...

...

let pool = mysql::Pool::new("mysql://root:a1128f69-e6f7-4e93-a2df-3d4db6030abc@localhost:3306/turretadb").unwrap();

...

Note that we are passing both the database user name and password.

With a database connection, we can now retrieve records from a table.

...
let all_persons: Vec<Person> =
        pool.prep_exec("SELECT person_id, person_name from person", ())

            .map(|result| {
                result.map(|x| x.unwrap()).map(|row| {
                    let (person_id, person_name) = mysql::from_row(row);

                    Person {
                        person_id,
                        person_name
                    }
                }).collect()
            }).unwrap(); // Unwrap `Vec<Person>`

    for person in all_persons.iter() {
        println!("{}: {}", person.person_id, person.person_name);
    }
...

...

let all_persons: Vec<Person> =

pool.prep_exec("SELECT person_id, person_name from person", ())

.map(|result| {

result.map(|x| x.unwrap()).map(|row| {

let (person_id, person_name) = mysql::from_row(row);

Person {

person_id,

person_name

}

}).collect()

}).unwrap(); // Unwrap `Vec<Person>`

for person in all_persons.iter() {

println!("{}: {}", person.person_id, person.person_name);

}

...

Output

When we run our application, we get the following output.

C:/Users/karldev/.cargo/bin/cargo.exe run --color=always --package rust-project1 --bin rust-project1
   Compiling rust-project1 v0.1.0 (C:\Users\karldev\Desktop\dev\blogs\rust\rust-project1)
    Finished dev [unoptimized + debuginfo] target(s) in 2.71s
     Running `target\debug\rust-project1.exe`
1: Gary
2: Steve

Process finished with exit code 0

C:/Users/karldev/.cargo/bin/cargo.exe run --color=always --package rust-project1 --bin rust-project1

Compiling rust-project1 v0.1.0 (C:\Users\karldev\Desktop\dev\blogs\rust\rust-project1)

Finished dev [unoptimized + debuginfo] target(s) in 2.71s

Running `target\debug\rust-project1.exe`

1: Gary

2: Steve

Process finished with exit code 0

Tested with Rust 1.37.0.