What to do with NULL values?

[pgundlach]

Question (request for doc enhancement :)

I have a database which contains NULL values (mysql). When I try to fill a struct using

 obj, err := dbmap.Get(mystruct{}, id)

I get this error:

sql: Scan error on column index 11: converting string "<nil>" to a int: strconv.ParseInt: parsing "<nil>": invalid syntax

Which makes sense, since I have NULL values in the table which cannot be converted to an int (the requested column / struct type is int).

Is there any automatic way to treat NULL values as 0 (for example) without changing the database?

[coopernurse]

Hi there,

The database/sql package provides some "null" types that you can use for this purpose. For example:

type Person struct {
    Id      int64
    Name sql.NullString
    Age sql.NullInt64
}

Name and Age can both be null in the db, and will scan properly. You would get the value via: person.Name.String.

[irlTopper]

Thanks for this - I hadn't seen "sql.NullInt64" etc before.
To help others, maybe you could update the readme to mention these.

[vinceyuan]

sql.NullString solves my problem. But it is not convenient. Wonder why string can not be used directly for mapping. Is it not possible to assign an empty string when there is a null?

[msabramo]

Same question as @vinceyuan

[nelsam]

An empty string is not a null string - they are considered two different values in the database and in go, and plenty of code relies on checking for null as compared to an empty string. null often means "not yet assigned", where empty string often means "assigned as an empty string".

If you just feel like sql.NullString is cumbersome, use *string (pointer to string). The pointer will be null if the database value is null; if the value in the database is an empty string, then the pointer will be non-nil but the string value will be empty.

If you just don't like doing more than one comparison (which the pointer doesn't really solve), but empty string and null are equivalent in your database, you can implement sql.Scanner and driver.Valuer in a custom type to use in your struct fields; something like the following:

package foo

import (
    "database/sql"
    "database/sql/driver"
)

type NullToEmptyString string

func (n NullToEmptyString) Value() (driver.Value, error) {
    return string(n), nil
}

func (n *NullToEmptyString) Scan(v interface{}) error {
    if v == nil {
        *n = ""
        return nil
    }
    s, ok := v.(string)
    if !ok {
        return fmt.Errorf("Cannot scan value %v: expected string type, got %T", v, v)
    }
    *n = NullToEmptyString(s)
    return nil
}