Golang中的动态SQL选择查询

Question

I am trying to build API, with database/sql and mysql driver, that will read data based on URL parameters.
Something like this

myapi.com/users?columns=id,first_name,last_name,country&sort=desc&sortColumn=last_name&limit=10&offset=20

I know how to get all columns or just specific columns when it is defined in struct. But I want to know is it possible to get columns from url and instead of predefined struct save it to map and than just scan those columns.
I have working code that will get data from above endpoint only if number of columns is same as in struct. If I remove country for example I get error that Scan expects 4 params but 3 are given.

I don't need specific code, just some directions since I am learning Go and my background is PHP where this is easier to do.

Update

Thanks to answers I have partly working solution.
Here is code:

cols := []string{"id", "first_name", "last_name"}
vals := make([]interface{}, len(cols))
w := map[string]interface{}{"id": 105}

var whereVal []interface{}
var whereCol []string

for k, v := range w {
    whereVal = append(whereVal, v)
    whereCol = append(whereCol, fmt.Sprintf("%s = ?", k))
}

for i := range cols {
    vals[i] = new(interface{})
}
err := db.QueryRow("SELECT "+strings.Join(cols, ",")+" FROM users WHERE "+strings.Join(whereCol, " AND "), whereVal...).Scan(vals...)

if err != nil {
    fmt.Println(err)
}

b, _ := json.Marshal(vals)
fmt.Println(string(b))

This should query SELECT id, first_name, last_name FROM users WHERE id = 105;

But how do I get data out to proper json object? Now it prints out strings encoded in base64 like this.

[105,"Sm9obm55","QnJhdm8="]

Answer 1

From what I know (also not much experienced in Go) if you don't assign a real type to value then Scan will return []byte and when it is marshalled it returns base64 encoded string.

So you have to assign a type to your columns and if you want proper json then assign keys to values.

In your example it can be done something like this:

cols := []string{"id", "first_name", "last_name"}
vals := make([]interface{}, len(cols))
result := make(map[string]interface{}, len(cols))

for i, key := range cols {
    switch key {
    case "id", "status":
        vals[i] = new(int)
    default:
        vals[i] = new(string)
    }

    result[key] = vals[i]
}

b, _ := json.Marshal(result)
fmt.Println(string(b))

So, instead of looping over cols and creating new interface for each column, now we are creating key/value pairs and assigning type based on column name.

Also, if you have nullable columns in table, and you probably have, then you'll get error because nil can't go into string. So I suggest this package gopkg.in/guregu/null.v3 and then assign type like null.String. That way you'll get back null as a value.

For example:

for i, key := range cols {
    switch key {
    case "id", "status":
        vals[i] = new(int)
    case "updated_at", "created_at":
        vals[i] = new(null.Time)
    default:
        vals[i] = new(null.String)
    }

    result[key] = vals[i]
}

Answer 2

You must first fetch the result columns count and then don't exceed the size.

If you meant the query fields, you need dynamic create the query string, the params size must be the same.

Answer 3

I would create a query statement with the dynamic fields(use placeholder for avoiding sql injection):

rows := db.QueryRow("SELECT {{YOUR_FIELDS}} from table_tbl")

Create variable carrier with the same size of columns

vals := make([]interface{}, len(rows.Columns()))

Use sql.RawBytes for field's type if you don't need type checking or can't know their types, otherwise use the same type of field.

for i, _ := range cols {
    vals[i] = new(sql.RawBytes)
    //check column name, if it is id, and you know it is integer
    //vals[i] = new(int)
}

Iterate rows and scan

for rows.Next() {
    err = rows.Scan(vals...)
}