数据库基础

关系型数据库基础

什么是关系型数据库？

关系型数据库（RDBMS）以**表（Table）为核心组织数据，表由行（Row）和列（Column）构成。表与表之间通过外键（Foreign Key）**建立关联关系。MySQL 和 PostgreSQL 是 Node.js 生态中最常用的两种关系型数据库。

┌─────────────────────────────────────────────────────────────┐
│                     关系型数据库核心概念                       │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  数据库 (Database)                                           │
│  └── 表 (Table)                                             │
│      ├── 列 (Column) ──── 字段名 + 数据类型 + 约束            │
│      ├── 行 (Row)    ──── 一条具体的数据记录                   │
│      ├── 主键 (PK)   ──── 唯一标识一行数据                    │
│      ├── 外键 (FK)   ──── 关联另一张表的主键                   │
│      └── 索引 (Index) ─── 加速查询的数据结构                   │
│                                                             │
│  关系类型:                                                   │
│  ├── 一对一 (1:1)   ── 用户 ↔ 用户详情                       │
│  ├── 一对多 (1:N)   ── 用户 → 文章（一个用户有多篇文章）        │
│  └── 多对多 (M:N)   ── 文章 ↔ 标签（通过中间表关联）           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

MySQL vs PostgreSQL 特性对比

数据类型差异

类别	MySQL	PostgreSQL	选择建议
整数	`INT`, `BIGINT`, `TINYINT`	`INTEGER`, `BIGINT`, `SMALLINT`	主键用 `BIGINT`，状态字段用 `SMALLINT`
小数	`DECIMAL(10,2)`, `FLOAT`	`NUMERIC(10,2)`, `REAL`	金额用 `DECIMAL`/`NUMERIC`，切勿用浮点
字符串	`VARCHAR(255)`, `TEXT`	`VARCHAR(255)`, `TEXT`	定长用 `CHAR`，变长用 `VARCHAR`
日期	`DATETIME`, `TIMESTAMP`	`TIMESTAMP`, `TIMESTAMPTZ`	PostgreSQL 优先用带时区的 `TIMESTAMPTZ`
布尔	`TINYINT(1)`	`BOOLEAN`	PostgreSQL 原生支持布尔类型
JSON	`JSON`	`JSON`, `JSONB`	PostgreSQL 的 `JSONB` 支持索引，性能更好
数组	不支持	`INTEGER[]`, `TEXT[]`	PostgreSQL 独有，避免多值字段
UUID	`CHAR(36)` / `BINARY(16)`	`UUID`	PostgreSQL 原生 UUID 类型更高效
枚举	`ENUM('a','b','c')`	`CREATE TYPE ... AS ENUM`	枚举变更不灵活，建议用查找表替代

核心特性对比

维度	MySQL	PostgreSQL
存储引擎	InnoDB / MyISAM 可切换	统一存储引擎
MVCC	InnoDB 支持，undo log 实现	原生 MVCC，行级版本控制
全文搜索	`FULLTEXT` 索引，中文需插件	内置 `tsvector`/`tsquery`，支持分词
窗口函数	MySQL 8.0+ 支持	全面支持，语法更丰富
CTE 递归	MySQL 8.0+ 支持	全面支持 `WITH RECURSIVE`
物化视图	不支持	`CREATE MATERIALIZED VIEW`
JSONB 索引	JSON 不支持索引	GIN 索引，支持 `@>`, `?` 运算符
分区表	`RANGE`/`LIST`/`HASH`	`RANGE`/`LIST`/`HASH` + 声明式分区
事务隔离	默认 `REPEATABLE READ`	默认 `READ COMMITTED`
复制方式	主从复制（异步/半同步）	流复制 + 逻辑复制
扩展能力	插件系统	强大的扩展系统（PostGIS/pg_trgm 等）
适用场景	读多写少、互联网业务	复杂查询、GIS、数据分析

选型决策:

  需要复杂的 JSON 查询？          → PostgreSQL (JSONB)
  需要地理空间计算？               → PostgreSQL (PostGIS)
  需要丰富的数据类型（数组/范围）？ → PostgreSQL
  团队熟悉 MySQL 且业务简单？      → MySQL
  已有 MySQL 生态且无迁移成本？     → MySQL
  需要全文搜索且不想引入 ES？       → PostgreSQL (tsvector)

SQL 基础：CRUD 完整语法

CREATE — 建表

sql

