Go API Sql.Query()

Question

I have an api in Go, when I try to do db.query("SELECT * FROM users;"), i never get any data. The sql connection is correct because when I try to select from a table that is not existing i get error, also when I ping i get no error.

My code:

package database

import (
    "database/sql"
    "fmt"
    "log"
    "os"

    _ "github.com/go-sql-driver/mysql"
)

var (
    api_db *sql.DB
)

func InitializeDatabase() error {
    db, err := sql.Open("mysql", dsn(os.Getenv("db_dbname")))
    api_db = db
    return err
}

func ExecuteCommand(command string) any {

    log.Printf("Executing %v\n", command)

    rows, err := api_db.Query(command)

    if err != nil {
        log.Printf("Error %v when executing DB\n", err.Error())
        return nil
    } else {
        return rows
    }
}

func dsn(dbName string) string {
    return fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8", os.Getenv("db_username"), os.Getenv("db_password"), os.Getenv("db_hostname"), dbName)
}

and in controller I just do this:

package controllers

import (
    "api/src/database"
    "encoding/json"
    "net/http"
)

func MainController(w http.ResponseWriter, r *http.Request) {
    res := database.ExecuteCommand("SELECT * FROM users;")
    w.Header().Set("Content-Type", "application/json")
    w.WriteHeader(http.StatusAccepted)
    json.NewEncoder(w).Encode(res)
}

I have tried numerus solutions I tried online but every time is the same. I have tried everything even selecting from another table. Every time I get no data.

I have data in the table. When I run the same query in the sql workbench I get data. Also I created another table with 'whatever' as name and did "SELECT * FROM whatever;" and was same. I created a simple node.js script and I can get the data for both tables.

Answer 1

func ExecuteCommand(command string) any

ExecuteCommand returns an any, an interface{}. Its underlying type is from the return value of sql.DB.Query: an sql.Rows. Or, maybe it's nil because the query failed.

You'll have to cast it back into an *sql.Rows with a type assertion. You can see that's not very ergonomic:

    res := database.ExecuteCommand("SELECT * FROM users;")
    rows := res.(*sql.Rows); //panics if `res` is not in fact an `*sql.Rows`

But what if the query failed? In that case the code will panic at runtime and depending on your server software may actually crash the server.

So we actually have to check that possibility first:

    res := database.ExecuteCommand("SELECT * FROM users;")
    if rows == nil { 
         // return some error
    }
      rows := res.(*sql.Rows); //still panics but at least not on nil
      ...

Morals of the story: don't return any, return a specific type. and don't use nil as an error sentinel, return a real error.

Define functions to return the actual type you return. Go has a very strict type system; if you continue to learn it you're going to have to deal with types on a constant basis. You don't want to be type casting every return value!

Expose errors to the function caller. Go errors are ubiquitous in the language and it is very explicit about errors, which makes error handling straight forward. They contain all sorts of useful information as well. So don't hide them behind some sentinel value; expose them as what they are.

    json.NewEncoder(w).Encode(res)

res remember is an interface{}, because that's what comes from database.ExecuteCommand. *sql.Rows doesn't implement any of the things encoding/json knows how to marshal, and so it tries to render the struct. However, sql.Rows has no exported fields. So the struct renders as an empty sql map {}.

You need to process the results of the query before you can include them in the http response. From sql.Rows again:

    defer rows.Close()

    names := make([]string, 0)
    for rows.Next() {
        var name string
        if err := rows.Scan(&name); err != nil {
            log.Fatal(err)
        }
        names = append(names, name)
    }
    // Check for errors from iterating over rows.
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }

That example queries for one column. You don't specify which columns you want in database.ExecuteCommand("SELECT * FROM users;"). So now you have a choice:

• continue to use SELECT * and hope the order of the columns you write your application to read from the database never changes;
• continue to use SELECT * and introspect the column names and types using *sql.Rows's Columns and ColumnTypes to figure out which columns are in which position;
• just define the columns you actually want, in the order you actually want.

Of course, the last option is the only serious solution.

database.ExecuteCommand("SELECT name, email FROM users;")

Now we know exactly which columns we'll get, in which order. Next question is, where to put their values. Start modeling this data with structs right away. Don't do what I did, applying habits from years of dynamic languages and trying to put everything in map[string]any. Lean into the struct:

type user struct {
   Name, Email string
}

note the uppercase. This is so that they're visible by a different package, encoding/json.

now we have variables to populate and we know which column is which.

   users:= make([]user, 0)
   for rows.Next() {
        var u user
        if err := rows.Scan(&u.Name, &u.Email); err != nil {
            log.Fatal(err)
        }
        users = append(users, user)
    }

finally we can encode the result as json and write it back to the client:

    json.NewEncoder(w).Encode(users)

encoding/json has pretty sensible default handling of a struct You'll get something like

[ { "Name": "sam", "Email": "[email protected]" ] }

So to summarize

• don't return any, return specific type or interface
• return errors, don't squelch them and return a sentinel
• you need to iterate over the results of sql.Query, Close() the *sql.Rows when you're done, and check for sql.Err().
• Don't select * it makes your application needlessly brittle at runtime
• Read each row into a struct or other appropriate data type to hold the data from the row within your program;
• json encode the final result after closing the result set.

Reading the results requires interaction between the sql server and the client; you're literally asking for the results of the query from the server. so make sure to do this quickly and then close the result set so it doesn't stay open on the server end.