数据库：MySQL

SQLite 只是为了效果演示。如果你需要一个生产环境可用的数据库，可尝试 MySQL 或 mongoDB。

切换到 MySQL

1. 在你机器上安装 MySQL 并启动。

注意：在实际生产项目中，记得给表中字段创建所需的索引。

2. 添加 mysql 依赖：

npm install mysql2

3. 更新代码。

添加 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();
  }
}

注意与之前的关键区别，此处构建数据库连接时使用来自 mysql2 的 mysql.createConnection(dsn) 而不是来自 sqlite3 的 new sqlite3.Database(dbFilePath, ...) 。

DSN 表示数据源名称（Data Source Name）。它是用于向应用程序提供数据实例连接信息的字符串。

向 DBConfig 接口添加 dsn 配置项，在 infrastructure/config/config.ts 中：

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

更新 WireHelper 以切换依赖，在 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 {

在 main.ts 中放入 dsn 值：

@@ -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);

瞧！你的 API 服务器现在由 MySQL 驱动啦！