Golang database

Go Database Patterns

pgx Connection Pool

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

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

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

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

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

-- +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: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