Rust API with Diesel and r2d2 on MySQL

Rust API with Diesel and r2d2 on MySQL

This is a second article about Rust where we are going to build rust API with Diesel ORM and r2d2 connection manager. Our storage is MySQL. We have started with a simple example of Rust with AWS Lambda. You can read it here. The end goal will be to create a template for future development where we have a good baseline for API running in a Serverless environment. Also, I don’t want to be fully dependant on a specific cloud provider. To make it happen, in addition to a simple Lambda handler we need to introduce the HTTP server.

Actix-web http server

After looking into available solutions I have decided to go with Actix. There is also Rocket, but it doesn’t work with rust stable and at this stage, Actix seems more advanced. Besides, it reminds me of Nest.js which I really like as TypesScript node API framework. I will cover it in future articles.

Let’s start by adding related crates and setting up the server.

actix = "0.9.0"
actix-web = "2.0.0"
actix-rt = "1.0"
use actix_web::middleware::Logger;
use actix_web::{web, App, HttpServer};

async fn main() -> std::io::Result<()> {
    std::env::set_var("RUST_LOG", "actix_web=debug");

    HttpServer::new(|| {

In this case, we are using async functions and to execute it by Actix framework we need to add #[actix_rt::main]. There is some logging enabled but all the action is in the HttpServer. We are passing connection to the database because in our example we will be using MySql. More on this in the next paragraph. After that, we are adding our first REST service. It is going to execute “get” function from handlers. It will return all users with a GET request to the API URL that is configured below that.

Diesel – MySql

Diesel is the most popular ORM in rust. Let’s start by setting up Diesel. To prepare configuration and test connection we will use diesel-cli installed via cargo. Then run setup to create a configuration file and test connection. We are using an already existing database so the new one won’t be created.

Diesel will require that database drivers are installed on the system, so we had to tell the installer that we need to use it only for MySQL. Also, we need to install MySQL drivers. On macOS, it comes with MySQL.

# If you don't have mysql drivers you need to install it first.
$ brew install mysql      
$ cargo install diesel_cli --features mysql       
$ diesel setup --database-url mysql://login:pass@ 

After that, the connection should be tested and new diesel.toml config file was created.

file = "src/"
filter = { only_tables = ["user"] }

Since we are only interested in the user table, we have added a filter to include only this table.

It’s time to add diesel config in Cargo.toml for our app. We need to include all the features we are going to use. Chrono is a date-time library. We are going to use it to add timestamp support.

version = "1.4.3"
default-features = false
features = ["r2d2","mysql", "chrono"]

Now the most important part is to generate a schema file. It will tell ORM what is what.

$ diesel print-schema --database-url mysql://login:pass@localhost:3306/user_db > src/

As a result, we have our table structure. If we are using an already existing database you may need to provide correct SQL names by using annotation like below.

table! {
    user (id) {
        id -> Varchar,
        #[sql_name = "firstName"]
        first_name -> Varchar,
        #[sql_name = "lastName"]
        last_name -> Varchar,
        email -> Varchar,
        name -> Varchar,
        #[sql_name = "createAt"]
        create_at -> Timestamp,
        #[sql_name = "avatarId"]
        avatar_id -> Nullable<Varchar>,
        #[sql_name = "phoneNo"]
        phone_no -> Nullable<Varchar>,
        #[sql_name = "companyName"]
        company_name -> Nullable<Varchar>,
        #[sql_name = "vatId"]
        vat_id -> Nullable<Varchar>,

Data model

Ok, we have diesel added to the project with correct schema declaration. Now let’s take a look at our User model.

We created our struct to represent the table and in addition added the first method to query user data.

use crate::schema::user;
use chrono::NaiveDateTime;
use diesel::MysqlConnection;

#[derive(Queryable, Serialize, Deserialize)]
pub struct User {
    pub id: String,
    pub first_name: String,
    pub last_name: String,
    pub email: String,
    pub name: String,
    pub create_at: NaiveDateTime,
    pub avatar_id: Option<String>,
    pub phone_no: Option<String>,
    pub company_name: Option<String>,
    pub vat_id: Option<String>,

#[derive(Serialize, Deserialize)]
pub struct UserList(pub Vec<User>);

impl UserList {
    pub fn list(connection: &MysqlConnection) -> Self {
        use crate::schema::user::dsl::*;
        use diesel::QueryDsl;
        use diesel::RunQueryDsl;

        let result = user
            .expect("Error loading users");



r2d2 is a connection pool manager and it is now available as Diesel feature..

To set it up, we need to initialize connection pool. It will be used by methods. Let’s create file and use r2d2.

use diesel::mysql::MysqlConnection;
use diesel::r2d2::{ConnectionManager, Pool, PoolError, PooledConnection};

pub type MysqlPool = Pool<ConnectionManager<MysqlConnection>>;
pub type MySqlPooledConnection = PooledConnection<ConnectionManager<MysqlConnection>>;

fn init(database_url: &str) -> Result<MysqlPool, PoolError> {
    let manager = ConnectionManager::<MysqlConnection>::new(database_url);

pub fn connect() -> MysqlPool {

As you can notice we are going to use dotenv! macro to read .env file that needs to be created in the root directory.


That’s it. The connection pool is finished.

Actix handlers

We just need to create the correct Actix handler to execute the list method form the user model and return it as a JSON response. To do that you need to create in handlers directory.

use actix_web::web;
use actix_web::{HttpRequest, HttpResponse};

use crate::db::{MySqlPooledConnection, MysqlPool};
use crate::model::user::UserList;

fn mysql_pool_handler(pool: web::Data<MysqlPool>) -> Result<MySqlPooledConnection, HttpResponse> {
        .map_err(|e| HttpResponse::InternalServerError().json(e.to_string()))

pub async fn get(
    _req: HttpRequest,
    pool: web::Data<MysqlPool>,
) -> Result<HttpResponse, HttpResponse> {
    let mysql_pool = mysql_pool_handler(pool)?;

Test API

Everything is ready. Just run the app and try to get some data by hitting your URL. (Of course, make sure there is some data in DB)

$ cargo run

When you hit your API URL you should see log in your console and JSON result as a response.

   Finished dev [unoptimized + debuginfo] target(s) in 19.06s
     Running `target/debug/user`
[2020-03-04T17:16:52Z INFO  actix_web::middleware::logger] "GET / HTTP/1.1" 200 611 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_3) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.0.5 Safari/605.1.15" 0.097459
[2020-03-04T17:16:53Z INFO  actix_web::middleware::logger] "GET /favicon.ico HTTP/1.1" 404 0 "http://localhost:8088/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_3) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.0.5 Safari/605.1.15" 0.000245     
    "id": "89251ab3-1cdc-4629-9086-ce022cf3549e",
    "first_name": "Marek",
    "last_name": "Test",
    "email": "",
    "name": "sufrago",
    "create_at": "2019-12-17T17:58:07.533406",
    "avatar_id": "1cb15088-afd4-4d00-a7fc-d95eae1abefb",
    "phone_no": "+48505884888",
    "company_name": "Sufrago sp z o.o.",
    "vat_id": "PL 9512468001"
}, {
    "id": "dfff1c26-e6b2-4b33-8531-e3bc280179e2",
    "first_name": "Marek",
    "last_name": "test2",
    "email": "",
    "name": "test2",
    "create_at": "2020-01-16T16:21:59.230964",
    "avatar_id": "e5c43243-5b73-4c2d-aef6-397767c62bd6",
    "phone_no": "+48666555444",
    "company_name": null,
    "vat_id": null

Congratulations, you have working Rust API with Diesel, r2d2 and MySQL! As usual, code is available on my GitHub

Other articles from this series:

If you have an interesting project or need a highly qualified team, take a look at to learn more about our company and get in touch.

Leave a Reply

Your email address will not be published. Required fields are marked *