Monday, June 11, 2018

Working with nulls with sql databases in Golang

Go is typed with no generics, so it's a little tricky working with databases where null values abound.

the sql package includes structs such as sql.NullString, but it's confusing for users to get a json response that looks like this, which is how the value is represented internally:

 "foo": {
  "Valid": true,
  "Value": 5

Below is a working golang HTTP server that handles null strings. It reads and writes to a sqlite3 database (in memory). When rows contain null values, they'll be properly displayed as null.

The trick to making things work is the null.v3 library, which implements the json marshal and unmarshal methods necessary to return a null value when querying the database.

Gist here.

package main

import (

 _ ""

// DB is the database connector
var DB *sql.DB

// Person represents a single row in a database. Using the type null.
type Person struct {
 Name     string      `json:"id"`
 Age      int         `json:"age"`
 NickName null.String `json:"nickname"` // Optional

// InsertPerson adds a person to the database
func InsertPerson(p Person) {
 cnx, _ := DB.Prepare(`
          INSERT INTO people (name, age, nickname) VALUES (?, ?, ?)`)
 defer cnx.Close()
 log.Printf("Adding person: %v\n", p)
 cnx.Exec(p.Name, p.Age, p.NickName)

// GetPeople will retur N number of people from database
func GetPeople(n int) []Person {
 people := make([]Person, 0)
 rows, _ := DB.Query(`SELECT name, age, nickname from people LIMIT ?`, n)
 for rows.Next() {
  p := new(Person)
  rows.Scan(&p.Name, &p.Age, &p.NickName)
  people = append(people, *p)
 return people

func addPersonRouter(w http.ResponseWriter, r *http.Request) {

 age, _ := strconv.Atoi(r.FormValue("age"))

 // Get nickname from the form and create a new null.String. If the string
 // is empty, it will be considered invalid (null) in the database and not
 // empty
 nick := r.FormValue("nickname")
 nickName := null.NewString(
  nick, nick != "")

 p := Person{
  Name:     r.FormValue("name"),
  Age:      age,
  NickName: nickName,



func getPeopleRouter(w http.ResponseWriter, r *http.Request) {
 limit, _ := strconv.Atoi(r.FormValue("limit"))
 people := GetPeople(limit)

 peopleJSON, _ := json.Marshal(people)
 w.Header().Set("Content-Type", "application/json")

// CreateTable is a helper function to create the table for the first run
func CreateTable() error {

 createSQL := `
    CREATE TABLE people (
  name TXT NOT NULL,
  nickname TXT
 statement, err := DB.Prepare(createSQL)
 if err != nil {
  return err


 return nil

func main() {
 var err error
 DB, err = sql.Open("sqlite3", ":memory:")
 if err != nil {

 err = CreateTable()
 if err != nil {

 http.HandleFunc("/add", addPersonRouter)
 http.HandleFunc("/list", getPeopleRouter)
 http.ListenAndServe(":8080", nil)

Here's some examples of the server when it's running

$ curl -XPOST "localhost:8080/add?name=Joseph&age=25&nickname=Joe"
$ curl -XPOST "localhost:8080/add?name=Stephen&age=33"
$ curl -s 'localhost:8080/list?limit=2' | jq
    "id": "Joseph",
    "age": 25,
    "nickname": "Joe"
    "id": "Stephen",
    "age": 33,
    "nickname": null

There you go. Now you can read and write valid or invalid values to your database.

No comments:

Post a Comment