数据库设计与 ORM 实践

基于 SQLite + SQLAlchemy 的个人博客系统数据库设计总结

一、技术选型：为什么选 SQLite？

个人博客的数据库选型其实没什么悬念——单用户、低并发、零运维，SQLite 是最优解。

对比一下常见选项：

维度	SQLite	MySQL / PostgreSQL	说明
安装	零配置	需安装服务进程	SQLite 只是一个文件
运维	无	备份、调优、连接池	一个人的博客不需要 DBA
性能	单机足够	高并发优势	日 PV < 1000 没区别
备份	cp 即可	mysqldump / pg_dump	复制文件就完成备份
功能	不支持并发写	完整事务支持	个人博客只有一个人在后台写

核心结论：不要为了用数据库而用数据库。 如果你的项目只有你一个人操作、数据量在百万以下、不需要分布式——SQLite 是正确选择，MySQL 反而是过度工程。

二、ER 图（文字版）

┌───────────┐    ┌────────────┐    ┌───────────┐
│  Category  │1──N│  Article    │N──M│    Tag     │
│           │    │            │    │           │
│ id        │    │ id         │    │ id        │
│ name      │    │ title      │    │ name      │
│ slug      │    │ slug       │    │ slug      │
│ desc      │    │ content_md │    └───────────┘
└───────────┘    │ content_htm│
                 │ category_id│─┐
                 │ tags (M:N) │ │
                 │ is_publishe│ │
                 │ view_count │ │
                 └────────────┘ │
                                │
┌───────────┐    ┌────────────┐ │
│  Comment   │N──1│  Article    │─┘
│           │    └────────────┘
│ id        │
│ article_id│─┘
│ parent_id │──┐（自引用，楼中楼预留）
│ author    │  │
│ content   │  │
│ is_approved│ │
└───────────┘  │

┌───────────┐  │  ┌──────────────┐
│   Admin   │    │  SiteSetting   │
│           │    │               │
│ id        │    │ key (PK)     │
│ username  │    │ value        │
│ password_h│    └──────────────┘
│ nickname  │
└───────────┘

┌───────────┐    ┌──────────────┐
│   Share    │1──N│ DownloadLog   │
│           │    │               │
│ id        │    │ share_id     │
│ file_name │    │ downloaded_at│
│ password_h│    └──────────────┘
│ download_l│
│ expire_at │
└───────────┘

8 张表，覆盖了博客的所有功能。

三、模型设计要点

3.1 文章表的多态字段设计

class Article(Base):
    __tablename__ = "articles"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(200), nullable=False)
    slug = Column(String(200), unique=True, nullable=False, index=True)
    content_md = Column(Text, nullable=False)
    content_html = Column(Text, default="")
    summary = Column(String(500), default="")
    cover_image = Column(String(500), default="")
    category_id = Column(Integer, ForeignKey("categories.id", ondelete="SET NULL"), nullable=True)
    is_published = Column(Boolean, default=False)
    is_top = Column(Boolean, default=False)
    allow_comment = Column(Boolean, default=True)
    view_count = Column(Integer, default=0)
    meta = Column(JSON, default=dict)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    published_at = Column(DateTime, nullable=True)

    category = relationship("Category", back_populates="articles")
    tags = relationship("Tag", secondary=article_tag, back_populates="articles")

几个实用设计：

meta 字段（JSON 类型）： 存放扩展属性而不改表结构。访问次数、阅读时长等临时性数据都往这里放，避免了频繁加列的麻烦。

content_md + content_html 双字段： Markdown 原文存一份，渲染后的 HTML 缓存一份。每次查询都实时渲染 Markdown 太贵了，写入时渲染一次就能让查询快一个数量级。

slug 唯一索引： URL 中用可读的标识而不是数字 ID，对 SEO 和用户体验都好：

✅ blog.vue2.xyz/p/database-design
❌ blog.vue2.xyz/p/42

3.2 多对多关系：文章 ↔ 标签

article_tag = Table(
    "article_tag", Base.metadata,
    Column("article_id", Integer, ForeignKey("articles.id", ondelete="CASCADE"), primary_key=True),
    Column("tag_id", Integer, ForeignKey("tags.id", ondelete="CASCADE"), primary_key=True),
)

SQLAlchemy 的 secondary 参数让多对多关系像一对多一样易用：

# 查询文章的所有标签
article.tags  # 直接返回 Tag 对象列表

# 给文章打标签
article.tags = [tag1, tag2, tag3]

框架自动维护中间表 article_tag，不需要手写增删关联记录的代码。

3.3 自引用关系：评论楼中楼

