在Go中绕过SQL空值问题

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. Look to the Go ORM libraries for help. Well I did that, but to my surprise none of them tackle this issue.

6. 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.

Answer 1

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"}]