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:
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.
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.
