One simple way to handle NULL database value in Golang

Photo by Kelly Sikkema on Unsplash

If you have worked on Go for a while or if you just start to learn Go for the very first time, the application you build usually use database to store data or at least use it to fetch some data. In that case, you may use the standard / built-in database/sql package to do sql queries.

While testing and using the app, you may come across a problem that states NULL value in the database cannot be scanned / read into string or int64 or basically any non-pointer types in Go.

panic: sql: Scan error on column index 1, name “mycolumn”: converting NULL to string is unsupported

How to solve that problem

I have a struct which represents a table in DB

type MyTable struct {
    ColumnA NullString `json:"column_a,omitempty"`
    ColumnB string     `json:"column_b,omitempty"`
    ColumnC int64      `json:"column_c,omitempty"`
}
// NullString type will be explained later

When doing some digging and searching across the internet for some best practices, I found mainly people suggesting two ways to handle those nulls.

1. Using sql.NullString, sql.NullInt64, etc
2. Using *string , *int64 , etc

Both of the solution is actually can solve my problem. I tried to implement that NullString type to be like this:

type NullString struct {
	sql.NullString
}

func (s NullString) MarshalJSON() ([]byte, error) {
	if !s.Valid {
		return []byte("null"), nil
	}
	return json.Marshal(s.String)
}

func (s *NullString) UnmarshalJSON(data []byte) error {
	if string(data) == "null" {
		s.String, s.Valid = "", false
		return nil
	}
	s.String, s.Valid = string(data), true
	return nil
}

this solution is actually pretty cool, because by nesting the sql.NullString we don’t have to implement our own sql.Scanner and sql.Valuer interface, what’s left is for us to implement our own json Marshaller interface)

But then another problem pops up, while testing the code, it seems that even though I’ve returned null in MarshalJSON function, and provide the omitempty tag on the field. That attributes is still present on the JSON.

After some digging and reading encoding/json package documentation, I realised that omitempty option cannot be used in this case.

From the encoding/json package documentation:

The “omitempty” option specifies that the field should be omitted from the encoding if the field has an empty value, defined as false, 0, a nil pointer, a nil interface value, and any empty array, slice, map, or string.

How I solve that problem

My application actually doesn’t care if the particular column’s value is null or not. I just want to know whether there’s string inside / empty (could be null / empty string it doesn’t matter to the application).

After some thinking, I just realised that if I don’t need to know the column’s value is null or not, why don’t I just use a usual string instead?

So here’s how I implemented it:

package main
import (
    "database/sql/driver"
    "errors"
)
type NullString string
func (s *NullString) Scan(value interface{}) error {
    if value == nil {
        *s = ""
        return nil
    }
    strVal, ok := value.(string)
    if !ok {
        return errors.New("Column is not a string")
    }
    *s = NullString(strVal)
    return nil
}
func (s NullString) Value() (driver.Value, error) {
    if len(s) == 0 { // if nil or empty string
        return nil, nil
    }
    return string(s), nil
}

NullString is declared as an alias of string. We also implement the sql.Scanner and sql.Valuer interface so that when null is returned from database, we regard it as an empty string in our code, and when empty string / null want to be saved on the db, we could save it as null in the database.

This solution could also implemented to other non-pointer types (integer, float, boolean). But it cannot be implemented if the application requires us to differentiate between null and zero values (empty string, 0, false, etc). I personally believe that most of the applications could be built without having those differentiation.

Please leave out comments or points out if you have opinions about better approach or if I missed edge cases where the solution will fail. It will be very helpful so that I and people reading this post could learn from one another.

Hope you enjoy this post.