Zero

Zero just provides some helpers for those Gophers prefer the zero values than touching the sql.Null* types when you have to work with some database tables with nullable fields.

Zero's main idea is using a function COALESCE that most popular databases support, as:

• SQLite
• MySQL
• PostgreSQL

And I first got the inspiration from here.

Usage

Now you must have known what the function COALESCE does, so what zero does is very simple, it just helps you write COALESCE function calls.

Let's create a sample table users at first:

Field	Type	Null
id	bigint(20)	NO
name	varchar(255)	NO
age	int(10)	YES
sign_at	datetime	YES

Then I show you how to use zero to work with a nullable field in a method call style:

import (
    "fmt"
    "github.com/railstack/zero"
)


func main() {
    zr := zero.New("mysql") // or "postgres", "sqlite"
    sql := fmt.Sprintf("SELECT id, name, %v FROM users", zr.Int("age"))
    // here the "sql" = `SELECT id, name, COALESCE(age, 0) AS age FROM users`
    // and then you can do a query with the "sql"
}

here we create an object using the New() function by passing a database name to it. Available database names're mysql, postgres and sqlite.

Of course you can call the equivalent function directly without creating an object:

import (
    "fmt"
    "github.com/railstack/zero"
)


func main() {
    sql := fmt.Sprintf("SELECT id, name, %v FROM users", zero.Int("age"))
    // here the "sql" = `SELECT id, name, COALESCE(age, 0) AS age FROM users`
    // and then you can do a query with the "sql"
}

but the function Time() is a little bit different due to it's database independent, so it'll be called in the method way:

zr := zero.New("mysql")
zr.Time("sign_at")

Or function way by passing the database name as the first parameter:

zero.Time("mysql", "sign_at")

Now available databases are: mysql, postgres and sqlite.

Note: If you want to use the solution for sqlite, you must use a forked version of the driver mattn/go-sqlite3. And this version still has some potential problems as discussed at: mattn/go-sqlite3#468, so it's up to you as a choice.

Functions avaliable

• String() is for string type variables in Go
• Int() is for all the int* typed variables in Go
• Float() is for all the float* typed variables
• Bool() is for the bool typed variables
• Time() is for the time.Time typed variables

And for each of above there's a correnponding TypeAs() function:

• StringAs()
• IntAs()
• FloatAs()
• BoolAs()
• TimeAs()

these functions take another parameter as a AS alias name, for example:

zero.StringAs("name", "last_name") // will return: COALESCE(name, '') AS last_name

And there're some database special functions, like:

• Inet() and InetAs() are only available in PostgreSQL

You can check all the available functions in the godoc.