CODEWITHSUNDEEP
mysqlgoquery-builder
khue bui
khue bui

Reputation: 1416

Get nil value from sql in Go

let say I have a struct Profile and sql table profiles.

type Profile struct {
    ID        int       `json:"id"`
    CreatedAt time.Time `json:"created_at"`
    UpdatedAt time.Time `json:"updated_at"`
    FirstName string    `json:"first_name"`
    LastName  string    `json:"last_name"`
    Age       int       `json:"age"`
}

And, I get a record from profiles table, assign into the Profile struct.

These fields are allow null, so that I used sql.NullString, sql.NullInt64, pq.NullTime to check valid value before assign to struct.

// ... DB.Query(...)
// ... rows.Next()
// ... rows.Scan()
// ...
if firstName.Valid {
    p.FirstName = firstName.String
}
if lastName.Valid {
    p.LastName = lastName.String
}
if age.Valid {
    p.Age = age.Int64
}
// ...

If I have more than 10 tables, each tables has more than 30 columns, I have to check all variables again and again. That make ugly code, someone have any suggestion to make it pretty?

Upvotes: 1

Views: 4943

Answers (1)

Jonathan Hall
Jonathan Hall

Reputation: 79546

You shouldn't be checking like that at all. The nullable types are intended for use directly in structs:

type Profile struct {
    ID        sql.NullInt64  `json:"id"`
    CreatedAt time.Time      `json:"created_at"`
    UpdatedAt time.Time      `json:"updated_at"`
    FirstName sql.NullString `json:"first_name"`
    LastName  sql.NullString `json:"last_name"`
    Age       sql.NullInt64  `json:"age"`
}

Then access the values using, for example, product.FirstName.String(). There is no reason for checking product.FirstName.Valid except where you actually care about the distinction between nil and an empty value (in the examples in your question, you clearly don't care about this distinction).

An alternative, which may be more suitable in some situations, is to just use a pointer:

type Profile struct {
    ID        *int      `json:"id"`
    CreatedAt time.Time `json:"created_at"`
    UpdatedAt time.Time `json:"updated_at"`
    FirstName *string   `json:"first_name"`
    LastName  *string   `json:"last_name"`
    Age       *int      `json:"age"`
}

Of course then any code that uses these variables must dereference them, and check for nil, so this may not always be the most desirable, depending on how you use the values.

A third option, if you never care about the distinction between a NULL value and and empty value, is to create your own type which treats these the same. Example:

type myString string

func (s *myString) Scan(src interface{}) error {
    if src == nil {
        return nil
    }
    if value, ok := src.(string); ok {
        *s = value
        return nil
    }
    return fmt.Errorf("Unsupported type for myString: %T", src)
}

Upvotes: 5

Related Questions