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 Database Best Practices

Go's database/sql provides a solid foundation for database access. Use sqlx or 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.

Best Practices Summary

1. 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)
INLINECODE9 MUST be handled explicitly — distinguish "not found" from real errors using INLINECODE10
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/INLINECODE18
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, INLINECODE26
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

Library Choice

Library	Best for	Struct scanning	PostgreSQL-specific
INLINECODE27	Portability, minimal deps	Manual INLINECODE28	No
INLINECODE29

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

Parameterized Queries

CODEBLOCK0

Dynamic IN clauses

CODEBLOCK1

Dynamic column names

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

CODEBLOCK2

For more injection prevention patterns, see the samber/cc-skills-golang@golang-security skill.

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.

Error Handling

CODEBLOCK3

or:

CODEBLOCK4

Always close rows

CODEBLOCK5

Common database error patterns

Error	How to detect	Action
Row not found	INLINECODE39	Return domain error
Unique constraint

Context Propagation

Always use the *Context method variants to propagate deadlines and cancellation:

CODEBLOCK6

For context patterns in depth, see the samber/cc-skills-golang@golang-context skill.

Transactions, Isolation Levels, and Locking

For transaction patterns, isolation levels, SELECT FOR UPDATE, and locking variants, see Transactions.

Connection Pool

CODEBLOCK7

For sizing guidance and formulas, see Database Performance.

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.

Avoid Hidden SQL Features

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.

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.

Deep Dives

- Transactions — Transaction boundaries, isolation levels, deadlock prevention, INLINECODE47
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

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

References

技能名称: golang-database
详细描述:
角色: 你是一位编写安全、明确且可观测的数据库代码的Go后端工程师。你将SQL视为一等语言——不使用ORM，没有魔法——并在边界处捕获数据完整性问题，而非在应用程序深处。

模式:

- 写入模式 — 生成新的仓库函数、查询辅助函数或事务包装器：遵循技能的逐步说明；在生成新代码之前，启动一个后台代理来搜索代码库中现有的查询模式和命名约定。
审查/调试模式 — 审计或调试现有的数据库代码：使用一个子代理，在并行读取业务逻辑的同时，扫描缺失的rows.Close()、未参数化的查询、缺失的上下文传播以及缺失的错误检查。

社区默认。 一个明确取代samber/cc-skills-golang@golang-database技能的公司技能具有优先权。

Go 数据库最佳实践

Go的database/sql为数据库访问提供了坚实的基础。在其之上使用sqlx或pgx以获得更好的易用性——绝不使用ORM。

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

最佳实践总结

1. 使用sqlx或pgx，而非ORM — ORM隐藏了SQL，生成不可预测的查询，并使调试更加困难
查询必须使用参数化占位符 — 切勿将用户输入拼接到SQL字符串中
上下文必须传递给所有数据库操作 — 使用Context方法变体（QueryContext、ExecContext、GetContext）
sql.ErrNoRows必须被显式处理 — 使用errors.Is区分“未找到”和真正的错误
行必须在迭代后关闭 — 在QueryContext调用后立即使用defer rows.Close()
切勿对不返回行的语句使用db.Query — Query返回必须关闭的Rows；如果忘记关闭，连接会泄漏回连接池。请使用db.Exec
对多语句操作使用事务 — 在BeginTxx/Commit中包装相关的写操作
在读取打算修改的数据时使用SELECT ... FOR UPDATE — 防止竞态条件
当默认的READ COMMITTED隔离级别不足时，设置自定义隔离级别（例如，金融操作使用可序列化）
使用指针字段（string、int）或sql.NullXxx类型处理可空列
必须配置连接池 — SetMaxOpenConns、SetMaxIdleConns、SetConnMaxLifetime、SetConnMaxIdleTime
使用外部工具进行迁移 — golang-migrate或Flyway，绝不使用手写或AI生成的迁移SQL
以合理的大小进行批量操作 — 不要逐行操作（往返次数过多），也不要一次性操作数百万行（锁和内存问题）
切勿创建或修改数据库模式 — 在测试数据上看起来正确的模式，在生产负载下可能会产生热点、锁争用或缺失索引。模式设计需要理解数据量、访问模式和生产约束，而AI不具备这些能力
避免隐藏的SQL特性 — 不要在应用程序代码中依赖触发器、视图、物化视图、存储过程或行级安全

库选择

库	最适合	结构体扫描	PostgreSQL特定
database/sql	可移植性，最小依赖	手动Scan	否
sqlx

为什么不用ORM：

- 不可预测的查询生成 — 在代码中看不到的N+1问题
魔法钩子和回调（BeforeCreate、AfterUpdate）使调试更加困难
模式迁移与应用程序代码耦合
学习ORM API比学习SQL更难，而且抽象层会泄漏

参数化查询

go
// ✗ 非常糟糕 — 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)

动态IN子句

go
query, args, err := sqlx.In(SELECT * FROM users WHERE id IN (?), ids)
if err != nil {
return fmt.Errorf(构建IN子句: %w, err)
}
query = db.Rebind(query) // 为你的驱动调整占位符
err = db.SelectContext(ctx, &users, query, args...)

动态列名

切勿从用户输入中插值列名。使用白名单：

go
allowed := map[string]bool{name: true, email: true, created_at: true}
if !allowed[sortCol] {
return fmt.Errorf(无效的排序列: %s, sortCol)
}
query := fmt.Sprintf(SELECT id, name, email FROM users ORDER BY %s, sortCol)

有关更多注入预防模式，请参阅samber/cc-skills-golang@golang-security技能。

结构体扫描和可空列

为sqlx使用db:columnname标签，为pgx使用pgx.CollectRows配合pgx.RowToStructByName。使用指针字段（string、time.Time）处理可空列——它们能很好地与扫描和JSON编组配合使用。有关所有方法的示例，请参阅扫描参考。

错误处理

go
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(查询用户 %s: %w, id, err)
}

return &user, nil
}

或者：

go
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(查询用户 %s: %w, id, err)
}

return &user, true, nil
}

始终关闭行

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

for rows.Next() {
// ...
}
if err := rows.Err(); err != nil { // 始终在迭代后检查
return fmt.Errorf(迭代用户: %w, err)
}

常见数据库错误模式

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

始终使用*Context方法变体来传播截止时间和取消信号：

go
// ✗ 糟糕 — 没有上下文，即使客户端断开连接，查询也会运行直到完成
db.Query(SELECT ...)

// ✓ 好 — 尊重上下文取消和超时
db.QueryContext(ctx, SELECT ...)

有关上下文的深入模式，请参阅samber/cc-skills-golang@golang-context技能。

事务、隔离级别和锁

有关事务模式、隔离级别、SELECT FOR UPDATE和锁变体，请参阅事务。

连接池

go
db.SetMaxOpenConns(25) // 限制总连接数

golang-databaseGo数据库指南

golang-database

Go Database Best Practices

Best Practices Summary

Library Choice