golang-database

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Persona: You are a Go backend engineer who writes safe, explicit, and observable database code. You treat SQL as a first-class language — no ORMs, no magic — and you catch data integrity issues at the boundary, not deep in the application.

Modes:

Write mode — generating new repository functions, query helpers, or transaction wrappers: follow the skill's sequential instructions; launch a background agent to grep for existing query patterns and naming conventions in the codebase before generating new code.
Review/debug mode — auditing or debugging existing database code: use a sub-agent to scan for missing
```
rows.Close()
```
, un-parameterized queries, missing context propagation, and absent error checks in parallel with reading the business logic.

Community default. A company skill that explicitly supersedes
samber/cc-skills-golang@golang-database
skill takes precedence.

角色定位：你是一名Go后端工程师，编写安全、明确且可观测的数据库代码。你将SQL视为一等语言——不使用ORM，不搞魔法——在边界处捕获数据完整性问题，而非在应用深处。

模式说明：

编写模式 — 生成新的仓储函数、查询助手或事务包装器：遵循本技能的分步说明；在生成新代码前，启动后台agent在代码库中查找现有查询模式和命名规范。
审查/调试模式 — 审计或调试现有数据库代码：使用子agent并行扫描缺失的
```
rows.Close()
```
、未参数化的查询、缺失的上下文传播以及遗漏的错误检查，同时分析业务逻辑。

社区默认规则：若存在明确替代
samber/cc-skills-golang@golang-database
技能的公司级技能，以公司级技能为准。

Go Database Best Practices

Go数据库最佳实践

Go's

database/sql

provides a solid foundation for database access. Use

sqlx

pgx

on top of it for ergonomics — never an ORM.

When using sqlx or pgx, refer to the library's official documentation and code examples for current API signatures.

Go的

database/sql

为数据库访问提供了坚实基础。可在其之上使用

sqlx

或

pgx

提升开发体验——绝不使用ORM。

使用sqlx或pgx时，请参考库的官方文档和代码示例获取当前API签名。

Best Practices Summary

最佳实践总结

Use sqlx or pgx, not ORMs — ORMs hide SQL, generate unpredictable queries, and make debugging harder
Queries MUST use parameterized placeholders — NEVER concatenate user input into SQL strings
Context MUST be passed to all database operations — use
```
*Context
```
method variants (
```
QueryContext
```
,
```
ExecContext
```
,
```
GetContext
```
)
```
sql.ErrNoRows
```
MUST be handled explicitly — distinguish "not found" from real errors using
```
errors.Is
```
Rows MUST be closed after iteration —
```
defer rows.Close()
```
immediately after
```
QueryContext
```
calls
NEVER use
```
db.Query
```
for statements that don't return rows —
```
Query
```
returns
```
*Rows
```
which must be closed; if you forget, the connection leaks back to the pool. Use
```
db.Exec
```
instead
Use transactions for multi-statement operations — wrap related writes in
```
BeginTxx
```
/
```
Commit
```
Use
SELECT ... FOR UPDATE
when reading data you intend to modify — prevents race conditions
Set custom isolation levels when default READ COMMITTED is insufficient (e.g., serializable for financial operations)
Handle NULLable columns with pointer fields (
```
*string
```
,
```
*int
```
) or
```
sql.NullXxx
```
types

Connection pool MUST be configured —

SetMaxOpenConns

SetMaxIdleConns

SetConnMaxLifetime

SetConnMaxIdleTime

Use external tools for migrations — golang-migrate or Flyway, never hand-rolled or AI-generated migration SQL
Batch operations in reasonable sizes — not row-by-row (too many round trips), not millions at once (locks and memory)
Never create or modify database schemas — a schema that looks correct on toy data can create hotspots, lock contention, or missing indexes under real production load. Schema design requires understanding of data volumes, access patterns, and production constraints that AI does not have
Avoid hidden SQL features — do not rely on triggers, views, materialized views, stored procedures, or row-level security in application code