CREATE TABLE users (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    username    VARCHAR(50) NOT NULL UNIQUE,
    email       VARCHAR(100) NOT NULL,
    age         INT DEFAULT 0,
    status      ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    metadata    JSON,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id        BIGINT PRIMARY KEY AUTO_INCREMENT,
    title     VARCHAR(200) NOT NULL,
    content   TEXT,
    user_id   BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE tags (
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id BIGINT NOT NULL,
    tag_id  BIGINT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

INSERT — 插入

sql

INSERT INTO users (username, email, age) VALUES
    ('alice', 'alice@example.com', 28),
    ('bob', 'bob@example.com', 32),
    ('charlie', 'charlie@example.com', 25);

INSERT INTO users (username, email, age)
VALUES ('david', 'david@example.com', 30)
ON DUPLICATE KEY UPDATE email = VALUES(email);

INSERT INTO users (username, email, age)
VALUES ('david', 'david@example.com', 30)
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

SELECT — 查询

sql

SELECT id, username, email FROM users WHERE age > 26;

SELECT * FROM users WHERE username LIKE 'a%' AND age BETWEEN 20 AND 30;

SELECT * FROM users WHERE status IN ('active', 'inactive') ORDER BY created_at DESC LIMIT 10 OFFSET 0;

SELECT DISTINCT status FROM users;

UPDATE — 更新

sql

UPDATE users SET age = 29 WHERE username = 'alice';

UPDATE users SET age = age + 1, updated_at = NOW() WHERE status = 'active';

UPDATE posts p
INNER JOIN users u ON p.user_id = u.id
SET p.content = CONCAT(p.content, ' [verified]')
WHERE u.status = 'active';

DELETE — 删除

sql

DELETE FROM users WHERE username = 'charlie';

DELETE FROM posts WHERE user_id IN (SELECT id FROM users WHERE status = 'banned');

TRUNCATE TABLE logs;

JOIN 类型详解

表结构:
┌─────────┐       ┌──────────┐       ┌────────┐
│  users  │ 1───N │  posts   │ N───M │  tags  │
│─────────│       │──────────│       │────────│
│ id (PK) │       │ id (PK)  │       │ id(PK) │
│ username│       │ title    │       │ name   │
│ email   │       │ content  │       └────────┘
└─────────┘       │ user_id  │           │
                  └──────────┘     ┌─────────────┐
                                   │  post_tags  │
                                   │─────────────│
                                   │ post_id(FK) │
                                   │ tag_id (FK) │
                                   └─────────────┘

五种 JOIN 图示

假设数据:
  users: [Alice, Bob, Charlie]     (Charlie 没发过文章)
  posts: [Post1→Alice, Post2→Alice, Post3→Bob, Post4→已删除用户]

INNER JOIN (内连接)              LEFT JOIN (左连接)
┌─────────┬─────────┐           ┌─────────┬─────────┐
│  users  │  posts  │           │  users  │  posts  │
│         │         │           │         │         │
│  Alice ─┼─ Post1  │           │  Alice ─┼─ Post1  │
│  Alice ─┼─ Post2  │           │  Alice ─┼─ Post2  │
│  Bob  ──┼─ Post3  │           │  Bob  ──┼─ Post3  │
│         │         │           │ Charlie ┼─ NULL   │
└─────────┴─────────┘           └─────────┴─────────┘
 只返回两边都匹配的行              左表全部 + 右表匹配(无则NULL)

RIGHT JOIN (右连接)              FULL OUTER JOIN (全外连接)
┌─────────┬─────────┐           ┌─────────┬─────────┐
│  users  │  posts  │           │  users  │  posts  │
│         │         │           │         │         │
│  Alice ─┼─ Post1  │           │  Alice ─┼─ Post1  │
│  Alice ─┼─ Post2  │           │  Alice ─┼─ Post2  │
│  Bob  ──┼─ Post3  │           │  Bob  ──┼─ Post3  │
│  NULL ──┼─ Post4  │           │ Charlie ┼─ NULL   │
└─────────┴─────────┘           │  NULL ──┼─ Post4  │
 左表匹配(无则NULL) + 右表全部    └─────────┴─────────┘
                                  两边全部，无匹配则 NULL

CROSS JOIN (交叉连接/笛卡尔积)
┌─────────┬─────────┐
│  users  │  posts  │
│ Alice  ─┼─ Post1  │
│ Alice  ─┼─ Post2  │
│ Alice  ─┼─ Post3  │
│ Alice  ─┼─ Post4  │
│ Bob   ──┼─ Post1  │
│ Bob   ──┼─ Post2  │
│ ...     │  ...    │
└─────────┴─────────┘
 M × N 行，每行与每行组合

JOIN SQL 示例

sql

SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

SELECT u.username, t.name AS tag_name
FROM users u
CROSS JOIN tags t;

SELECT u.username, p.title, t.name AS tag_name
FROM users u
INNER JOIN posts p ON u.id = p.user_id
INNER JOIN post_tags pt ON p.id = pt.post_id
INNER JOIN tags t ON pt.tag_id = t.id;

SELECT u.username, COALESCE(p.title, '无文章') AS title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;

子查询与 CTE

子查询

sql

SELECT username FROM users
WHERE id IN (
    SELECT user_id FROM posts
    GROUP BY user_id
    HAVING COUNT(*) > 5
);

SELECT u.username, sub.post_count
FROM users u
INNER JOIN (
    SELECT user_id, COUNT(*) AS post_count
    FROM posts
    GROUP BY user_id
) sub ON u.id = sub.user_id;

SELECT username,
    (SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id) AS post_count
FROM users;

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p
    WHERE p.user_id = u.id AND p.title LIKE '%Node%'
);

CTE（WITH 语句）

CTE（Common Table Expression）通过 WITH 关键字定义临时命名结果集，让复杂查询变得可读。CTE 在语义上等价于子查询，但可以被多次引用，并且支持递归。

sql

WITH active_authors AS (
    SELECT user_id, COUNT(*) AS post_count
    FROM posts
    WHERE published = true
    GROUP BY user_id
    HAVING COUNT(*) >= 3
)
SELECT u.username, u.email, a.post_count
FROM users u
INNER JOIN active_authors a ON u.id = a.user_id
ORDER BY a.post_count DESC;

WITH
monthly_stats AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        COUNT(*) AS total_posts,
        COUNT(DISTINCT user_id) AS active_users
    FROM posts
    GROUP BY DATE_TRUNC('month', created_at)
),
avg_stats AS (
    SELECT
        AVG(total_posts) AS avg_posts,
        AVG(active_users) AS avg_users
    FROM monthly_stats
)
SELECT
    ms.month,
    ms.total_posts,
    ms.active_users,
    ROUND(ms.total_posts::numeric / NULLIF(a.avg_posts, 0), 2) AS posts_ratio
FROM monthly_stats ms
CROSS JOIN avg_stats a
ORDER BY ms.month;

递归 CTE

递归 CTE 可以处理树形或层级数据（如分类目录、组织架构）。

sql

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 AS depth, name::text AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.depth + 1,
           ct.path || ' > ' || c.name
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY path;

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 1

    UNION ALL

    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

递归 CTE 执行过程:

  第 1 轮 (锚点):  SELECT ... WHERE parent_id IS NULL
                    → 取出根节点 [电子产品]

  第 2 轮 (递归):  SELECT ... WHERE parent_id IN (根节点ID)
                    → 取出 [手机, 电脑, 平板]

  第 3 轮 (递归):  SELECT ... WHERE parent_id IN (第2轮ID)
                    → 取出 [iPhone, 华为, MacBook, ...]

  终止条件: 某一轮 UNION ALL 没有产出新行 → 递归结束

聚合函数与 GROUP BY / HAVING

sql

SELECT COUNT(*) AS total_users FROM users;

SELECT AVG(age) AS avg_age, MAX(age) AS max_age, MIN(age) AS min_age, SUM(age) AS sum_age FROM users;

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 3
ORDER BY post_count DESC;

SELECT
    CASE
        WHEN age < 25 THEN '青年'
        WHEN age BETWEEN 25 AND 35 THEN '中青年'
        ELSE '资深'
    END AS age_group,
    COUNT(*) AS count
FROM users
GROUP BY age_group;

SELECT
    user_id,
    COUNT(*) AS total_posts,
    COUNT(*) FILTER (WHERE published = true) AS published_posts,
    ROUND(
        COUNT(*) FILTER (WHERE published = true)::numeric / NULLIF(COUNT(*), 0) * 100,
        1
    ) AS publish_rate
FROM posts
GROUP BY user_id;

SELECT
    username,
    COUNT(*) OVER () AS total_users,
    ROW_NUMBER() OVER (ORDER BY created_at) AS row_num,
    RANK() OVER (ORDER BY age DESC) AS age_rank,
    LAG(username) OVER (ORDER BY created_at) AS prev_user,
    LEAD(username) OVER (ORDER BY created_at) AS next_user
FROM users;

GROUP BY 执行顺序:

  FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

  WHERE  在分组前过滤行
  HAVING 在分组后过滤组

  常见错误:
    ✗ SELECT username, COUNT(*) FROM posts GROUP BY user_id
      (username 不在 GROUP BY 中也不是聚合函数)
    ✓ SELECT user_id, COUNT(*) FROM posts GROUP BY user_id

MongoDB 文档模型

文档模型 vs 关系模型的思维差异

MongoDB 使用文档（Document）作为基本数据单位，文档是类似 JSON 的 BSON（Binary JSON）格式。设计 MongoDB Schema 时需要从关系思维转换到文档思维。

┌─────────────────────────────────────────────────────────────┐
│                   思维模式对比                                │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  关系型思维:                                                 │
│  "数据应该怎样存储才最规范？"                                  │
│  → 第三范式 → 消除冗余 → 多表 JOIN                           │
│  → 先设计 Schema，再考虑查询                                  │
│                                                             │
│  文档型思维:                                                 │
│  "应用程序需要怎样读取数据？"                                  │
│  → 按访问模式设计 → 嵌入优先 → 减少 JOIN                     │
│  → 先明确查询模式，再设计 Schema                              │
│                                                             │
│  核心差异:                                                   │
│  ├── 关系型: 写入时规范 (Write-optimized)                    │
│  └── 文档型: 读取时优化 (Read-optimized)                     │
│                                                             │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│                   MongoDB 数据层级                           │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  数据库 (Database)                                           │
│  └── 集合 (Collection)  ≈  关系型的「表」                     │
│      └── 文档 (Document) ≈  关系型的「行」                    │
│          └── 字段 (Field) ≈  关系型的「列」                   │
│                                                             │
│  关键区别:                                                   │
│  ├── 无固定 Schema —— 同一集合中文档结构可以不同               │
│  ├── 嵌套文档     —— 支持深层嵌套，无需 JOIN                  │
│  ├── 数组字段     —— 原生支持数组，多值存储更自然              │
│  └── _id 字段     —— 每个文档自动生成 ObjectId 作为主键        │
│                                                             │
└─────────────────────────────────────────────────────────────┘

