Data Persistence in Go

Quick Reference

Topic	Reference
Connection pool internals, sizing, pgx pools, monitoring	references/connection-pooling.md
golang-migrate setup, file conventions, CI/CD integration

references/migrations.md | | Transaction helpers, service-layer transactions, isolation levels | references/transactions.md |

Choosing Your Approach

Pick the right tool based on your project's needs:

Factor	Raw SQL (sqlx/pgx)	ORM (Ent/GORM)
Complex queries	Preferred	Awkward
Type safety

When to Use Raw SQL (sqlx/pgx)

- You need full control over query performance and execution plans
Your domain has complex joins, CTEs, window functions, or recursive queries
You want zero abstraction overhead and direct access to PostgreSQL features
Your team is comfortable writing and maintaining SQL
You need advanced PostgreSQL features like LISTEN/NOTIFY, advisory locks, or INLINECODE1

pgx is the recommended PostgreSQL driver for Go. It provides native PostgreSQL protocol support, better performance than database/sql, and access to PostgreSQL-specific features. Use sqlx when you need database/sql compatibility or work with multiple database backends.

When to Use an ORM (Ent/GORM)

- You want type-safe, generated query builders and avoid writing SQL
Your schema is mostly CRUD with straightforward relationships
You value generated code, schema-as-code, and automatic migrations (Ent)
You are prototyping quickly and want to iterate on the schema fast

Ent is preferred over GORM for new projects. It uses code generation for type safety, has a declarative schema DSL, built-in migration support, and integrates with GraphQL. GORM is suitable if the team already knows it or if the project is small.

Connection Setup

Every Go application connecting to a database needs a properly configured connection pool. The database/sql package manages pooling automatically, but the defaults are not suitable for production.

CODEBLOCK0

Pool Settings Explained

MaxOpenConns -- The maximum number of open connections to the database. This prevents your application from overwhelming the database with too many concurrent connections. Set to approximately 25 for typical web apps. To calculate: divide your database's max_connections (minus a reserve for admin and replication) by the number of application instances. If your DB allows 100 connections, you have 3 app instances, and you reserve 10 for admin, set this to (100 - 10) / 3 = 30.

MaxIdleConns -- The number of connections kept alive in the pool when not in use. These warm connections avoid the latency of establishing new connections for each request. Set to approximately 10, or roughly 40% of MaxOpenConns. Setting this too high wastes database connections; setting it too low causes frequent reconnections.

ConnMaxLifetime -- The maximum amount of time a connection can be reused. After this duration, the connection is closed and a new one is created on the next request. This helps pick up DNS changes (important for cloud databases that failover to new IPs), rebalance load across read replicas, and prevent connections from becoming stale. A value of 5 minutes is typical. Set shorter (1-2 min) if your infrastructure uses DNS-based failover.

ConnMaxIdleTime -- The maximum amount of time a connection can sit idle before it is closed. This releases connections back to the database during low-traffic periods, freeing resources. A value of 1 minute is typical. This should be shorter than ConnMaxLifetime.

For pgx-specific pooling with native PostgreSQL support, see references/connection-pooling.md.

Repository Pattern

Define a store interface at the consumer for testability. Implement against a concrete database driver. This pattern keeps your domain logic decoupled from the database.

CODEBLOCK1

Model Struct Tags

Use db tags for sqlx column mapping and keep models close to the store:

CODEBLOCK2

Sentinel Errors

Define domain-specific errors that callers can check without importing database packages:

CODEBLOCK3

Map database errors to domain errors in the store layer:

CODEBLOCK4

Migrations

Use golang-migrate for managing schema changes. Migrations are pairs of SQL files: one for applying changes (up) and one for reverting them (down).

CODEBLOCK5

Run migrations at application startup:

CODEBLOCK6

Key rules: always write both up and down migrations, use IF NOT EXISTS / IF EXISTS for idempotency, never modify a migration that has been applied in production. For full migration patterns, CI/CD integration, and safe migration strategies, see references/migrations.md.

Transactions

Use a transaction helper to ensure consistent commit/rollback handling. Transactions should be managed at the service layer, not the store layer, so that multiple store operations can be composed into a single atomic unit.

CODEBLOCK7

Store methods accept a *sql.Tx parameter so they can participate in a caller-controlled transaction:

CODEBLOCK8

For isolation levels, deadlock prevention, context propagation, and testing strategies, see references/transactions.md.

