Configuring SQLx

Firstly, we must add the feature sqlx in Cargo.toml:

...
[dependencies]
kalgan = { version = "0.9.0", features = ["sqlx"] }

Be aware that current version of SQLx makes comulsory to re-import the crate in our project. In addition, we might need to import some other features. For example, if we're working with datetime fields we're going to need to import the feature chrono :

...
[dependencies]
sqlx = { version = "0.5.9", features = [ "mysql", "chrono" ] }

Connection pools

Connection to a database is an expensive task, especially in web applications where multiple request are handled at the same time. For this reason connection pools are the central piece of database access. They allow to reuse the running connections so that the full connection work must not be done again and again.

Configuring connection pools

To set up a connection pool we must pass the connection parameters to Kalgan. As usual, we must do this in our settings files. However, we'll have to set different parameters depending on the driver we're using (MySQL, PostgreSQL or SQLite).

An example for MySQL and PostgreSQL:

...
db:
    server: localhost
    name: kalgan
    user: root
    password: mypassword
    max_connections: 50

An example for SQLite:

...
db:
    path: /path_to_sqlite.db
    max_connections: 50

Find a description of these fields in the table below:

Built-in services

For this purpose Kalgan offers out of the box a pools for the supported engines. These services take the settings parameters above defined to build the connection pool.

We just have to pass this pool as reference when executing a query. For example:

use kalgan::service::db::mysql_pool;
use sqlx::mysql::MySqlQueryResult;

#[derive(Debug)]
pub(crate) struct User {
    pub id: i32,
    pub username: String,
    pub password: String,
    pub email: String
}
pub(crate) async fn find_all() -> Result<Vec<User>, sqlx::Error> {
    sqlx::query_as!(User, "SELECT id, username, password, email FROM user")
    .fetch_all(&mysql_pool().await).await
}
pub(crate) async fn create(username: &str, password: &str, email: &str) -> Result<MySqlQueryResult, sqlx::Error> {
    sqlx::query_as!(User, "INSERT INTO user (username, password, email) VALUES(?, ?, ?)",
        username, password, email).execute(&mysql_pool().await).await
}

The Data Model

Kalgan is a MVC framework. This last section is dedicated to the Model (M) component. You can find below some guidelines to organize and work with the tables of our database and how to manage the data from our models. However this wayforward is not always suitable for every project. Feel free to follow a different approach if this one doesn't fulfil your needs.

The model structure

Models contain the fields and behavior of the data stored in our database. Generally, each model maps to a single database table.

In the example above we had in src/model.rs the struct User which represents the table user. However it's expected to count with many more tables. Keep them all together in a single file would not be appropiate.

A better approach would be to split every table (model) in a single file.

Let's add a second model, for example, one for to the articles of our blog. Our files would be organized as follows:

project/
    Cargo.toml
    src/
        controller.rs
        ...
        model.rs
        model/
            article.rs
            user.rs
    ...

Our src/model.rs should be empty now. However it's a good place to declare all our model modules:

pub(crate) mod article;
pub(crate) mod user;

Active Record approach

The Active Record pattern is an architectural pattern aim to encapsulate database tables in business objects. These objects typically support an interface for CRUD operations (that is to say: create, read, update and delete methods).

In the example above we had a couple of functions to work with table user. For example, if we wanted to insert a new row in the table we'd proceed as follows:

use crate::model::user;
    ...
    match user::create("John Doe", "john's password", "john.doe@gmail.com").await {
        Ok(result) => {
            print!("{:#?}", result);
        }, Err(e) => {
            error!("{}", e);
        }
    }
    ...

This is ok and should work without any problems. However this wouldn't be a good idea when working with a table with many more fields as we'd have to pass the same amount of -unnamed- parameters. This approach would be difficult to maintain.

What if we try a more OOP style? An active record approach would be something similiar to this:

use kalgan::service::db::mysql_pool;
use sqlx::mysql::MySqlQueryResult;

#[derive(Debug)]
pub(crate) struct User {
    pub id: i32,
    pub username: String,
    pub password: String,
    pub email: String
}
impl User {
    pub fn new() -> Self {
        Self {
            id: 0,
            username: "".to_string(),
            password: "".to_string(),
            email: "".to_string()
        }
    }
    pub async fn find_all() -> Result, sqlx::Error> {
        sqlx::query_as!(Self, "SELECT id, username, password, email FROM user")
        .fetch_all(&mysql_pool().await).await
    }
    pub async fn create(&self) -> Result {
        sqlx::query_as!(Self, "INSERT INTO user (username, password, email) VALUES(?, ?, ?)",
        self.username, self.password, self.email).execute(&mysql_pool().await).await
    }
}

use crate::model::user;
    ...
    let mut user = model::test::User::new();
    user.username = "John Doe".to_string();
    user.password = "john's password".to_string();
    user.email = "john.doe@gmail.com".to_string();
    match user.create().await {
        Ok(result) => {
            print!("{:#?}", result);
        }, Err(e) => {
            error!("{}", e);
        }
    }
    ...

Our code is now much more verbose but much more readble as well and it will sacale better.

Let's imagine we need to update User object immediately afterwards, we could do something similiar to this:

use kalgan::service::db::mysql_pool;
use sqlx::mysql::MySqlQueryResult;

#[derive(Debug)]
pub(crate) struct User {
    pub id: i32,
    pub username: String,
    pub password: String,
    pub email: String
}
impl User {
    ...
    pub async fn update(&self) -> Result {
        sqlx::query_as!(self,
        "UPDATE user SET username = ?, password = ?, email = ? WHERE id = ?",
        self.username, self.password, self.email, self.id).execute(&mysql_pool().await).await
    }
    ...
}

use crate::model::user;
    ...
    let mut user = model::test::User::new();
    ...
    match user.create().await {
        Ok(result) => {
            print!("{:#?}", result);
            user.password = "John's new password"
            match user.update().await { ... }
        }, Err(e) => {
            error!("{}", e);
        }
    }
    ...

Which seems quite straight forward. :) Notice however that for MySQL case we'd need to get the User.id first.