Go nulls and SQL
Tags: go, sql, beginners, programming
Published: 2022-05-22 11:32:27

_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](https://twitter.com/enteio).

Or if you'd hang out with a bunch of engineers building an e2ee photo storage service, come say hello on [Discord](https://ente.io/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/