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:

1use test;
2CREATE TABLE Article(
3  `id` int NOT NULL AUTO_INCREMENT,
4  `title` varchar(100) NOT NULL,
5  `pubdate` datetime DEFAULT NULL,
6  `body` text,
7  `userid` int DEFAULT NULL,
8  PRIMARY KEY(`id`)
9);
10SELECT * FROM Article; (Empty Set)

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

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

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

1type Article struct {
2    Id      int       `json:"id"`
3    Title   string    `json:"title"`
4    PubDate time.Time `json:"pub_date"`
5    Body    string    `json:"body"`
6    User    int       `json:"user"`
7}

Customizing Null Types for JSON Marshalling

The complete go program would be:

1package main
2import (
3    "database/sql"
4    "fmt"
5    "time"
6    _ "github.com/go-sql-driver/mysql"
7)
8type Article struct {
9    Id      int       `json:"id"`
10    Title   string    `json:"title"`
11    PubDate time.Time `json:"pub_date"`
12    Body    string    `json:"body"`
13    User    int       `json:"user"`
14}
15func main() {
16    db, err := sql.Open("mysql", "user:pwd@/test?charset=utf8")
17    checkErr(err)
18    // insert
19    rows, err := db.Query("SELECT * FROM Article")
20    checkErr(err)
21    for rows.Next() {
22        var a Article
23        err = rows.Scan(&a.Id, &a.Title, &a.PubDate, &a.Body, &a.User)
24        checkErr(err)
25        fmt.Printf("%#v", a)
26    }
27    db.Close()
28}
29func checkErr(err error) {
30    if err != nil {
31        panic(err)
32    }
33}

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.

1package main
2import (
3    "database/sql"
4    "fmt"
5    "github.com/go-sql-driver/mysql"
6)
7type Article struct {
8    Id      int            `json:"id"`
9    Title   string         `json:"title"`
10    PubDate mysql.NullTime `json:"pub_date"`
11    Body    sql.NullString `json:"body"`
12    User    sql.NullInt64  `json:"user"`
13}

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:

1if a.PubDate.Valid:
2    // handle a.PubDate.Time
3else:
4    // 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:

1// NullInt64 is an alias for sql.NullInt64 data type
2type NullInt64 sql.NullInt64
3// NullBool is an alias for sql.NullBool data type
4type NullBool sql.NullBool
5// NullFloat64 is an alias for sql.NullFloat64 data type
6type NullFloat64 sql.NullFloat64
7// NullString is an alias for sql.NullString data type
8type NullString sql.NullString
9// NullTime is an alias for mysql.NullTime data type
10type 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.

1// Scan implements the Scanner interface for NullInt64
2func (ni *NullInt64) Scan(value interface{}) error {
3    var i sql.NullInt64
4    if err := i.Scan(value); err != nil {
5        return err
6    }
7    // if nil the make Valid false
8    if reflect.TypeOf(value) == nil {
9        *ni = NullInt64{i.Int64, false}
10    } else {
11        *ni = NullInt64{i.Int64, true}
12    }
13    return nil
14}

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:

1// MarshalJSON for NullInt64
2func (ni *NullInt64) MarshalJSON() ([]byte, error) {
3    if !ni.Valid {
4        return []byte("null"), nil
5    }
6    return json.Marshal(ni.Int64)
7}

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:

So, that’s how I solved my problem. Hope sharing this would help!

UPDATE

As Alexandre Bodin suggested in comments that we can extend sql.Nullxxxtype into our struct so that we can totally avoid the hassle of re-implement Scan method. It was good suggestion and I would totally recommend it! I also replaced this style of code in my application!

So, now you can do something like this.

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

You should also follow this fashion unless you want to do some customization inside Scan and Value method but I don’t see any use cases. And you know what, it reduces one more call of using reflect as I had in my initial version of code in customized Scan method.

FYI, Scan method from database/sql package, uses convertAssign method, which also uses reflect internally.

Anyway, I’ve made another gist to replicate this style in my program, you can find it here. So the updated code looks like now:

