Ways to handle SQL NULL values in Go

If you have dealt with SQL in Golang, you know that handling NULL values didn’t come easily with it. The database/sql interface already support NULL values by usage of Go’s nil, but the design of the language doesn’t permit to assign nil to a string or int variable. In this post, I will show you the ways.

1) Pointers

In Go, if the primitive variable isn’t a pointer, you can’t assign nil. Pointers are usually using for passing the shared data to another function to make memory usage more efficient, but we have also another good reason to use this; handling NULL values.

type User struct {
	Id   int64
	Name *string
	Age  *int32
}

err := db.QueryRow("SELECT NULL, 2").Scan(&u.Name, &u.Age)

In this case, we can set Name field as nil of its pointer. The sql.Scan() handling NULL values by set the given pointer arguments as nil. json.Marshall() operations are also supported to set nil pointers as null in JSON format. But every usage of the variables or the struct fields that are pointers, you have to check are these nil or not. If u.Age is a nil, then you do dereference it, the program will be crashed.

2) sql.Null*

There are a few structs defined in the sql package. It’s allow to use variables in different primitive types that can be nullable. These structs are also implements a scanner and a value interface to allow to use in sql operations. But it doesn’t handle JSON marshalling by default, so you have to implement your own. Documentation

type User struct {
	Id   int64
	Name sql.NullString
	Age  sql.NullInt32
}

err := db.QueryRow("SELECT 'Ekin', NULL").Scan(&u.Name, &u.Age)

For example, we looking at sql.NullString struct that has two fields that are String and Valid. For the non-NULL values, u.Name.Valid field will equal to true and u.Name.String equal to "Ekin". Otherwise, u.Name.Valid will be false and u.Name.String will be "". If your field type is NullString, first you have to check is the string valid or not. If is valid, we can obtain value by the u.Name.String field.

3) github.com/guregu/null

This is the third way, an external package from Github for the handle NULL values and very similar to second way. But it’s more easier to create nullable types. It’s also support JSON marshall operations. Documentation

4) Use COALESCE function

COALESCE is the SQL function in the most of relational database systems (SQLite, PostgreSQL, MySQL, SQL Server, and Oracle). This function does try to return first non-NULL value according to its given arguments. Documentation

type User struct {
	Id   int64
	Name string
	Age  int32
}

err := db.QueryRow("SELECT COALESCE(NULL, '')").Scan(&u.Name)

5) Don’t use NULL values

Yes, this isn’t a programmatical solution but we can avoid NULL returns from queries by not using NULL values. Usually NULL values come from the default value of the column, or we set values as NULL. So, if we not use NULL values, we don’t need to handle it. 😎

But it doesn’t means that we shouldn’t use NULL. It’s sometimes can be necessary sometimes not. I suggest you decide it by yourself.