嵌入文档 vs 引用文档设计决策

模式一: 嵌入文档 (Embedding)          模式二: 引用文档 (Referencing)

┌──────────────────────┐           ┌──────────────┐   ┌───────────────┐
│ {                    │           │ {            │   │ {             │
│   _id: ObjectId,     │           │   _id: "u1", │   │   _id: "a1",  │
│   name: "Alice",     │           │   name: "A", │   │   street: "x",│
│   address: {         │           │   addr: "a1" │──→│   city: "SH"  │
│     street: "xxx",   │           │ }            │   │ }             │
│     city: "Shanghai" │           └──────────────┘   └───────────────┘
│   }                  │
│ }                    │           需要二次查询，但数据独立
└──────────────────────┘
一次查询即可获取全部数据

对比维度	嵌入文档	引用文档
读取性能	一次查询，速度快	需要多次查询或 `$lookup`
写入性能	嵌套更新较复杂	独立更新，简单直接
数据一致性	可能数据冗余	单一数据源，一致性好
文档大小	可能超 16MB 限制	文档体积小
适用场景	一对一、一对少量	一对多、多对多、频繁变更

设计决策树:

  子数据是否和父数据一起读取？
  ├── 是 → 子数据数量是否有上界？
  │        ├── 是（< 100）→ 嵌入
  │        └── 否 / 可能无限增长 → 引用
  └── 否 → 子数据是否需要独立访问？
           ├── 是 → 引用
           └── 否 → 嵌入（但注意文档 16MB 限制）

javascript

const userWithEmbedding = {
  _id: new ObjectId(),
  username: "alice",
  profile: {
    bio: "Full-stack developer",
    avatar: "https://example.com/alice.png",
    social: {
      github: "alice-dev",
      twitter: "@alice"
    }
  },
  addresses: [
    { type: "home", city: "Shanghai", street: "Nanjing Road 100" },
    { type: "work", city: "Shanghai", street: "Lujiazui 200" }
  ]
};

const userWithReference = {
  _id: new ObjectId(),
  username: "bob",
  profile_id: new ObjectId("65a1b2c3d4e5f6a7b8c9d0e1"),
  address_ids: [
    new ObjectId("65a1b2c3d4e5f6a7b8c9d0e2"),
    new ObjectId("65a1b2c3d4e5f6a7b8c9d0e3")
  ]
};

CRUD 操作

javascript

const { MongoClient } = require("mongodb");

const client = new MongoClient("mongodb://localhost:27017");
const db = client.db("myapp");
const users = db.collection("users");

await users.insertOne({
  username: "alice",
  email: "alice@example.com",
  age: 28,
  tags: ["developer", "nodejs"]
});

await users.insertMany([
  { username: "bob", email: "bob@example.com", age: 32 },
  { username: "charlie", email: "charlie@example.com", age: 25 }
]);

const user = await users.findOne({ username: "alice" });

const youngUsers = await users.find({ age: { $lt: 30 } }).toArray();

const result = await users.find({
  $and: [
    { age: { $gte: 25 } },
    { tags: { $in: ["nodejs"] } }
  ]
}).sort({ age: -1 }).limit(10).toArray();

await users.updateOne(
  { username: "alice" },
  {
    $set: { age: 29 },
    $push: { tags: "typescript" },
    $inc: { loginCount: 1 },
    $currentDate: { lastModified: true }
  }
);

await users.updateMany(
  { age: { $lt: 25 } },
  { $set: { status: "junior" } }
);

await users.replaceOne(
  { username: "alice" },
  { username: "alice", email: "new@example.com", age: 30 }
);

await users.deleteOne({ username: "charlie" });

await users.deleteMany({ status: "inactive" });

常用查询运算符:

  比较:  $eq  $ne  $gt  $gte  $lt  $lte  $in  $nin
  逻辑:  $and  $or  $not  $nor
  元素:  $exists  $type
  数组:  $all  $elemMatch  $size
  更新:  $set  $unset  $inc  $push  $pull  $addToSet  $pop

聚合管道

聚合管道是 MongoDB 最强大的数据处理工具，数据像流水线一样依次通过每个阶段（stage），每个阶段对数据进行一次变换。

聚合管道执行流程:
  集合数据 → $match → $project → $unwind → $group → $sort → $lookup → $limit → 输出

各阶段作用:
  $match   ── 过滤文档（尽早使用，可利用索引）
  $project ── 重塑字段（投影/计算/重命名）
  $group   ── 按字段分组，计算聚合值
  $sort    ── 排序
  $limit   ── 限制输出数量
  $skip    ── 跳过指定数量
  $unwind  ── 展开数组字段为多个文档
  $lookup  ── 关联其他集合（类似 LEFT JOIN）
  $out     ── 将结果写入新集合

$match — 过滤文档

javascript

const activeUsers = await users.aggregate([
  { $match: { age: { $gte: 18 }, status: "active" } }
]).toArray();

$group — 分组聚合

javascript

const statsByCity = await orders.aggregate([
  {
    $group: {
      _id: "$city",
      totalRevenue: { $sum: "$amount" },
      avgOrder: { $avg: "$amount" },
      maxOrder: { $max: "$amount" },
      orderCount: { $sum: 1 }
    }
  },
  { $sort: { totalRevenue: -1 } }
]).toArray();

$project — 字段投影

javascript

const formatted = await users.aggregate([
  {
    $project: {
      _id: 0,
      fullName: { $concat: ["$firstName", " ", "$lastName"] },
      ageGroup: {
        $switch: {
          branches: [
            { case: { $lt: ["$age", 25] }, then: "youth" },
            { case: { $lt: ["$age", 40] }, then: "adult" }
          ],
          default: "senior"
        }
      },
      email: 1
    }
  }
]).toArray();

$lookup — 关联查询

javascript

const postsWithAuthors = await db.collection("posts").aggregate([
  {
    $lookup: {
      from: "users",
      localField: "authorId",
      foreignField: "_id",
      as: "author"
    }
  },
  { $unwind: "$author" },
  {
    $project: {
      title: 1,
      content: 1,
      "author.username": 1,
      "author.email": 1
    }
  }
]).toArray();

$lookup 管道形式

javascript

const usersWithRecentPosts = await users.aggregate([
  {
    $lookup: {
      from: "posts",
      let: { userId: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$authorId", "$$userId"] } } },
        { $match: { published: true } },
        { $sort: { createdAt: -1 } },
        { $limit: 5 },
        { $project: { title: 1, createdAt: 1 } }
      ],
      as: "recentPosts"
    }
  }
]).toArray();

完整聚合管道示例 — 电商订单分析

javascript

