Scan SQL NULL values in golang

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"`
}

Although a bit tedious this solution worked well for me:

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq" // PostgreSQL driver
)

// NullableString represents a nullable string value.
type NullableString struct {
    sql.NullString
}

// ValueOrDefault returns the value if not NULL, or a default value (e.g., empty string) if NULL.
func (ns *NullableString) ValueOrDefault(defaultValue string) string {
    if ns.Valid {
        return ns.String
    }
    return defaultValue
}

func main() {
    db, err := sql.Open("postgres", "your_database_connection_string_here")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    var _firstName, _lastName, NullableString

    err = db.QueryRow(cmd).Scan(&_firstName, &_lastName)

    if err != nil {
        panic(err)
    }

    // Use the helper function to handle nullable columns
    firstName := _firstName.ValueOrDefault("N/A")
    lastName := _lastName.ValueOrDefault("N/A")

    fmt.Println("firstName:", firstName)
    fmt.Println("lastName:", lastName)
}

// 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
}