How do you interact with a SQL database in Go using the standard database/sql package?

import (
    "database/sql"
    _ "github.com/lib/pq" // side-effect import registers the driver
)

// Open — creates the pool, does NOT establish a connection yet
db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatal(err)
}
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)

// Verify connection
if err := db.PingContext(ctx); err != nil {
    log.Fatalf("database unreachable: %v", err)
}

// SELECT — always pass context
func getUserByID(ctx context.Context, db *sql.DB, id int) (*User, error) {
    row := db.QueryRowContext(ctx,
        "SELECT id, name, email FROM users WHERE id = $1", id)
    var u User
    err := row.Scan(&u.ID, &u.Name, &u.Email)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    if err != nil {
        return nil, fmt.Errorf("getUserByID: %w", err)
    }
    return &u, nil
}

// SELECT multiple rows — MUST close rows
func listUsers(ctx context.Context, db *sql.DB) ([]User, error) {
    rows, err := db.QueryContext(ctx, "SELECT id, name FROM users")
    if err != nil {
        return nil, fmt.Errorf("listUsers query: %w", err)
    }
    defer rows.Close()  // ALWAYS close — frees connection back to pool

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name); err != nil {
            return nil, fmt.Errorf("listUsers scan: %w", err)
        }
        users = append(users, u)
    }
    return users, rows.Err() // check iteration error
}

// Transaction
func transferFunds(ctx context.Context, db *sql.DB, from, to, amount int) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil { return err }
    defer tx.Rollback() // no-op if Commit succeeds

    if _, err := tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from); err != nil {
        return fmt.Errorf("debit: %w", err)
    }
    if _, err := tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to); err != nil {
        return fmt.Errorf("credit: %w", err)
    }
    return tx.Commit()
}