Go: Working effectively with database nulls

This post covers how to marshall null values from a database into a Go struct type and how to save nulls back to the database. I’ll cover the standard library’s sql.NullString, NullInt64, NullFloat64, etc types — which I’ll refer to as sql.NullXYZ when indicating the collection of types and not a specific Null type — important methods on those types, telltale errors, and some helpers for working with values coming from form posts.

This code uses SQL and the standard library.

Takeaways

• In structs, use sql.NullString, sql.NullInt64, and its ilk instead of string and int for fields that persist to a database and are nullable
• For display, use type.Field.Value instead of type.Field.String/type.Field.Int64/etc
• Use helper functions to populate sql.NullXYZ types from strings

Telltale error

sql: Scan error on column index 1: unsupported driver -> Scan pair: <nil> -> *string

Given a type Member and a function for fetching data:

type Member struct {
	Username                string
	SomethingOptional       string
	SomethingOptionalInt    int64
}

func (s *MemberService) GetMembers() []Member {
	memberRows, err := s.DB.Query(`select username, somethingoptional from members;`)
	PanicIf(err)
	defer memberRows.Close()
	members := []Member{}
	for memberRows.Next() {
		m := Member{}
		err = memberRows.Scan(&m.Username, &m.SomethingOptional)
		PanicIf(err)
		members = append(members, m)
	}
	return members
}

The Scan error ... Scan pair... is coming from memberRows.Scan(&m.Username, &m.SomethingOptional) and indicates that the database driver isn’t sure how to stuff a nil into a *string. Note that the loop above may work fine for a few iterations but will issue this error as soon as it hits a row where the nullable (“somethingoptional”) column has a null value.

sql.NullXYZ types

To remedy this, in the struct replace string with sql.NullString. See the docs for the handful of NullXYZ types.

Your code will now look like:

type Member struct {
	Username                string
	SomethingOptional       sql.NullString
	SomethingOptionalInt    sql.NullInt64
}

With that one change, the error should be resolved.

Displaying sql.NullXYZ types

The sql.NullXYZ types have two fields: a typed value and a boolean Valid. You can use the typed value to get either the value that’s been set, or the type’s “zero value” if it hasn’t been set. For example, on field with type sql.NullInt64, you can use SomethingOptionalInt.Int64 to get a number that’s been set, or 0 if it hasn’t.

However, when displaying those fields, you often don’t want the zero value if it’s not set. In Go templates, use Value:

  Some optional int: {{.Member.SomethingOptionalInt.Value}}

Saving sql.NullXYZ types

As with SELECT, your INSERT and UPDATE SQL won’t change as a result of using sql.NullXYZ types. However, there is a gotcha.

Given the following code:

_, err := s.DB.Exec(`Update members set somethingoptional=$2, somethingoptionalint=$3 where id=$1`,
		member.ID, member.SomethingOptional, member.SomethingOptionalInt)

The driver calls the type’s Value() method, which in turn checks first if Valid is false. If false, it returns nil for the value, and consequently the column will be NULLed.

For inserting and updating these types, to ensure null values are set when the type has its “zero value”, you must ensure that the Valid field is set to false. When you do that, DB.Exec() calls will work as expected.

Creating a new instance of the type thus looks like:

member := Member{Username:"banjer", SomethingOptional:sql.NullString{String:"Pickin", Valid:true}, SomethingOptionalInt:sql.NullInt64{Int64:0, Valid:false}}

The tedious part comes when changing one of those NullXYZ fields from valid to invalid, and vice-versa:

member.SomethingOptional = sql.NullString{String:"", Valid:false}
member.SomethingOptionalInt = sql.NullInt64{Int64:42, Valid:true}

Helpers for sql.NullXYZ types

Ensuring that Valid is set appropriately will get tiresome after about 1 form field, so use helpers to avoid duplication. Something like this will get you started:

//ToNullString invalidates a sql.NullString if empty, validates if not empty
func ToNullString(s string) sql.NullString {
  return sql.NullString{String : s, Valid : s != ""}
}

//ToNullInt64 validates a sql.NullInt64 if incoming string evaluates to an integer, invalidates if it does not
 func ToNullInt64(s string) sql.NullInt64 {
   i, err := strconv.Atoi(s)
   return sql.NullInt64{Int64 : int64(i), Valid : err == nil}
 }