# Golang Database

> When to activate: database/sql, pgx, PostgreSQL in Go, connection pools, prepared statements, migrations with goose

- Skill: `mattakushi432/golang-database` (Agent Skill)
- Install (CLI): `npx skillmds@latest add mattakushi432/golang-database`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mattakushi432/golang-database/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: Mattakushi432 (https://skillmd.com/u/mattakushi432)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/mattakushi432/golang-database

---


# Go Database Patterns

## pgx Connection Pool

```go
import "github.com/jackc/pgx/v5/pgxpool"

func NewPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
    cfg, err := pgxpool.ParseConfig(dsn)
    if err != nil { return nil, err }
    cfg.MaxConns = 25
    cfg.MinConns = 5
    cfg.MaxConnLifetime = 5 * time.Minute
    cfg.MaxConnIdleTime = 1 * time.Minute
    pool, err := pgxpool.NewWithConfig(ctx, cfg)
    if err != nil { return nil, err }
    return pool, pool.Ping(ctx)
}
```

## Repository Pattern

```go
type UserRepository struct{ pool *pgxpool.Pool }

func (r *UserRepository) FindByID(ctx context.Context, id string) (User, error) {
    var u User
    err := r.pool.QueryRow(ctx,
        `SELECT id, email, name, created_at FROM users WHERE id = $1`, id,
    ).Scan(&u.ID, &u.Email, &u.Name, &u.CreatedAt)
    if errors.Is(err, pgx.ErrNoRows) { return User{}, ErrNotFound }
    return u, err
}

func (r *UserRepository) List(ctx context.Context, limit, offset int, search string) ([]User, error) {
    rows, err := r.pool.Query(ctx,
        `SELECT id, email, name, created_at FROM users
         WHERE ($1 = '' OR email ILIKE '%' || $1 || '%')
         ORDER BY created_at DESC LIMIT $2 OFFSET $3`,
        search, limit, offset,
    )
    if err != nil { return nil, err }
    defer rows.Close()
    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Email, &u.Name, &u.CreatedAt); err != nil { return nil, err }
        users = append(users, u)
    }
    return users, rows.Err()
}

func (r *UserRepository) Create(ctx context.Context, u User) (User, error) {
    err := r.pool.QueryRow(ctx,
        `INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id, created_at`,
        u.Email, u.Name,
    ).Scan(&u.ID, &u.CreatedAt)
    return u, err
}
```

## Transactions

```go
func (r *UserRepository) Transfer(ctx context.Context, fromID, toID string, amount int) error {
    tx, err := r.pool.Begin(ctx)
    if err != nil { return err }
    defer tx.Rollback(ctx)

    if _, err = tx.Exec(ctx,
        `UPDATE accounts SET balance = balance - $1 WHERE user_id = $2 AND balance >= $1`,
        amount, fromID,
    ); err != nil { return fmt.Errorf("debit: %w", err) }

    if _, err = tx.Exec(ctx,
        `UPDATE accounts SET balance = balance + $1 WHERE user_id = $2`,
        amount, toID,
    ); err != nil { return fmt.Errorf("credit: %w", err) }

    return tx.Commit(ctx)
}
```

## Bulk Insert with CopyFrom

```go
func (r *UserRepository) BulkInsert(ctx context.Context, users []User) error {
    _, err := r.pool.CopyFrom(ctx,
        pgx.Identifier{"users"},
        []string{"email", "name"},
        pgx.CopyFromSlice(len(users), func(i int) ([]any, error) {
            return []any{users[i].Email, users[i].Name}, nil
        }),
    )
    return err
}
```

## Migrations with goose

```bash
goose -dir migrations create add_users_table sql
goose -dir migrations postgres "$DATABASE_URL" up
goose -dir migrations postgres "$DATABASE_URL" down
```

```sql
-- +goose Up
CREATE TABLE users (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email      TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- +goose Down
DROP TABLE users;
```

```go
//go:embed migrations/*.sql
var embedMigrations embed.FS

func runMigrations(db *sql.DB) error {
    goose.SetBaseFS(embedMigrations)
    return goose.Up(db, "migrations")
}
```

## Common Anti-Patterns

- **`db.Query` without closing rows** — always `defer rows.Close()`; open rows hold a connection
- **String formatting for SQL** — always use `$1, $2` placeholders, never `fmt.Sprintf` into SQL
- **No pool limits** — set `MaxConns` to prevent overloading the database
- **Transactions without deferred rollback** — `defer tx.Rollback(ctx)` is a no-op after commit
- **AutoMigrate in production** — use goose/golang-migrate for controlled, reversible migrations