Create

map_struct.go

package main

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"reflect"
	"strings"

	_ "github.com/go-sql-driver/mysql"
)

type ExampleStruct struct {
	ID   int    `json:"id" db:"id"`
	Name string `json:"name" db:"name"`
}

func main() {
	db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/database")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	var result ExampleStruct
	err = QueryAndMapStruct(db, "SELECT * FROM example_table WHERE id = ?", &result, 1)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("%+v\n", result)

	jsonData, err := json.Marshal(result)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println(string(jsonData))
}

func QueryAndMapStruct(db *sql.DB, query string, out interface{}, args ...interface{}) error {
	rows, err := db.Query(query, args...)
	if err != nil {
		return err
	}
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return err
	}

	outValue := reflect.ValueOf(out)
	if outValue.Kind() != reflect.Ptr || outValue.Elem().Kind() != reflect.Struct {
		return fmt.Errorf("expected a pointer to a struct, got %T", out)
	}

	outStruct := outValue.Elem()

	for rows.Next() {
		scanArgs, err := mapFieldsToColumns(outStruct, columns)
		if err != nil {
			return err
		}

		err = rows.Scan(scanArgs...)
		if err != nil {
			return err
		}
	}

	return rows.Err()
}

func mapFieldsToColumns(outStruct reflect.Value, columns []string) ([]interface{}, error) {
	outType := outStruct.Type()
	scanArgs := make([]interface{}, len(columns))

	for i, col := range columns {
		fieldFound := false

		for j := 0; j < outType.NumField(); j++ {
			field := outType.Field(j)

			if field.Type.Kind() == reflect.Struct {
				innerStruct := outStruct.Field(j)
				innerScanArgs, err := mapFieldsToColumns(innerStruct, columns)
				if err == nil {
					fieldFound = true
					copy(scanArgs, innerScanArgs)
					break
				}
			}

			jsonTag := field.Tag.Get("json")
			if strings.Split(jsonTag, ",")[0] == col {
				scanArgs[i] = outStruct.Field(j).Addr().Interface()
				fieldFound = true
				break
			}
		}

		if !fieldFound {
			return nil, fmt.Errorf("field not found for column '%s'", col)
		}
	}

	return scanArgs, nil
}