When to Load References

Load connection-pooling.md when:

- Configuring pgx native pools (pgxpool.Pool)
Sizing connection pools for production workloads
Working with cloud databases, PgBouncer, or connection limits
Monitoring pool health and metrics

Load migrations.md when:

- Setting up golang-migrate for the first time
Writing new migration files
Integrating migrations into CI/CD pipelines
Dealing with migration failures or rollbacks

Load transactions.md when:

- Implementing multi-step operations that must be atomic
Designing service-layer transaction boundaries
Choosing transaction isolation levels
Debugging deadlocks or long-running transactions

Anti-Patterns

Using string concatenation for queries

CODEBLOCK9

Always use parameterized queries ($1, $2, etc.) or named parameters (:name).

Leaking database types into handlers

CODEBLOCK10

Return domain errors (ErrNotFound) from the store and check those in handlers instead.

Opening a new connection per request

CODEBLOCK11

Open the database connection once at startup and share the pool across the application.

SELECT * in production code

CODEBLOCK12

Explicitly list the columns you need. This makes the query resilient to schema changes and avoids fetching unnecessary data.

Not handling context cancellation

CODEBLOCK13

Always use the Context variants (QueryContext, ExecContext, GetContext) and pass the request context so that queries are cancelled when the caller gives up.

Transactions in store methods

CODEBLOCK14

Let the service layer manage transactions and pass *sql.Tx into store methods. See references/transactions.md for the correct pattern.

Go语言数据持久化

快速参考

主题	参考文档
连接池内部机制、大小调整、pgx连接池、监控	references/connection-pooling.md
golang-migrate设置、文件规范、CI/CD集成

references/migrations.md | | 事务辅助函数、服务层事务、隔离级别 | references/transactions.md |

选择你的方案

根据项目需求选择正确的工具：

因素	原生SQL (sqlx/pgx)	ORM (Ent/GORM)
复杂查询	推荐	笨拙
类型安全

何时使用原生SQL (sqlx/pgx)

- 需要完全控制查询性能和执行计划
领域涉及复杂连接、CTE、窗口函数或递归查询
希望零抽象开销并直接访问PostgreSQL特性
团队熟悉编写和维护SQL
需要使用高级PostgreSQL特性，如LISTEN/NOTIFY、咨询锁或COPY

pgx 是Go语言推荐的PostgreSQL驱动。它提供原生PostgreSQL协议支持，性能优于database/sql，并可访问PostgreSQL特有功能。当需要database/sql兼容性或处理多个数据库后端时，使用sqlx。

何时使用ORM (Ent/GORM)

- 希望使用类型安全的生成查询构建器，避免编写SQL
数据模型主要是CRUD操作，关系简单直接
重视代码生成、代码即模式和自动迁移（Ent）
快速原型开发，希望快速迭代数据模型

Ent 在新项目中优于GORM。它通过代码生成实现类型安全，拥有声明式模式DSL、内置迁移支持，并与GraphQL集成。如果团队已熟悉GORM或项目规模较小，GORM也是合适的选择。

连接设置

每个连接数据库的Go应用都需要正确配置的连接池。database/sql包自动管理连接池，但默认设置不适合生产环境。

go
db, err := sql.Open(postgres, connStr)
if err != nil {
return fmt.Errorf(打开数据库: %w, err)
}

// 连接池配置
db.SetMaxOpenConns(25) // 最大并发连接数
db.SetMaxIdleConns(10) // 空闲时保持存活的连接数
db.SetConnMaxLifetime(5 * time.Minute) // 连接最大复用时间
db.SetConnMaxIdleTime(1 * time.Minute) // 关闭空闲连接

// 验证连接
if err := db.PingContext(ctx); err != nil {
return fmt.Errorf(数据库连接测试: %w, err)
}

连接池设置说明

MaxOpenConns -- 数据库的最大打开连接数。防止应用使用过多并发连接压垮数据库。典型Web应用设置为约25。计算方法：将数据库的max_connections（减去管理维护和复制的预留连接）除以应用实例数。如果数据库允许100个连接，有3个应用实例，预留10个连接用于管理，则设置为(100 - 10) / 3 = 30。

MaxIdleConns -- 不使用时在连接池中保持存活的连接数。这些热连接避免了为每个请求建立新连接的延迟。设置为约10，或约为MaxOpenConns的40%。设置过高会浪费数据库连接；设置过低会导致频繁重新连接。

