This post shows how to use Rust Rocket SQLX for MySQL for basic CRUD (Create, Retrieve, Update, and Delete) operations.
Prerequisites For Building the Rust Application Based on Rocket
We used the following items for this post.
- Rust Stable – 1.64.0
- Rocket 0.5.0-rc.2
- Windows 10
- Docker For Windows
- MySQL 8
- IntelliJ IDEA 2022.2.3 (Ultimate Edition)
- Rust Plugin for IntelliJ
Before running Rust Rocket SQLX codes against MySQL 8, we need to start an instance of that database, which we can have in the form of a Docker container. For more information, please see docker-compose.yml for MySQL For Local Development. Also, we will use the same database credentials in this Rust application.
Essential Crate Dependencies for Rust, Rocket, and SQLX
Although we are using a release candidate of Rocket, the final release (0.5) would still be compatible with our codes. We will tweak the codes if we need to adjust something. Generally, the codes would still work.
Our Cargo.toml file has the following dependencies.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [package] name = "alerto" version = "0.1.0" edition = "2021" [dependencies] sqlx = "0.5.13" rocket = { version = "0.5.0-rc.2", features = ["json"] } validator = { version = "0.16.0", features = ["derive"] } [dependencies.rocket_db_pools] version = "0.1.0-rc.2" features = ["sqlx_mysql"] |
We need a Rocket.toml For Rocket and SQLX
To work with SQLX within Rocket, we need the specify the database properties in another file – Rocket.toml. We can specify the connection string, database credentials, and connection-related properties in this file.
1 2 3 4 5 6 7 | [default.databases.alertodb] url = "mysql://alerto:0c3b2750-8b3e-435c-8b4e-4b920ec391c1@localhost:3310/alertodb?allowPublicKeyRetrieval=true&useSSL=false" min_connections = 64 max_connections = 1024 connect_timeout = 5 idle_timeout = 120 |
Next, we need to create the following struct and label it “alertodb” using the database attribute.
1 2 3 4 5 | use rocket_db_pools::{Database}; #[derive(Database)] #[database("alertodb")] pub struct AlertoDB(sqlx::MySqlPool); |
Then, use that struct in our main function to invoke the init function.
1 2 3 4 5 6 7 8 9 10 11 | #[macro_use] extern crate rocket; use rocket_db_pools::{Database}; use crate::alerto_common::alerto_common::{AlertoDB}; use crate::alerto_controller::tenant_controller::tenant_create; #[launch] fn rocket() -> _ { rocket::build().attach(AlertoDB::init()) .mount("/tenants", routes![tenant_create]) } |
Rust Rocket HTTP Handlers with SQLX Database Connection
Where do we get our database connection? We must pass it as a parameter to our HTTP handler functions to work with a database connection. Consider the following codes.
1 2 3 4 5 6 7 8 9 | #[post("/", data = "<request>")] pub async fn tenant_create(request: Json<CreateTenantRequest<'_>>, mut db_conn: Connection<AlertoDB>) -> Option<String> { let createTenantRequest = request.into_inner(); // … return Option::Some(String::from("OK")); } |
Then, in our DAO layer (if it makes sense in Rust lang), we use the database connection as shown in the following codes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | pub async fn create_tenant(request: CreateTenantServiceRequest<'_>, mut db_conn: Connection<AlertoDB>) { sqlx::query("INSERT INTO tenants(NAME, DESCRIPTION, CREATED_AT, UPDATED_AT) values(?, ?, now(), now())") .bind(request.name) .bind(request.description) .execute(&mut *db_conn).await; } pub async fn update_tenant(request: UpdateTenantServiceRequest<'_>, mut db_conn: Connection<AlertoDB>) { sqlx::query("UPDATE tenants set name=?, description=?. updated_at=now() WHERE ID=?") .bind(request.name) .bind(request.description) .bind(request.id) .execute(&mut *db_conn).await } pub async fn delete_tenant(request: DeleteTenantServiceRequest<'_>, mut db_conn: Connection<AlertoDB>) { sqlx::query("DELETE FROM tenants WHERE ID=?") .bind(request.id) .execute(&mut *db_conn).await } // Omitted find_tenant function |
That’s how we can use Rust Rocket SQLX for MySQL for basic CRUD (Create, Retrieve, Update, and Delete) operations.