使用sqlx或pgx，而非ORM — ORM会隐藏SQL，生成不可预测的查询，且增加调试难度
查询必须使用参数化占位符 — 绝不能将用户输入拼接进SQL字符串
所有数据库操作必须传递Context — 使用
```
*Context
```
方法变体（
```
QueryContext
```
、
```
ExecContext
```
、
```
GetContext
```
）
必须显式处理
```
sql.ErrNoRows
```
— 使用
```
errors.Is
```
区分“未找到”与真实错误
迭代完成后必须关闭Rows — 在调用
```
QueryContext
```
后立即使用
```
defer rows.Close()
```
绝不为不返回行的语句使用
```
db.Query
```
—
```
Query
```
返回的
```
*Rows
```
必须关闭；若遗忘会导致连接泄漏回池。应使用
```
db.Exec
```
替代
多语句操作使用事务 — 用
```
BeginTxx
```
/
```
Commit
```
包裹相关写入操作
修改数据前使用
SELECT ... FOR UPDATE
— 防止竞态条件
默认READ COMMITTED隔离级别不足时，设置自定义隔离级别（例如金融操作使用serializable）
使用指针字段（
*string
、
*int
）或
sql.NullXxx
类型处理可空列

必须配置连接池 —

SetMaxOpenConns

、

SetMaxIdleConns

、

SetConnMaxLifetime

、

SetConnMaxIdleTime

使用外部工具进行迁移 — 如golang-migrate或Flyway，绝不手动编写或由AI生成迁移SQL
合理批量处理操作 — 不要逐行处理（往返次数过多），也不要一次性处理数百万条（会导致锁和内存问题）
绝不创建或修改数据库模式 — 在测试数据上看似正确的模式，在真实生产负载下可能会产生热点、锁竞争或缺失索引。模式设计需要了解数据量、访问模式和生产约束，而AI不具备这些能力
避免隐藏的SQL特性 — 不要在应用代码中依赖触发器、视图、物化视图、存储过程或行级安全

Library Choice

库选择

Library	Best for	Struct scanning	PostgreSQL-specific
`database/sql`	Portability, minimal deps	Manual `Scan`	No
`sqlx`	Multi-database projects	`StructScan`	No
`pgx`	PostgreSQL (30-50% faster)	`pgx.RowToStructByName`	Yes (COPY, LISTEN, arrays)
GORM/ent	Avoid	Magic	Abstracted away

Why NOT ORMs:

Unpredictable query generation — N+1 problems you cannot see in code
Magic hooks and callbacks (BeforeCreate, AfterUpdate) make debugging harder
Schema migrations coupled to application code
Learning the ORM API is harder than learning SQL, and the abstraction leaks

库	适用场景	结构体扫描	PostgreSQL专属特性
`database/sql`	可移植性、最小依赖	手动 `Scan`	无
`sqlx`	多数据库项目	`StructScan`	无
`pgx`	PostgreSQL（速度快30-50%）	`pgx.RowToStructByName`	有（COPY、LISTEN、数组）
GORM/ent	避免使用	魔法实现	完全抽象

为何不使用ORM：

生成不可预测的查询——代码中无法发现N+1问题
魔法钩子和回调（BeforeCreate、AfterUpdate）增加调试难度
模式迁移与应用代码耦合
学习ORM API比学习SQL更难，且抽象会泄漏细节

Parameterized Queries

参数化查询

// ✗ VERY BAD — SQL injection vulnerability
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)

// ✓ Good — parameterized (PostgreSQL)
var user User
err := db.GetContext(ctx, &user, "SELECT id, name, email FROM users WHERE email = $1", email)

// ✓ Good — parameterized (MySQL)
err := db.GetContext(ctx, &user, "SELECT id, name, email FROM users WHERE email = ?", email)

// ✗ 非常危险 — 存在SQL注入漏洞
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)

// ✓ 安全 — 参数化（PostgreSQL）
var user User
err := db.GetContext(ctx, &user, "SELECT id, name, email FROM users WHERE email = $1", email)

// ✓ 安全 — 参数化（MySQL）
err := db.GetContext(ctx, &user, "SELECT id, name, email FROM users WHERE email = ?", email)

Dynamic IN clauses

动态IN子句

query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
if err != nil {
    return fmt.Errorf("building IN clause: %w", err)
}
query = db.Rebind(query) // adjust placeholders for your driver
err = db.SelectContext(ctx, &users, query, args...)

query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
if err != nil {
    return fmt.Errorf("building IN clause: %w", err)
}
query = db.Rebind(query) // 根据驱动调整占位符
err = db.SelectContext(ctx, &users, query, args...)

Dynamic column names

动态列名

Never interpolate column names from user input. Use an allowlist:

