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

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
 }

Answer 2

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