正确的方法来执行多个查询Go Rest API

Question

I am attempting to create a REST API in Go. I have it partially working in that it will return 4 separate json objects like such:

[{"Name":"QA1","Server":"BOT1","Description":"Tools","Apps":""},
{"Name":"QA1","Server":"","Description":"","Apps":"Duo"},
{"Name":"QA1","Server":"","Description":"","Apps":"Git"},
{"Name":"QA1","Server":"","Description":"","Apps":"php"}]

What I want is a single returned object like:

[{"Name":"QA1","Server":"BOT1","Description":"Tools","Apps": "Duo|Git|php"}]

I obviously have the way that I am either making my queries or the structs (or both or something else) not quite correct. I want to make sure I understand how to do this right because I would like to expand on it for other queries and such down the road. I have included the "full" go code below.

To be clear, I'm not simply looking for the solution (though I would of course appreciate that to compare with), but where I've gone wrong in my thinking and what the correct approach would be.

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "io/ioutil"
    "log"
    "net/http"
)

// There can be zero or more apps on a volume
type Apps struct {
    Name string
}

// Volumes have a name, description, are on a server and have multiple services/apps
type Volume struct {
    Name        string
    Server      string
    Description string
    Services    Apps
}

//Handle all requests
func Handler(response http.ResponseWriter, request *http.Request) {
    response.Header().Set("Content-type", "text/html")
    webpage, err := ioutil.ReadFile("index.html")
    if err != nil {
        http.Error(response, fmt.Sprintf("home.html file error %v", err), 500)
    }
    fmt.Fprint(response, string(webpage))
}

// DB Connection
const (
    DB_HOST = "mydbhost"
    DB_NAME = "mydb"
    DB_USER = "mydbuser"
    DB_PASS = "mydbpass"
)

// Respond to URLs of the form /api
func APIHandler(response http.ResponseWriter, request *http.Request) {

    //Connect to database
    dsn := DB_USER + ":" + DB_PASS + "@" + DB_HOST + "/" + DB_NAME + "?charset=utf8"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        fmt.Println(err.Error())
    }
    defer db.Close()

    // Open doesn't open a connection. Validate DSN data:
    err = db.Ping()
    if err != nil {
        fmt.Println(err.Error())
    }

    //set mime type to JSON
    response.Header().Set("Content-type", "application/json")

    result := []*Volume{}

    switch request.Method {
    case "GET":
        srvrnm := request.URL.Query().Get("srvrnm")
        appnm := request.URL.Query().Get("appnm")
        srvrs, err := db.Prepare("select VOLUMES.name as volnm, SERVERS.name as srvrnm, VOLUMES.description as descr From VOLUMES LEFT JOIN SERVERS ON VOLUMES.server_id = SERVERS.id where SERVERS.name = ?")
        if err != nil {
            fmt.Print(err)
        }
        srvcs, err := db.Prepare("select VOLUMES.name as volnm, SUPPRTSVCS.name as app_name From VOLUMES as VOLUMES JOIN HOSTSVCS ON VOLUMES.id = HOSTSVCS.volume_id JOIN SUPPRTSVCS ON SUPPRTSVCS.id = HOSTSVCS.supportsvcs_id where VOLUMES.name = ?")
        if err != nil {
            fmt.Print(err)
        }

        // Run the SQL Query to Get Volum & Description From Hostname
        srvrrows, err := srvrs.Query(srvrnm)
        if err != nil {
            fmt.Print(err)
        }
        for srvrrows.Next() {
            var volnm string
            var srvrnm string
            var descr string
            // Scan the First Query
            err = srvrrows.Scan(&volnm, &srvrnm, &descr)
            if err != nil {
                fmt.Println("Error scanning: " + err.Error())
                return
            }
            // Append Slice with results from the scan
            result = append(result, &Volume{Name: volnm, Server: srvrnm, Description: descr})
        }

        // Run the SQL Query for Services/Apps
        srvcrows, err := srvcs.Query(appnm)
        if err != nil {
            fmt.Print(err)
        }

        for srvcrows.Next() {
            var volnm string
            var appnm string
            // Scan the Second Query
            err = srvcrows.Scan(&volnm, &appnm)
            if err != nil {
                fmt.Println("Error scanning: " + err.Error())
                return
            }
            // Append Slice with results from the scan
            result = append(result, &Volume{Name: volnm, Apps: appnm})
        }
    default:
    }

    json, err := json.Marshal(result)
    if err != nil {
        fmt.Println(err)
        return
    }

    fmt.Fprintf(response, string(json))
    db.Close()
}

func main() {
    port := "1236"
    var err string

    mux := http.NewServeMux()
    mux.Handle("/api", http.HandlerFunc(APIHandler))
    mux.Handle("/", http.HandlerFunc(Handler))

    // Start listing on a given port with these routes on this server.
    log.Print("Listening on port " + port + " ... ")
    errs := http.ListenAndServe(":"+port, mux)
    if errs != nil {
        log.Fatal("ListenAndServe error: ", err)
    }
}

Answer 1

From the sounds of it, you want to your result to look like:

[
  {"Name":"QA1","Server":"BOT1","Description":"Tools","Apps": ["Duo","Git","php"]
]

Hence you want your Volumes struct to look like:

type Volume struct {
    Name        string
    Server      string
    Description string
    Services    []Apps
}

If you want the Apps to actually output Duo|Git|php then you could create a custom type instead of []Apps with a JSON Marshaler implementation. This could simply return json.Marshal(strings.join(names,"|"))

Rather than run two separate queries, it would be more efficient to run a single query that selects the product of volumes & apps together. It is important that this query is sorted by volume so all volume rows are contiguous. Example query output would be:

Name | Server | Desc  | App
---- | ------ | ----- | ---
Vol1 | Srv1   | Desc1 | App1 
Vol1 | Srv1   | Desc1 | App2
Vol2 | Srv2   | Desc2 | App3

You would then loop over this and detect if you are looking at a new volume. If so, create a new entry in the result. If not, add the App to the list of apps. For example:

var (
   volnm string 
   srvrnm string
   descr string
   appnm string 
   v     *Volume
   result []*Volume
)

for srvrrows.Next() {
    if err = srvcrows.Scan(&volnm, &srvrnm, &descr, &appnm);err!=nil {
       // Handle error
    }

    // Add App to current volume if same, otherwise start a new volume
    if v!=nil && v.Name == volnm {
       v.Services = append(v.Services,Apps{appnm}) 
    } else {
       v = &Volume{
          Name: volnm, 
          Server: svrnm, 
          Description: descr, 
          Services: []Apps{appnm}}
       result = append(result,v)
    }
}

// Finished, return result etc...

When taking this approach, you need an appropriate parent record discriminator. I'd just used v.Name == volnm for illustration purposes but this should really be checking the primary key. You can make this an unexported (lowercase) field in the struct if you do not wish to export it through the API.