const monthlyReport = await db.collection("orders").aggregate([
  { $match: { status: "completed", createdAt: { $gte: new Date("2024-01-01") } } },
  {
    $group: {
      _id: { year: { $year: "$createdAt" }, month: { $month: "$createdAt" } },
      totalRevenue: { $sum: "$totalAmount" },
      orderCount: { $sum: 1 },
      avgOrderValue: { $avg: "$totalAmount" },
      uniqueCustomers: { $addToSet: "$customerId" }
    }
  },
  {
    $project: {
      _id: 0,
      year: "$_id.year",
      month: "$_id.month",
      totalRevenue: { $round: ["$totalRevenue", 2] },
      orderCount: 1,
      avgOrderValue: { $round: ["$avgOrderValue", 2] },
      customerCount: { $size: "$uniqueCustomers" }
    }
  },
  { $sort: { year: 1, month: 1 } }
]).toArray();

索引类型

MongoDB 支持多种索引类型，正确使用索引是查询性能的关键。

javascript

await users.createIndex({ email: 1 });

await users.createIndex({ username: 1, age: -1 });

await users.createIndex({ email: 1 }, { unique: true });

await posts.createIndex({ title: "text", content: "text" });

await stores.createIndex({ location: "2dsphere" });

await sessions.createIndex({ expireAt: 1 }, { expireAfterSeconds: 0 });

await users.createIndex(
  { premium_features: 1 },
  { partialFilterExpression: { role: "premium" } }
);

索引类型一览:

  类型            声明方式            适用场景
  ─────────────────────────────────────────────────────────
  单字段索引       { field: 1 }       单字段等值/范围查询
  复合索引         { a: 1, b: -1 }   多字段组合查询
  多键索引         { arrayField: 1 }  数组字段查询（自动识别）
  文本索引         { field: "text" }  全文搜索 ($text/$search)
  地理空间索引     { loc: "2dsphere" } 地理位置查询 ($near/$geoWithin)
  哈希索引         { field: "hashed" } 分片键，等值查询
  TTL 索引         { date: 1 }        文档自动过期（expireAfterSeconds）
  唯一索引         { unique: true }    唯一性约束
  部分索引         { partialFilter }   条件性索引，节省空间
  稀疏索引         { sparse: true }    仅索引存在该字段的文档

索引选择 ESR 原则 (Equality → Sort → Range):
  复合索引字段顺序: 等值匹配字段 → 排序字段 → 范围查询字段

  示例: db.orders.find({ status: "active", amount: { $gt: 100 } }).sort({ date: -1 })
  最优索引: { status: 1, date: -1, amount: 1 }

  注意: 索引占空间且影响写性能，每个集合建议不超过 10 个，用 explain() 分析命中情况

ORM 工具对比

Prisma vs TypeORM vs Drizzle vs Sequelize

维度	Prisma	TypeORM	Drizzle	Sequelize
Schema 定义	自定义 DSL (.prisma)	TypeScript 装饰器	TypeScript 函数	JavaScript 对象 / TS 装饰器
类型安全	⭐⭐⭐⭐⭐ 代码生成	⭐⭐⭐ 装饰器推断	⭐⭐⭐⭐⭐ 类型推导	⭐⭐ 手动类型
学习曲线	中（需学 DSL）	较高（装饰器 + 多模式）	低（SQL 映射）	中（API 多）
SQL 贴近度	低（高抽象 API）	中（QueryBuilder）	高（SQL-like）	低（高抽象）
包体积	大（含 Rust 引擎）	中	极小（~50KB）	大
冷启动	慢（Rust 引擎启动）	快	极快	中
数据库支持	PG/MySQL/SQLite/MongoDB	PG/MySQL/SQLite/Oracle/MSSQL	PG/MySQL/SQLite	PG/MySQL/SQLite/MSSQL
迁移工具	`prisma migrate`	`typeorm migration`	`drizzle-kit`	`sequelize-cli`
关联查询	`include`/`select` 声明式	`relations`/QueryBuilder	`with`/手动 join	`include` 声明式
原始 SQL	`$queryRaw`	`query()`	`sql` 模板	`sequelize.query()`
Serverless	较差（冷启动慢）	一般	极佳	一般
社区生态	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐（快速增长）	⭐⭐⭐⭐

选型决策树

ORM 选型决策:

  项目类型？
  ├── Serverless / Edge Runtime
  │   └── Drizzle（零冷启动、极小体积）
  │
  ├── 全栈快速开发（Next.js / Remix）
  │   └── Prisma（类型安全、DX 优秀、生态完善）
  │
  ├── 企业级大型项目
  │   ├── 团队熟悉 Java/NestJS 装饰器模式？
  │   │   └── TypeORM（装饰器 + Repository 模式）
  │   └── 需要精细 SQL 控制？
  │       └── Drizzle（SQL-like API）或 TypeORM（QueryBuilder）
  │
  ├── 已有 Express/Koa 项目
  │   ├── 使用 JavaScript？→ Sequelize（最早的 JS ORM，文档多）
  │   └── 使用 TypeScript？→ Prisma 或 Drizzle
  │
  └── 学习/原型项目
      └── Prisma（入门友好、Studio 可视化工具）

Prisma — Schema 语法与迁移工作流

Prisma 使用自定义 DSL（.prisma 文件），声明式定义数据模型。核心理念是"Schema 即真相（Single Source of Truth）"。

prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  username  String   @unique @db.VarChar(50)
  email     String   @db.VarChar(100)
  age       Int?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
  @@map("users")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String   @db.VarChar(200)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  tags      Tag[]
  createdAt DateTime @default(now())

  @@map("posts")
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  avatar String?
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique

  @@map("profiles")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]

  @@map("tags")
}

enum Role {
  ADMIN
  USER
  GUEST
}

迁移工作流

Prisma 迁移工作流:

  编写/修改 schema.prisma
         │
         ▼
  prisma migrate dev --name <name>     ← 开发环境：生成 SQL + 执行 + 生成 Client
         │
         ▼
  prisma/migrations/                   ← 生成的 SQL 迁移文件（提交到 Git）
  └── 20240101_init/
      └── migration.sql
         │
         ▼
  prisma migrate deploy                ← 生产环境：执行未应用的迁移

bash

npx prisma init

npx prisma migrate dev --name init

npx prisma migrate dev --name add_user_role

npx prisma generate

npx prisma db push

npx prisma migrate deploy

npx prisma studio

npx prisma db seed

Prisma CRUD

typescript

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

const prisma = new PrismaClient();

const user = await prisma.user.create({
  data: {
    username: "alice",
    email: "alice@example.com",
    age: 28,
    profile: {
      create: { bio: "Full-stack developer" }
    },
    posts: {
      create: [
        { title: "First Post", content: "Hello World" },
        { title: "Second Post", content: "Prisma is awesome" }
      ]
    }
  },
  include: { profile: true, posts: true }
});

const users = await prisma.user.findMany({
  where: {
    OR: [
      { username: { contains: "ali" } },
      { email: { endsWith: "@example.com" } }
    ],
    age: { gte: 20, lte: 40 },
    posts: { some: { published: true } }
  },
  select: {
    id: true,
    username: true,
    _count: { select: { posts: true } }
  },
  orderBy: { createdAt: "desc" },
  take: 10
});

const updated = await prisma.user.update({
  where: { id: 1 },
  data: {
    age: { increment: 1 },
    posts: {
      updateMany: {
        where: { published: false },
        data: { published: true }
      }
    }
  }
});

await prisma.user.upsert({
  where: { username: "alice" },
  update: { age: 30 },
  create: { username: "alice", email: "alice@example.com", age: 30 }
});

