sqlgogenericsselect

Golang SQL rows.Scan function for all fields of generic type


I want to use the Scan() function from the sql package for executing a select statement that might (or not) return multiple rows, and return these results in my function.

I´m new to Golang generics, and am confused about how to achieve this. Usually, we would use the Scan function on a *sql.Rows and provide the references to all fields of our expected 'result type' we want to read the rows into, e.g.:

var alb Album
rows.Scan(&alb.ID, &alb.Title, &alb.Artist,
            &alb.Price, &alb.Quantity)

where Album is a struct type with those five fields shown.

Now, for the purpose of not writing a similar function N times for every SQL table I have, I want to use a generic type R instead. R is of generic interface type Result, and I will define this type as one of N different structs:

type Result interface {
    StructA | StructB | StructC
}

func ExecSelect[R Result](conn *sql.DB, cmd Command, template R) []R

How can I now write rows.Scan(...) to apply the Scan operation on all fields of my struct of R´s concrete type? e.g. I would want to have rows.Scan(&res.Field1, &res.Field2, ...) where res is of type R, and Scan should receive all fields of my current concrete type R. And do I actually need to provide a 'template' as argument of R´s concrete type, so that at runtime it becomes clear which struct is now relevant?

Please correct me on any mistake I´m making considering the generics.


Solution

  • Another answer shows how to create a map of types as asked in the question. You don't actually need a map of types.

    For each type, you need a function to create a pointer to a new value and a function to deference the pointer. Let's declare an interface for the required functionality:

    type FieldTyper interface {
        // Return pointer to new value.
        New() (ptr any)
        // Dereference pointer.
        Deref(ptr any) (val any)
    }
    

    Create a generic implementation of that interface:

    type FieldType[T any] struct{}
    
    func (v FieldType[T]) New() any {
        return new(T)
    }
    
    func (v FieldType[T]) Deref(p any) any {
        return *p.(*T)
    }
    

    Create a map of column names to FieldTypers:

    var fieldTypes = map[string]FieldTyper{
        "id":   FieldType[Type_int]{},
        "name": FieldType[Type_string]{},
    }
    

    Use the field typers to setup the scan args and deference those args and add to map.

    func SetupScanArgs(columnNames []string, fieldTypes map[string]FieldTyper) []any {
        args := make([]any, len(columnNames))
        for i, n := range columnNames {
            args[i] = fieldTypes[n].New()
        }
        return args
    }
    
    func ArgsToValueMap(columnNames []string, fieldTypes map[string]FieldTyper, args []any) map[string]any {
        result := make(map[string]any)
        for i, n := range columnNames {
            result[n] = fieldTypes[n].Deref(args[i])
        }
        return result
    }
    

    Scan like this:

    args := SetupScanArgs(columnNames, fieldTypes)
    if err := rows.Scan(args...); err != nil {
        return err
    }
    m := ArgsToValueMap(columnNames, fieldTypes, args)