Go nulls and SQL

Dealing with SQL NULL values in Go

Go has "zero" values. These are the values that variables of primitive types get when they are created and we don't explicitly give them a initial value.

var i int
fmt.Printf("%#v\n", i) // => 0

var s string
fmt.Printf("%#v\n", s) // => ""

Numbers start off as 0, booleans as false, and strings as "".

Go also has nil, and "nilable" types to which a nil can be assigned. The canonical example is a pointer. If we create a pointer and don't explicitly give it an initial value, then it starts off as nil.

var p *int
fmt.Printf("%#v\n", p) // => nil

You can look but you cannot touch, these nilables. If you try doing something that uses a nilable type that currently has a nil value, Go will panic.

fmt.Printf("%#v\n", *p) // => 💥

Apart from pointers, uninitialized variables for slices, maps, interfaces, functions and channels also start off as nil.

So, for example, the predeclared type error is an interface, and conventionally we use nil to represent no error.

Structs are recursively auto initialized.

type R struct {
    i int
    s string
    p *int
}

var r R
fmt.Printf("%#v\n", r) // => {i:0, s:"", p:(*int)(nil)}

Elementary, Watson, you say (if you've managed to reach this point). Ah but the case is not out in the open yet.

The issue that happens is – zeroables and nilables don't mix.

This is something which comes up when we try to interact with, for example, SQL or JSON. In SQL, a string may both be empty ("", just like a zeroable in Go) and NULL (like a Go nil). But a Go string can only be zeroable, not nil. So how do we assign a string we get from SQL to a Go variable?

Pointers. We can point to a string. The pointer can be nil. And the string it points to can be empty.

Nice and easy. However, not everyone likes this approach [2].

Pointers are easy first, but then you realize that you have to put nilness checks everywhere. But that time those easy to use pointers are everywhere, and appear only at run time, only in prod, at night...

So the Go stdlib provides so called "Null wrappers" specifically for use with SQL. For our string example, we can use a NullString.

var ns sql.NullString
fmt.Printf("%#v\n", ns) // => {String:"", Valid:false}

We can also deal with this problem at the SQL level, by using COALESCE (which returns the first non-NULL value from the list it is given).

db.QueryRow("SELECT COALESCE(NULL, '')")

Finally, as usual, sometimes the best way to solve a problem is to avoid the problem 🏖; by declaring our SQL columns as NOT NULL when possible.

---

If you'd like to hear more about our experience with Go, follow us on Twitter.

Or if you'd hang out with a bunch of engineers building an e2ee photo storage service, come say hello on Discord.

---

References:

1. https://go.dev/ref/spec
2. https://www.reddit.com/r/golang/comments/eqaam0/dealing_with_database_null_values_pointers_vs/
3. https://pkg.go.dev/database/sql#NullString
4. https://iamdual.com/en/posts/handle-sql-null-golang/

Comments

[geraldew]

Your description of COALESCE is not quite correct as it can be mis-read as inherently ensuring a non-NULL value. However if all items in the list are NULL then it returns NULL. Of course, putting a literal constant at the end does ensure a non-NULL.

Personally in data work I rarely suggest the best solution is to declare columns as NOT NULL as it tends to mask issues rather than resolve them.