1package main
2
3import (
4	"database/sql"
5	"encoding/json"
6	"fmt"
7	"log"
8	"time"
9
10	"github.com/go-sql-driver/mysql"
11)
12
13// Article struct
14type Article struct {
15	ID      int        `json:"id"`
16	Title   string     `json:"title"`
17	PubDate NullTime   `json:"pub_date"`
18	Body    NullString `json:"body"`
19	User    NullInt64  `json:"user"`
20}
21
22// NullInt64 is an alias for sql.NullInt64 data type
23type NullInt64 struct {
24	sql.NullInt64
25}
26
27// MarshalJSON for NullInt64
28func (ni *NullInt64) MarshalJSON() ([]byte, error) {
29	if !ni.Valid {
30		return []byte("null"), nil
31	}
32	return json.Marshal(ni.Int64)
33}
34
35// UnmarshalJSON for NullInt64
36// func (ni *NullInt64) UnmarshalJSON(b []byte) error {
37//  err := json.Unmarshal(b, &ni.Int64)
38//  ni.Valid = (err == nil)
39//  return err
40// }
41
42// NullBool is an alias for sql.NullBool data type
43type NullBool struct {
44	sql.NullBool
45}
46
47// MarshalJSON for NullBool
48func (nb *NullBool) MarshalJSON() ([]byte, error) {
49	if !nb.Valid {
50		return []byte("null"), nil
51	}
52	return json.Marshal(nb.Bool)
53}
54
55// UnmarshalJSON for NullBool
56// func (nb *NullBool) UnmarshalJSON(b []byte) error {
57//  err := json.Unmarshal(b, &nb.Bool)
58//  nb.Valid = (err == nil)
59//  return err
60// }
61
62// NullFloat64 is an alias for sql.NullFloat64 data type
63type NullFloat64 struct {
64	sql.NullFloat64
65}
66
67// MarshalJSON for NullFloat64
68func (nf *NullFloat64) MarshalJSON() ([]byte, error) {
69	if !nf.Valid {
70		return []byte("null"), nil
71	}
72	return json.Marshal(nf.Float64)
73}
74
75// UnmarshalJSON for NullFloat64
76// func (nf *NullFloat64) UnmarshalJSON(b []byte) error {
77//  err := json.Unmarshal(b, &nf.Float64)
78//  nf.Valid = (err == nil)
79//  return err
80// }
81
82// NullString is an alias for sql.NullString data type
83type NullString struct {
84	sql.NullString
85}
86
87// MarshalJSON for NullString
88func (ns *NullString) MarshalJSON() ([]byte, error) {
89	if !ns.Valid {
90		return []byte("null"), nil
91	}
92	return json.Marshal(ns.String)
93}
94
95// UnmarshalJSON for NullString
96// func (ns *NullString) UnmarshalJSON(b []byte) error {
97//  err := json.Unmarshal(b, &ns.String)
98//  ns.Valid = (err == nil)
99//  return err
100// }
101
102// NullTime is an alias for mysql.NullTime data type
103type NullTime struct {
104	mysql.NullTime
105}
106
107// MarshalJSON for NullTime
108func (nt *NullTime) MarshalJSON() ([]byte, error) {
109	if !nt.Valid {
110		return []byte("null"), nil
111	}
112	val := fmt.Sprintf("\"%s\"", nt.Time.Format(time.RFC3339))
113	return []byte(val), nil
114}
115
116// UnmarshalJSON for NullTime
117// func (nt *NullTime) UnmarshalJSON(b []byte) error {
118//  err := json.Unmarshal(b, &nt.Time)
119//  nt.Valid = (err == nil)
120//  return err
121// }
122
123// MAIN program starts here
124func main() {
125	log.Println("connectiong to database...")
126	db, err := sql.Open("mysql", "user:pass@tcp(127.0.0.1:3306)/test?charset=utf8")
127	if err != nil {
128		log.Fatal(err)
129	}
130	defer db.Close()
131
132	// read articles
133	rows, err := db.Query("SELECT * FROM Article")
134	if err != nil {
135		log.Fatal(err)
136	}
137	defer rows.Close()
138
139	// loop over articles
140	for rows.Next() {
141		var a Article
142		if err = rows.Scan(&a.ID, &a.Title, &a.PubDate, &a.Body, &a.User); err != nil {
143			log.Fatal(err)
144		}
145
146		log.Printf("article instance := %#v\n", a)
147		articleJSON, err := json.Marshal(&a)
148		if err != nil {
149			log.Fatal(err)
150		} else {
151			log.Printf("json marshal := %s\n\n", articleJSON)
152		}
153	}
154
155	err = rows.Err()
156	if err != nil {
157		log.Fatal(err)
158	}
159}

You also might want to have a look at this library which takes care of nullable values in go as suggested by Stephen Wood and Steve Heyns! I wasn’t aware of this library when I solved the problem but anyways this is another option in which you might be interested!

You see how it is helpful when you crack the problems and people make more correction in your solution. It’s great that at initial stage we solve the problems and make it functional and sharing it would make more perfect and more correct.

I hope you enjoyed reading the article! Please check out my new post about the experience on working with MySQL JSON data type with prepared statements and using it in Go.