Golang Server In Memory SQLite3 example

main.go

// go run main.go
// curl -XPOST "localhost:9999/person?name=ryan&age=88"
// curl -XGET "localhost:9999/person?name=ryan"

// Philosophy: Write the least amount of code to get to the DB which includes
// internal/external packages

package main

import (
	"database/sql"
	"fmt"
	"net/http"
	"strconv"
	"strings"

	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
)

type Person struct {
	Name string `db:"name" json:"name"`
	Age  int    `db:"age" json:"age"`
}

func (p Person) String() string {
	return "Name=" + p.Name + " Age=" + strconv.Itoa(p.Age)
}

type Server struct {
	DB *sqlx.DB
}

func (s *Server) HandlerPerson(w http.ResponseWriter, r *http.Request) {
	switch r.Method {
	case http.MethodGet:
		s.HandlerPersonGET(w, r)
	case http.MethodPost:
		s.HandlerPersonPOST(w, r)
	default:
		http.NotFound(w, r)
	}
}

func (s *Server) HandlerPersonGET(w http.ResponseWriter, r *http.Request) {
	name := r.URL.Query().Get("name")
	if strings.TrimSpace(name) == "" {
		fmt.Println("ERROR: No name provided")
		http.Error(w, http.StatusText(http.StatusBadRequest), http.StatusBadRequest)
		return
	}

	var person Person
	if err := s.DB.Get(&person, "SELECT * FROM person WHERE name=$1 LIMIT 1;", name); err != nil {
		if err == sql.ErrNoRows {
			fmt.Println("Person not found: " + name)
			http.Error(w, http.StatusText(http.StatusNotFound), http.StatusNotFound)
			return
		}

		fmt.Println("Failed running query:", err)
		http.Error(w, http.StatusText(http.StatusInternalServerError), http.StatusInternalServerError)
		return
	}

	fmt.Println("GET:", person)
	fmt.Fprintln(w, person)
}

func (s *Server) HandlerPersonPOST(w http.ResponseWriter, r *http.Request) {
	name := r.URL.Query().Get("name")
	if strings.TrimSpace(name) == "" {
		fmt.Println("ERROR: No name provided")
		http.Error(w, http.StatusText(http.StatusBadRequest), http.StatusBadRequest)
		return
	}

	age, err := strconv.Atoi(r.URL.Query().Get("age"))
	if err != nil {
		fmt.Println("Failed converting Atoi:", err)
		http.Error(w, http.StatusText(http.StatusInternalServerError), http.StatusInternalServerError)
		return
	}

	person := &Person{
		Name: name,
		Age:  age,
	}
	rowsCnt, err := s.DB.MustExec("INSERT INTO person (name, age) VALUES (?, ?);", person.Name, person.Age).RowsAffected()
	if err != nil {
		fmt.Println("Failed inserting:", err)
		http.Error(w, http.StatusText(http.StatusInternalServerError), http.StatusInternalServerError)
		return
	}

	if rowsCnt == 0 {
		fmt.Println("ERROR: zero rows inserted")
		http.Error(w, http.StatusText(http.StatusInternalServerError), http.StatusInternalServerError)
		return
	}

	fmt.Println("POST:", person)
	fmt.Fprintln(w, http.StatusText(http.StatusOK))
}

func main() {
	fmt.Println("Creating in memory DB...")

	db, err := sqlx.Connect("sqlite3", ":memory:")
	if err != nil {
		fmt.Println("Unable to connect to db:", err)
		return
	}

	// The server should never manipulate the database. This is for demo purposes only
	schema := `CREATE TABLE person (
    name text,
		age integer
	);`

	if _, err := db.Exec(schema); err != nil {
		fmt.Println("Unable to create table:", err)
		return
	}

	s := Server{
		DB: db,
	}

	fmt.Println("Starting server on :9999")
	http.HandleFunc("/person", s.HandlerPerson)
	http.ListenAndServe(":9999", nil)
}