Last active
February 6, 2025 00:09
-
-
Save stephen-mw/f55cb15f1d097f0fc3a81221a5835804 to your computer and use it in GitHub Desktop.
Golang http server with sql null values and json
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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