Scan SQL NULL values in golang

 Using standard database/sql Row.Scan() I have a problem with null values in the row. Without considering possible null values in a row, I can get scan errors like <nil> -> *string. This is quite common using LEFT JOIN queries or weak defined tables missing NO NULL column constraints.

There are sql.NullXXX types (e.g. sql.NullInt64) which can be used to scan possible null value from a row, and then check if the value is .Valid. However, these types don’t implement JSON marshalling, and those more logic and coding is required.

To solve this, is it better approach to COALESCE() column values in SQL query or do some extra coding in golang code?

You can use aliases for types provided by sql package such as (NullInt64, NullString etc...). There are two advantages of using that, one you can scan null values and can get in golang structure and second you can marshal that struct in JSON.

Please, look at the sample code:

// NullInt64 is an alias for sql.NullInt64 data type
type NullInt64 sql.NullInt64

// Scan implements the Scanner interface for NullInt64
func (ni *NullInt64) Scan(value interface{}) error {
    var i sql.NullInt64
    if err := i.Scan(value); err != nil {
        return err
    }
    // if nil the make Valid false
    if reflect.TypeOf(value) == nil {
        *ni = NullInt64{i.Int64, false}
    } else {
        *ni = NullInt64{i.Int64, true}
    }
    return nil
}

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

Please, have a look at this article, it would be very helpful about handling null values in golang and how to use it in JSON.

As an option you can implement your custom data type that would match the JSON Marshaler interface. After that you would be able to marshal your struct using regular tags.

Check the example:

type UserTitleType sql.NullString

func (s UserTitleType) MarshalJSON() ([]byte, error) {
    if s.Valid {
        return json.Marshal(s.String)
    }
    return jsonNull, nil
}

type User struct {
    Id    int64         `json:"id"`
    Title UserTitleType `json:"title"`
}

// Scan implements the Scanner interface.
func (n *NullInt64) Scan(value interface{}) error {
	var ok bool
	if value == nil {
		n.Int64, n.Valid = 0, false
		return nil
	}
	
	n.Valid = true
	n.Int64, ok = value.(int64)
	if !ok {
		return errors.New("convert error")
	}
	return nil
}

// Value implements the driver Valuer interface.
func (n NullInt64) Value() (driver.Value, error) {
	if !n.Valid {
		return nil, nil
	}
	return n.Int64, nil
}

I implemented this approach that allows you to scan the rows, where nil values are scanned as zero-values, although you will have to replace rows.Scan(args...) with RowScan(rows, args...).
Also, this implementation only supports string, int, and bool. You may add additional types if you need them from sql.Nullxxx

func RowScan(row *sql.Rows, args ...interface{}) error {

	scanArgs := make([]interface{}, len(args))

	for i, arg := range args {

		switch reflect.ValueOf(arg).Elem().Kind() {

		case reflect.Int64, reflect.Int:
			scanArgs[i] = &sql.NullInt64{}
		case reflect.Bool:
			scanArgs[i] = &sql.NullBool{}
		case reflect.String:
			scanArgs[i] = &sql.NullString{}
		default:
			return errors.New("unsupported type")
		}
	}
	err := row.Scan(scanArgs...)

	if err != nil {
		return err
	}

	for i, sArg := range scanArgs {

		switch reflect.ValueOf(sArg).Elem().Field(0).Kind() {

		case reflect.Int64, reflect.Int:
			reflect.ValueOf(args[i]).Elem().SetInt(scanArgs[i].(*sql.NullInt64).Int64)
		case reflect.Bool:
			reflect.ValueOf(args[i]).Elem().SetBool(scanArgs[i].(*sql.NullBool).Bool)
		case reflect.String:
			reflect.ValueOf(args[i]).Elem().SetString(scanArgs[i].(*sql.NullString).String)
		default:
			return errors.New("unsupported type")
		}
	}
	return nil
}