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 (
 "database/sql"
 "encoding/json"
 "log"
 "net/http"
 "strconv"

 _ "github.com/mattn/go-sqlite3"
 "gopkg.in/guregu/null.v3"
)

// 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) {
 r.ParseForm()

 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,
 }

 InsertPerson(p)

 w.WriteHeader(http.StatusCreated)
}

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

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

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

 createSQL := `
    CREATE TABLE people (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TXT NOT NULL,
  age INTEGER NOT NULL,
  nickname TXT
 );`
  
 statement, err := DB.Prepare(createSQL)
 if err != nil {
  return err
 }

 statement.Exec()
 statement.Close()

 return nil
}

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

 err = CreateTable()
 if err != nil {
  log.Fatal(err)
 }

 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.

Verifying JSON easily on the command line

You can pipe stdout into python -mjson.tool to validate it. It makes for quick and easy json validation on the command line.

We'll create a simple json file:

> somefile.txt
{"someval": "something", "anotherval": 3}

Now pipe this into json.tools and check the output.

$ cat somefile.txt | python -mjson.tool
{
    "anotherval": 3,
    "someval": "something"
}

Cool. It even formatted it nicely for us. Let's break it and see what happens.

Single quotes are not valid according rfc4627.

> somefile.txt
{'someval': 'something', 'anotherval': 3}

$ cat somefile.txt | python -mjson.tool
Expecting property name: line 1 column 2 (char 1)

Not the most useful traceback, but at least you know it's not valid.