go gcsql

main.go

package main

import (
	"database/sql"
	"database/sql/driver"
	"encoding/json"
	"fmt"
	"io/ioutil"
	"log"
	"net/http"
	"os"

	"github.com/go-sql-driver/mysql"
)

func main() {

	var err error
	DB, err = configureCloudSQL(cloudSQLConfig{
		Username: os.Getenv("MYSQL_USER"),
		Password: os.Getenv("MYSQL_PASSWORD"),
	})

	if err != nil {
		panic(err)
	}

	http.HandleFunc("/guest", handle)
	http.HandleFunc("/healthz", healthCheckHandler)
	log.Print("Listening on port 8080")
	log.Fatal(http.ListenAndServe(":8080", nil))
}

func handle(w http.ResponseWriter, r *http.Request) {
	if r.URL.Path != "/guest" {
		http.NotFound(w, r)
		return
	}

	switch r.Method {
	case "GET":
		entries, err := DB.Entries()
		if err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		b, err := json.Marshal(entries)
		if err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		fmt.Fprint(w, string(b))
		return
	case "POST":
		body, err := ioutil.ReadAll(r.Body)
		if err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		defer r.Body.Close()

		var entry Entry

		if err = json.Unmarshal(body, &entry); err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		fmt.Printf("%+#v\n", entry)
		if err = DB.Insert(entry); err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		fmt.Fprint(w, "Ok!")
		return
	}

	http.NotFound(w, r)
}

func healthCheckHandler(w http.ResponseWriter, r *http.Request) {
	if err := DB.Ping(); err != nil {
		http.Error(w, err.Error(), 504)
		return
	}
	fmt.Fprint(w, "ok")
}

// Database

type Entry struct {
	EntryID   int64  `json:"-"`
	GuestName string `json:"guestName"`
	Content   string `json:"content"`
}

type EntriesDatabase interface {
	Ping() error
	Entries() ([]*Entry, error)
	Insert(geust Entry) error
	Close()
}

var DB EntriesDatabase

type cloudSQLConfig struct {
	Username, Password string
}

func configureCloudSQL(config cloudSQLConfig) (EntriesDatabase, error) {
	// Running locally.
	return newMySQLDB(MySQLConfig{
		Username: config.Username,
		Password: config.Password,
		Host:     "localhost",
		Port:     3306,
	})
}

var createTableStatements = []string{
	`CREATE DATABASE IF NOT EXISTS guestbook DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci';`,
	`USE guestbook;`,
	`CREATE TABLE entries (
		guestName VARCHAR(255),
		content VARCHAR(255),
		entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID)
	);`,
	// `INSERT INTO entries (guestName, content) values ("first guest", "I got here!");`,
	// `INSERT INTO entries (guestName, content) values ("second guest", "Me too!");`,
}

const insertStatement = `INSERT INTO entries (guestName, content) VALUES (?, ?)`

const listStatement = `SELECT guestName, content FROM entries ORDER BY guestName`

// ensureTableExists checks the table exists. If not, it creates it.
func (config MySQLConfig) ensureTableExists() error {

	conn, err := sql.Open("mysql", config.dataStoreName(""))
	if err != nil {
		return fmt.Errorf("mysql: could not get a connection: %v", err)
	}
	defer conn.Close()

	// Check the connection.
	if conn.Ping() == driver.ErrBadConn {
		return fmt.Errorf("mysql: could not connect to the database. " +
			"could be bad address, or this address is not whitelisted for access.")
	}

	if _, err := conn.Exec("USE guestbook"); err != nil {
		// MySQL error 1049 is "database does not exist"
		if mErr, ok := err.(*mysql.MySQLError); ok && mErr.Number == 1049 {
			return createTable(conn)
		}
	}

	if _, err := conn.Exec("DESCRIBE entries"); err != nil {
		// MySQL error 1146 is "table does not exist"
		if mErr, ok := err.(*mysql.MySQLError); ok && mErr.Number == 1146 {
			return createTable(conn)
		}
		// Unknown error.
		return fmt.Errorf("mysql: could not connect to the database: %v", err)
	}
	return nil
}

