数据库访问规范:连接、事务与扫描

数据库访问是服务端开发的基础环节,也是线上事故的高发区域。常见故障集中于三类:连接池未显式配置,请求在池外排队;rows 未调用 Close,连接持续泄漏;事务边界不明确,数据一致性难以保证。本文规定 database/sql 层的使用规范,并直接给出选型结论:SQL 应显式编写,保证可见与可测试;以 database/sql 为基础,按需使用 sqlx 或 pgx,不引入 ORM。

不推荐 ORM 的核心原因是查询不可见:隐式的 N+1 查询在代码中难以发现,BeforeCreate 这类隐式钩子增加调试成本,schema 迁移被绑定在应用代码中。理解几条 SQL 的成本,显著低于排查一套 ORM 隐式行为的成本。

选型

首选 database/sql,配合 sqlx(多数据库项目)或 pgx(PostgreSQL 专用),不使用 ORM。 按项目的数据库栈三选一:

pgx 是 PostgreSQL 项目的性能首选,COPY、LISTEN、原生数组等能力仅它提供;多数据库混合栈则选择 sqlx,SQL 方言由开发团队自行处理。

另一条容易被忽略的规范:不应将业务逻辑放在触发器、视图、存储过程、行级安全等"隐藏 SQL 特性"上。它们不出现在任何调用栈中,出现问题时无法在 Go 代码中定位原因。SQL 保持显式,才能进入代码评审与版本控制。

表结构变更应交给 golang-migrate、Flyway、Atlas 这类迁移工具:SQL 由开发者编写并评审,迁移文件纳入版本控制,通过 CI/CD 执行。未经评审的 schema 在小规模数据上表现正常,在真实数据量下容易产生热点、锁争用与索引缺失。

连接池

连接池必须显式配置,四个 Set 调用缺一不可。 默认值几乎不适合任何生产负载,漏配的后果要么是请求排队,要么是数据库过载:

db, err := sqlx.Connect("postgres", dsn)
if err != nil {
    return fmt.Errorf("connecting to database: %w", err)
}
db.SetMaxOpenConns(25)                 // 总连接上限,匹配数据库容量
db.SetMaxIdleConns(10)                 // 保留空闲连接,避免重复握手开销
db.SetConnMaxLifetime(5 * time.Minute) // 定期回收,容忍 DNS 变更与故障切换
db.SetConnMaxIdleTime(1 * time.Minute) // 空闲连接更快释放

四个参数都有双向代价:

监控应接入 db.Stats() 采样,WaitCount 持续上涨是池耗尽的直接信号:

stats := db.Stats()
slog.Info("db pool",
    "open", stats.OpenConnections,
    "in_use", stats.InUse,
    "idle", stats.Idle,
    "wait_count", stats.WaitCount, // 排队等待连接的总次数
)

处理顺序是先优化慢查询,再考虑调大 MaxOpenConns。告警基线:open 连接数逼近 MaxOpenConns,表明存在排队风险;idle 连接数持续偏高,应调低 MaxIdleConns 或缩短 ConnMaxIdleTime。

查询

一切含外部输入的查询必须参数化,用户输入不得拼接进 SQL 字符串。 这是强制性要求,不是可选建议:

// ❌ SQL 注入:email 中的单引号即可改写查询语义
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)

// ✅ 占位符参数化(PostgreSQL 用 $1,MySQL 用 ?)
err := db.GetContext(ctx, &user,
    "SELECT id, name, email FROM users WHERE email = $1", email)

两个动态场景各有标准解法。IN 子句用 sqlx.In 展开占位符,再按驱动 Rebind;动态列名(例如排序字段)无法参数化,只能做白名单校验,通过后才允许拼接。注入防护的完整方法见安全实践:

// IN 子句:动态展开占位符,再按驱动改写
query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
query = db.Rebind(query)
err = db.SelectContext(ctx, &users, query, args...)

