数据库与事务

学习目标

这课把书签从内存搬进 SQLite：用 database/sql 建连接池，写书签和标签两张表的事务，处理唯一约束冲突，搞清连接和结果集的生命周期。

问题场景

重启服务书签就没了——内存实现的宿命。持久化要回答三个问题：表怎么设计（书签和一对多标签）、写入怎么保证原子（书签写成功标签写一半怎么办）、冲突怎么办（同一 URL 存两次）。G03 的 Store 接口这时兑现价值：新写一个 SQLiteStore，业务代码零改动。

核心解释

驱动选纯 Go 的 modernc.org/sqlite，Windows 上不用装 gcc；mattn/go-sqlite3 性能更好但需要 CGO 工具链，按机器条件二选一，database/sql 这层的代码完全一样：

go get modernc.org/sqlite && go mod tidy

import (
	"database/sql"
	_ "modernc.org/sqlite" // 注册驱动，包名不直接使用
)

func NewSQLiteStore(dsn string) (*SQLiteStore, error) {
	db, err := sql.Open("sqlite", dsn) // Open 并不真正连库
	if err != nil {
		return nil, fmt.Errorf("open sqlite: %w", err)
	}
	db.SetMaxOpenConns(1) // SQLite 单写者，写并发收敛更稳
	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("ping sqlite: %w", err)
	}
	if _, err := db.Exec(schema); err != nil {
		return nil, fmt.Errorf("apply schema: %w", err)
	}
	return &SQLiteStore{db: db}, nil
}

const schema = `
CREATE TABLE IF NOT EXISTS bookmarks (
	id         INTEGER PRIMARY KEY,
	title      TEXT NOT NULL,
	url        TEXT NOT NULL UNIQUE,
	created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS bookmark_tags (
	bookmark_id INTEGER NOT NULL REFERENCES bookmarks(id),
	tag         TEXT NOT NULL,
	PRIMARY KEY (bookmark_id, tag)
);`

书签写多张表，用事务保证原子，defer Rollback 是标配。顺便说透 SetMaxOpenConns(1)：SQLite 同一时刻只允许一个写者，连接开多了写事务会互相等锁，收敛到 1 反而更稳，G16 复盘时还会回到这行：

func (s *SQLiteStore) Add(b Bookmark) (int64, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return 0, fmt.Errorf("begin: %w", err)
	}
	defer tx.Rollback() // Commit 成功后再调用是无害的空操作

	res, err := tx.Exec("INSERT INTO bookmarks(title, url, created_at) VALUES(?, ?, ?)",
		b.Title, b.URL, time.Now().Format(time.RFC3339))
	if err != nil {
		if strings.Contains(err.Error(), "UNIQUE constraint failed") {
			return 0, fmt.Errorf("add bookmark: %w", ErrDuplicate)
		}
		return 0, fmt.Errorf("insert bookmark: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("last insert id: %w", err)
	}
	for _, t := range b.Tags {
		if _, err := tx.Exec("INSERT INTO bookmark_tags VALUES(?, ?)", id, t); err != nil {
			return 0, fmt.Errorf("insert tag %q: %w", t, err)
		}
	}
	return id, tx.Commit()
}

三个要点：唯一约束冲突是驱动返回的错误，在这里转译成 ErrDuplicate 哨兵，调用方继续用 errors.Is 判断，接口行为和内存版完全一致；defer tx.Rollback() 覆盖所有中途 return 的路径，Commit 成功后它返回 sql.ErrTxDone 被忽略即可，这是官方推荐的写法；参数全部走占位符 ?，字符串拼接 SQL 是注入漏洞的起点。created_at 用 RFC3339 文本存取，读出来 Scan 进 time.Time，展示层不用猜格式。查询侧同理：rows 用完必须 Close，否则连接被占死，池子很快耗尽。

示例拆解

go run .

curl -X POST http://127.0.0.1:8080/bookmarks \
  -d '{"title":"Go","url":"https://go.dev","tags":["go"]}'
# 201；重启服务再 GET /bookmarks/1，数据还在——持久化生效

curl -X POST http://127.0.0.1:8080/bookmarks -d '{"title":"Go again","url":"https://go.dev"}'
# 409 Conflict（把 ErrDuplicate 映射成 409）

handler 里对 ErrDuplicate 返回 409 比返回 400 准确：数据本身合法，只是和已有资源冲突。重启验证是持久化的试金石，别省这一步。没有 SQLite 客户端的话，用 Go 写个临时查询或直接靠接口验证都行。另外留意 DSN：以后会在里面加 busy_timeout 之类的 pragma（G16 复盘会用到），驱动的能力很多藏在 DSN 参数里。

动手任务

1. 实现 Get(id)：QueryRowContext + Scan 各字段，sql.ErrNoRows 用 errors.Is 转译成 ErrNotFound。
2. 实现 List：rows 循环 Scan，defer rows.Close()，循环结束后检查 rows.Err()。
3. 写 Delete(id)：事务里先删 bookmark_tags 再删 bookmarks，故意颠倒顺序观察外键报错，再修复。

常见错误

• 忘记 import 驱动的下划线包：sql.Open 报 unknown driver "sqlite"。
• rows 没有 Close：连接池耗尽，症状是请求越来越慢最后超时。
• for 循环结束不看 rows.Err()：循环中途的驱动错误被静默吞掉，列表悄悄缺数据。
• 用 err == sql.ErrNoRows 等值判断：经过 %w 包装的错误要用 errors.Is，等值判断会漏。

检查与复盘

完成下方检查题。自查清单：

• 多表写入全部包在事务里，defer Rollback 紧跟 Begin 之后
• 唯一冲突、无记录都转译成业务哨兵，上层只见 ErrDuplicate/ErrNotFound
• 每个 rows 都有 Close，循环结束后检查 rows.Err()

参考资料

• 标准库 database/sql —— 连接池、事务与错误约定总览
• 标准库 database/sql.Tx —— Rollback/Commit 的官方用法（含 defer 模式）
• modernc.org/sqlite —— 纯 Go 驱动，免 CGO
• mattn/go-sqlite3 —— CGO 驱动备选