ConnMaxLifetime -- 连接可被复用的最大时间。超过此时间后，连接会被关闭，并在下一个请求时创建新连接。这有助于获取DNS变更（对故障转移到新IP的云数据库很重要）、在只读副本间重新平衡负载，以及防止连接过时。典型值为5分钟。如果基础设施使用基于DNS的故障转移，设置更短（1-2分钟）。

ConnMaxIdleTime -- 连接在关闭前可保持空闲的最大时间。在低流量期间将连接释放回数据库，释放资源。典型值为1分钟。此值应小于ConnMaxLifetime。

关于pgx原生PostgreSQL支持的连接池配置，请参见 references/connection-pooling.md。

仓库模式

在消费者端定义存储接口以实现可测试性。针对具体数据库驱动实现。此模式保持领域逻辑与数据库解耦。

go
// 可测试的存储接口
type UserStore interface {
GetUser(ctx context.Context, id string) (*User, error)
ListUsers(ctx context.Context, limit, offset int) ([]*User, error)
CreateUser(ctx context.Context, u *User) error
}

// sqlx实现
type PostgresUserStore struct {
db *sqlx.DB
}

func NewPostgresUserStore(db sqlx.DB) PostgresUserStore {
return &PostgresUserStore{db: db}
}

func (s PostgresUserStore) GetUser(ctx context.Context, id string) (User, error) {
var u User
err := s.db.GetContext(ctx, &u, SELECT * FROM users WHERE id = $1, id)
if errors.Is(err, sql.ErrNoRows) {
return nil, ErrNotFound
}
return &u, err
}

func (s PostgresUserStore) ListUsers(ctx context.Context, limit, offset int) ([]User, error) {
var users []*User
err := s.db.SelectContext(ctx, &users,
SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2,
limit, offset,
)
return users, err
}

func (s PostgresUserStore) CreateUser(ctx context.Context, u User) error {
_, err := s.db.NamedExecContext(ctx,
INSERT INTO users (id, email, name, createdat, updatedat)
VALUES (:id, :email, :name, :createdat, :updatedat), u)
return err
}

模型结构体标签

使用db标签进行sqlx列映射，并将模型保持在存储层附近：

go
type User struct {
ID string db:id
Email string db:email
Name string db:name
CreatedAt time.Time db:created_at
UpdatedAt time.Time db:updated_at
}

哨兵错误

定义领域特定错误，调用方可检查而无需导入数据库包：

go
var (
ErrNotFound = errors.New(未找到)
ErrConflict = errors.New(冲突)
)

在存储层将数据库错误映射到领域错误：

go
func (s PostgresUserStore) CreateUser(ctx context.Context, u User) error {
_, err := s.db.NamedExecContext(ctx, query, u)
if err != nil {
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) && pgErr.Code == 23505 {
return ErrConflict
}
return fmt.Errorf(插入用户: %w, err)
}
return nil
}

数据库迁移

使用golang-migrate管理数据模式变更。迁移文件成对出现：一个用于应用变更（up），一个用于回滚（down）。

migrations/
├── 000001createusers.up.sql
├── 000001createusers.down.sql
├── 000002adduser_roles.up.sql
└── 000002adduser_roles.down.sql

在应用启动时运行迁移：

go
import github.com/golang-migrate/migrate/v4

func runMigrations(dbURL string) error {
m, err := migrate.New(file://migrations, dbURL)
if err != nil {
return fmt.Errorf(创建迁移器: %w, err)
}

if err := m.Up(); err != nil && err != migrate.ErrNoChange {
return fmt.Errorf(运行迁移: %w, err)
}

return nil
}

关键规则：始终编写up和down迁移，使用IF NOT EXISTS/IF EXISTS实现幂等性，切勿修改已在生产环境应用的迁移。完整的迁移模式、CI/CD集成和安全迁移策略，请参见 references/migrations.md。

事务

使用事务辅助函数确保一致的提交/回滚处理。事务应在服务层管理，而非存储层，以便多个存储操作可以组合成单个原子单元。

go
func WithTx(ctx context.Context, db sql.DB, fn func(tx sql.Tx) error) error {
tx, err := db.BeginTx(ctx, nil)
if err != nil {

go-data-persistenceGo数据持久化

go-data-persistence

Data Persistence in Go

Quick Reference