// 动态列名:白名单校验通过后才允许拼接
allowed := map[string]bool{"name": true, "email": true, "created_at": true}
if !allowed[sortCol] {
    return fmt.Errorf("invalid sort column: %s", sortCol)
}

*一律使用 Context 版 API:QueryContext、ExecContext、GetContext、SelectContext。 非 Context 版本无法接收取消信号,客户端断开后查询仍占用连接直至执行完毕。context 的传递方式与超时设定见context:

// ❌ 无 ctx:取消与超时全部失效
db.Query("SELECT ...")

// ✅ 取消、截止时间随 ctx 传播
db.QueryContext(ctx, "SELECT ...")

不返回行的语句应使用 Exec,不应使用 Query。Query 返回的 *Rows 必须关闭,遗漏 Close 会使连接泄漏在池外,这是连接泄漏的常见来源。写操作使用 ExecContext,从源头消除这类泄漏。

事务

多条相关写语句应包裹在单个事务中,用 BeginTxx 开启,保证中间任何一步失败时整体回退:

tx, err := db.BeginTxx(ctx, nil) // 隔离级别为数据库默认值(PostgreSQL 为 READ COMMITTED)
if err != nil {
    return fmt.Errorf("beginning transaction: %w", err)
}
defer tx.Rollback() // Commit 成功后为 no-op;作为最终防线,确保事务不悬挂占用连接

// ... 用 tx 执行多条语句 ...

if err := tx.Commit(); err != nil {
    return fmt.Errorf("committing transaction: %w", err)
}

defer tx.Rollback() 是事务处理函数的标准模式。 它保证任何一处提前 return 时事务都被回滚,而不是保持打开并占用连接;Commit 成功之后 Rollback 自动变为 no-op,不存在"误回滚"。

读-改-写(read-modify-write)操作涉及的值,必须用 SELECT ... FOR UPDATE 锁行。不加锁时,两个并发事务会读到同一个旧值,后提交的覆盖先提交的,形成丢失更新:

var balance int
err := tx.GetContext(ctx, &balance,
    "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", accountID)
// 行锁保持到 Commit / Rollback

锁变体按需选择:FOR UPDATE NOWAIT 获取不到锁时立即报错,FOR SHARE 允许其他事务读取、阻止其写入。

默认的 READ COMMITTED 不满足需求时,应显式声明事务隔离级别:事务内需要一致读用 LevelRepeatableRead,金融、库存等强一致场景用 LevelSerializable;序列化冲突(PostgreSQL 错误码 40001)必须整体重试事务:

tx, err := db.BeginTxx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})

事务对象只向下传递,不在 repository 内部另开。事务由开启方定义边界:将 tx 注入 repository(例如 NewUserRepository(tx)),同一组写操作才落在同一个事务上。

扫描

SELECT 应逐列列出字段,不使用 SELECT *。它不仅搬运不需要的数据,表结构变更后,按列序扫描的代码还会静默错位;显式列名让查询与扫描目标一一对应。

sqlx 用 db 标签做映射,单行 GetContext,多行 SelectContext:

type User struct {
    ID        int64      `db:"id"`
    Name      string     `db:"name"`
    Email     string     `db:"email"`
    DeletedAt *time.Time `db:"deleted_at"` // NULLable
}

var user User
err := db.GetContext(ctx, &user,
    "SELECT id, name, email, deleted_at FROM users WHERE id = $1", id)

var users []User
err = db.SelectContext(ctx, &users,
    "SELECT id, name, email, deleted_at FROM users WHERE active = true")

pgx v5 对应写法是 pgx.CollectRows(rows, pgx.RowToStructByName[User]),一行完成收集。

可空列优先使用指针字段,nil 即 NULL。另外两种方式:sql.NullString / sql.Null[T] 显式但代码冗长,需要判断 .Valid,JSON 还需自定义序列化;COALESCE(bio, '') 将转换移入查询,适用于"NULL 等价于空默认值"的列。

