Generalizing *sql.Rows Scan in Go

My proposal to deal with "common code" is quite simple, relying mostly on the premise that retrieve/get operations most of the times are used to return those results to a web browser client. So, those results are, in turn, transformed into JSON leveraging the json package.

file: db_common.go

// Interface for things that performs an scan over a given row.
// Actually it is a common interface for https://pkg.go.dev/database/sql#Rows.Scan and https://pkg.go.dev/database/sql#Row.Scan
type RowScanner interface {
    Scan(dest ...interface{}) error
}

// Scans a single row from a given query
type RowScanFunc func(rows RowScanner) (interface{}, error)

// Scans multiples rows using a scanner function in order to build a new "scanable" struct
func ScanMultiples(rows *sql.Rows, rowScanFunc RowScanFunc) ([]interface{}, error) {
    scaneables := []interface{}{}
    for rows.Next() {
        scanable, err := rowScanFunc(rows)
        if scanable == nil {
            return nil, err
        }
        scaneables = append(scaneables, scanable)
    }
    err := rows.Err()
    if err != nil {
        return nil, err
    }
    return scaneables, nil
}

And then I used the above abstractions like this:

file: dao/crud_operations.go

// Type that models a row of a given db table
type TableRow struct {
    Id int
    Name string
    // more fields...
}

func GetAll() ([]interface{}, error) {
    rows, err := Db.Query(`
        SELECT id, name
        FROM a_table`
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    return ScanMultiples(rows, scanRow)
}

// Gets an specific legal entity by id
func GetById(id int) (interface{}, error) {
    row := Db.QueryRow(`
        SELECT id, name
        FROM a_table
        WHERE id = $1`, id)
    return scanRow(row)
}


// Scans a row interpreting it as 'TableRow' struct
func scanRow(rows RowScanner) (*TableRow, error) { // DO NOTE that implements the RowScanner interface!
    var tableRow TableRow

    err := rows.Scan(tableRow.Id,tableRow.Name)
    if err != nil {
        return nil, err
    }
    return &tableRow, nil
}

• The good thing is that you only must code a single function that scans a Row.
• Another good thing is that is not code tangled with external dependencies.
• Another good thing is that the code doesn't resort to reflect package or those complicated things.
• The bad thing is that you return interface{} and you may has to cast it to the original data type, having to write, 3 lines (in the worst case) for that.
• Again you don't have to worry if the resulting results are going to be Marshaled like bytes, err := json.Marshal(results), because the json package receives an interface{} (=any) as parameter.

"... but reflect package cannot create a slice of struct from a passed interface{} type." -- You looking for this?

func sliceFromElemValue(v interface{}) (interface{}) {
    rt := reflect.TypeOf(v)
    rs := reflect.MakeSlice(reflect.SliceOf(rt), 0, 0)

    for i := 0; i < 3; i++ { // dummy loop
        rs = reflect.Append(rs, reflect.New(rt).Elem())
    }

    return rs.Interface()
}

https://play.golang.com/p/o4AJ-f71egw

"What I need to achieve is something like as follows"

func RowsToStructs(rows *sql.Rows, model interface{}) ([]interface{}, error) { ...

Or are you looking for this?

func sliceFromElemValue(v interface{}) ([]interface{}) {
    rt := reflect.TypeOf(v)
    s := []interface{}{}

    for i := 0; i < 3; i++ { // dummy loop
        s = append(s, reflect.New(rt).Elem().Interface())
    }

    return s
}

https://play.golang.com/p/i57Z8OO8n7G