An instance of the *sql.Rows type is not something that can be marshaled into json directly. It does not implement the json.Marshaler interface and all of its fields are unexported and therefore inaccessible to the encoding/json package.
What you need to do is to scan the contents of the rows into an intermediary object, one that can be marshaled, and then marshal that object into json.
So first, start by declaring a type that will represent this "intermediary" object, for example:
type Course struct {
Name string
Price int
}
Then, since you're selecting multiple records, you'll need to iterate over the rows object using its Next method and on each iteration scan the contents of the record into an instance of the Course type.
var courses []*Course // declare a slice of courses that will hold all of the Course instances scanned from the rows object
for rows.Next() { // this stops when there are no more rows
c := new(Course) // initialize a new instance
err := rows.Scan(&c.Name, &c.Price) // scan contents of the current row into the instance
if err != nil {
return err
}
courses = append(courses, c) // add each instance to the slice
}
if err := rows.Err(); err != nil { // make sure that there was no issue during the process
return err
}
And finally you can turn the courses slice into json by passing it to the encoder.
if err := json.NewEncoder(w).Encode(courses); err != nil {
log.Println(err)
}
If you apply the above suggestions to your handler you should start seeing the result that you expect, or something similar to it... However your handler has a couple other issues that you need to resolve if you don't want your application to crash.
First:
db, err := sql.Open("mysql", "root:@/academy")
It is unnecessary to open a connection every time the handler is executed and so it would be much better if you move the open-db-connection code outside of the handler and just make the db variable accessible to the handler. If, however, you want to keep openning the connection every time you need to make sure to also close it every time, otherwise you'll run out of available connections.
Second:
rows, err := db.Query("SELECT course_name,price FROM course;")
The returned rows object needs to be closed for the same reasons that you need to close the db handle if you keep opening it every time, i.e. you'll run out of available connections and your app will crash.
So a more complete version of the code that should work correctly would look like this:
var db *sql.DB // declare a global variable that will be used by all handlers
func init() {
var err error
db, err = sql.Open("mysql", "root:@/academy") // initialize the global connection
if err != nil {
panic(err)
}
}
type Course struct {
Name string
Price int
}
func GetCoursesEndpoint(w http.ResponseWriter, req *http.Request) {
rows, err := db.Query("SELECT course_name,price FROM course;")
if err != nil {
fmt.Println(err)
return
}
defer rows.Close() // make sure rows is closed when the handler exits
var courses []*Course
for rows.Next() {
c := new(Course)
err := rows.Scan(&c.Name, &c.Price)
if err != nil {
fmt.Println(err)
return
}
courses = append(courses, c)
}
if err := rows.Err(); err != nil {
fmt.Println(err)
return
}
if err := json.NewEncoder(w).Encode(courses); err != nil {
fmt.Println(err)
}
return
}
