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="]