Go sql package provides sql.Null* structs for us to map with nullable fields in database. We’re gonna find out why we need to be careful with these.

One issue we’re dealing with everyday is null (or nil) handling. Null values are everywhere from web requests/response, service classes and down to database. Null values can represent lots of things, the absence of value or we just don’t care about it (relevant in function/method/API parameters/arguments).

We’re going to talk about NULL value in database, which Go sql package provides some convenience structs for. Any field/column in database can be NULL either by default or set on purpose.

We can set a certain field/column in database not to be NULL-able by setting a constraint NOT NULL, something like this:

CREATE TABLE table_name(
   ...
   column_name data_type NOT NULL,
   ...
);

And in order to avoid NULL value in database, we can do database normalization. Let say we have a table of employees like this:

Some employees don’t have desks, so we put NULL values there. We can normalize the database by moving desk_number to a new table:

Here’s the employees table:

And here’s the desk_assignments table:

With this database design in place, we avoid NULL values in employees table. This design will easily accommodate employees with more than 1 desk assignments as well.

Or better yet, we can just create a new table desks so that we can do many to many relationship with employees table (if we need to).

But the point of this article is not about database normalization, sometimes we just cannot change the existing design, especially in legacy projects, so sometimes we have to deal with NULL database values in our codebase.

Handling Database Null in Golang

In Golang, there’s built in sql package that provides us with some conveniences of handling database NULL values.

The usage will be something like this, let’s use the unnormalized employees table above:

type Employee struct {
	ID         int64          `db:"id"`
	Name       string         `db:"name"`
	DeskNumber sql.NullString `db:"desk_number"`
}

Querying sample:

rows, err := db.Queryx("SELECT id, name, desk_number FROM employees LIMIT 10")

employees := make([]Employee, 0)
for rows.Next() {
    var e Employee
    err = rows.StructScan(&e)
    if err != nil {
      return nil, err
    }

    employees = append(employees, e)
}

Queryx and StructScan above are from sqlx library: https://jmoiron.github.io/sqlx/.

As we can see desk_number can be NULL and we use sql.NullString to handle it. Here’s how sql.NullString looks like:

type NullString struct {
    String string
    Valid  bool
}

If value is not NULL, Valid will be true and String will contain the value we want, otherwise Valid will be false and String will contain empty string. So we can use it like this: employee.DeskNumber.String and to check if null value: if employee.DeskNumber.Valid.

This is fine, but if we’re not careful, we’ll be exposing the sql.NullString as return type of the method:

type EmployeeRepo interface {
  // ...
  GetDeskNumberByEmployeeID(ctx context.Context, int64 employeeID) (sql.NullString, error)
  UpdateDeskNumberByEmployeeID(ctx context.Context, int64 employeeID, deskNumber sql.NullString) (error)
  CreateNewEmployee(ctx context.Context, employee Employee) (error)
  // ...
}

That’s not what we want, by saying sql.NullString in Employee struct above, we intent the Employee to be used for SQL only. Once we move the implementation detail somewhere else (example: ElasticSearch or 3rd party API), the code will be misleading. The caller of the interface will be expecting to interact with sql.NullString too, which is misleading if it turns out that it is no longer SQL we’re dealing with. A leaky abstraction.

https://en.wikipedia.org/wiki/Leaky_abstraction

In order to solve this, we can do these approaches:

1. If we still want to use sql.Null* structs provided by sql package, make sure that it is used only in implementing code, inside the methods only, and expose *string as return value instead, because pointer can be nil.
2. Or, change the struct to be like this:

type Employee struct {
	ID         int64          `db:"id"`
	Name       string         `db:"name"`
	DeskNumber *string        `db:"desk_number"`
}

So that our method signatures look like this:

  GetDeskNumberByEmployeeID(ctx context.Context, int64 employeeID) (*string, error)
  UpdateDeskNumberByEmployeeID(ctx context.Context, int64 employeeID, deskNumber *string) (error)

With this approach we don’t need to worry about exposing the implementation detail to the caller, plus we can reuse the Employee struct for many things else, like JSON marshalling or unmarshalling.

type Employee struct {
	ID         int64   `db:"id" json:"id"`
	Name       string  `db:"name" json:"name"`
	DeskNumber *string `db:"desk_number" json:"desk_number,omitempty"`
}

3. If we don’t want to deal with this pointer thing and just want to return empty string if NULL value is found. At database level we can change the query to use COALESCE instead, so that our DeskNumber will simply use string.

https://www.postgresql.org/docs/14/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL https://www.mysqltutorial.org/mysql-coalesce/