Knowledgebase

Introduction

MySQL is a free and open-source relational database management system (RDBMS) that manages data stored in tables using Structured Query Language (SQL). In a relational database, data is organized into one or more data tables which may be related to each other, while an RDBMS is a piece of software that lets you create, update, and manage such databases.

In MySQL, data is organized into tables consisting of rows and columns, with each column representing a different piece of data, and each row representing a unique record. For instance, a table of customer information might have columns for names, phone numbers, social security numbers (SSN), and amount purchased; each row would represent a different customer with their values for those columns. SQL is used to create, modify, and query tables and other database objects, as well as insert, update and delete data stored in tables.

MySQL is commonly used in web applications such as e-commerce, data warehousing, or logging applications.

This guide covers how to build a Rust application connecting to a MySQL Rcs managed database.

Prerequisites

• Working knowledge of Rust and SQL.
• Properly installed Rust toolchain including cargo (Rust version >= 1.65).

Create the Managed Database

To create the Rcs managed MySQL database, recreate the following steps:

1. Log in to your Rcs account, and navigate to the Add Managed Database section.
2. Choose the MySQL database engine.
3. Select the Server Type from the options. The options include Cloud Compute, Cloud Compute High Performance - AMD or Intel, Optimized Cloud Compute - General Purpose, Storage, or Memory Optimized.
4. Select zero or more replica nodes. A replica node is the same server type and plan as the primary node. This guide opts for the Cloud Compute server type without any replica node for demonstration purposes.
5. Choose a server location.
6. Add a label for the database cluster.
7. Click on Deploy Now to create the cluster. It takes a few minutes for the cluster to be available, and the Status should be changed to Running.

The MySQL database is ready. Now you can connect to it using a Rust program.

Setting Up

Initialize the project crate using Cargo:

$ cargo new vmd_demo --bin

Navigate to the newly created project directory:

$ cd vmd_demo

The project directory should look like this:

.
|-- Cargo.toml
`-- src
   `-- main.rs

1 directory, 2 files

Open the Cargo.toml file, and add the following dependency:

mysql = "*"

This adds the mysql crate as a dependency of the project. The Cargo.toml should look like this:

[package]
name = "vmd_demo"
version = "0.1.0"
edition = "2021"

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

[dependencies]
mysql = "*"

Import Libraries

Open the main.rs file in the src/ directory, and overwrite its contents with the following import lines:

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

Define Data Structure

In this guide, CRUD (Create, Read, Update, Delete) operations will be performed on customer information against the database. Create a struct to hold customer information to be saved by adding the following lines:

#[derive(Debug, PartialEq, Eq)]
struct Customer {
    id: i32,
    name: String,
    amount: i32,
}

This struct represents customer entries in the database.

Initialize Customers Table in the MySQL Database

To create a customers table in the database, add the following function to initialize the table:

