Building a Go(lang) API with echo and MySQL

In this post we'll have a look at how to build a MySQL backed API with Go and echo. This is part of the series that explains how we did photographerexcuses.com

The site is a Vue.js single page app, that gets its data (the excuses) from the Go API. We are storing the excuses in MySQL, because we can easily edit them at the same time with MySQL workbench or similar tools that don't require flat files, git or other methods that would be more annoying to deal with.

Picking a Golang web framework

Let's start by setting up the main.go file and popuplating it with bootstrapping echo and registering the correct routes. Note that for now we're importing database/sql prepended by a _ to make the compiler overlook that we're not using it just yet.

package main

import (
  _ "database/sql"
  "fmt"
  "net/http"

  _ "github.com/go-sql-driver/mysql"
  "github.com/labstack/echo"
  "github.com/labstack/echo/middleware"
  "github.com/JonathanMH/goClacks/echo"
)

func main() {
  // Echo instance
  e := echo.New()
  e.Use(goClacks.Terrify) // optional ;)

  // Middleware
  e.Use(middleware.Logger())
  e.Use(middleware.Recover())

  e.Use(middleware.CORSWithConfig(middleware.CORSConfig{
    AllowOrigins: []string{"*"},
    AllowMethods: []string{echo.GET, echo.PUT, echo.POST, echo.DELETE},
  }))

  // Route => handler
  e.GET("/", func(c echo.Context) error {

    return c.JSON(http.StatusOK, "Hi!")
  })

  e.GET("/id/:id", func(c echo.Context) error {
    requested_id := c.Param("id")
    fmt.Println(requested_id);
    return c.JSON(http.StatusOK, requested_id)
  })

  e.Logger.Fatal(e.Start(":4000"))
}

Note that the middleware.CORSWithConfig may not be necessary for you, depending on how you set up your frontend code. I recommend having it on for development at least.

If you open localhost:4000 you should see the string "Hi!", if you visit localhost:4000/id/42, you should see "42". That's exactly what we want, since we want to be able to either get a random result or a specific ID to have linkable excuses.

Go JSON response type

type (
  Excuse struct {
    Error string `json:"error"`
    Id string `json:"id"`
    Quote string `json:"quote"`
  }
)

Since error handling is an important part of, well, everything, we'll start by defining a field for the Error or status if you will. In our later frontend code (future blog post) we'll check for that field to see if all our SQL queries went smoothly.

Selecting a Random Row with SQL in Go

var quote string;
var id string;
err = db.QueryRow("SELECT id, quote FROM excuses ORDER BY RAND() LIMIT 1").Scan(&id, &quote)

This snippet shows how to select a random entry from the database table excuses and with .Scan() we write it to the initiated variables.

Next we use the struct Excuse to set the variables as values to the correct fields and return a c , for the echo context, JSON() with a status code of 200 and our now populated response:

response := Excuse{Id: id, Error: "false", Quote: quote}
return c.JSON(http.StatusOK, response)

That will result in the following HTTP response using httpie:

(master)⚡ % http localhost:3131

HTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Length: 78
Content-Type: application/json; charset=utf-8
Date: Sat, 01 Apr 2017 12:51:01 GMT
Vary: Origin
X-Clacks-Overhead: GNU Terry Pratchett

{
    "error": "false",
    "id": "48",
    "quote": "This is just how the industry works now."
}

The X-Clacks-Overhead: GNU Terry Pratchett comes from the middleware collection I wrote for adding this nerdy relic in honor of the fantasy author: goClacks.

Putting it all together

CREATE TABLE `excuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quote` text,
  `author` varchar(191) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Filling in the rest of the blanks, without some parts of the error handling your code could look like the last snippet in the end. Note that you can also re-use the MySQL connection instead of opening a new connection for every incoming request.

Fun tasks you play around with after doing something like this:

• Keeping a cache around of recently queried database objects and checking an in memory map before starting another SQL query
• rate limiting by IP address.

For the next blog post, we're going to have a look at how to deploy Go web apps (without docker) or other elaborate build setups, please let me know what you think of code and post in the comments!

See the simplified, but full version below:

package main

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

  _ "github.com/go-sql-driver/mysql"
  "github.com/labstack/echo"
  "github.com/labstack/echo/middleware"
  "github.com/JonathanMH/goClacks/echo"
)

type (
  Excuse struct {
    Error string `json:"error"`
    Id string `json:"id"`
    Quote string `json:"quote"`
  }
)

func main() {
  // Echo instance
  e := echo.New()
  e.Use(goClacks.Terrify)

  // Middleware
  e.Use(middleware.Logger())
  e.Use(middleware.Recover())

  e.Use(middleware.CORSWithConfig(middleware.CORSConfig{
    AllowOrigins: []string{"*"},
    AllowMethods: []string{echo.GET, echo.PUT, echo.POST, echo.DELETE},
  }))

  // Route => handler
  e.GET("/", func(c echo.Context) error {
    db, err := sql.Open("mysql", "db_user:db_password@tcp(SERVER_IP:PORT)/database_name")

    if err != nil {
      fmt.Println(err.Error())
      response := Excuse{Id: "", Error: "true", Quote: ""}
      return c.JSON(http.StatusInternalServerError, response)
    }
    defer db.Close()

    var quote string;
    var id string;
    err = db.QueryRow("SELECT id, quote FROM excuses ORDER BY RAND() LIMIT 1").Scan(&id, &quote)

    if err != nil {
      fmt.Println(err)
    }

    fmt.Println(quote);
    response := Excuse{Id: id, Error: "false", Quote: quote}
    return c.JSON(http.StatusOK, response)
  })

  e.GET("/id/:id", func(c echo.Context) error {
    requested_id := c.Param("id")
    fmt.Println(requested_id);
      db, err := sql.Open("mysql", "db_user:db_password@tcp(SERVER_IP:PORT)/database_name")

    if err != nil {
      fmt.Println(err.Error())
      response := Excuse{Id: "", Error: "true", Quote: ""}
      return c.JSON(http.StatusInternalServerError, response)
    }
    defer db.Close()

    var quote string;
    var id string;
    err = db.QueryRow("SELECT id, quote FROM excuses WHERE id = ?", requested_id).Scan(&id, &quote)

    if err != nil {
      fmt.Println(err)
    }

    response := Excuse{Id: id, Error: "false", Quote: quote}
    return c.JSON(http.StatusOK, response)
  })

  e.Logger.Fatal(e.Start(":4000"))
}