How can I work with SQL NULL values and JSON in a good way?

Adding JSON and SQL null safety with Go's standard library

We often struggle with sql.Null*** types in Go's database/sql package due to their inelegant serialization to JSON. The solution? Craft custom types that gracefully accommodate SQL NULL values and convert to JSON smoothly.

Custom marshallers for JSON serialization

Add sql.NullInt64 to a custom type and have it implement json.Marshaler and json.Unmarshaler interfaces:

type NullInt64 struct {
   sql.NullInt64 // Invasion of the NULL snatchers!
}

func (ni *NullInt64) MarshalJSON() ([]byte, error) {
   // NULLs, assemble!
   if !ni.Valid {
      return []byte("null"), nil
   }
   // Avengers, disassemble!
   return json.Marshal(ni.Int64)
}

func (ni *NullInt64) UnmarshalJSON(data []byte) error {
   var x *int64
   if err := json.Unmarshal(data, &x); err != nil {
      // Oops... we cannot unmarshal.
      return err
   }
   if x != nil {
      ni.Valid = true
      ni.Int64 = *x
   } else {
      ni.Valid = false // Like Thor without his hammer 😢
   }
   return nil
}


NullInt64 now transmogrifies NULL SQL integers to JSON null and back without breaking a sweat🏋️‍♀️.

Dealing with null values via pointers in struct fields

If you're dealing with optional JSON structure fields, consider using pointers:

type User struct {
   Name *string `json:"name"`
}

func (u *User) UnmarshalJSON(data []byte) error {
   // custom unmarshalling magic here
}


Pointers can be nil, making them a perfect fit for representing optional JSON fields.

Custom JSON helpers with 'guregu/null.v3' package

Work smarter, not harder using gopkg.in/guregu/null.v3. This package simplifies how SQL NULL values are handled in Go. It includes some nifty features:

import "gopkg.in/guregu/null.v3" // Welcome aboard, matey!

type User struct {
   Age null.Int `json:"age"` // That's what I call living your best life!
}


It outperforms sql.Null*** types while providing a streamlined API. The package is a win-win situation with marshaling and unmarshalling.

Validate your code with play.golang.org

To confirm that your SQL NULLs and JSON code works as expected, test it at play.golang.org. It helps you run Go code online — superb for testing JSON/SQL handling.

Preempting extra JSON layers

Creating a NullInt64 custom type eliminates the awkward nested JSON objects that sql.Null*** structs can produce:

// What sql.NullInt64 does to your JSON
{
  "Int64": 123,
  "Valid": true
}

// And here's the custom NullInt64 play
123


Condensed JSON structure leads to readability that everyone appreciates.

Handling the usual and the unusual

Understand JSON marshaling principles

Remember that when marshaling Go structs into JSON, unexported fields are ignored. If you're dealing with SQL NULL fields, make sure they are exported and correctly tagged for smooth serialization.

Shiny working example

For an expanded viewpoint, refer to the link (found in the references section) that provides a practical example of a Go web server using SQLite and JSON.