Create a gist now

Instantly share code, notes, and snippets.

Embed
Golang http server with sql null values and json
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)
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment