Implement abstract raw query execution in Golang
Golang is becoming a de facto standard to implement more advanced application backends like distributed microservices, streaming services, etc. For instance, Docker is fully crafted in Go programming. This article is not mainly explaining the features of Go, rather it explains writing SQL abstraction layer to generalize raw query execution.
There are various libraries available to work with RDBMS systems in Go, like gorm(https://gorm.io/index.html) or xorm (https://xorm.io/xorm/), these libraries are providing full-featured ORM to work with RDBMS systems, but there are few use cases where you need to have raw SQL access and process data.
For example, assume that we need to execute a random query that is constructed from user HTTP request dynamic parameters like below
How can we use these ORM libraries to process queries like this, and execute them in a general way, here I am going to explain two different functions that provide convert row results in a Go map and an array of the map?
Here is the idea is to execute the raw query and iterate the results to build the array of interface maps, then that can be marshaled using JSON encoder libraries to build JSON response.
type DbLayer struct{ dbConnection *gorm.DB}// since we don't know the result structure, we will keep it in map of interface structuretype Params map[string]interface{}func (dl *DbLayer) QueryRowsToListOfMap(rawQuery string) (int64, error, []Params) {
var arrayOfMaps []util.Params
//execute the raw query
rs, err := dl.dbConnection.Raw(rawQuery).Rows()
if err != nil {
return 0, err, arrayOfMaps
}
var (
refs []interface{}
cnt int64
)
for rs.Next() {
var rowMapData util.Params
rowMapData = make(util.Params)
columns, _ := rs.Columns()
refs = make([]interface{}, len(columns))
for i := range refs {
var ref sql.NullString
refs[i] = &ref
}
if err := rs.Scan(refs...); err != nil {
return 0, err, arrayOfMaps
}
for i := range refs {
value := reflect.Indirect(reflect.ValueOf(refs[i])).Interface().(sql.NullString) // keep the results in key value
rowMapData[columns[i]] = value.String
}
// constructing the array of map
arrayOfMaps = append(arrayOfMaps, rowMapData)
cnt++
}
return cnt, nil, arrayOfMaps
}
In this function, the user can define the interesting column they need, then it will be queried using a raw query to build the response map.
func (dl *DbLayer) queryRowsToMap(rawQuery string, container interface{}, keyCol, valueCol string) (int64, error) {
var (
maps util.Params
ind *reflect.Value
)
var typ int
switch container.(type) {
case *util.Params:
typ = 1
default:
typ = 2
vl := reflect.ValueOf(container)
id := reflect.Indirect(vl)
if vl.Kind() != reflect.Ptr || id.Kind() != reflect.Struct {
panic(fmt.Errorf("<RawSeter> RowsTo unsupport type `%T` need ptr struct", container))
}
ind = &id
}
rs, err := dl.dbConnectio.Raw(rawQuery).Rows()
if err != nil {
return 0, err
}
var (
refs []interface{}
cnt int64
cols []string
)
var (
keyIndex = -1
valueIndex = -1
)
for rs.Next() {
if cnt == 0 {
columns, err := rs.Columns()
if err != nil {
return 0, err
}
cols = columns
refs = make([]interface{}, len(cols))
for i := range refs {
fmt.Println("cols :", cols[i])
if keyCol == cols[i] {
keyIndex = i
}
if typ == 1 || keyIndex == i {
var ref sql.NullString
refs[i] = &ref
} else {
var ref interface{}
refs[i] = &ref
}
if valueCol == cols[i] {
valueIndex = i
}
}
if keyIndex == -1 || valueIndex == -1 {
panic(fmt.Errorf("<RawSeter> RowsTo unknown key, value column name `%s: %s`", keyCol, valueCol))
}
}
if err := rs.Scan(refs...); err != nil {
return 0, err
}
if cnt == 0 {
switch typ {
case 1:
maps = make(util.Params)
}
}
key := reflect.Indirect(reflect.ValueOf(refs[keyIndex])).Interface().(sql.NullString).String
switch typ {
case 1:
value := reflect.Indirect(reflect.ValueOf(refs[valueIndex])).Interface().(sql.NullString)
if value.Valid {
maps[key] = value.String
} else {
maps[key] = nil
}
default:
if id := ind.FieldByName(util.CamelString(key)); id.IsValid() {
ab.setFieldValue(id, reflect.ValueOf(refs[valueIndex]).Elem().Interface())
}
}
cnt++
}
if typ == 1 {
v, _ := container.(*util.Params)
*v = maps
}
return cnt, nil
}// helper function to set the field value based on data typefunc (ab *AssetBase) setFieldValue(ind reflect.Value, value interface{}) {
switch ind.Kind() {
case reflect.Bool:
if value == nil {
ind.SetBool(false)
} else if v, ok := value.(bool); ok {
ind.SetBool(v)
} else {
v, _ := util.StrTo(ToStr(value)).Bool()
ind.SetBool(v)
}
case reflect.String:
if value == nil {
ind.SetString("")
} else {
ind.SetString(ToStr(value))
}
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
if value == nil {
ind.SetInt(0)
} else {
val := reflect.ValueOf(value)
switch val.Kind() {
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
ind.SetInt(val.Int())
case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
ind.SetInt(int64(val.Uint()))
default:
v, _ := util.StrTo(ToStr(value)).Int64()
ind.SetInt(v)
}
}
case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
if value == nil {
ind.SetUint(0)
} else {
val := reflect.ValueOf(value)
switch val.Kind() {
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
ind.SetUint(uint64(val.Int()))
case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
ind.SetUint(val.Uint())
default:
v, _ := util.StrTo(ToStr(value)).Uint64()
ind.SetUint(v)
}
}
case reflect.Float64, reflect.Float32:
if value == nil {
ind.SetFloat(0)
} else {
val := reflect.ValueOf(value)
switch val.Kind() {
case reflect.Float64:
ind.SetFloat(val.Float())
default:
v, _ := util.StrTo(ToStr(value)).Float64()
ind.SetFloat(v)
}
}
case sql.NullString, sql.NullInt64, sql.NullFloat64, sql.NullBool:
indi := reflect.New(ind.Type()).Interface()
sc, ok := indi.(sql.Scanner)
if !ok {
return
}
err := sc.Scan(value)
if err == nil {
ind.Set(reflect.Indirect(reflect.ValueOf(sc)))
}
}
case reflect.Ptr:
if value == nil {
ind.Set(reflect.Zero(ind.Type()))
break
}
ind.Set(reflect.New(ind.Type().Elem()))
ab.setFieldValue(reflect.Indirect(ind), value)
}
}
We can execute the above function like below
responseMap Params
numberOfRecords, err := dbLayer.queryRowsToMap("select A,B from Table 1",responseMap, "A","B")
fmt.Println(responseMap)as shown in this example, we can use these functions to generalize our raw queries apart from ORM functionalities.
References : https://gorm.io/docs/sql_builder.html
