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)
}

// 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)
         VALUES (:id, :email, :name, :created_at, :updated_at)`, u)
    return err
}

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"`
}

var (
    ErrNotFound  = errors.New("not found")
    ErrConflict  = errors.New("conflict")
)

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("inserting user: %w", err)
    }
    return nil
}

migrations/
├── 000001_create_users.up.sql
├── 000001_create_users.down.sql
├── 000002_add_user_roles.up.sql
└── 000002_add_user_roles.down.sql

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("creating migrator: %w", err)
    }

    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return fmt.Errorf("running migrations: %w", err)
    }

    return nil
}

func WithTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("beginning transaction: %w", err)
    }

    if err := fn(tx); err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return fmt.Errorf("rollback failed: %v (original error: %w)", rbErr, err)
        }
        return err
    }

    return tx.Commit()
}

func (s *OrderService) PlaceOrder(ctx context.Context, order *Order) error {
    return WithTx(ctx, s.db, func(tx *sql.Tx) error {
        if err := s.orderStore.CreateWithTx(ctx, tx, order); err != nil {
            return fmt.Errorf("creating order: %w", err)
        }
        if err := s.inventoryStore.DecrementWithTx(ctx, tx, order.Items); err != nil {
            return fmt.Errorf("updating inventory: %w", err)
        }
        return nil
    })
}

// BAD -- SQL injection vulnerability
query := "SELECT * FROM users WHERE name = '" + name + "'"

// BAD -- handler depends on sql.ErrNoRows
func (s *Server) handleGetUser(w http.ResponseWriter, r *http.Request) {
    user, err := s.store.GetUser(ctx, id)
    if errors.Is(err, sql.ErrNoRows) { // handler knows about sql package
        http.NotFound(w, r)
        return
    }
}

// BAD -- bypasses connection pooling entirely
func (s *Server) handleGetUser(w http.ResponseWriter, r *http.Request) {
    db, _ := sql.Open("postgres", connStr) // new pool per request!
    defer db.Close()
}

// BAD -- fragile, breaks when columns change
err := db.GetContext(ctx, &u, "SELECT * FROM users WHERE id = $1", id)

// BAD -- ignores context, query runs even if client disconnects
rows, err := db.Query("SELECT * FROM large_table")

// BAD -- store controls transaction, caller cannot compose
func (s *UserStore) CreateUser(ctx context.Context, u *User) error {
    tx, _ := s.db.BeginTx(ctx, nil)
    // ... insert user ...
    return tx.Commit()
}