database/sql — Find the Bug¶

func listUsers(db *sql.DB) ([]string, error) {
    rows, err := db.Query(`SELECT name FROM users`)
    if err != nil {
        return nil, err
    }
    var names []string
    for rows.Next() {
        var n string
        if err := rows.Scan(&n); err != nil {
            return nil, err               // BUG: rows never closed on error
        }
        names = append(names, n)
    }
    return names, rows.Err()              // BUG: still no Close on happy path
}

rows, err := db.Query(`SELECT id FROM orders WHERE total > ?`, 100)
if err != nil { return err }
defer rows.Close()

if err := rows.Err(); err != nil {        // BUG: Err is meaningful only after Next returns false
    return err
}
for rows.Next() {
    var id int64
    rows.Scan(&id)
    process(id)
}
return nil                                // BUG: never inspects Err after the loop

func transferBatch(db *sql.DB, transfers []Transfer) error {
    tx, err := db.Begin()
    if err != nil { return err }
    defer tx.Rollback()

    var wg sync.WaitGroup
    for _, t := range transfers {
        wg.Add(1)
        go func(t Transfer) {                                  // BUG: tx shared across goroutines
            defer wg.Done()
            tx.Exec(`UPDATE accounts SET bal = bal - ? WHERE id = ?`, t.Amount, t.From)
            tx.Exec(`UPDATE accounts SET bal = bal + ? WHERE id = ?`, t.Amount, t.To)
        }(t)
    }
    wg.Wait()
    return tx.Commit()
}

func handler(w http.ResponseWriter, r *http.Request) {
    db, err := sql.Open("sqlite3", "app.db")
    if err != nil { http.Error(w, err.Error(), 500); return }
    defer db.Close()                                            // BUG: pool dies on every request

    var name string
    db.QueryRow(`SELECT name FROM users WHERE id = ?`, r.URL.Query().Get("id")).Scan(&name)
    fmt.Fprintln(w, name)
}

func search(db *sql.DB, name string) ([]User, error) {
    q := fmt.Sprintf(`SELECT id, name FROM users WHERE name = '%s'`, name)   // BUG
    rows, err := db.Query(q)
    if err != nil { return nil, err }
    defer rows.Close()
    var out []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name); err != nil { return nil, err }
        out = append(out, u)
    }
    return out, rows.Err()
}

type Profile struct {
    ID   int64
    Bio  string                                                 // BUG: NULL-able column
}
func loadProfile(db *sql.DB, id int64) (Profile, error) {
    var p Profile
    err := db.QueryRow(`SELECT id, bio FROM profiles WHERE id = ?`, id).Scan(&p.ID, &p.Bio)
    return p, err                                               // returns sql: Scan error on column index 1: converting NULL to string is unsupported
}

func getName(db *sql.DB, id int64) (string, error) {
    stmt, err := db.Prepare(`SELECT name FROM users WHERE id = ?`)   // BUG: every call
    if err != nil { return "", err }
    // no stmt.Close()
    var name string
    err = stmt.QueryRow(id).Scan(&name)
    return name, err
}

func main() {
    db, _ := sql.Open("mysql", dsn)
    // BUG: never call SetMaxOpenConns; default is 0 = unlimited
    http.HandleFunc("/r", func(w http.ResponseWriter, r *http.Request) {
        db.QueryRow(`SELECT pg_sleep(2)`).Scan(new(string))
        fmt.Fprintln(w, "ok")
    })
    http.ListenAndServe(":8080", nil)
}

func getBalance(db *sql.DB, id int64) (float64, error) {
    var bal float64
    err := db.QueryRow(`SELECT balance FROM accounts WHERE id = ?`, id).Scan(&bal)
    if err == sql.ErrNoRows {
        return 0, nil                                           // BUG: zero != "missing"
    }
    return bal, err
}

func auditAndUpdate(db *sql.DB, id int64) error {
    rows, err := db.Query(`SELECT event FROM audit WHERE user_id = ?`, id)   // BUG: never closed
    if err != nil { return err }
    if rows.Next() {
        // peek at first row, decide to update
        var ev string
        rows.Scan(&ev)
        if ev == "blocked" {
            return nil                                            // BUG: returns with rows still open
        }
    }
    _, err = db.Exec(`UPDATE users SET seen_at = NOW() WHERE id = ?`, id)
    return err                                                    // BUG: rows still open here too
}

func charge(db *sql.DB, userID int64, amount float64) error {
    tx, err := db.Begin()
    if err != nil { return err }

    if _, err := tx.Exec(`UPDATE accounts SET bal = bal - ? WHERE id = ?`, amount, userID); err != nil {
        return err                                              // BUG: tx leaked
    }
    if _, err := tx.Exec(`INSERT INTO charges (uid, amount) VALUES (?, ?)`, userID, amount); err != nil {
        return err                                              // BUG: tx leaked
    }
    return tx.Commit()
}

func handler(w http.ResponseWriter, r *http.Request) {
    rows, err := db.QueryContext(context.Background(),         // BUG: ignored request context
        `SELECT * FROM big_table`)
    if err != nil { http.Error(w, err.Error(), 500); return }
    defer rows.Close()
    // ... slow loop
}

rows, err := db.Query(`SELECT name FROM users WHERE id = ?`, id)
if err != nil { return err }
defer rows.Close()

var name string
if err := rows.Scan(&name); err != nil {                       // BUG: no Next before Scan
    return err
}
return nil

// Schema (SQLite or MySQL):
// CREATE TABLE events (id INTEGER PRIMARY KEY, ts INTEGER NOT NULL);   -- stored as Unix seconds
type Event struct {
    ID int64
    At time.Time                                                // BUG: column is INT, not TIMESTAMP
}

err := db.QueryRow(`SELECT id, ts FROM events WHERE id = ?`, id).Scan(&e.ID, &e.At)
// sql: Scan error on column index 1: converting driver.Value type int64 ("1716998400") to a time.Time: unsupported Scan