SQL Queries Iteration

Using inversion of control for SQL Query set iteration

Using SQL in Go requires doing some manual resource management of query result sets. This can be automated by using inversion of control:

var iterationEarlyExit error = errors.New("Early iteration exit requested")

type RowFunc func(rows *sql.Rows) error

func ForEachRowQuery(DB, *sql.DB, rowf RowFunc, query string, args ...interface{}) error {
    rows, err := DB.Query(query, args...)
    if err != nil {
        log.Printf("Error running query list: %v\n", err)
        return errors.New("DB Error running query for list")
    }
    // Close the query object when we have finished iteration
    defer rows.Close()

    for rows.Next() {
        err = rowf(rows)

        if err != nil {
            if err == iterationEarlyExit {
                // Request to exit early - not an error
                return nil
            }

            log.Printf("Error iterating over values for query %v: %v\n", query, err)
            return errors.New("DB Error iterating over results for query " + query)
        }
    }
    // Final check for errors during iteration
    err = rows.Err()
    if err != nil {
        log.Printf("Error after iterating for query %v: %v\n", query, err)
        return errors.New("DB Error iterating over query " + query)
    }
    // The defer will fire here and clean-up the query
    return nil
}

Writing code to consume all rows in an SQL query now becomes much simpler and less error prone:

err := ForEachRowQuery(func(rows *sql.Rows) error {
        var name string

        err := rows.Scan(&name)
        if err == nil {
            if name == "Test" {
                // We want to exit early
                return iterationEarlyExit
            }
            // Use the name somewhere and keep iterating
        }
        return err
    }, "SELECT name FROM people WHERE status = ?", status)