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

34

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

edited Jun 19, 2023 at 8:26

answered Mar 22, 2020 at 7:17

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

infiniteLearner Over a year ago

Something: sql.NullString{String: "a string goes here", Valid: true}, worked out for me, thank you.

Marko Over a year ago

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

2

package main

import (
    "github.com/guregu/null"
)

func main() {

    db.Create(&Day{
        Nameday: null.StringFrom("Monday"),
    })
}

answered Sep 20, 2022 at 7:18

Song

67111 silver badges21 bronze badges

Collectives™ on Stack Overflow

Unable to use type string as sql.NullString

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related