type User struct {
    ID        int64      `db:"id"         json:"id"`
    Bio       *string    `db:"bio"        json:"bio,omitempty"` // NULL → JSON 中省略
    DeletedAt *time.Time `db:"deleted_at" json:"deleted_at"`    // NULL → JSON null
}

db 与 json 双标签组合使用可避免二次转换:NULL 对应的指针字段序列化为 JSON 时输出 null(或被 omitempty 省略),API 出参无需再加工。

错误处理

sql.ErrNoRows 必须用 errors.Is 显式处理——"没查到"不是技术故障,而是领域状态。 两种出口任选其一:翻译成领域错误(ErrUserNotFound),或返回 (nil, false, nil) 表示不存在。不应使用 err == sql.ErrNoRows,错误一旦被包装即匹配失败,错误包装的完整规则见错误处理:

err := db.GetContext(ctx, &user, "SELECT id, name FROM users WHERE id = $1", id)
if err != nil {
    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrUserNotFound // 翻译成领域错误
    }
    return nil, fmt.Errorf("querying user %s: %w", id, err)
}

QueryContext 之后第一行就应 defer rows.Close(),迭代结束后必须检查 rows.Err();这两处是最常见的遗漏。Close 遗漏导致连接泄漏;而 rows.Next() 在中途 I/O 出错时只是静默结束循环,不检查 rows.Err() 就会把部分数据误当作完整数据集:

rows, err := db.QueryContext(ctx, "SELECT id, name FROM users")
if err != nil {
    return fmt.Errorf("querying users: %w", err)
}
defer rows.Close() // 防止连接泄漏

for rows.Next() {
    // ...
}
if err := rows.Err(); err != nil { // 迭代中途的错误在此处返回
    return fmt.Errorf("iterating users: %w", err)
}

数据库错误应按类型分流,不应一律映射为 500:

性能

循环内逐条查询(N+1)必须消除,改为 JOIN 或批量 WHERE id IN (...)。一次列表请求附带 N 次详情查询,延迟随之放大 N 倍;该问题常见于在循环中调用 repository 的代码,通常在负载测试中暴露。

批量写入不应逐行执行,也不应单批过大,每批 100~1000 行:逐行是 N 次网络往返;单批过大则长时间持锁、内存占用高、易超时并阻塞其他事务。行宽越大,批次应越小,需按行宽与负载调整:

func insertUsersBatch(ctx context.Context, db *sqlx.DB, users []User) error {
    const batchSize = 500
    for i := 0; i < len(users); i += batchSize {
        end := min(i+batchSize, len(users))
        _, err := db.NamedExecContext(ctx,
            `INSERT INTO users (name, email) VALUES (:name, :email)`, users[i:end])
        if err != nil {
            return fmt.Errorf("inserting users batch %d-%d: %w", i, end, err)
        }
    }
    return nil
}

同一语句需跨批反复执行时,应用 PrepareNamed 预编译一次,复用返回的 *NamedStmt,省去每批重复解析。PostgreSQL 大数据量导入应直接使用 pgx.CopyFrom 走 COPY 二进制协议,吞吐显著高于多值 INSERT。

深分页用游标条件,不用 OFFSET。OFFSET 每页都要扫描并丢弃前面的行,页越深越慢;游标分页代价恒为 O(limit),游标列必须有索引,按实际索引选列:

-- ❌ OFFSET:O(offset + limit),深页越来越慢
SELECT * FROM events ORDER BY created_at LIMIT 100 OFFSET 10000;

-- ✅ 游标:O(limit),与页深无关
SELECT * FROM events WHERE created_at > $1 ORDER BY created_at LIMIT 100;

优化应先测量,以 EXPLAIN ANALYZE 的执行计划为依据。存在性判断用 EXISTS 不用 COUNT:前者命中第一行即返回,后者必须统计全部匹配行。分页必须携带 ORDER BY,否则页序不稳定。