// createTable creates the table, and if necessary, the database.
func createTable(conn *sql.DB) error {
	for _, stmt := range createTableStatements {
		_, err := conn.Exec(stmt)
		if err != nil {
			return err
		}
	}
	return nil
}

type MySQLConfig struct {
	Username, Password string

	// Host of the MySQL instance.
	Host string

	// Port of the MySQL instance.
	Port int
}

// dataStoreName returns a connection string suitable for sql.Open.
func (c MySQLConfig) dataStoreName(databaseName string) string {
	var cred string
	// [username[:password]@]
	if c.Username != "" {
		cred = c.Username
		if c.Password != "" {
			cred = cred + ":" + c.Password
		}
		cred = cred + "@"
	}

	return fmt.Sprintf("%stcp([%s]:%d)/%s", cred, c.Host, c.Port, databaseName)
}

type mysqlDB struct {
	conn *sql.DB

	list   *sql.Stmt
	insert *sql.Stmt
}

// Ensure mysqlDB conforms to the BookDatabase interface.
var _ EntriesDatabase = &mysqlDB{}

func newMySQLDB(config MySQLConfig) (EntriesDatabase, error) {
	// Check database and table exists. If not, create it.
	if err := config.ensureTableExists(); err != nil {
		return nil, err
	}

	conn, err := sql.Open("mysql", config.dataStoreName("guestbook"))
	if err != nil {
		return nil, fmt.Errorf("mysql: could not get a connection: %v", err)
	}
	if err := conn.Ping(); err != nil {
		conn.Close()
		return nil, fmt.Errorf("mysql: could not establish a good connection: %v", err)
	}

	db := &mysqlDB{
		conn: conn,
	}

	// Prepared statements. The actual SQL queries are in the code near the
	// relevant method (e.g. addBook).
	if db.list, err = conn.Prepare(listStatement); err != nil {
		return nil, fmt.Errorf("mysql: prepare list: %v", err)
	}
	if db.insert, err = conn.Prepare(insertStatement); err != nil {
		return nil, fmt.Errorf("mysql: prepare insert: %v", err)
	}

	return db, nil
}

func (db *mysqlDB) Ping() error {
	if db.conn.Ping() == driver.ErrBadConn {
		return fmt.Errorf("mysql: could not connect to the database. "+
			"could be bad address, or this address is not whitelisted for access.", 504)
	}

	return nil
}

func (db *mysqlDB) Entries() ([]*Entry, error) {
	rows, err := db.list.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var entries []*Entry
	for rows.Next() {
		entry, err := scanEntry(rows)
		if err != nil {
			return nil, fmt.Errorf("mysql: could not read row: %v", err)
		}

		entries = append(entries, entry)
	}

	return entries, nil
}

func (db *mysqlDB) Insert(entry Entry) error {
	r, err := db.insert.Exec(entry.GuestName, entry.Content)
	if err != nil {
		return fmt.Errorf("mysql: could not execute statement: %v", err)
	}
	rowsAffected, err := r.RowsAffected()
	if err != nil {
		return fmt.Errorf("mysql: could not get rows affected: %v", err)
	} else if rowsAffected != 1 {
		return fmt.Errorf("mysql: expected 1 row affected, got %d", rowsAffected)
	}
	return nil
}

func (db *mysqlDB) Close() {
	db.conn.Close()
}

// rowScanner is implemented by sql.Row and sql.Rows
type rowScanner interface {
	Scan(dest ...interface{}) error
}

// scanEntry reads a Entry from a sql.Row or sql.Rows
func scanEntry(s rowScanner) (*Entry, error) {
	var (
		entryID   int64
		guestName sql.NullString
		content   sql.NullString
	)
	if err := s.Scan(&guestName, &content); err != nil {
		return nil, err
	}

	entry := &Entry{
		EntryID:   entryID,
		GuestName: guestName.String,
		Content:   content.String,
	}
	return entry, nil
}