Difference between *string and sql.NullString

Question

I've been strugguling with SQL NULL values in Golang recently. After trying to unsuccessfully unmarshal JSON objects using Decode() and sql.NullString, I came to this answer on StackOverflow :

Assigning null to JSON fields instead of empty strings in Golang

The solution using a string pointer seems to work perfectly with Decode() and nil values. So what is the difference between sql.NullString and *string ? Is it just about nil checking ?

Solenne

A NULL coming from sql is not the same that a Go nil. Take a look to NullString – Jade

Answer 1

SQL has different null values than Golang.

If you look at the definition of sql.NullString then this is what you get:

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

As you can see, sql.NullString is a way to represent null string coming from SQL (which correspond to "NULL"). On the other hand, a nil *string is a pointer to a string which is nil, so the two are different.

Vernonvernor

That's pretty smart and clean – Tsarevitch

Is thr any advantage using sql.NullString over using *string? – Erdei

From Russ Cox: There's no effective difference. We thought people might want to use NullString because it is so common and perhaps expresses the intent more clearly than *string. But either will work. – Isbell

thanks @Isbell that quotes makes sense. The answer is misleading. Strictly speaking a null is not a nil, but at a higher level they both represent the very same concept. – Freezedry

nil has no type, sql.NullString does, so it's better to use sql.NullString, but people don't want to do the extra typing so they just defaulted to *string, which IMHO is an ugly hack. – Authoritarian

How would you convert *string to sql.NullString? What I can think of is uglier than the "hack". – Sheenasheeny

@TimoHuovinen What you said "uglier hack" might not be uglier. Using pointer is not database specific. And the result can be marshaled into JSON directly. – Nica

Answer 2

Making @sepehr's comment in the previous answer an answer of it's own, and adding some conversion functions I find helpful.

From Russ Cox

There's no effective difference. We thought people might want to use NullString because it is so common and perhaps expresses the intent more clearly than *string. But either will work

In fact, a few generic functions make it easy to convert from *string and sql.NullString with a minimum of fuss:

func DerefOrEmpty[T any](val *T) T {
    if val == nil {
        var empty T
        return empty
    }
    return *val
}

func IsNotNil[T any](val *T) bool {
    return val != nil
}

Usage:

func main() {
    var comment string = "hello"
    commentPtr := &comment

    sqlStrComment := sql.NullString{
        String: DerefOrEmpty(commentPtr),
        Valid:  IsNotNil(commentPtr),
    }
    fmt.Printf("%#v\n", sqlStrComment)

    commentPtr = nil
    sqlStrComment = sql.NullString{
        String: DerefOrEmpty(commentPtr),
        Valid:  IsNotNil(commentPtr),
    }
    fmt.Printf("%#v\n", sqlStrComment)
}

Playground link at https://go.dev/play/p/KFNHe2iJmGU

Dendrology