Working with database nullable fields

Rails is easy with database nullable fields, it return a nil if the column value is NULL. It's idiomatic and transparent for us developers.

But Go is a strict typed language, if you set a struct attribute as string, then you can't evaluate it as nil. Here's is a post for the situation: working-with-db-nulls.

Zero values

Go hasn't a nil for its basic types, but it provides zero values for them:

string:     ""
int:        0
bool:       false
float:      0.0
time.Time:  '0001-01-01 00:00:00','+00:00','UTC'

So for the database nullable field, if a string typed field value is NULL, we can set it as "" to make it consistent with Go's zero value specification.

COALESCE function

I hope go-on-rails can work with the nullable fields like Rails does, so I searched the web and found the COALESCE, a function available in most popular databases, here is the spark of inspiration: https://github.com/go-sql-driver/mysql/issues/34#issuecomment-158391340.

Its basic usage is like this:

 COALESCE(column_a, [column_b, "default_string"]) AS an_alias_for_column_a

Here it means that if column_a is NULL, we return one of its parameter in turn from the second ones which maybe is a column_b for query or simply a same typed value for return.

So we can take the zero values for the COALESCE's second parameter, it'll be returned if the nullable field is NULL.

That's it!

Then go-on-rails v0.1.11 is released with the feature built in. All the functions generated by go-on-rails with a SELECT query is enhanced with the COALESCE.

A Go package for it

And I write a Go package as a helper for you to write your own SQL clause that's also safe with nullable fields: zero.

For example, we has a struct:

type Person struct {
    Name string    // Not NULL
    Age  int       // Nullable field in database  
    Address        // Nullable field in database
}

we write a select clause for it with zero and then use the go-on-rails generated FindPersonsBySql() function to execute the query:

import (
    "fmt"

    "github.com/railstack/zero"
    // here we import the models in a relative path, 
    // we assume it is in the up folder of the current "controllers" folder 
    m "../models"
)

sql := fmt.Sprintf("SELECT id, name, %v, %v FROM persons", zero.Int("age"), zero.String("address"))

ps, err := m.FindPersonsBySql(sql)
if err != nil {
    /* ... */
}
fmt.Printf("Persons: %+v", ps)