await prisma.user.delete({ where: { id: 1 } });

TypeORM — 装饰器模式与 Repository 模式

TypeORM 使用 TypeScript 装饰器在 Class 上定义实体，强调"代码即模型"。

TypeORM 装饰器体系:

  @Entity()               ── 定义实体类，映射数据库表
  ├── @PrimaryGeneratedColumn()  ── 自增主键 / UUID 主键
  ├── @Column()                  ── 普通列（type/length/nullable/unique/default）
  ├── @CreateDateColumn()        ── 自动创建时间
  ├── @UpdateDateColumn()        ── 自动更新时间
  ├── @DeleteDateColumn()        ── 软删除时间戳
  ├── @VersionColumn()           ── 乐观锁版本号
  ├── @OneToOne / @OneToMany / @ManyToOne / @ManyToMany  ── 关系
  ├── @JoinColumn()              ── 指定外键列
  └── @JoinTable()               ── 指定中间表

typescript

import {
  Entity, PrimaryGeneratedColumn, Column, OneToMany, ManyToOne,
  ManyToMany, JoinTable, OneToOne, JoinColumn,
  CreateDateColumn, UpdateDateColumn
} from "typeorm";

@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column({ type: "varchar", length: 50, unique: true })
  username: string;

  @Column({ type: "varchar", length: 100 })
  email: string;

  @Column({ type: "int", nullable: true })
  age: number | null;

  @OneToMany(() => Post, (post) => post.author, { cascade: true })
  posts: Post[];

  @OneToOne(() => Profile, (profile) => profile.user)
  profile: Profile;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

@Entity("posts")
export class Post {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: "varchar", length: 200 })
  title: string;

  @Column({ type: "text", nullable: true })
  content: string | null;

  @Column({ type: "boolean", default: false })
  published: boolean;

  @ManyToOne(() => User, (user) => user.posts)
  author: User;

  @ManyToMany(() => Tag, (tag) => tag.posts)
  @JoinTable({ name: "post_tags" })
  tags: Tag[];

  @CreateDateColumn()
  createdAt: Date;
}

@Entity("tags")
export class Tag {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: "varchar", length: 50, unique: true })
  name: string;

  @ManyToMany(() => Post, (post) => post.tags)
  posts: Post[];
}

Repository 模式

typescript

import { DataSource, MoreThanOrEqual, Like } from "typeorm";

const AppDataSource = new DataSource({
  type: "postgres",
  host: "localhost",
  port: 5432,
  username: "postgres",
  password: "password",
  database: "myapp",
  entities: [User, Post, Tag],
  migrations: ["src/migrations/*.ts"],
  synchronize: false,
  logging: true,
  poolSize: 10
});

await AppDataSource.initialize();

const userRepo = AppDataSource.getRepository(User);

const user = userRepo.create({
  username: "alice",
  email: "alice@example.com",
  age: 28
});
await userRepo.save(user);

const users = await userRepo.find({
  where: { age: MoreThanOrEqual(25) },
  relations: { posts: true, profile: true },
  order: { createdAt: "DESC" },
  take: 10
});

const result = await userRepo
  .createQueryBuilder("user")
  .select(["user.id", "user.username"])
  .addSelect("COUNT(post.id)", "postCount")
  .leftJoin("user.posts", "post")
  .where("user.age >= :minAge", { minAge: 25 })
  .groupBy("user.id")
  .having("COUNT(post.id) > :min", { min: 3 })
  .orderBy("postCount", "DESC")
  .getRawMany();

TypeORM 迁移

bash

npx typeorm migration:generate src/migrations/CreateUsers -d src/data-source.ts

npx typeorm migration:create src/migrations/AddUserRole

npx typeorm migration:run -d src/data-source.ts

npx typeorm migration:revert -d src/data-source.ts

Drizzle — SQL-like API 设计哲学

Drizzle 是新一代 TypeScript ORM，核心理念是"如果你懂 SQL，你就懂 Drizzle"。查询 API 几乎是 SQL 的一对一映射，无需代码生成步骤，类型直接从 Schema 推导。

Drizzle 核心特点:
  1. 零抽象泄漏 —— API 与 SQL 语义一一对应
  2. 无代码生成 —— 类型直接从 Schema 推导
  3. 极致轻量  —— 无 Rust 引擎，包体积约 50KB
  4. SQL-like  —— select().from().where().orderBy()
  5. Serverless 友好 —— 无冷启动开销，适合 Edge/Lambda

typescript

import {
  pgTable, serial, varchar, text, integer,
  boolean, timestamp, pgEnum
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const roleEnum = pgEnum("role", ["ADMIN", "USER", "GUEST"]);

export const users = pgTable("users", {
  id:        serial("id").primaryKey(),
  username:  varchar("username", { length: 50 }).notNull().unique(),
  email:     varchar("email", { length: 100 }).notNull(),
  age:       integer("age"),
  role:      roleEnum("role").default("USER"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow()
});

export const posts = pgTable("posts", {
  id:        serial("id").primaryKey(),
  title:     varchar("title", { length: 200 }).notNull(),
  content:   text("content"),
  published: boolean("published").default(false),
  authorId:  integer("author_id").notNull().references(() => users.id),
  createdAt: timestamp("created_at").defaultNow()
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts)
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] })
}));

SQL-like 查询 API

typescript

import { eq, gte, and, desc, like, sql, count } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "./schema";

const db = drizzle(pool, { schema });

await db.insert(users).values({
  username: "alice",
  email: "alice@example.com",
  age: 28
});

const result = await db
  .select()
  .from(users)
  .where(and(gte(users.age, 25), like(users.email, "%@example.com")))
  .orderBy(desc(users.createdAt))
  .limit(10);

const userPosts = await db
  .select({
    username: users.username,
    postTitle: posts.title,
    postCount: count(posts.id)
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.username, posts.title)
  .having(gte(count(posts.id), 1));

await db.update(users).set({ age: 30 }).where(eq(users.username, "alice"));

await db.delete(users).where(eq(users.id, 1));

const usersWithPosts = await db.query.users.findMany({
  where: gte(users.age, 25),
  with: {
    posts: {
      where: eq(posts.published, true),
      limit: 5
    }
  },
  orderBy: desc(users.createdAt),
  limit: 10
});

await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: sql`balance - 100` }).where(eq(accounts.id, 1));
  await tx.update(accounts).set({ balance: sql`balance + 100` }).where(eq(accounts.id, 2));
});

Drizzle 迁移

bash

npx drizzle-kit generate

npx drizzle-kit push

npx drizzle-kit migrate

npx drizzle-kit studio

typescript

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!
  }
});

Sequelize — 经典 ORM

Sequelize 是 Node.js 生态中最早的 ORM 之一，API 风格接近 ActiveRecord 模式，支持 JavaScript 和 TypeScript。

typescript

import { Sequelize, DataTypes, Model } from "sequelize";

const sequelize = new Sequelize(process.env.DATABASE_URL!, {
  dialect: "postgres",
  logging: false,
  pool: {
    max: 10,
    min: 2,
    acquire: 30000,
    idle: 10000
  }
});

class User extends Model {}
User.init(
  {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    username: { type: DataTypes.STRING(50), allowNull: false, unique: true },
    email: { type: DataTypes.STRING(100), allowNull: false },
    age: { type: DataTypes.INTEGER, allowNull: true }
  },
  { sequelize, modelName: "User", tableName: "users", timestamps: true }
);

