CODEWITHSUNDEEP
sqljsongo
kajaptz
kajaptz

Reputation: 383

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?

Upvotes: 16

Views: 32503

Answers (5)

Carlo Nyte
Carlo Nyte

Reputation: 766

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

Upvotes: 1

l Kay l
l Kay l

Reputation: 1

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
}

Upvotes: 0

TiGo
TiGo

Reputation: 617

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

Upvotes: 0

rsudip90
rsudip90

Reputation: 869

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.

Upvotes: 16

Bohdan Kostko
Bohdan Kostko

Reputation: 393

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

Upvotes: 4

Related Questions