Talking to a database with database/sql

import (
    "database/sql"
    // the driver, imported for side effects only
    _ "modernc.org/sqlite"
)

db, err := sql.Open("sqlite", "app.db")
if err != nil {
    return fmt.Errorf("opening database: %w", err)
}
defer db.Close()

if err := db.Ping(); err != nil {
    return fmt.Errorf("connecting: %w", err)
}

db.SetMaxOpenConns(25)                   // hard ceiling on connections
db.SetMaxIdleConns(25)                   // keep them warm
db.SetConnMaxLifetime(5 * time.Minute)   // recycle, for load balancers
db.SetConnMaxIdleTime(5 * time.Minute)

func GetUser(ctx context.Context, db *sql.DB, id int) (*User, error) {
    var u User
    err := db.QueryRowContext(ctx,
        `SELECT id, name, email FROM users WHERE id = ?`, id,
    ).Scan(&u.ID, &u.Name, &u.Email)

    if errors.Is(err, sql.ErrNoRows) {
        return nil, fmt.Errorf("user %d: %w", id, ErrNotFound)
    }
    if err != nil {
        return nil, fmt.Errorf("querying user %d: %w", id, err)
    }
    return &u, nil
}

func ListUsers(ctx context.Context, db *sql.DB) ([]User, error) {
    rows, err := db.QueryContext(ctx,
        `SELECT id, name, email FROM users ORDER BY id`)
    if err != nil {
        return nil, fmt.Errorf("listing users: %w", err)
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
            return nil, fmt.Errorf("scanning user: %w", err)
        }
        users = append(users, u)
    }
    // The check everybody forgets: the loop ends the same way on error.
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("iterating users: %w", err)
    }
    return users, nil
}

res, err := db.ExecContext(ctx,
    `INSERT INTO users (name, email) VALUES (?, ?)`, name, email)
if err != nil {
    return fmt.Errorf("creating user: %w", err)
}

id, err := res.LastInsertId()     // SQLite and MySQL
n, err := res.RowsAffected()

// NEVER do this.
query := "SELECT * FROM users WHERE email = '" + email + "'"

// Always this.
db.QueryRowContext(ctx, "SELECT ... WHERE email = ?", email)

sql: Scan error on column index 2: converting NULL to string is unsupported

// 1. Best: make the column NOT NULL DEFAULT '' in the schema.

// 2. A pointer, when NULL is genuinely meaningful.
var bio *string
rows.Scan(&u.ID, &u.Name, &bio)

// 3. sql.NullString, when you want the distinction explicit.
var bio sql.NullString
rows.Scan(&u.ID, &u.Name, &bio)
if bio.Valid {
    u.Bio = bio.String
}

var bio sql.Null[string]

func Transfer(ctx context.Context, db *sql.DB,
    from, to int, amount int64) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("beginning transaction: %w", err)
    }
    // Safe to call after Commit: it becomes a no-op. This is the guard
    // that
    // makes every early return below correct.
    defer tx.Rollback()

    _, err = tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance - ? WHERE id = ?`,
        amount, from)
    if err != nil {
        return fmt.Errorf("debiting %d: %w", from, err)
    }

    var balance int64
    err = tx.QueryRowContext(ctx,
        `SELECT balance FROM accounts WHERE id = ?`, from).Scan(&balance)
    if err != nil {
        return fmt.Errorf("checking balance: %w", err)
    }
    if balance < 0 {
        return ErrInsufficientFunds      // the deferred Rollback undoes it
    }

    _, err = tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance + ? WHERE id = ?`,
           amount, to)
    if err != nil {
        return fmt.Errorf("crediting %d: %w", to, err)
    }

    return tx.Commit()
}

posts, _ := ListPosts(ctx, db)              // 1 query
for i := range posts {
    posts[i].Author, _ = GetUser(ctx, db, posts[i].AuthorID)   // N queries
}

ids := make([]int, 0, len(posts))
for _, p := range posts {
    ids = append(ids, p.AuthorID)
}

// Build the right number of placeholders.
ph := strings.Repeat("?,", len(ids))
ph = ph[:len(ph)-1]
args := make([]any, len(ids))
for i, id := range ids {
    args[i] = id
}

rows, err := db.QueryContext(ctx,
    `SELECT id, name FROM users WHERE id IN (`+ph+`)`, args...)
// ... build map[int]User, then attach to posts