Scan SQL NULL values in golang

Question

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?

Refer to this [SO post](https://stackoverflow.com/a/33072822) handling nullable values on your own. Suggestion: you can give it try with this library `github.com/guregu/null`. — jeevatkm, Jun 21 '17 at 08:14
What about SQL solution? I can see some pros to use `COALESCE()` values in queries. You can scan values directly to standard types and also define default values other than zeros. — kajaptz, Jun 21 '17 at 15:18
In both cases you have to take some extra to handle null and assign default values wherever appropriate. If I look at it, it's design choice you have take. Where would you like to draw a line in 'separation the concerns'. Application code or Database Query? — jeevatkm, Jun 21 '17 at 18:26

rsudip90 · Answer 1 · 2017-10-15T08:51:14.903

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.

score 4 · Answer 2 · edited Jun 21 '17 at 08:57

4

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

edited Jun 21 '17 at 08:57

jeevatkm

4,571
1
23
24

answered Jun 21 '17 at 08:27

Bohdan Kostko

373
2
10

Also I found this issue in Golang repo. Apparently they didn't want to confuse people with include of JSON features into sql lib. You can find more info here: https://github.com/golang/go/issues/9719 – Bohdan Kostko Jun 21 '17 at 08:31

score 0 · Answer 3 · answered Dec 08 '19 at 06:01

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

l Kay l · Answer 4 · 2022-10-31T13:59:25.247

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
}

Scan SQL NULL values in golang

4 Answers4