class Comment(Base):
    parent_id = Column(Integer, ForeignKey("comments.id", ondelete="SET NULL"), nullable=True)
    replies = relationship("Comment", backref="parent", remote_side=[id])

remote_side=[id] 告诉 SQLAlchemy「哪个字段是父记录的主键」，否则它无法区分方向。

注意： 自引用关系上不能设 cascade="all, delete-orphan"——SQLAlchemy 不支持。删除父评论时需要手动处理子评论。

3.4 键值对配置表

class SiteSetting(Base):
    __tablename__ = "site_settings"
    key = Column(String(100), primary_key=True)
    value = Column(Text, default="")

最简单的配置存储模式。不需要为每个配置项建列，不用改表结构就能新增配置：

site_name       → My Blog
author_name     → Leap
author_avatar   → data:image/...
background_url  → https://...
social_links    → [{"platform":"github","url":"..."}]

读取时一次查完转成字典：

settings = {row.key: row.value for row in db.query(SiteSetting).all()}

四、SQLAlchemy 实践技巧

4.1 会话管理

# database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False}  # SQLite 多线程必需
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

FastAPI 中的依赖注入模式：

# main.py
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# routers/articles.py
@router.get("/articles")
def list_articles(db: Session = Depends(get_db)):
    return db.query(Article).all()

这样每个请求自动获取和关闭会话，不会泄漏连接。

4.2 分页查询

page = 1
size = 24
offset = (page - 1) * size

articles = db.query(Article).filter(
    Article.is_published == True
).order_by(
    Article.is_top.desc(),    # 置顶优先
    Article.published_at.desc()  # 最新在前
).offset(offset).limit(size).all()

total = db.query(Article).filter(
    Article.is_published == True
).count()

Pydantic 返回模型要设定默认值，否则 NULL 字段炸 500：

class ArticleListItem(BaseModel):
    cover_image: str = ""      # NOT Optional[str]，否则 None 进来就炸
    is_top: bool = False
    summary: str = ""

这是踩过的坑——Pydantic v2 严格模式下，数据库 NULL 直接报 ValidationError。

4.3 级联删除策略

外键	ondelete	说明
article.category_id	SET NULL	删除分类，文章保留（分类设为空）
comment.article_id	CASCADE	删除文章，评论一起删
article_tag (中间表)	CASCADE	删除文章或标签，关联记录自动清除

SQLAlchemy 的 cascade 参数控制的是 Python 层面的级联，而 ondelete 是数据库层面的。两者最好保持一致。

五、数据库迁移的策略

没有用 Alembic——个人博客不值得上迁移工具。我的策略：

小改动： 直接加列，给默认值：

view_count = Column(Integer, default=0)  # 新增字段

# 已有数据库添加列
conn.execute("ALTER TABLE articles ADD COLUMN view_count INTEGER DEFAULT 0")

大改动： 导数据脚本：

# 从旧表读出 → 处理 → 写入新表
old_data = conn.execute("SELECT * FROM articles_old").fetchall()
for row in old_data:
    conn.execute("INSERT INTO articles (...) VALUES (...)", processed_row)
conn.execute("DROP TABLE articles_old")

这个策略的前提是：你清楚自己在做什么，并且有备份。 如果团队协作或数据重要，还是上 Alembic。

六、踩坑最快报错

1. SQLite 并发写入限制

SQLite 不支持并发写入——写操作会锁整个数据库。但对个人博客完全不是问题：

前台只有读操作（查文章、看评论）
写操作只有后台管理员一个人
不会出现两个人同时写的情况

2. `check_same_thread=False`

SQLite 默认不允许跨线程使用同一个连接。FastAPI 是异步框架，请求可能在不同线程中执行，必须设置：

engine = create_engine(URL, connect_args={"check_same_thread": False})

不设就报 SQLite objects created in a thread can only be used in that same thread。

3. Windows 文件锁

Windows 上 SQLite 文件被打开时不能删除或重命名。部署脚本中不要 os.remove + os.rename 更新数据库，改为 shutil.copy2 覆盖写入。

七、总结

原则	说明
选型务实	单用户博客不需要 MySQL，SQLite 够用且零运维
双字段缓存	写时渲染 Markdown→HTML，读时直接给 HTML
Slug 优先	URL 用可读标识而不是数字 ID
JSON 扩展	一个 meta 字段代替频繁改表
默认值保底	Pydantic schema 全字段给默认值，防 NULL 炸 API
迁移从简	小改直接 SQL，大改写脚本，不上 Alembic

数据库设计与 ORM 实践——个人博客项目复盘