Gorp, sql.NullString and JSON

Tue, Mar 11, 2014

Using the Gorp package provides the ability for a struct to be populated directly from an SQL backend. Go also provides the ability for this same struct to be populated directly from JSON data. It’s a nice combination but has some gotchas to watch out for.

Recently I struck a problem where a column in my database that was NULL was causing me some grief. I was unsure at first how to handle the database constraints while continuing to be able to unmarshall JSON the way I had been.

To illustrate, I have the following struct which maps to a table in my Postgresql database:

type User struct {
        Id       int          `db:"id"`
        Username string       `db:"username"`
        Passhash string       `db:"passhash"`
        Email    string       `db:"email"`
}

Database columns id,username andpasshash are all defined as NOT NULL however email isn’t.

If I attempt to retrieve a record from the database, I see the following error:

err sql: Scan error on column index 3: unsupported driver -> Scan pair: <nil> -> *string

To rectify this I need to set Email to type sql.NullString. I can now retrieve data from the database without error. However if I attempt to unmarshall some JSON into this struct, I see this error:

json: cannot unmarshal string into Go value of type sql.NullString

What to do?

The answer is to wrap sql.NullString inside a new type, then implement the json.Unmarshaler interface. You might think to try something like:

type NullString sql.NullString

type User struct {
        Id       int          `db:"id"`
        Username string       `db:"username"`
        Passhash string       `db:"passhash"`
        Email    NullString   `db:"email"`
}

func (s *NullString) UnmarshalJSON(data []byte) (error) {
        s.String = strings.Trim(string(data),`"`)
        s.Valid = true
        return nil
}

However when I attempt to retrieve a record from the database I see a new error:

sql: Scan error on column index 3: unsupported driver -> Scan pair: []uint8 -> *db.NullString

The trick is to change the NullString definition slightly like so:

type NullString struct {
        sql.NullString
}

sql.NullString is acting as an anonymous or embedded field.