Skip to content
/ zero Public

⭕️An alternative approach to work with database's nullable types without using sql.Null*

Notifications You must be signed in to change notification settings

railstack/zero

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

36 Commits
 
 
 
 
 
 
 
 

Repository files navigation

Zero

GoDoc Build Status

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:

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.

About

⭕️An alternative approach to work with database's nullable types without using sql.Null*

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages