go-data-persistence

# Data Persistence in Go

## Quick Reference

| Topic | Reference |
|-------|-----------|
| Connection pool internals, sizing, pgx pools, monitoring | [references/connection-pooling.md](references/connection-pooling.md) |
| golang-migrate setup, file conventions, CI/CD integration | [references/migrations.md](references/migrations.md) |
| Transaction helpers, service-layer transactions, isolation levels | [references/transactions.md](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 | Manual | Auto-generated |
| Performance control | Full | Limited |
| Rapid prototyping | Slower | Faster |
| Schema migrations | golang-migrate | Built-in (Ent) |
| Learning curve | SQL knowledge | ORM API |

### 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 `COPY`

**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.

```go
db, err := sql.Open("postgres", connStr)
if err != nil {
    return fmt.Errorf("opening db: %w", err)
}

// Connection pool configuration
db.SetMaxOpenConns(25)                 // Max simultaneous connections
db.SetMaxIdleConns(10)                 // Connections kept alive when idle
db.SetConnMaxLifetime(5 * time.Minute) // Recycle connections
db.SetConnMaxIdleTime(1 * time.Minute) // Close idle connections

// Verify connection
if err := db.PingContext(ctx); err != nil {
    return fmt.Errorf("pinging db: %w", err)
}
```

### 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](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.

```go
// Store interface for testability
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 implementation
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, created_at, updated_at)