索引变更应以建议形式提出,不应未经评估擅自执行。加索引看外键列(注意 PostgreSQL 不会自动为外键建索引)和高频 WHERE / JOIN / ORDER BY 列;删索引看 idx_scan 接近零的长期未用索引。以扫描统计与表体量为依据,DDL 由熟悉生产负载的维护者执行。

测试

单元测试注入 repository 接口的 mock,不连接任何数据库。它验证业务逻辑,不验证 SQL 正确性,执行快、零外部依赖:

type UserRepository interface { // 生产实现持有 *sqlx.DB
    GetByID(ctx context.Context, id int64) (*User, bool, error)
    Create(ctx context.Context, user *User) error
}

// service 层测试:使用 testify/mock 提供 mock 实现
repo := new(mockUserRepo)
repo.On("GetByID", mock.Anything, int64(1)).Return(&User{Name: "Alice"}, true, nil)
svc := NewUserService(repo)
user, err := svc.GetUser(context.Background(), 1)
require.NoError(t, err)

需要在没有真实数据库时核对 SQL 文本与参数,可用 DATA-DOG/go-sqlmock 的 ExpectQuery / WithArgs / WillReturnRows;但它无法验证 SQL 对真实 schema 是否成立,该职责由集成测试承担。

集成测试用 build tag 隔离:文件头一行 //go:build integration,默认 go test ./... 不执行,CI 中 go test -tags=integration 显式触发。

用例之间用事务回滚隔离:每个用例开启一个事务,TearDown 统一回滚,无需手工清表,每个用例都从干净状态开始:

func (s *UserRepoSuite) SetupTest() {
    tx, err := s.db.Beginx()
    s.Require().NoError(err)
    s.tx = tx
}

func (s *UserRepoSuite) TearDownTest() {
    s.tx.Rollback() // 回滚全部变更,每个用例从干净状态开始
}

func (s *UserRepoSuite) TestCreateAndGet() {
    repo := NewUserRepository(s.tx) // repository 接收 tx
    // ...
}

CI 环境通常不提供数据库实例,可用 testcontainers-go 启动一次性 PostgreSQL 测试容器,镜像、账号、库名均在代码中声明,环境可复现:

container, err := postgres.Run(ctx, "postgres:16-alpine",
    postgres.WithDatabase("testdb"),
    postgres.WithUsername("test"),
    postgres.WithPassword("test"),
)
connStr, err := container.ConnectionString(ctx, "sslmode=disable")

职责划分:mock 验证"逻辑是否正确",真实数据库验证"SQL 是否正确"——业务逻辑与错误路径归 mock;SQL 正确性、事务边界、NULL 往返、约束冲突必须在真实数据库上验证。最后一条硬性规定:禁止对生产数据库执行任何测试。

速查清单

关键要求汇总:

• 连接池:四个 Set 参数必须显式配置;Stats() 的 WaitCount 持续上涨即池耗尽,应先优化慢查询
• 注入:查询必须全部参数化;IN 子句用 sqlx.In + Rebind;动态列名走白名单校验
• API:统一使用 *Context 系列;写操作用 Exec,不用 Query
• 事务:BeginTxx + defer Rollback + Commit;读-改-写用 FOR UPDATE;40001 冲突整体重试事务
• 扫描:不用 SELECT *,显式列出列名;可空列用指针字段,次选 sql.NullXxx 或 COALESCE
• 错误:用 errors.Is(err, sql.ErrNoRows) 翻译成领域错误;rows.Close() 与 rows.Err() 必须检查
• 性能:消除 N+1 查询;批量写入每批 100~1000 行;PostgreSQL 大数据量导入用 COPY;深分页用游标;优化前先 EXPLAIN ANALYZE;用 EXISTS 替代 COUNT
• 测试:单元测试用接口 mock 或 sqlmock;集成测试用 build tag + 事务回滚 + testcontainers-go 测试容器;禁止连接生产数据库