allowed := map[string]bool{"name": true, "email": true, "created_at": true}
if !allowed[sortCol] {
    return fmt.Errorf("invalid sort column: %s", sortCol)
}
query := fmt.Sprintf("SELECT id, name, email FROM users ORDER BY %s", sortCol)

For more injection prevention patterns, see the

samber/cc-skills-golang@golang-security

skill.

绝不要从用户输入中插入列名，使用允许列表：

allowed := map[string]bool{"name": true, "email": true, "created_at": true}
if !allowed[sortCol] {
    return fmt.Errorf("invalid sort column: %s", sortCol)
}
query := fmt.Sprintf("SELECT id, name, email FROM users ORDER BY %s", sortCol)

更多注入防护模式，请参考

samber/cc-skills-golang@golang-security

技能。

Struct Scanning and NULLable Columns

结构体扫描与可空列

Use

db:"column_name"

tags for sqlx,

pgx.CollectRows

with

pgx.RowToStructByName

for pgx. Handle NULLable columns with pointer fields (

*string

*time.Time

) — they work cleanly with both scanning and JSON marshaling. See Scanning Reference for examples of all approaches.

sqlx使用

db:"column_name"

标签，pgx使用

pgx.CollectRows

搭配

pgx.RowToStructByName

。使用指针字段（

*string

、

*time.Time

）处理可空列——它们在扫描和JSON编组时都能正常工作。所有实现方式的示例请参考扫描参考文档。

Error Handling

错误处理

func GetUser(id string) (*User, error) {
    var user User

    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 // translate to domain error
        }
        return nil, fmt.Errorf("querying user %s: %w", id, err)
    }

    return &user, nil
}

or:

func GetUser(id string) (u *User, exists bool, err error) {
    var user User

    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, false, nil // "no user" is not a technical error, but a domain error
        }
        return nil, false, fmt.Errorf("querying user %s: %w", id, err)
    }

    return &user, true, nil
}

func GetUser(id string) (*User, error) {
    var user User

    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)
    }

    return &user, nil
}

或者：

func GetUser(id string) (u *User, exists bool, err error) {
    var user User

    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, false, nil // "无用户"不是技术错误，而是领域错误
        }
        return nil, false, fmt.Errorf("querying user %s: %w", id, err)
    }

    return &user, true, nil
}

Always close rows

始终关闭Rows

rows, err := db.QueryContext(ctx, "SELECT id, name FROM users")
if err != nil {
    return fmt.Errorf("querying users: %w", err)
}
defer rows.Close() // prevents connection leaks

for rows.Next() {
    // ...
}
if err := rows.Err(); err != nil { // always check after iteration
    return fmt.Errorf("iterating users: %w", 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)
}

Common database error patterns

常见数据库错误模式

Error	How to detect	Action
Row not found	`errors.Is(err, sql.ErrNoRows)`	Return domain error
Unique constraint	Check driver-specific error code	Return conflict error
Connection refused	`err != nil` on `db.PingContext`	Fail fast, log, retry with backoff
Serialization failure	PostgreSQL error code `40001`	Retry the entire transaction
Context canceled	`errors.Is(err, context.Canceled)`	Stop processing, propagate

错误类型	检测方式	处理动作
未找到行	`errors.Is(err, sql.ErrNoRows)`	返回领域错误
唯一约束冲突	检查驱动特定错误码	返回冲突错误
连接被拒绝	`db.PingContext` 返回 `err != nil`	快速失败、记录日志、退避重试
序列化失败	PostgreSQL错误码 `40001`	重试整个事务
上下文已取消	`errors.Is(err, context.Canceled)`	停止处理、传播错误

Context Propagation

上下文传播

Always use the

*Context

method variants to propagate deadlines and cancellation:

// ✗ Bad — no context, query runs until completion even if client disconnects
db.Query("SELECT ...")

// ✓ Good — respects context cancellation and timeouts
db.QueryContext(ctx, "SELECT ...")

For context patterns in depth, see the

samber/cc-skills-golang@golang-context

skill.

始终使用

*Context

方法变体传播截止时间和取消信号：

// ✗ 错误示例 — 无上下文，即使客户端断开连接，查询仍会执行完成
db.Query("SELECT ...")

// ✓ 正确示例 — 遵循上下文取消和超时设置
db.QueryContext(ctx, "SELECT ...")

深入了解上下文模式，请参考

samber/cc-skills-golang@golang-context

技能。

Transactions, Isolation Levels, and Locking

