Bypass sql null value problems in Go

Question

I want to use Go to make an API for an existing database that uses null values extensively. Go will not scan nulls to empty strings (or equivalent), and so I need to implement a workaround.

The workarounds I have discovered have left me unsatisfied. In fact I went looking for a dynamic language because of this problem, but Go has certain attractions and I would like to stick with it if possible. Here are the workarounds that did not satisfy:

Don't use nulls in the database. Unsuitable because the database is pre-existing and I do not have liberty to interfere with its structure. The database is more important than my app, not the other way around.
In sql queries, use COALESCE, ISNULL, etc to convert nulls to empty strings (or equiv) before the data gets to my app. Unsuitable because there are many fields and many tables. Apart from a couple of obvious ones (primary key, surname), I don't know for sure which fields can be relied upon not to give me a null value, so I would be defensively cluttering my sql queries everywhere.
Use sql.NullString, sql.NullInt64, sql.NullFloat64, etc to convert nulls to empty strings (or equiv) as an intermediate step before settling them into their destination type. This suffers from the same problem as above, only I am cluttering my Go code instead of my sql queries.
Use a combination of *pointers and []byte, to scan each item in to a memory location without committing it to a particular type (other than []byte), and then somehow work with the raw data. But to do something meaningful with the data you have to convert it to something more useful, and then you are back to sql.Nullstring or if x==nil{handle it}, and this again is happening on a case by case basis for any field that I need to work with. So, again, we are looking at cluttered, messy, error-prone code and I'm repeating myself all the time instead of being DRY in my coding.
Look to the Go ORM libraries for help. Well I did that, but to my surprise none of them tackle this issue.
Make my own helper package to convert all null strings to "", null ints to 0, null floats to 0.00, null bools to false, etc, and make it part of the process of scanning in from the sql driver, resulting in regular, normal strings, ints, floats and bools.

Unfortunately if 6 is the solution, I do not have the expertise. I suspect the solution would involve something like "if the intended type of the item to be scanned to is a string, make it an sql.NullString and extract an empty string from it. But if the item to be scanned to is an int, make it a NullInt64 and get a zero from that. But if ...(etc)"

Is there anything I have missed? Thank you.

No. golang.org/pkg/database/sql/#NullString is the answer for this. — elithrar
– elithrar, Commented Aug 30, 2015 at 12:04
A number of your items assume that an SQL NULL should be treated as "" or 0. That's almost never the case, if it was the database wouldn't bother using NULL (which normally means, "unset", "unavailable", "inapplicable", etc). For programming languages that don't have a concept of NULL basic types (e.g. a pointer can be nil but not an int) you have to use either two variables (e.g. value int & valid bool as with sql.NullString) per null-able column or flags field per row/record or some such to record the validity state. This simply is not an issue that can be waved away by magic code. — Dave C
– Dave C, Commented Aug 30, 2015 at 18:16
Indeed. In our database some people have middle names. The middle name is truly not "", so a NULL appears. This saves me from the error of thinking that people literally have "" as their middle name. And inadvertently trying to email them at address "" or call them on phone number 0. But seriously I think the database makers used NULL just because it was there, and it was slightly more correct than an empty string. — Josh B
– Josh B, Commented Aug 31, 2015 at 6:11
@elithrar sql.Nullstring is not the answer for this; please see point 3. The database uses null values extensively. The null value means nothing more special than "not there". I will not perform a dance for each potentially null database field. Not using Go would be a better answer. — Josh B
– Josh B, Commented Sep 9, 2015 at 1:02
I commented having read #3 - it is the most appropriate option <i>if you are going to use Go</i>—which is a fair assumption given you posted it under the 'go' tag. You could easily write an isNil helper to simplify this. You're welcome to use another language that has a null zero value for types. — elithrar
– elithrar, Commented Sep 9, 2015 at 1:06

Josh B · Accepted Answer · 2015-09-15 10:39:05Z

7

The use of pointers for the sql-scanning destination variables enables the data to be scanned in, worked with (subject to checking if != nil) and marshalled to json, to be sent out from the API, without having to put hundreds of sql.Nullstring, sql.Nullfloat64 etc everywhere. Nulls are miraculously preserved and sent out through the marshalled json. (See Fathername at the bottom). At the other end, the client can work with the nulls in javascript which is better equipped to handle them.

func queryToJson(db *sql.DB) []byte {
    rows, err := db.Query(
      "select mothername, fathername, surname from fams" +
      "where surname = ?", "Nullfather"
    )
    defer rows.Close()

    type record struct {
        Mname, Fname, Surname *string  // the key: use pointers
    }
    records := []record{}

    for rows.Next() {
        var r record
        err := rows.Scan(r.Mname, r.Fname, r.Surname) // no need for "&"
        if err != nil {
            log.Fatal(err)
        }
        fmt.Println(r)
        records = append(records, r)
    }
    j, err := json.Marshal(records)
    if err != nil {
        log.Fatal(err)
    }
    return j
}
j := queryToJson(db)
fmt.Println(string(j)) // [{"Mothername":"Mary", "Fathername":null, "Surname":"Nullfather"}]

edited Sep 15, 2015 at 10:39

answered Sep 13, 2015 at 14:36

Josh B

5875 silver badges11 bronze badges

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

2 Comments

MattyB Over a year ago

This is fine if you need the json anyway. Marshaling is slow though, and if you don't need the json, this is a big waste of computation.

Josh B Over a year ago

@MattyB You could stop the function just before the marshalling line and return "records" variable if you didn't want the marshalling, but then you need to deal with those potentially nil pointers some other way before using the data. For me the marshalling seems plenty fast; it is sent over a network and consumed by JavaScript in a web browser.

Collectives™ on Stack Overflow

Bypass sql null value problems in Go

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related