fn init_table(conn: &mut mysql::PooledConn) {
    // Drop table if it already exists
    conn.query_drop(
        r"DROP TABLE customers
        ").unwrap();

    // Create a table for customers.
    conn.query_drop(
        r"CREATE TABLE customers (
            id int not null,
            name text not null,
            amount int not null,
            PRIMARY KEY(id)
    )").unwrap();

    println!("Table created.");
}

The init_table function takes a mysql::PooledConn as an argument. SQL queries and statements are executed on the PooledConn object.

The query_drop method takes a text query as an argument, performs it, and drops the result. It is first used to drop the customers table if it already exists in the database.

Next, query_drop is used to create the customers table with the required columns; the id column is also used as the primary key for the table.

Adding Data to the customers Table

Create a function to insert row entries into the table:

fn insert_customers(conn: &mut mysql::PooledConn, customers: Vec<Customer>) {
    // Insert vector of customers to the database
    conn.exec_batch(
        r"INSERT INTO customers (id, name, amount)
        VALUES (:id, :name, :amount)",
        customers.iter().map(|customer| params! {
            "id" => customer.id,
            "name" => &customer.name,
            "amount" => customer.amount,
        })
    ).unwrap();

    println!("Inserted customer entries successfully.");
}

The insert_customers function takes two parameters - a PooledConn object and a vector of type Customer to be added to the database.

The exec_batch method takes two arguments, a statement to be executed and a params iterator. The statement is executed against each item in the given iterator. The named parameters in the query statement - (:id, :name, :amount), are replaced by the values caught in the iterator on each iteration. The params! macro is a convenient way to pass named parameters to a statement.

Updating Data in the customers Table

To update row entries in the table, add the following function:

fn update_customer(conn: &mut mysql::PooledConn, customer: &mut Customer) {
    // Look for the matching id and update the columns
    conn.exec_drop(
        r"UPDATE customers SET name=:name, amount=:amount WHERE id=:id",
        params! {
            "name" => &customer.name,
            "amount" => customer.amount,
            "id" => customer.id,
        }
    ).unwrap();

    println!("Successfully updated customer.");
}

The function takes a PooledConn object and a Customer whose column values are to be updated.

The exec_drop method takes two arguments - a statement to be executed and a Params type to pass named parameters to the query statement. exec_drop executes the given statement and drops the result. In this example, the customer with the matching id in the database is updated with the new name and amount fields.

Reading Data in the customers Table

To access the rows in the table, add the following lines:

fn read_customers(conn: &mut mysql::PooledConn) {
    // Read all database values
    let customers = conn.query_map(
            "SELECT id, name, amount from customers",
            |(id, name, amount)| {
                Customer { id, name, amount }
            },
        ).unwrap();

    println!("{:#?}", customers);
}

The query_map method takes a text query and a closure that maps each row of the first result set. The closure is used to capture the columns of the database rows into a Customer type struct and collects each row into a vector type. This vector contains each entry in the database represented as Customer types.

Deleting Data in the customers Table

To delete rows in the table:

fn delete_customer(conn: &mut mysql::PooledConn, customer_id: i32) {
    // Delete matching id entry
    conn.exec_drop(
        r"DELETE FROM customers WHERE id=:id",
            params! {
                "id" => customer_id,
            }
    ).unwrap();

    println!("Deleted customer with id - {}.", customer_id);
}

Here, the database is queried for a row with the corresponding id, if a match is found - the row is deleted from the table.

Connect to Rcs MySQL with Rust

After writing the helper functions, it's time to bring them all together in the main function.

Before proceeding, you need to get the connection details for the managed database to connect to it.

1. Navigate to the Managed Databases section.
2. Select the previously deployed database.
3. Navigate to the Connection Details section, and click the Copy MySQL URL icon to get the connection URL.

Next, add the following lines to the src/main.rs file:

fn main() {
    let url = "[paste-url-string-here]";
    let pool = Pool::new(url).unwrap();

    let mut conn = pool.get_conn().unwrap();

Save the copied URL string in a variable - url.

Pool::new is used to create a Pool, the connection is initiated and checked if alive using the get_conn method. When this connection is facilitated, queries and operations can then be run against it.

Add the following lines to run operations:

    // Initialize tables
    init_table(&mut conn);

    // Create vector of customer information to insert into the database
    let customers = vec![
            Customer { id: 1, name: "Gates Bill".to_string(), amount: 1000 },
            Customer { id: 2, name: "Buffer Warren".to_string(), amount: 999 },
            Customer { id: 3, name: "Musk Noel".to_string(), amount: 7 },
    ];

    // Insert customers into database
    insert_customers(&mut conn, customers);
    
    // Update entry
    let mut customer3 = Customer { id: 3, name: "Musk Elon".to_string(), amount: 876 };
    update_customer(&mut conn, &mut customer3);

    // Delete entry with matching id
    let customer_id = 2;
    delete_customer(&mut conn, customer_id);

    // Print list
    read_customers(&mut conn);
}

The above code simply makes use of the functions created earlier to perform database operations.

Final Code

For reference, the full code in the src/main.rs file:

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

#[derive(Debug, PartialEq, Eq)]
struct Customer {
    id: i32,
    name: String,
    amount: i32,
}

fn init_table(conn: &mut mysql::PooledConn) {
    // Drop table if it already exists
    conn.query_drop(
            r"DROP TABLE customers
        ").unwrap();

    // Create a table for customers.
    conn.query_drop(
        r"CREATE TABLE customers (
                id int not null,
                name text not null,
                amount int not null,
                PRIMARY KEY(id)
    )").unwrap();

    println!("Table created.");
}

fn insert_customers(conn: &mut mysql::PooledConn, customers: Vec<Customer>) {
    // Insert vector of customers to the database
    conn.exec_batch(
            r"INSERT INTO customers (id, name, amount)
            VALUES (:id, :name, :amount)",
            customers.iter().map(|customer| params! {
                    "id" => customer.id,
                    "name" => &customer.name,
                    "amount" => customer.amount,
            })
    ).unwrap();

    println!("Inserted customer entries successfully.");
}

fn update_customer(conn: &mut mysql::PooledConn, customer: &mut Customer) {
    // Look for the matching id and update the columns
    conn.exec_drop(
            r"UPDATE customers SET name=:name, amount=:amount WHERE id=:id",
            params! {
                    "name" => &customer.name,
                    "amount" => customer.amount,
                    "id" => customer.id,
            }
    ).unwrap();

    println!("Successfully updated customer.");
}

fn read_customers(conn: &mut mysql::PooledConn) {
    // Read all database values
    let customers = conn.query_map(
                "SELECT id, name, amount from customers",
                |(id, name, amount)| {
                    Customer { id, name, amount }
                },
        ).unwrap();

    println!("{:#?}", customers);
}

fn delete_customer(conn: &mut mysql::PooledConn, customer_id: i32) {
    // Delete matching id entry
    conn.exec_drop(
            r"DELETE FROM customers WHERE id=:id",
            params! {
                    "id" => customer_id,
            }
    ).unwrap();

println!("Deleted customer with id - {}.", customer_id);
}

fn main() {
    let url = "[paste-url-string-here]";
    let pool = Pool::new(url).unwrap();

    let mut conn = pool.get_conn().unwrap();

    // Initialize tables
    init_table(&mut conn);

    // Create vector of customer information to insert into the database
    let customers = vec![
            Customer { id: 1, name: "Gates Bill".to_string(), amount: 1000 },
            Customer { id: 2, name: "Buffer Warren".to_string(), amount: 999 },
            Customer { id: 3, name: "Musk Noel".to_string(), amount: 7 },
    ];

    // Insert customers into database
    insert_customers(&mut conn, customers);
    
    // Update entry
    let mut customer3 = Customer { id: 3, name: "Musk Elon".to_string(), amount: 876 };
    update_customer(&mut conn, &mut customer3);

    // Delete entry with matching id
    let customer_id = 2;
    delete_customer(&mut conn, customer_id);

    // Print list
    read_customers(&mut conn);
}

Running the Code

To run the code, enter the following command:

$ cargo run

You should see output similar to this:

Table created.
Inserted customer entries successfully.
Successfully updated customer.
Deleted customer with id - 2.
[
   Customer {
       id: 1,
       name: "Gates Bill",
       amount: 1000,
   },
   Customer {
       id: 3,
       name: "Musk Elon",
       amount: 876,
   },
]

This means the program ran successfully and all database operations on the MySQL Rcs Managed Database executed successfully.

Delete the Managed Database

While logged into your Rcs account, navigate to the Managed Databases section:

• For the database you created earlier, click the delete icon.
• In the Destroy Managed Database? pop-up window, select the checkbox Yes, destroy this Managed Database, and click on Destroy Managed Database.

Conclusion

This guide covered how to use Rcs's managed MySQL database in Rust.

To learn more about Rcs Managed Databases, you can refer to the following documentation:

• Rcs Managed Databases Quickstart.
• Rcs Managed Databases for MySQL Reference Guide.