Unable to use type string as sql.NullString

Question

I am creating a gorm model

// Day is a corresponding day entry type Day struct { gorm.Model Dateday string `json:"dateday" gorm:"type:date;NOT NULL"` Nameday string `json:"nameday" gorm:"type:varchar(100);NOT NULL"` Something sql.NullString `json:"salad"` Holyday bool `json:"holyday"` }

I am using sql.NullString for the field Something cause it may be NULL.

So when I try to execute a typical gorm example to validate my setup works:

 db.Create(&Day{ Nameday: "Monday", Dateday: "23-10-2019", Something: "a string goes here", Holyday: false, })

I get:

cannot use "a string goes here", (type string) as type sql.NullString in field value

What type should I use for the Something field given it may be NULL?

sql.NullString is a struct type and is not convertible to a string type. You need to initialize a value of sql.NullString and set that to the field. One way to do that would be Something: sql.NullString{String: "a string goes here", Valid: true}. golang.org/pkg/database/sql/#NullString — mkopriva
– mkopriva, Commented Mar 21, 2020 at 19:31
thanks for taking the time to respond; I would suggest you post it as a normal answer so I can accept / upvote it — pkaramol
– pkaramol, Commented Mar 21, 2020 at 20:18
If I remember correctly you can use *string type for NULL-able columns — ceth
– ceth, Commented Mar 22, 2020 at 5:40

mkopriva · Accepted Answer · 2023-06-19 08:26:55Z

The sql.NullString type is not actually a string type but a struct type. It's defined as:

type NullString struct { String string Valid bool // Valid is true if String is not NULL }

Therefore you need to initialize it as such:

db.Create(&Day{ Nameday: "Monday", Dateday: "23-10-2019", Something: sql.NullString{String: "a string goes here", Valid: true}, Holyday: false, })

As an alternative, if you want to keep using the simpler syntax when initializing a nullable string, you could declare your own nullable string type, have it implement the sql.Scanner and driver.Valuer interfaces, and leverage the null byte to signal a NULL value.

type MyString string const MyStringNull MyString = "\x00" // implements driver.Valuer, will be invoked automatically when written to the db func (s MyString) Value() (driver.Value, error) { if s == MyStringNull { return nil, nil } return []byte(s), nil } // implements sql.Scanner, will be invoked automatically when read from the db func (s *MyString) Scan(src interface{}) error { switch v := src.(type) { case string: *s = MyString(v) case []byte: *s = MyString(v) case nil: *s = MyStringNull } return nil }

With this, if you declare the field Something to be of type MyString you can initialize it as you originally intended.

db.Create(&Day{ Nameday: "Monday", Dateday: "23-10-2019", // here the string expression is an *untyped* string constant // that will be implicitly converted to MyString because // both `string` and `MyString` have the same *underlying* type. Something: "a string goes here", Holyday: false, })

Just keep in mind that this works only with untyped constants, once you have a constant or variable of type string, to be able to assign that to a MyString you'll need to use an explicit conversion.

var s string var ms MyString s = "a string goes here" ms = s // won't compile because s is not an untyped constant ms = MyString(s) // you have to explicitly convert

Something: sql.NullString{String: "a string goes here", Valid: true}, worked out for me, thank you.
Really nice answer. A small typo in Scan function. It should return MyStringNull in case of nil, and not StringNull. Too small of an edit so SO doesn't allow me to suggest it.

Song · Accepted Answer · 2022-09-20 07:18:23Z

package main import ( "github.com/guregu/null" ) func main() { db.Create(&Day{ Nameday: null.StringFrom("Monday"), }) }

Collectives™ on Stack Overflow

Unable to use type string as sql.NullString

2 Answers 2

2 Comments

Comments

Hot Network Questions