How to marshal and unmarshal sql.Null types to JSON in golang

README.md

Answer

Use custom nullable types instead of original sql.Null types like sql.NullBool, sql.NullFloat64, sql.NullInt64 or sql.NullString.

nullables.go

package models

import (
	"database/sql"
	"encoding/json"
)

// Nullable Bool that overrides sql.NullBool
type NullBool struct {
	sql.NullBool
}

func (nb NullBool) MarshalJSON() ([]byte, error) {
	if nb.Valid {
		return json.Marshal(nb.Bool)
	}
	return json.Marshal(nil)
}

func (nb *NullBool) UnmarshalJSON(data []byte) error {
	var b *bool
	if err := json.Unmarshal(data, &b); err != nil {
		return err
	}
	if b != nil {
		nb.Valid = true
		nb.Bool = *b
	} else {
		nb.Valid = false
	}
	return nil
}

// Nullable Float64 that overrides sql.NullFloat64
type NullFloat64 struct {
	sql.NullFloat64
}

func (nf NullFloat64) MarshalJSON() ([]byte, error) {
	if nf.Valid {
		return json.Marshal(nf.Float64)
	}
	return json.Marshal(nil)
}

func (nf *NullFloat64) UnmarshalJSON(data []byte) error {
	var f *float64
	if err := json.Unmarshal(data, &f); err != nil {
		return err
	}
	if f != nil {
		nf.Valid = true
		nf.Float64 = *f
	} else {
		nf.Valid = false
	}
	return nil
}

// Nullable Int64 that overrides sql.NullInt64
type NullInt64 struct {
	sql.NullInt64
}

func (ni NullInt64) MarshalJSON() ([]byte, error) {
	if ni.Valid {
		return json.Marshal(ni.Int64)
	}
	return json.Marshal(nil)
}

func (ni *NullInt64) UnmarshalJSON(data []byte) error {
	var i *int64
	if err := json.Unmarshal(data, &i); err != nil {
		return err
	}
	if i != nil {
		ni.Valid = true
		ni.Int64 = *i
	} else {
		ni.Valid = false
	}
	return nil
}

// Nullable String that overrides sql.NullString
type NullString struct {
	sql.NullString
}

func (ns NullString) MarshalJSON() ([]byte, error) {
	if ns.Valid {
		return json.Marshal(ns.String)
	}
	return json.Marshal(nil)
}

func (ns *NullString) UnmarshalJSON(data []byte) error {
	var s *string
	if err := json.Unmarshal(data, &s); err != nil {
		return err
	}
	if s != nil {
		ns.Valid = true
		ns.String = *s
	} else {
		ns.Valid = false
	}
	return nil
}

@hweeks
Not only PHP, Javascript also handles this well (of course JSON is convertible to Javascript after all).
And if you say it's because Go is a strictly typed language, then so does other language like Swift.
Swift handles it naturally even though it's a strictly typed language. It's because Swift has optional type.
A programming language that handles backend usually interacts with database and JSON a lot.
And not having optional type is a big downside.

Let's compare Typescript, Swift and Go:

Typescript

interface User {
  firstName: string
  lastName?: string
}

Swift

struct User {
  let firstName: String
  let lastName: String?
}

Go

type User struct {
  FirstName string
  LastName sql.NullString
}

LOL, look at Go. It even needs a helper type just to be able to represent nullable string.

I'm not saying "just use PHP". Each programming language is designed with different goal. Go is made to achieve the following goals: fast compilation, fast run time execution, and (they say) simplicity (can't agree on this).

Javascript is slower being an interpreted language, but for me personally the development time is significantly less.
Remember, the performance can still be remedied by JIT compiler.
The development time is even faster if we use PHP with Laravel but the performance is worse than Go and Node.

Choose which gives you best benefit from business perspective.
If I were to choose, I'll choose Javascript all the time because it's balance in the terms of strict typing (using Typescript), compilation time, run time performance, and development time.

This awesome, Thank you Sir!

@errogaht @edwardanthony here we're talking about the simple example of unmarshalling SQL null strings. Imagine a more complex case e.g. unmarshalling gps coordinates like #355488020131816##1#0000#AUT#01#2260012b9cbb47#2112.703000,E,4545.754120,N,0.07,0.00#031213#184625.000## (possibly base64 encoded) into a Point struct. With Go (and Rust, C++ etc) you can do that.

type Point struct {
	lat float64
	lng float64
}

What's the difference between this and https://github.com/guregu/null?