事务、隔离级别与锁

For transaction patterns, isolation levels,

SELECT FOR UPDATE

, and locking variants, see Transactions.

事务模式、隔离级别、

SELECT FOR UPDATE

及锁的变体，请参考事务文档。

Connection Pool

连接池

db.SetMaxOpenConns(25)              // limit total connections
db.SetMaxIdleConns(10)              // keep warm connections ready
db.SetConnMaxLifetime(5 * time.Minute)  // recycle stale connections
db.SetConnMaxIdleTime(1 * time.Minute)  // close idle connections faster

For sizing guidance and formulas, see Database Performance.

db.SetMaxOpenConns(25)              // 限制总连接数
db.SetMaxIdleConns(10)              // 保持预热连接就绪
db.SetConnMaxLifetime(5 * time.Minute)  // 回收过期连接
db.SetConnMaxIdleTime(1 * time.Minute)  // 更快关闭空闲连接

连接池大小调整指南及公式，请参考数据库性能文档。

Migrations

迁移

Use an external migration tool. Schema changes require human review with understanding of data volumes, existing indexes, foreign keys, and production constraints.

Recommended tools:

golang-migrate — CLI + Go library, supports all major databases
Flyway — JVM-based, widely used in enterprise environments
Atlas — modern, declarative schema management

Migration SQL should be written and reviewed by humans, versioned in source control, and applied through CI/CD pipelines.

使用外部迁移工具。模式变更需要人工审核，且需了解数据量、现有索引、外键及生产约束。

推荐工具：

golang-migrate — CLI + Go库，支持所有主流数据库
Flyway — 基于JVM，在企业环境中广泛使用
Atlas — 现代声明式模式管理工具

迁移SQL应由人工编写和审核，在版本控制系统中管理，并通过CI/CD流水线应用。

Avoid Hidden SQL Features

避免隐藏的SQL特性

Do not rely on triggers, views, materialized views, stored procedures, or row-level security in application code — they create invisible side effects and make debugging impossible. Keep SQL explicit and visible in Go where it can be tested and version-controlled.

不要在应用代码中依赖触发器、视图、物化视图、存储过程或行级安全——它们会产生不可见的副作用，使调试无法进行。保持SQL明确且在Go代码中可见，以便测试和版本控制。

Schema Creation

模式创建

This skill does NOT cover schema creation. AI-generated schemas are often subtly wrong — missing indexes, incorrect column types, bad normalization, or missing constraints. Schema design requires understanding data volumes, access patterns, query profiles, and business constraints. Use dedicated database tooling and human review.

本技能不涉及模式创建。AI生成的模式往往存在细微错误——缺失索引、列类型错误、规范化不当或约束缺失。模式设计需要了解数据量、访问模式、查询概况及业务约束。请使用专用数据库工具并进行人工审核。

Deep Dives

深入探讨

Transactions — Transaction boundaries, isolation levels, deadlock prevention,
```
SELECT FOR UPDATE
```
Testing Database Code — Mock connections, integration tests with containers, fixtures, schema setup/teardown
Database Performance — Connection pool sizing, batch processing, indexing strategy, query optimization
Struct Scanning — Struct tags, NULLable column handling, JSON marshaling patterns

事务 — 事务边界、隔离级别、死锁预防、
```
SELECT FOR UPDATE
```
数据库代码测试 — 模拟连接、容器化集成测试、测试数据、模式搭建/清理
数据库性能 — 连接池大小调整、批处理、索引策略、查询优化
结构体扫描 — 结构体标签、可空列处理、JSON编组模式

Cross-References

交叉参考

→ See
```
samber/cc-skills-golang@golang-security
```
skill for SQL injection prevention patterns
→ See
```
samber/cc-skills-golang@golang-context
```
skill for context propagation to database operations
→ See
```
samber/cc-skills-golang@golang-error-handling
```
skill for database error wrapping patterns
→ See
```
samber/cc-skills-golang@golang-testing
```
skill for database integration test patterns

→ 参考
```
samber/cc-skills-golang@golang-security
```
技能获取SQL注入防护模式
→ 参考
```
samber/cc-skills-golang@golang-context
```
技能获取数据库操作的上下文传播方式
→ 参考
```
samber/cc-skills-golang@golang-error-handling
```
技能获取数据库错误包装模式
→ 参考
```
samber/cc-skills-golang@golang-testing
```
技能获取数据库集成测试模式