class Post extends Model {}
Post.init(
  {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    title: { type: DataTypes.STRING(200), allowNull: false },
    content: { type: DataTypes.TEXT },
    published: { type: DataTypes.BOOLEAN, defaultValue: false }
  },
  { sequelize, modelName: "Post", tableName: "posts", timestamps: true }
);

User.hasMany(Post, { foreignKey: "userId", as: "posts" });
Post.belongsTo(User, { foreignKey: "userId", as: "author" });

const user = await User.create({
  username: "alice",
  email: "alice@example.com",
  age: 28
});

const users = await User.findAll({
  where: { age: { [Op.gte]: 25 } },
  include: [{ model: Post, as: "posts", where: { published: true }, required: false }],
  order: [["createdAt", "DESC"]],
  limit: 10
});

await User.update({ age: 30 }, { where: { username: "alice" } });

await User.destroy({ where: { id: 1 } });

const result = await sequelize.transaction(async (t) => {
  const sender = await Account.findByPk(1, { transaction: t });
  const receiver = await Account.findByPk(2, { transaction: t });

  await sender.decrement("balance", { by: 100, transaction: t });
  await receiver.increment("balance", { by: 100, transaction: t });

  return { sender, receiver };
});

Sequelize vs 新一代 ORM:

  Sequelize 的历史包袱:
  ├── TypeScript 支持是后加的，类型推断不完整
  ├── API 风格偏 JavaScript，装饰器需要 sequelize-typescript 扩展
  ├── 迁移工具 (sequelize-cli) 生成的是 JavaScript 文件
  └── 查询 API 中 Op 符号写法略显冗余

  仍然选择 Sequelize 的理由:
  ├── 已有大量 JavaScript 项目在使用
  ├── 社区文档和 Stack Overflow 资源最多
  ├── 支持数据库种类多（含 MSSQL）
  └── 团队对 ActiveRecord 模式更熟悉

Node.js 数据库连接

连接池原理与配置

没有连接池:
  请求 ──→ 建立连接(TCP+认证~20ms) ──→ 执行SQL ──→ 关闭连接
  请求 ──→ 建立连接(TCP+认证~20ms) ──→ 执行SQL ──→ 关闭连接
  ... 每次请求都重复

使用连接池:
  ┌─────────────────────────────────────────────┐
  │                 连接池                        │
  │  ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐       │
  │  │空闲 │ │使用中│ │空闲 │ │空闲 │ │使用中│       │
  │  └────┘ └────┘ └────┘ └────┘ └────┘       │
  └─────────────────────────────────────────────┘
  请求 ──→ 获取空闲连接 ──→ 执行SQL ──→ 归还连接   耗时: ~1ms

  连接生命周期:
  创建 → 空闲等待 → 被借出 → 执行查询 → 归还 → 空闲等待 → ... → 超时销毁

连接池大小经验公式:

  Pool Size = (CPU 核心数 × 2) + 有效磁盘数

  例如: 4 核 CPU + 1 块 SSD → Pool Size = 9 ≈ 10
  PM2 cluster 模式（4 个进程）: 每个进程 = 总连接数 / 进程数

配置项	推荐值	说明
`max` / `connectionLimit`	10-20	最大连接数，根据公式计算
`min` / `minPoolSize`	2-5	最小空闲连接，避免冷启动
`idleTimeoutMillis`	10000-30000	空闲连接超时释放时间
`connectionTimeoutMillis`	2000-5000	获取连接超时
`acquireTimeout`	10000	等待队列超时

PostgreSQL 连接池（pg）

typescript

import { Pool } from "pg";

const pool = new Pool({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || "5432"),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  max: 15,
  min: 3,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 3000
});

pool.on("error", (err) => {
  console.error("Unexpected pool error:", err);
});

async function query(text: string, params?: any[]) {
  const start = Date.now();
  const res = await pool.query(text, params);
  const duration = Date.now() - start;
  if (duration > 1000) {
    console.warn("Slow query detected:", { text, duration });
  }
  return res;
}

MySQL 连接池（mysql2）

typescript

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || "3306"),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  maxIdle: 10,
  idleTimeout: 60000,
  queueLimit: 0
});

事务处理模式

事务 ACID:

  A (Atomicity)  原子性 ── 事务中的操作要么全成功，要么全回滚
  C (Consistency) 一致性 ── 事务前后数据满足所有约束
  I (Isolation)   隔离性 ── 并发事务互不干扰
  D (Durability)  持久性 ── 提交后的数据持久保存

隔离级别（从低到高）:
  READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE
  脏读       ✗            ✓               ✓                ✓
  不可重复读  ✗            ✗               ✓                ✓
  幻读       ✗            ✗               ✗(InnoDB gap lock) ✓

原生 SQL 事务

typescript

const client = await pool.connect();
try {
  await client.query("BEGIN");

  await client.query(
    "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
    [100, senderId]
  );

  await client.query(
    "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
    [100, receiverId]
  );

  await client.query("COMMIT");
} catch (e) {
  await client.query("ROLLBACK");
  throw e;
} finally {
  client.release();
}

Prisma 事务

typescript

const [updatedSender, updatedReceiver] = await prisma.$transaction([
  prisma.account.update({
    where: { id: 1 },
    data: { balance: { decrement: 100 } }
  }),
  prisma.account.update({
    where: { id: 2 },
    data: { balance: { increment: 100 } }
  })
]);

const result = await prisma.$transaction(async (tx) => {
  const sender = await tx.account.update({
    where: { id: 1 },
    data: { balance: { decrement: 100 } }
  });

  if (sender.balance < 0) {
    throw new Error("Insufficient balance");
  }

  const receiver = await tx.account.update({
    where: { id: 2 },
    data: { balance: { increment: 100 } }
  });

  return { sender, receiver };
}, {
  maxWait: 5000,
  timeout: 10000,
  isolationLevel: "Serializable"
});

数据库迁移（Migration）工作流

迁移的本质:

  迁移是数据库 Schema 的版本控制。
  每个迁移文件描述一次 Schema 变更，包含 up（应用）和 down（回滚）两个方向。

  代码仓库                          数据库
  ┌──────────────┐                ┌──────────────┐
  │ migration_001│ ──── up() ───→ │ CREATE TABLE │
  │ migration_002│ ──── up() ───→ │ ADD COLUMN   │
  │ migration_003│ ──── up() ───→ │ CREATE INDEX │
  └──────────────┘                └──────────────┘
                   ←── down() ──  (回滚到上一版本)

  工作流:
  1. 修改 Schema 定义（ORM Model / Prisma Schema）
  2. 生成迁移文件（自动 diff 或手动编写）
  3. 本地执行迁移，验证正确性
  4. 提交迁移文件到 Git
  5. CI/CD 在部署时执行 migrate deploy

ORM	生成迁移	执行迁移	回滚	迁移文件格式
Prisma	`prisma migrate dev`	`prisma migrate deploy`	不支持自动回滚	SQL
TypeORM	`migration:generate`	`migration:run`	`migration:revert`	TypeScript
Drizzle	`drizzle-kit generate`	`drizzle-kit migrate`	手动	SQL
Sequelize	`migration:generate`	`db:migrate`	`db:migrate:undo`	JavaScript

环境变量管理数据库凭证

