databases

README

Databases

One of the most asked questions I get about web development in Go is how to connect to a SQL database. Thankfully, Go has a fantastic SQL package in the standard library that allows us to use a whole slew of drivers for different SQL databases. In this example we will connect to a SQLite database, but the syntax (minus some small SQL semantics) is the same for a MySQL or PostgreSQL database.

Below is an example web application that has two endpoints: POST /books to create a book and GET /books to show all books that have been created.

To create a new book, run:

curl -vX POST http://localhost:8080/books \
--header "Content-Type: application/json" \
-d "{\"author\": \"George Orwell\", \"title\": \"1984\"}"

To create another:

curl -vX POST http://localhost:8080/books \
--header "Content-Type: application/json" \
-d "{\"author\": \"Victor Hugo\", \"title\": \"Les Misérables\"}"

To view all books:

curl -v http://localhost:8080/books --header "Content-Type: application/json"

package main

import (
	"database/sql"
	"encoding/json"
	"log"
	"net/http"

	"github.com/julienschmidt/httprouter"
	_ "github.com/mattn/go-sqlite3"
)

type book struct {
	Author string `json:"author"`
	Title  string `json:"title"`
}

type errResponse struct {
	Message string `json:"message"`
}

func main() {
	db, err := newDB()
	if err != nil {
		log.Fatalln("Could not connect to database")
	}

	r := httprouter.New()
	r.GET("/books", getHandler(db))
	r.POST("/books", postHandler(db))

	log.Println("Listening on :8080")
	http.ListenAndServe(":8080", r)
}

func getHandler(db *sql.DB) httprouter.Handle {
	return func(rw http.ResponseWriter, r *http.Request, p httprouter.Params) {
		books, err := getBooks(db)
		if err != nil {
			respondError(rw, err)
			return
		}

		rw.Header().Set("Content-Type", "application/json")

		if err := json.NewEncoder(rw).Encode(books); err != nil {
			respondError(rw, err)
			return
		}
	}
}

func postHandler(db *sql.DB) httprouter.Handle {
	return func(rw http.ResponseWriter, r *http.Request, p httprouter.Params) {
		var b book
		if err := json.NewDecoder(r.Body).Decode(&b); err != nil {
			respondError(rw, err)
			return
		}

		if err := createBook(db, b); err != nil {
			respondError(rw, err)
			return
		}

		rw.WriteHeader(http.StatusNoContent)
	}
}

func newDB() (*sql.DB, error) {
	db, err := sql.Open("sqlite3", "example.sqlite")
	if err != nil {
		log.Println(err)
		return nil, err
	}

	q := "CREATE TABLE IF NOT EXISTS books(title TEXT, author TEXT)"
	if _, err := db.Exec(q); err != nil {
		log.Println(err)
		return nil, err
	}

	return db, nil
}

func getBooks(db *sql.DB) ([]book, error) {
	q := "SELECT title, author FROM books"
	rows, err := db.Query(q)
	if err != nil {
		log.Println(err)
		return nil, err
	}

	var books []book
	for rows.Next() {
		var b book
		if err := rows.Scan(&b.Title, &b.Author); err != nil {
			log.Println(err)
			return nil, err
		}

		books = append(books, b)
	}

	return books, nil
}

func createBook(db *sql.DB, b book) error {
	q := "INSERT INTO books(title, author) VALUES ($1, $2)"
	if _, err := db.Exec(q, b.Title, b.Author); err != nil {
		log.Println(err)
		return err
	}

	return nil
}

func respondError(rw http.ResponseWriter, err error) {
	rw.Header().Set("Content-Type", "application/json")
	rw.WriteHeader(http.StatusInternalServerError)

	er := errResponse{
		Message: err.Error(),
	}

	if err := json.NewEncoder(rw).Encode(er); err != nil {
		log.Println(err)
	}
}

Exercises

1. Make use of the Query function on our sql.DB instance to extract a collection of rows and map them to structs.
2. Add the ability to insert new records into our database by using an HTML form.
3. go get github.com/jmoiron/sqlx and observe the improvements made over the existing database/sql package in the standard library.