How I handled possible null values from database rows in Golang?

Few weeks ago, I was working on one of the web services of my project, where back-end has been developed using Go and MySQL. For the web services, we had to show some data over web browser in w2ui grid layout. The data comes from a SQL query that joins multiple tables.

Identifying Null Values

We prepared a data struct for the web service, containing fields with built-in data types (int64, time.Time, string etc…). But we found that there were possibilities of null values (NULL) from the SQL query result and we had to deal with it.

Let’s start with an example and let me explain how I dealt with the problem. We’ll go with the basic information of an article. The rough structure with data type is as below:

Modifying the Article Struct

Article
- Id           = int
- Title        = string
- PubDate      = datetime
- Body         = text
- User         = int

Let’s create schema for it in MySQL:

use test;
CREATE TABLE Article(
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `pubdate` datetime DEFAULT NULL,
  `body` text,
  `userid` int DEFAULT NULL,
  PRIMARY KEY(`id`)
);
SELECT * FROM Article; (Empty Set)

Now, let’s insert our very first article with only title information:

INSERT INTO Article(`title`) VALUES("first article");

Let’s prepare some basic struct in go to get those values by scanning sql rows:

type Article struct {
    Id      int       `json:"id"`
    Title   string    `json:"title"`
    PubDate time.Time `json:"pub_date"`
    Body    string    `json:"body"`
    User    int       `json:"user"`
}

Customizing Null Types for JSON Marshalling

The complete go program would be:

package main
import (
    "database/sql"
    "fmt"
    "time"
    _ "github.com/go-sql-driver/mysql"
)
type Article struct {
    Id      int       `json:"id"`
    Title   string    `json:"title"`
    PubDate time.Time `json:"pub_date"`
    Body    string    `json:"body"`
    User    int       `json:"user"`
}
func main() {
    db, err := sql.Open("mysql", "user:pwd@/test?charset=utf8")
    checkErr(err)
    // insert
    rows, err := db.Query("SELECT * FROM Article")
    checkErr(err)
    for rows.Next() {
        var a Article
        err = rows.Scan(&a.Id, &a.Title, &a.PubDate, &a.Body, &a.User)
        checkErr(err)
        fmt.Printf("%#v", a)
    }
    db.Close()
}
func checkErr(err error) {
    if err != nil {
        panic(err)
    }
}

save it, compile and run it.
You will see something like this:

panic: sql: Scan error on column index 2: unsupported Scan, storing driver.Value type <nil> into type *time.Time

Why?
Because, we have defined PubDate in Article at index 2 with time.Time data type, and the error says that Scanner is unable to convert null values into time.Time (i.e, PubDate) data type.

That was surprising! (I was expecting zero-value of Date or NULL value of Date, but NULL is different story in Go). On searching the issue and I came to my notice that nil is a different data type in Go and because of Go’s static nature, you can’t assign nil values in other built-in data types (int, string, time.Time etc…).

Well, later I found that database/sql package does provide NullInt64, NullString, NullFloat64 etc., structs to handle null values. These structs are embedded with one additional field Valid which is boolean type, indicates whether field is NULL or not.

Look at the one of struct (NullInt64) implementation here.

Now let’s change Article struct to handle null values properly.

package main
import (
    "database/sql"
    "fmt"
    "github.com/go-sql-driver/mysql"
)
type Article struct {
    Id      int            `json:"id"`
    Title   string         `json:"title"`
    PubDate mysql.NullTime `json:"pub_date"`
    Body    sql.NullString `json:"body"`
    User    sql.NullInt64  `json:"user"`
}

Now Article struct field’s data type has been modified and also we’ve removed the import line for "time” package and changed
_ “github.com/go-sql-driver/mysql” to ”github.com/go-sql-driver/mysql”because now we’re now using mysql.NullTime for datetime fields that may have null value.

You may also find alternatives of NullTime implementation based on the driver you work with (for example, lib/pq has implementation for it here).

Also read: Automated MySQL 5.7 Community Server Installation on Amazon EC2 Instances with Amazon Linux AMI

Full Code and Testing

Build and run it!
You should see a result like this:

main.Article{Id:2, Title:"first article", PubDate:mysql.NullTime{Time:time.Time{sec:0, nsec:0, loc:(*time.Location)(nil)}, Valid:false}, Body:sql.NullString{String:"", Valid:false}, User:sql.NullInt64{Int64:0, Valid:false}}

So, we can do something like this:

if a.PubDate.Valid:
    // handle a.PubDate.Time
else:
    // handle nil

Hurrray!!

Also read: Dynamic Serializer Selection in Django Rest Framework: A Guide for Handling Varying API Data Needs

Conclusion

But hey, how can we show data properly with json marshalling that contains two possible values i.e. either null or field value?

For json marshalling, I added aliases in my package, as the compiler mentioned, you can’t extend existing types in another package i.e. you can’t write UnMarshalJSON or MarshalJSON on sql.NullString, sql.NullInt64, etc… directly in your package.

If you want to support json marshalling and unmarshalling to user defined data type, then you should implement MarshalJSON and UnMarshalJSON interface methods on that data type.

In my code, aliases look as below:

// NullInt64 is an alias for sql.NullInt64 data type
type NullInt64 sql.NullInt64
// NullBool is an alias for sql.NullBool data type
type NullBool sql.NullBool
// NullFloat64 is an alias for sql.NullFloat64 data type
type NullFloat64 sql.NullFloat64
// NullString is an alias for sql.NullString data type
type NullString sql.NullString
// NullTime is an alias for mysql.NullTime data type
type NullTime mysql.NullTime

As we have these kind of user defined data types, to read data from a sql database, Go provides a mechanism that is implement Scanner database/sql interface.

So, now we shall define Scan method for these user defined data types (aliases) to implement Scanner interface.

Below is the example for NullInt64.

// Scan implements the Scanner interface for NullInt64
func (ni *NullInt64) Scan(value interface{}) error {
    var i sql.NullInt64
    if err := i.Scan(value); err != nil {
        return err
    }
    // if nil the make Valid false
    if reflect.TypeOf(value) == nil {
        *ni = NullInt64{i.Int64, false}
    } else {
        *ni = NullInt64{i.Int64, true}
    }
    return nil
}

Inside Scan implementation, it scans the record and later checks for a null value from the database, then marks Valid flag to false. The same implementation style can be applied for other user defined data types (NULLFloat64, NullString, etc…) also.

Now, we are going to provide implementation for json marshalling, which is as below:

// MarshalJSON for NullInt64
func (ni *NullInt64) MarshalJSON() ([]byte, error) {
    if !ni.Valid {
        return []byte("null"), nil
    }
    return json.Marshal(ni.Int64)
}

Inside, MarshalJSON method implementation, it checks that NullInt64 typed variable’s Valid flag is true or false. If it’s True then it does json marshal over int64 data else return bytes of string “null” (which is one of the primitive values in JavaScript).

You’ll see a similar style of code in the gist.

Here is the full code of gist:

type NullInt64 struct {
    sql.NullInt64
}
// now you don't have to take care of Scan method.
// Just focus on marshal logic of JSON