Database: MySQL

SQLite here is for demonstration only. If you want a more production-ready database, try MySQL or mongoDB.

Switch to MySQL

1. Install MySQL on your machine and start it.

Note: Remember to create indexes on tables based on your need in a production project.

2. Add mysql dependency:

cargo add mysql

Tip: Other choices are sqlx or Diesel.

Diff in Cargo.toml:

@@ -8,6 +8,7 @@ edition = "2021"
 [dependencies]
 chrono = { version = "0.4.35", features = ["serde"] }
 lazy_static = "1.4.0"
+mysql = "24.0.0"
 rocket = { version = "0.5.0", features = ["json"] }
 rusqlite = "0.31.0"
 serde = { version = "1.0.197", features = ["derive"] }

3. Update code.

Add infrastructure/database/mysql.rs:

use std::error::Error;

use chrono::Utc;
use mysql::prelude::Queryable;
use mysql::{Error as MySQLError, Pool};

use crate::domain::gateway::BookManager;
use crate::domain::model;

pub struct MySQLPersistence {
    pool: Pool,
}

impl MySQLPersistence {
    pub fn new(dsn: &str) -> Result<Self, MySQLError> {
        let pool = Pool::new(dsn)?;
        Ok(MySQLPersistence { pool })
    }
}

impl BookManager for MySQLPersistence {
    fn create_book(&self, b: &model::Book) -> Result<u32, Box<dyn Error>> {
        let mut conn = self.pool.get_conn()?;
        conn.exec::<usize, &str, (String, String, String, String, String, u32)>(
            "INSERT INTO books (title, author, published_at, description, isbn, total_pages)
             VALUES (?, ?, ?, ?, ?, ?)",
            (
                b.title.clone(),
                b.author.clone(),
                b.published_at.clone(),
                b.description.clone(),
                b.isbn.clone(),
                b.total_pages,
            ),
        )?;
        Ok(conn.last_insert_id() as u32)
    }

    fn update_book(&self, id: u32, b: &model::Book) -> Result<(), Box<dyn Error>> {
        let mut conn = self.pool.get_conn()?;
        conn.exec::<usize, &str, (String, String, String, String, String, u32, String, u32)>(
            "UPDATE books SET title = ?, author = ?, published_at = ?, description = ?, isbn = ?, total_pages = ?, updated_at = ?
            WHERE id = ?",
            (b.title.clone(), b.author.clone(), b.published_at.clone(), b.description.clone(), b.isbn.clone(), b.total_pages, Utc::now().format("%Y-%m-%d %H:%M:%S").to_string(),  id),
        )?;
        Ok(())
    }

    fn delete_book(&self, id: u32) -> Result<(), Box<dyn Error>> {
        let mut conn = self.pool.get_conn()?;
        conn.exec::<usize, &str, (u32,)>("DELETE FROM books WHERE id = ?", (id,))?;
        Ok(())
    }

    fn get_book(&self, id: u32) -> Result<Option<model::Book>, Box<dyn Error>> {
        let mut conn = self.pool.get_conn()?;
        let books = conn.query_map(
            format!("SELECT * FROM books WHERE ID = {}", id),
            |(
                id,
                title,
                author,
                published_at,
                description,
                isbn,
                total_pages,
                created_at,
                updated_at,
            ): (
                u64,
                String,
                String,
                String,
                String,
                String,
                u64,
                String,
                String,
            )| {
                model::Book {
                    id: id as u32,
                    title,
                    author,
                    published_at,
                    description,
                    isbn,
                    total_pages: total_pages as u32,
                    created_at,
                    updated_at,
                }
            },
        )?;
        Ok(books.first().cloned())
    }

    fn get_books(&self) -> Result<Vec<model::Book>, Box<dyn Error>> {
        let mut conn = self.pool.get_conn()?;
        let books = conn.query_map(
            "SELECT * FROM books",
            |(
                id,
                title,
                author,
                published_at,
                description,
                isbn,
                total_pages,
                created_at,
                updated_at,
            ): (
                u64,
                String,
                String,
                String,
                String,
                String,
                u64,
                String,
                String,
            )| {
                model::Book {
                    id: id as u32,
                    title,
                    author,
                    published_at,
                    description,
                    isbn,
                    total_pages: total_pages as u32,
                    created_at,
                    updated_at,
                }
            },
        )?;
        Ok(books)
    }
}

DSN stands for Data Source Name. It's a string that provides the necessary information for an application to connect to a specific database instance.

Tune infrastructure/config/mod.rs to export symbols:

@@ -1,3 +1,3 @@
-mod sqlite;
+mod mysql;
 
-pub use sqlite::SQLitePersistence;
+pub use mysql::MySQLPersistence;

Add dsn config item into the DBConfig struct in infrastructure/config/mod.rs:

@@ -9,6 +9,7 @@ pub struct Config {
 #[derive(Debug, Deserialize, Serialize)]
 pub struct DBConfig {
     pub file_name: String,
+    pub dsn: String,
 }
 
 #[derive(Debug, Deserialize, Serialize)]

Update WireHelper to switch the dependency in application/wire_helper.rs:

@@ -5,12 +5,12 @@ use crate::infrastructure::database;
 use crate::infrastructure::Config;
 
 pub struct WireHelper {
-    persistence: Arc<database::SQLitePersistence>,
+    persistence: Arc<database::MySQLPersistence>,
 }
 
 impl WireHelper {
     pub fn new(c: &Config) -> Result<Self, Box<dyn std::error::Error>> {
-        let persistence = Arc::new(database::SQLitePersistence::new(&c.db.file_name)?);
+        let persistence = Arc::new(database::MySQLPersistence::new(&c.db.dsn)?);
         Ok(WireHelper { persistence })
     }

Put in a dsn value in main.rs:

@@ -14,6 +14,7 @@ fn rocket() -> _ {
         app: ApplicationConfig { port: 8000 },
         db: DBConfig {
             file_name: "test.db".to_string(),
+            dsn: "mysql://test_user:test_pass@127.0.0.1:3306/lr_book".to_string(),
         },
     };
     let wire_helper = application::WireHelper::new(&c).expect("Failed to create WireHelper");

Voila! Your api server is powered by MySQL now!