How do I handle nil return values from database?

Question

I am writing a basic program to read values from database table and print in table. The table was populated by an ancient program. Some of the fields in the row are optional and when I try to read them as string, I get the following error:

panic: sql: Scan error on column index 2: unsupported driver -> Scan pair: <nil> -> *string

After I read other questions for similar issues, I came up with following code to handle the nil values. The method works fine in practice. I get the values in plain text and empty string instead of the nil values.

However, I have two concerns:

1. This does not look efficient. I need to handle 25+ fields like this and that would mean I read each of them as bytes and convert to string. Too many function calls and conversions. Two structs to handle the data and so on...
2. The code looks ugly. It is already looking convoluted with 2 fields and becomes unreadable as I go to 25+

Am I doing it wrong? Is there a better/cleaner/efficient/idiomatic golang way to read values from database?

I find it hard to believe that a modern language like Go would not handle the database returns gracefully.

Thanks in advance!

Code snippet:

// DB read format
type udInfoBytes struct {
  id                     []byte
  state                  []byte
}

// output format
type udInfo struct {
  id                     string
  state                  string
}

func CToGoString(c []byte) string {
  n := -1
  for i, b := range c {
    if b == 0 {
      break
    }
    n = i
  }
  return string(c[:n+1])
}

func dbBytesToString(in udInfoBytes) udInfo {

  var out udInfo
  var s string
  var t int

  out.id = CToGoString(in.id)
  out.state = stateName(in.state)
  return out
}

func GetInfo(ud string) udInfo {

  db := getFileHandle()
  q := fmt.Sprintf("SELECT id,state FROM Mytable WHERE id='%s' ", ud)

  rows, err := db.Query(q)
  if err != nil {
    log.Fatal(err)
  }
  defer rows.Close()
  ret := udInfo{}
  r := udInfoBytes{}
  for rows.Next() {
    err := rows.Scan(&r.id, &r.state)

    if err != nil {
      log.Println(err)
    }
    break
  }
  err = rows.Err()
  if err != nil {
    log.Fatal(err)
  }

  ret = dbBytesToString(r)
  defer db.Close()
  return ret
}

edit:

I want to have something like the following where I do no have to worry about handling NULL and automatically read them as empty string.

// output format
type udInfo struct {
  id                     string
  state                  string
}

func GetInfo(ud string) udInfo {

  db := getFileHandle()
  q := fmt.Sprintf("SELECT id,state FROM Mytable WHERE id='%s' ", ud)

  rows, err := db.Query(q)
  if err != nil {
    log.Fatal(err)
  }
  defer rows.Close()
  r := udInfo{}

  for rows.Next() {
    err := rows.Scan(&r.id, &r.state)

    if err != nil {
      log.Println(err)
    }
    break
  }
  err = rows.Err()
  if err != nil {
    log.Fatal(err)
  }

  defer db.Close()
  return r
}

Answer 1

Two ways to handle those nulls:

Using sql.NullString

if value.Valid { 
   return value.String
} 

Using *string

if value != nil {
   return *value
}

https://medium.com/@raymondhartoyo/one-simple-way-to-handle-null-database-value-in-golang-86437ec75089

Answer 2

An alternative solution would be to handle this in the SQL statement itself by using the COALESCE function (though not all DB's may support this).

For example you could instead use:

q := fmt.Sprintf("SELECT id,COALESCE(state, '') as state FROM Mytable WHERE id='%s' ", ud)

which would effectively give 'state' a default value of an empty string in the event that it was stored as a NULL in the db.

Answer 3

I've started to use the MyMySql driver as it uses a nicer interface to that of the std library.

https://github.com/ziutek/mymysql

I've then wrapped the querying of the database into simple to use functions. This is one such function:

import "github.com/ziutek/mymysql/mysql"
import _ "github.com/ziutek/mymysql/native"

// Execute a prepared statement expecting multiple results.
func Query(sql string, params ...interface{}) (rows []mysql.Row, err error) {
    statement, err := db.Prepare(sql)
    if err != nil {
        return
    }
    result, err := statement.Run(params...)
    if err != nil {
        return
    }
    rows, err = result.GetRows()
    return
}

To use this is as simple as this snippet:

rows, err := Query("SELECT * FROM table WHERE column = ?", param)

for _, row := range rows {
    column1 = row.Str(0)
    column2 = row.Int(1)
    column3 = row.Bool(2)
    column4 = row.Date(3)
    // etc...
}

Notice the nice row methods for coercing to a particular value. Nulls are handled by the library and the rules are documented here:

https://github.com/ziutek/mymysql/blob/master/mysql/row.go

Answer 4

go's database/sql package handle pointer of the type.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
    "log"
)

func main() {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    _, err = db.Exec("create table foo(id integer primary key, value text)")
    if err != nil {
        log.Fatal(err)
    }
    _, err = db.Exec("insert into foo(value) values(null)")
    if err != nil {
        log.Fatal(err)
    }
    _, err = db.Exec("insert into foo(value) values('bar')")
    if err != nil {
        log.Fatal(err)
    }
    rows, err := db.Query("select id, value from foo")
    if err != nil {
        log.Fatal(err)
    }
    for rows.Next() {
        var id int
        var value *string
        err = rows.Scan(&id, &value)
        if err != nil {
            log.Fatal(err)
        }
        if value != nil {
            fmt.Println(id, *value)
        } else {
            fmt.Println(id, value)
        }
    }
}

You should get like below:

1 <nil>
2 bar

Answer 5

There are separate types to handle null values coming from the database such as sql.NullBool, sql.NullFloat64, etc.

For example:

 var s sql.NullString
 err := db.QueryRow("SELECT name FROM foo WHERE id=?", id).Scan(&s)
 ...
 if s.Valid {
    // use s.String
 } else {
    // NULL value
 }