R2D2 is a connection pool for Rust applications, e.g., Actix-Web, that use databases like MySQL. Opening and closing a database connection is an expensive operation. With connection pooling, the database connection creation happens once, and the application can reuse it numerous times without even closing it. Therefore, programs that use a connection pool are efficient and run faster.
Update Cargo.toml With Actix-Web And Others
First, let’s create a Rust application that is dependent on Actix-Web, MySQL, and R2D2. Update Cargo.toml as follows.
1 2 3 4 5 6 7 8 9 10 11 | [package] name = "actix-web-mysql-with-db-pooling" version = "0.1.0" authors = ["Karl San Gabriel"] edition = "2018" [dependencies] actix-web = "2.0.0" r2d2 = "0.8.8" actix-rt = "1.0.0" r2d2_mysql = "17.0.0" |
We need Actix-rt to use #[actix-rt::main]. It enables us to make the main function asyn fn.
MySQL Docker Compose
Then, we start a MySQL server Docker container via Docker Compose and create a Person table with sample data.
1 2 3 4 5 6 | create table person ( person_id int auto_increment, person_name varchar(100) null, constraint person_pk primary key (person_id) ); |
We will use these sample data:
1 2 3 | PERSON_ID PERSON_NAME 1 Gary 2 Steve |
Before moving on to the next section, please ensure the container is running, and MySQL has the Person table with data. Actix-web and R2D2 will use this MySQL database.
Add Codes For Actix-Web And R2D2
Then, we update the main.rs in our Rust application. First, import and use stuff as follows.
1 2 3 4 5 6 7 8 | extern crate r2d2; extern crate r2d2_mysql; use actix_web::{get, web, App, HttpServer, Responder}; use r2d2_mysql::mysql::{OptsBuilder, QueryResult, from_row}; use std::sync::Arc; use r2d2::Pool; use r2d2_mysql::MysqlConnectionManager; |
We then write a function that returns Option<Arc<Pool<MysqlConnectionManager>>> whose content represents an R2D2 pool of MySQL database connections. We store the pool as part of an application state within Actix-web.
1 2 3 4 5 6 7 8 9 10 11 12 13 | fn get_pool() -> Option<Arc<Pool<MysqlConnectionManager>>> { let mut o = OptsBuilder::new(); o.db_name(Option::Some("turretadb")); o.user(Option::Some("root")); o.pass(Option::Some("a1128f69-e6f7-4e93-a2df-3d4db6030abc")); let manager = r2d2_mysql::MysqlConnectionManager::new(o); println!("Getting pool"); let pool = Arc::new(r2d2::Pool::new(manager).unwrap()); return Option::Some(pool); } |
Then, create a struct for Actix-web for application state.
1 2 3 4 | struct AppState { app_name: String, pool: Arc<Pool<MysqlConnectionManager>>, } |
Then, create an HTTP GET handler function. It runs whenever Actix-web receives a URL request for /persons/{id}. Also, it retrieves the R2D2 connection pool from the Actix-web application state and uses the id to query against the MySQL table for a specific person.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | #[get("/persons/{id}")] async fn index(info: web::Path<i32>, data: web::Data<AppState>) -> impl Responder { let app_name = &data.app_name; // <- get app_name let pool = &data.pool; let pool = pool.clone(); let mut conn = pool.get().unwrap(); let param = info.into_inner(); let qr: QueryResult = conn.prep_exec("select person_id, person_name from person where person_id = ?", (param, )).unwrap(); let mut rec: Option<(i32, String)> = None; for row in qr { rec = Some(from_row(row.unwrap())); break; } let unwrap_rec = rec.unwrap(); format!("Hello {} ({})! \n from {}", unwrap_rec.1, unwrap_rec.0, app_name) } |
As an example, we use a simple SQL Select statement and only pick up the first record from the query result.
Lastly, let’s create the main function. As previously mentioned, it is an async fn because we are using #[actix_rt::main].
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | #[actix_rt::main] async fn main() -> std::io::Result<()> { let app_data = web::Data::new(AppState { app_name: String::from("turreta"), pool: get_pool().unwrap(), }); HttpServer::new(move || { App::new().app_data(app_data.clone() ).service(index) }) .bind("127.0.0.1:8080")? .run() .await } |
When we access http://localhost:8080/persons/1, the application should return a response that contains the name of the person in the database with person_id = 1.
We used Rust 1.41.0.
