Using prepared statements & pointers in Golang

In MySQL, you can use a question mark (?) in a prepared statement to stand in for a value.

func GetUserId(name string) (int, error) {
  stmt, err := dbconn.Db.Prepare(`SELECT id FROM user
    WHERE name="?";`) // (1) - BAD
  stmt, err := dbconn.Db.Prepare(`SELECT id FROM user
    WHERE name=?;`) // (2) – GOOD
  if err != nil {
    log.Fatal(err)
  }
  defer stmt.Close()

  var userId int
  err = stmt.QueryRow(name).Scan(&userId) // (3)
  if err != nil {
    if err != sql.ErrNoRows {
      log.Fatal(err)
    }
    return 0, err
  }
  return userId, nil
}

In the Go code above, dbconn is a connection to a MySQL server. Line 1 defines a prepared statement. And Line 3 queries the table for a row where the name column matches the value of variable name. I assumed the ? in this query would be interpolated with an actual string. I added quotation marks since they are needed around strings in MySQL CLI.

This fails to return anything. Removing the quotation marks solved the problem. The correct code is on Line 2.

I also find Line 3 quite interesting. Here, first, stmt.QueryRow() returns a pointer to a sql.Row object. Its Scan() method then does two fascinating things: a) it sets a “dest” to the output value and b) returns an error if there's an error or nil if there's no error.

Thingy a is quite interesting. In this case, Scan() method will find the memory address of variable userId and write the output value (the user's ID) there.

It feels as if getting the value from a DB is a side effect.

The same goes when you decode a JSON string. In this example, on Line 4, Unmarshal() method unpacks the JSON string and writes it to &animals, the address of animals variable. And this is a side effect. The return value is again, either an error or nil — if there is an error, you simply can't ignore it.

var jsonBlob = []byte(`[
    {"Name": "Platypus", "Order": "Monotremata"},
    {"Name": "Quoll",    "Order": "Dasyuromorphia"}
]`)
type Animal struct {
    Name  string
    Order string
}
var animals []Animal
// (4)
err := json.Unmarshal(jsonBlob, &animals)