10 · 数据建模与数据访问：SQL + ORM 使用侧

前端用对象在内存里倒腾数据，后端要把数据落进表里。本篇讲"怎么建模、怎么建表、怎么查、怎么保证正确"——只讲使用侧，内部原理（索引/B+树/事务隔离）留给 DB 域。

速览卡

项	内容
一句话定位	前端转后端的第二块硬骨头：从 JSON 对象到关系表，再从表读回对象
核心内容	对象 → 表的建模思维、迁移、ORM 基本用法、查询与分页、索引使用侧、事务边界、连接池
读完能回答	一个嵌套 JSON 该怎么拆表？ORM 到底帮我干了什么、又藏了什么坑？什么时候该自己写 SQL？
相关板块	C09 · 服务端语言上手 ｜ DB 数据库与存储 ｜ C04 · 一致性事务

---

一、建模思维：从 JSON 到表

前端对象天生支持嵌套，关系型数据库用扁平行 + 外键表达关系。关键转换：

{
  "user": { "id": 1, "name": "张三" },
  "orders": [
    { "id": 10, "total": 99.0 }
  ]
}

拆成三张表：

表	行示例
users	id=1, name='张三'
orders	id=10, user_id=1, total=99.0

建模判断：一个字段是"一个值"→ 放当前表；是"多个值"→ 拆子表；是"别的对象"→ 存外键或关联表。

1.1 一对一、一对多、多对多

关系	数据库表示	前端直觉
一对一	一边存另一边的主键作外键	一个对象里嵌一个对象
一对多	多的那边存少的那边的主键	一个对象里嵌对象数组
多对多	中间关联表（两列外键）	两个集合互相引用

⚠️ 前端"对象里嵌数组"的直觉在数据库里是反的：一对多时外键放在"多"的那张表上。

---

二、建表与迁移

2.1 为什么用迁移，而不是手敲 DDL

迁移（migration）是"带版本号的建表脚本"，对应前端的数据库 schema 的 git：

• 每次结构变更生成一个迁移文件，可回滚、可评审、可多人协作
• 生产库按迁移顺序执行，避免"我本地加了字段你没加"

2.2 SQLAlchemy + Alembic 最小示例

# models.py
from sqlalchemy import String, Integer, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))

alembic init alembic
alembic revision --autogenerate -m "create users"
alembic upgrade head

类比：alembic upgrade head 像 git pull 把数据库 schema 拉到最新；downgrade 像 git checkout 回到上个版本。

---

三、CRUD 与查询：ORM 使用侧

from sqlalchemy import select
from sqlalchemy.orm import Session


def create_user(session: Session, name: str) -> User:
    user = User(name=name)
    session.add(user)
    session.commit()
    session.refresh(user)   # 拿回数据库生成的自增 id
    return user


def list_users(session: Session, page: int = 1, size: int = 20):
    stmt = select(User).offset((page - 1) * size).limit(size)
    return session.scalars(stmt).all()

三件要主动做的事（ORM 不会替你自动做）：

1. commit() 才真正落库；
2. refresh() 拿回数据库自动生成的值；
3. 分页要自己算 offset/limit（还要注意第 1 页是 offset 0）。

3.1 什么时候自己写 SQL

场景	用 ORM	手写 SQL
简单增删改查	✅	—
复杂统计、报表、多表聚合	—	✅（或 ORM 的 select 聚合）
需要精细控制索引使用	—	✅（配合 EXPLAIN）

原则：默认 ORM，性能或可读性成问题时再手写，并保留 EXPLAIN 验证。

---

四、索引使用侧：知道这些就够了

详细原理（B+树、聚簇索引）见 DB 域。使用侧只需记住：

原则	说明
主键自动索引	按 id 查就是快的
外键列要加索引	关联查询/删除时的性能关键
高频过滤列加索引	WHERE status = ? 高频就加
不要到处加索引	每个索引都拖慢写入，只加在"真的在查"的列上
看执行计划	EXPLAIN ANALYZE SELECT ...，不信直觉信它

用 EXPLAIN 验证是否走了索引，这是后端排查慢查询的第一动作。

---

五、事务边界：把"多步操作"当一件事

def transfer(session: Session, from_id: int, to_id: int, amount: int):
    with session.begin():                 # 事务开始
        a = session.get(Account, from_id)
        b = session.get(Account, to_id)
        a.balance -= amount
        b.balance += amount
    # with 结束自动 commit，异常自动 rollback

什么时候包事务：多步写操作必须"要么全成功要么全失败"时。转账、下单、注册（写用户 + 初始化配置）都要包。

什么时候别乱包：只有读不用事务；跨服务无法用本地事务（见 C04）。

---

六、连接池：别每次新建连接

建立数据库连接很贵（TCP + 握手 + 认证），所以要复用：

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg://user:pass@localhost/db",
    pool_size=10,          # 保持的连接数
    max_overflow=20,       # 峰值额外可建的连接
)

类比：连接池像前端的 HTTP keep-alive 连接复用。连接数不是越大越好——数据库能同时处理的数量有限，超了只会排队。

---

自检清单

• [ ] 能把一个嵌套 JSON 正确拆成表（知道外键放"多"那边）
• [ ] 会用 Alembic 做一次迁移（建表/改表/回滚）
• [ ] 会写 ORM 的 CRUD，并记得 commit / refresh
• [ ] 会用 EXPLAIN 判断查询是否走索引
• [ ] 能说清什么时候该用事务、什么时候别乱用
• [ ] 知道连接池是什么、为什么连接数不是越大越好

常见误区

• 误区：ORM 会自动建表。 不会。表结构变更要显式做迁移。
• 误区：对象嵌套直接对应表嵌套。 反了。一对多的外键放在"多"那边。
• 误区：加索引只有好处。 每个索引都拖慢写，只加高频查询列。
• 误区：连接池越大越好。 数据库并发处理有限，过大只是排队。

动手练习

练习 A：把 Todo 模型落库

给 C09 的 Todo API 加 PostgreSQL 存储：建 todos 表，写迁移，把内存存储换成 ORM。

练习 B：做一个一对多

建 users + orders 两张表，实现"按用户查订单"接口，并用 EXPLAIN 确认走了外键索引。

练习 C：事务练习

写一个 transfer 接口（余额转移），故意制造失败，确认两边要么都变要么都不变。

---

相关文档

本区

• 09 · 服务端语言上手 —— 前置：先有服务
• 04 · 一致性与事务 —— 事务的正确性深挖
• 11 · 后端测试实战 —— 给数据访问层写测试
• 13 · 缓存与 Redis —— 读写分离与缓存

其他域

• DB 数据库与存储 —— 索引/B+树/事务隔离内部原理
• 11 · pgvector 向量检索 —— 向量列与 RAG 存储