Chapter 6: Database

1. MySQL (mysql2)

mysql2 is the best-performing MySQL driver for Node.js, with built-in Promise support and TypeScript types.

npm install mysql2

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

// 创建连接池（推荐）
const pool = mysql.createPool({
    host: "localhost",
    user: "root",
    password: process.env.DB_PASSWORD,
    database: "myapp",
    waitForConnections: true,
    connectionLimit: 10,
});

// 查询 —— 使用参数化防止 SQL 注入
interface User extends RowDataPacket {
    id: number;
    name: string;
    email: string;
}

const [users] = await pool.query<User[]>(
    "SELECT * FROM users WHERE name LIKE ?",
    ["%张%"]
);

// 插入
const [result] = await pool.execute<ResultSetHeader>(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ["李明", "liming@example.com"]
);
console.log("新用户 ID:", result.insertId);

// 事务
const conn = await pool.getConnection();
try {
    await conn.beginTransaction();
    await conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [100, 1]);
    await conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [100, 2]);
    await conn.commit();
} catch (e) {
    await conn.rollback();
    throw e;
} finally {
    conn.release();
}

2. PostgreSQL (pg)

PostgreSQL uses the pg library, with an API style similar to mysql2.

npm install pg
npm install -D @types/pg

import { Pool, QueryResult } from "pg";

const pool = new Pool({
    host: "localhost",
    port: 5432,
    user: "postgres",
    password: process.env.PG_PASSWORD,
    database: "myapp",
    max: 10,
});

interface Product {
    id: number;
    name: string;
    price: number;
}

// 参数化查询：$1, $2 占位符
const res: QueryResult<Product> = await pool.query(
    "SELECT * FROM products WHERE price > $1 ORDER BY price DESC LIMIT $2",
    [100, 20]
);

for (const row of res.rows) {
    console.log(`${row.name}: ¥${row.price}`);
}

3. MongoDB (mongoose)

Mongoose is MongoDB's ODM (Object Document Mapper), providing Schema validation and type safety.

npm install mongoose

import mongoose, { Schema, Document } from "mongoose";

// 连接
await mongoose.connect("mongodb://localhost:27017/myapp");

// 定义接口 + Schema
interface IArticle extends Document {
    title: string;
    content: string;
    tags: string[];
    createdAt: Date;
}

const articleSchema = new Schema<IArticle>({
    title:     { type: String, required: true },
    content:   { type: String, required: true },
    tags:      { type: [String], default: [] },
    createdAt: { type: Date, default: Date.now },
});

const Article = mongoose.model<IArticle>("Article", articleSchema);

// CRUD 操作
const doc = await Article.create({
    title: "TypeScript 入门",
    content: "...",
    tags: ["typescript", "tutorial"],
});

const articles = await Article.find({ tags: "typescript" }).sort({ createdAt: -1 }).limit(10);
await Article.updateOne({ _id: doc._id }, { $set: { title: "TS 进阶" } });
await Article.deleteOne({ _id: doc._id });

4. SQLite (better-sqlite3)

SQLite is a lightweight embedded database suitable for local tools, small applications, and testing. better-sqlite3 provides a synchronous API with excellent performance.

npm install better-sqlite3
npm install -D @types/better-sqlite3

import Database from "better-sqlite3";

const db = new Database("app.db");

// 建表
db.exec(`
    CREATE TABLE IF NOT EXISTS notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        body TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
`);

// 插入（预编译语句）
const insert = db.prepare("INSERT INTO notes (title, body) VALUES (?, ?)");
insert.run("会议纪要", "讨论了 Q4 目标...");

// 查询
interface Note { id: number; title: string; body: string; created_at: string; }
const notes = db.prepare("SELECT * FROM notes ORDER BY id DESC").all() as Note[];

db.close();

5. Prisma ORM

Prisma is a modern TypeScript ORM whose biggest advantage is auto-generating type-safe client code from the Schema.

npm install prisma @prisma/client
npx prisma init

Define prisma/schema.prisma:

// schema.prisma 文件内容（非 TS，但用 TS 高亮展示结构）
// datasource db { provider = "postgresql" url = env("DATABASE_URL") }
// model User {
//   id    Int     @id @default(autoincrement())
//   email String  @unique
//   name  String?
//   posts Post[]
// }
// model Post {
//   id       Int    @id @default(autoincrement())
//   title    String
//   author   User   @relation(fields: [authorId], references: [id])
//   authorId Int
// }

npx prisma migrate dev --name init  # 生成迁移并执行
npx prisma generate                  # 生成类型安全的客户端

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// 所有操作都有完整类型提示
const user = await prisma.user.create({
    data: { email: "alice@example.com", name: "Alice" },
});

const usersWithPosts = await prisma.user.findMany({
    include: { posts: true },
    where: { email: { contains: "example" } },
});

await prisma.$disconnect();

6. Best Practices

// 优雅关闭示例
process.on("SIGTERM", async () => {
    console.log("正在关闭数据库连接...");
    await pool.end();       // mysql2 / pg
    await prisma.$disconnect(); // Prisma
    process.exit(0);
});

📝 Chapter Summary