原则:
  1. 数据库密码、连接字符串 永远不能硬编码在源码中
  2. 不同环境（dev/staging/prod）使用不同凭证
  3. .env 文件 永远不能提交到 Git

文件结构:
  project/
  ├── .env                 ← 本地开发（Git 忽略）
  ├── .env.example         ← 模板文件（提交到 Git）
  ├── .gitignore           ← 包含 .env
  └── src/
      └── config/
          └── database.ts  ← 读取环境变量

DATABASE_URL=postgresql://user:password@localhost:5432/mydb?schema=public

typescript

import { z } from "zod";

const envSchema = z.object({
  DATABASE_URL: z.string().url(),
  DB_HOST: z.string().default("localhost"),
  DB_PORT: z.coerce.number().default(5432),
  DB_USER: z.string(),
  DB_PASSWORD: z.string(),
  DB_NAME: z.string(),
  DB_POOL_MAX: z.coerce.number().default(10),
  DB_POOL_MIN: z.coerce.number().default(2),
  NODE_ENV: z.enum(["development", "staging", "production"]).default("development")
});

const env = envSchema.parse(process.env);

export const dbConfig = {
  host: env.DB_HOST,
  port: env.DB_PORT,
  user: env.DB_USER,
  password: env.DB_PASSWORD,
  database: env.DB_NAME,
  max: env.DB_POOL_MAX,
  min: env.DB_POOL_MIN
};

SQL 注入防御

什么是 SQL 注入？

SQL 注入是最经典的 Web 安全攻击之一。攻击者通过在用户输入中嵌入恶意 SQL 片段，篡改原始 SQL 语句的逻辑，从而窃取数据、修改数据甚至删除整个数据库。

攻击原理:

  正常输入: username = "alice"
  拼接后:   SELECT * FROM users WHERE username = 'alice'   ← 正常

  恶意输入: username = "' OR '1'='1"
  拼接后:   SELECT * FROM users WHERE username = '' OR '1'='1'   ← 返回全部数据！

  更危险:   username = "'; DROP TABLE users; --"
  拼接后:   SELECT * FROM users WHERE username = ''; DROP TABLE users; --'
            ← 删除整张表！

常见注入攻击模式

1. 万能密码:
   输入: admin' --
   SQL:  SELECT * FROM users WHERE username = 'admin' --' AND password = '...'
   效果: 注释掉密码校验

2. UNION 注入:
   输入: ' UNION SELECT username, password FROM users --
   SQL:  SELECT name FROM products WHERE id = '' UNION SELECT username, password FROM users --'
   效果: 泄露用户表数据

3. 盲注 (Boolean-based):
   输入: ' AND (SELECT COUNT(*) FROM users) > 0 --
   效果: 根据页面返回差异推断数据库信息

4. 时间盲注 (Time-based):
   输入: ' AND SLEEP(5) --
   效果: 根据响应时间推断查询是否成功

5. 二次注入:
   注册用户名: admin'--
   修改密码时: UPDATE users SET password='new' WHERE username='admin'--'
   效果: 修改了 admin 的密码

参数化查询（核心防御手段）

参数化查询将 SQL 结构和用户输入完全分离，数据库驱动会自动对参数进行转义处理。

typescript

const username = req.body.username;
const badQuery = `SELECT * FROM users WHERE username = '${username}'`;

const { rows } = await pool.query(
  "SELECT * FROM users WHERE username = $1",
  [username]
);

const [rows] = await mysqlPool.execute(
  "SELECT * FROM users WHERE username = ? AND age > ?",
  [username, minAge]
);

const result = await pool.query(
  "SELECT * FROM users WHERE username = $1 AND status = ANY($2::text[])",
  [username, ["active", "verified"]]
);

const orderBy = ["created_at", "username", "age"].includes(req.query.sort)
  ? req.query.sort
  : "created_at";
const direction = req.query.order === "ASC" ? "ASC" : "DESC";
const { rows } = await pool.query(
  `SELECT * FROM users ORDER BY ${orderBy} ${direction} LIMIT $1 OFFSET $2`,
  [limit, offset]
);

参数化查询原理:

  普通拼接:
    SQL = "SELECT * FROM users WHERE name = '" + input + "'"
    → 输入和 SQL 结构混在一起 → 可被注入

  参数化:
    SQL = "SELECT * FROM users WHERE name = $1"
    参数 = [input]
    → 数据库先编译 SQL 结构，再绑定参数
    → 参数只被当作「值」处理，永远不会被解释为 SQL 关键字

ORM 的自动防护

所有主流 ORM 默认使用参数化查询，提供了天然的 SQL 注入防护。

typescript

const user = await prisma.user.findFirst({
  where: { username: userInput }
});

const safe = await prisma.$queryRaw`
  SELECT * FROM users WHERE username = ${userInput}
`;

const unsafe = await prisma.$queryRawUnsafe(
  `SELECT * FROM users WHERE username = '${userInput}'`
);

const users = await userRepo.find({
  where: { username: userInput }
});

const safe = await userRepo
  .createQueryBuilder("user")
  .where("user.username = :name", { name: userInput })
  .getMany();

const unsafe = await userRepo
  .createQueryBuilder("user")
  .where(`user.username = '${userInput}'`)
  .getMany();

const result = await db
  .select()
  .from(users)
  .where(eq(users.username, userInput));

ORM 防注入总结:

  ✅ 安全:
  ├── Prisma: findMany/findFirst + where 条件
  ├── Prisma: $queryRaw 模板字符串（Tagged Template）
  ├── TypeORM: find + where / QueryBuilder + 参数绑定
  ├── Drizzle: eq/gte/like 等函数
  └── 所有 ORM: 原生参数化查询

  ❌ 危险:
  ├── 任何 ORM 的 rawQuery + 字符串拼接
  ├── Prisma: $queryRawUnsafe + 拼接
  ├── TypeORM: QueryBuilder + 模板字符串拼接
  └── 动态表名/列名（不经过白名单校验）

防御清单

措施	优先级	说明
参数化查询	必须	永远不要拼接 SQL 字符串
使用 ORM	推荐	默认参数化，减少手写 SQL
输入校验	必须	用 zod/joi 校验输入类型和格式
最小权限原则	必须	应用账户只赋予必要的数据库权限
白名单校验	重要	动态列名/表名必须经过白名单
WAF	辅助	Web 应用防火墙拦截常见注入模式
错误信息脱敏	必须	生产环境不暴露 SQL 错误详情
定期审计	重要	代码审查 + 安全扫描工具

面试高频问题

1. 解释什么是 N+1 查询问题，以及在 ORM 中如何解决？

关键点：N+1 是指查询 N 条主记录后，为每条记录单独查询关联数据，导致 1+N 次数据库请求。解决方案：在 Prisma 中使用 include 或 select 实现 eager loading；在 TypeORM 中使用 relations 选项或 leftJoinAndSelect；在 Drizzle 中使用 with 进行关联查询。也可以使用 DataLoader 进行批量加载（合并多个 WHERE id = ? 为 WHERE id IN (...)）。

2. 连接池的作用是什么？如何确定合适的连接池大小？

关键点：连接池预先创建并维护一组数据库连接，避免每次请求都经历 TCP 握手和认证的开销（每次约 20ms）。推荐公式是 (CPU核心数 × 2) + 磁盘数。在 PM2 cluster 模式下，需要将总连接数除以进程数。连接池过大会导致数据库端资源竞争加剧（上下文切换、内存占用），过小会导致请求排队等待。需要监控 idleCount、waitingCount 等指标进行调优。

