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:

npm install mysql2

3. Update code.

Add infrastructure/database/mysql.ts:

import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";

import { Book } from "@/domain/model/book";
import { BookManager } from "@/domain/gateway/book_manager";

export class MySQLPersistence implements BookManager {
  private db: mysql.Connection;

  constructor(dsn: string) {
    this.db = mysql.createConnection(dsn);
    this.db.addListener("error", (err) => {
      console.error("Error connecting to MySQL:", err.message);
    });

    this.db.execute(
      `CREATE TABLE IF NOT EXISTS books (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        author VARCHAR(255) NOT NULL,
        published_at VARCHAR(15) NOT NULL,
        description TEXT NOT NULL,
        isbn VARCHAR(255) NOT NULL,
        total_pages INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )`,
      (err) => {
        if (err) {
          console.error("Error in MySQL:", err.message);
        } else {
          console.log("Successfully initialized tables.");
        }
      }
    );
  }

  async createBook(b: Book): Promise<number> {
    const { title, author, published_at, description, isbn, total_pages } = b;
    const [result] = await this.db
      .promise()
      .query(
        "INSERT INTO books (title, author, published_at, description, isbn, total_pages) VALUES (?, ?, ?, ?, ?, ?)",
        [title, author, published_at, description, isbn, total_pages]
      );
    return (result as ResultSetHeader).insertId;
  }

  async updateBook(id: number, b: Book): Promise<void> {
    const { title, author, published_at, description, isbn, total_pages } = b;
    await this.db
      .promise()
      .query(
        "UPDATE books SET title = ?, author = ?, published_at = ?, description = ?, isbn = ?, total_pages = ? WHERE id = ?",
        [title, author, published_at, description, isbn, total_pages, id]
      );
  }

  async deleteBook(id: number): Promise<void> {
    await this.db.promise().query("DELETE FROM books WHERE id = ?", [id]);
  }

  async getBook(id: number): Promise<Book | null> {
    let [rows] = await this.db
      .promise()
      .query("SELECT * FROM books WHERE id = ?", [id]);
    rows = rows as RowDataPacket[];
    return rows.length ? (rows[0] as Book) : null;
  }

  async getBooks(): Promise<Book[]> {
    const [rows] = await this.db.promise().query("SELECT * FROM books");
    return rows as Book[];
  }

  close(): void {
    this.db.end();
  }
}

Notice that the key difference is that we use mysql.createConnection(dsn) from mysql2 instead of new sqlite3.Database(dbFilePath, ...) from sqlite3.

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.

Add dsn config item into the DBConfig interface in infrastructure/config/config.ts:

@@ -1,5 +1,6 @@
 interface DBConfig {
   fileName: string;
+  dsn: string;
 }

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

@@ -1,13 +1,13 @@
-import { SQLitePersistence } from "@/infrastructure/database";
+import { MySQLPersistence } from "@/infrastructure/database";
 import { Config } from "@/infrastructure/config";
 import { BookManager } from "@/domain/gateway";
 
 // WireHelper is the helper for dependency injection
 export class WireHelper {
-  private persistence: SQLitePersistence;
+  private persistence: MySQLPersistence;
 
   constructor(c: Config) {
-    this.persistence = new SQLitePersistence(c.db.fileName);
+    this.persistence = new MySQLPersistence(c.db.dsn);
   }
 
   bookManager(): BookManager {

Put in a dsn value in main.ts:

@@ -9,6 +9,7 @@ const c = {
   },
   db: {
     fileName: "test.db",
+    dsn: "mysql://test_user:test_pass@127.0.0.1:3306/lr_book?charset=utf8mb4",
   },
 };
 const wireHelper = new WireHelper(c);

Voila! Your api server is powered by MySQL now!