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.