3. Prisma、TypeORM、Drizzle 三者的类型安全有什么区别？

关键点：Prisma 通过 prisma generate 生成完整的 TypeScript 类型，类型覆盖最全面，包括查询参数和返回值的精确类型。Drizzle 利用 TypeScript 高级类型推导，从 Schema 定义中自动推断类型，无需代码生成步骤。TypeORM 依赖装饰器和运行时元数据，类型安全程度最低，QueryBuilder 返回的类型通常需要手动断言。

4. 在 MongoDB 中，嵌套文档和引用文档如何选择？

关键点：嵌套适合一对一、一对少量的关系，数据读取频繁、整体操作的场景（如用户的地址信息）。引用适合一对多、多对多的关系，子数据独立变更频繁的场景（如文章与评论）。核心考虑因素是：文档大小限制（16MB）、数据冗余程度、查询模式和更新频率。如果子文档数量不确定且可能无限增长，必须使用引用模式。

5. ACID 和 BASE 分别是什么？各适用于什么场景？

关键点：ACID（原子性、一致性、隔离性、持久性）是关系型数据库的事务保证，适用于金融交易、库存管理等强一致场景。BASE（基本可用、软状态、最终一致性）是 NoSQL 的设计理念，适用于社交动态、日志采集、实时推荐等允许短暂不一致的高可用场景。现代数据库中两者的界限在模糊——MongoDB 4.0+ 支持多文档 ACID 事务，PostgreSQL 也在增强 JSON 处理能力。

6. 游标分页和偏移分页的区别？为什么大数据量场景推荐游标分页？

关键点：偏移分页（OFFSET/LIMIT）在翻到后面页时，数据库仍需扫描并丢弃前面的所有行，性能随页数线性下降（第 100 页需要扫描前 99 页的所有数据）。游标分页（WHERE id > cursor LIMIT N）利用索引直接定位起始点，性能稳定不受页数影响。缺点是不能随机跳页，只能前后翻页，适合无限滚动、时间线等场景。实现要点：游标字段必须有唯一索引，排序方向必须与索引方向一致。

7. 如何防止 SQL 注入？ORM 是否能完全防止注入？

关键点：核心防御手段是参数化查询，将 SQL 结构和数据分离。ORM 默认使用参数化查询，提供了天然防护，但不能完全防止——如果使用 ORM 提供的原始 SQL 接口（如 Prisma 的 $queryRawUnsafe、TypeORM 的 query()）并拼接用户输入，仍然会被注入。此外，动态表名和列名无法参数化，必须通过白名单校验。完整防御需要：参数化查询 + 输入校验 + 最小权限原则 + 错误信息脱敏。

8. Prisma 的 `$transaction` 有几种用法？它们有什么区别？

关键点：两种用法：（1）批量事务——传入 Prisma 操作数组，所有操作在一个事务中顺序执行，但无法使用中间结果进行条件判断；（2）交互式事务——传入异步回调函数，可以获取每步结果并基于结果做条件判断，支持 maxWait、timeout、isolationLevel 等配置。交互式事务更灵活但持有连接时间更长，适用于转账、库存扣减等需要中间状态校验的场景。批量事务适用于简单的多步写入操作。

数据库基础 ​

关系型数据库基础 ​

什么是关系型数据库？ ​

MySQL vs PostgreSQL 特性对比 ​

数据类型差异 ​

核心特性对比 ​

SQL 基础：CRUD 完整语法 ​

CREATE — 建表 ​

INSERT — 插入 ​

SELECT — 查询 ​

UPDATE — 更新 ​

DELETE — 删除 ​

JOIN 类型详解 ​

五种 JOIN 图示 ​

JOIN SQL 示例 ​

子查询与 CTE ​

子查询 ​

CTE（WITH 语句） ​

递归 CTE ​

聚合函数与 GROUP BY / HAVING ​

MongoDB 文档模型 ​

文档模型 vs 关系模型的思维差异 ​

嵌入文档 vs 引用文档设计决策 ​

CRUD 操作 ​

聚合管道 ​

索引类型 ​

ORM 工具对比 ​

Prisma vs TypeORM vs Drizzle vs Sequelize ​

选型决策树 ​

Prisma — Schema 语法与迁移工作流 ​

TypeORM — 装饰器模式与 Repository 模式 ​

Drizzle — SQL-like API 设计哲学 ​

Sequelize — 经典 ORM ​

Node.js 数据库连接 ​

连接池原理与配置 ​

事务处理模式 ​

数据库迁移（Migration）工作流 ​

环境变量管理数据库凭证 ​

SQL 注入防御 ​

什么是 SQL 注入？ ​

常见注入攻击模式 ​

参数化查询（核心防御手段） ​

ORM 的自动防护 ​

防御清单 ​

面试高频问题 ​

1. 解释什么是 N+1 查询问题，以及在 ORM 中如何解决？ ​

2. 连接池的作用是什么？如何确定合适的连接池大小？ ​

3. Prisma、TypeORM、Drizzle 三者的类型安全有什么区别？ ​

4. 在 MongoDB 中，嵌套文档和引用文档如何选择？ ​

5. ACID 和 BASE 分别是什么？各适用于什么场景？ ​

6. 游标分页和偏移分页的区别？为什么大数据量场景推荐游标分页？ ​

7. 如何防止 SQL 注入？ORM 是否能完全防止注入？ ​

8. Prisma 的 $transaction 有几种用法？它们有什么区别？ ​

延伸阅读 ​

数据库基础

关系型数据库基础

什么是关系型数据库？

MySQL vs PostgreSQL 特性对比

数据类型差异

核心特性对比

SQL 基础：CRUD 完整语法

CREATE — 建表

INSERT — 插入

SELECT — 查询

UPDATE — 更新

DELETE — 删除

JOIN 类型详解

五种 JOIN 图示

JOIN SQL 示例

子查询与 CTE

子查询

CTE（WITH 语句）

递归 CTE

聚合函数与 GROUP BY / HAVING

MongoDB 文档模型

文档模型 vs 关系模型的思维差异

嵌入文档 vs 引用文档设计决策

CRUD 操作

聚合管道

索引类型

ORM 工具对比

Prisma vs TypeORM vs Drizzle vs Sequelize

选型决策树

Prisma — Schema 语法与迁移工作流

TypeORM — 装饰器模式与 Repository 模式

Drizzle — SQL-like API 设计哲学

Sequelize — 经典 ORM

Node.js 数据库连接

连接池原理与配置

事务处理模式

数据库迁移（Migration）工作流

环境变量管理数据库凭证

SQL 注入防御

什么是 SQL 注入？

常见注入攻击模式

参数化查询（核心防御手段）

ORM 的自动防护

防御清单

面试高频问题

1. 解释什么是 N+1 查询问题，以及在 ORM 中如何解决？

2. 连接池的作用是什么？如何确定合适的连接池大小？

3. Prisma、TypeORM、Drizzle 三者的类型安全有什么区别？

4. 在 MongoDB 中，嵌套文档和引用文档如何选择？

5. ACID 和 BASE 分别是什么？各适用于什么场景？

6. 游标分页和偏移分页的区别？为什么大数据量场景推荐游标分页？

7. 如何防止 SQL 注入？ORM 是否能完全防止注入？

8. Prisma 的 `$transaction` 有几种用法？它们有什么区别？

延伸阅读