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

Go's database/sql package provides a driver-agnostic interface for SQL databases. It manages a connection pool automatically. The key patterns are: always close rows, always use parameterised queries to prevent SQL injection, and pass context to every query.

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

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.