API REST with Go and MySQL

In this article about programming in the Go language (also known as Golang) we will see how to create a REST API that communicates through JSON, saving and displaying data from a MySQL / MariaDB database.

In the end we will have a REST API with Go using the 4 methods: POST, PUT, DELETE and GET using the Mux router. We are also going to implement the 4 operations of a database: insert, update, delete and select.

I will leave the full open source code at the end of the post.

Database

We are going to consume a video game database. Therefore remember to create a database with the name of your preference (I have called it video_games) and place the following table inside:

CREATE TABLE video_games
(
    id    bigint unsigned not null primary key auto_increment,
    name  VARCHAR(255)    NOT NULL,
    genre VARCHAR(255)    NOT NULL,
    year  INTEGER         NOT NULL
);

The data we are going to handle is id as bigint which will become an int64 when mapping it for Go. The name and genre will be of type varchar, converted to string in Go. And finally the year will be an integer of type int64 for golang.

Setting up database access credentials

The first thing you have to do is configure the access credentials to the database in the file called .env. If it doesn’t exist, you must create it based on the .env.example file. In my case it looks like this:

user="root"
pass=""
host="localhost"
port="3306"
db_name="video_games"

From the file we can notice several things; all related to the access credentials to the MySQL database.

In this case my user is root, I don’t have a password; the host is localhost on port 3306 and the name of the database is video_games (remember to create it from the MySQL console or phpmyadmin).

The environment file will be readed and loaded into the variables. In the variables file we also define the domain allowed for CORS, because in this way we can consume this API from another domain:

package main

import (
 "fmt"
 "github.com/joho/godotenv"
 "os"
)

var _ = godotenv.Load(".env") // Cargar del archivo llamado ".env"
var (
 ConnectionString = fmt.Sprintf("%s:%s@tcp(%s:%s)/%s",
  os.Getenv("user"),
  os.Getenv("pass"),
  os.Getenv("host"),
  os.Getenv("port"),
  os.Getenv("db_name"))
)

const AllowedCORSDomain = "http://localhost"

As you can see, in line 10 we are defining the connection string to the database. Finally we define the function that will help us connect to MySQL from Go:

package main

import (
 "database/sql"
 _ "github.com/go-sql-driver/mysql"
)

func getDB() (*sql.DB, error) {
 return sql.Open("mysql", ConnectionString)
}

Remember that I already showed you how to connect MySQL with Go, but the example was by using the console.

Useful structs and functions

In all this API that we are creating we are going to handle video games to exemplify it in a simple way.

To do this, we define the Videogame struct that has the same fields that we define in the database. What is between backticks is to indicate the name that the property will have when it is read and decoded from JSON.

type VideoGame struct {
 Id    int64  `json:"id"`
 Name  string `json:"name"`
 Genre string `json:"genre"`
 Year  int64  `json:"year"`
}

In addition to the struct we define some tools, for example, a function to convert from string to int64 that will help us read variables from the URL and convert them to integer.

package main

import "strconv"

func stringToInt64(s string) (int64, error) {
 numero, err := strconv.ParseInt(s, 0, 64)
 if err != nil {
  return 0, err
 }
 return numero, err
}

The controller

I don’t plan on using any methodology or things like that; but I have placed all the operations of the model (the CRUD) in the same file. It looks like this:

package main

func createVideoGame(videoGame VideoGame) error {
 bd, err := getDB()
 if err != nil {
  return err
 }
 _, err = bd.Exec("INSERT INTO video_games (name, genre, year) VALUES (?, ?, ?)", videoGame.Name, videoGame.Genre, videoGame.Year)
 return err
}

func deleteVideoGame(id int64) error {

 bd, err := getDB()
 if err != nil {
  return err
 }
 _, err = bd.Exec("DELETE FROM video_games WHERE id = ?", id)
 return err
}

// It takes the ID to make the update
func updateVideoGame(videoGame VideoGame) error {
 bd, err := getDB()
 if err != nil {
  return err
 }
 _, err = bd.Exec("UPDATE video_games SET name = ?, genre = ?, year = ? WHERE id = ?", videoGame.Name, videoGame.Genre, videoGame.Year, videoGame.Id)
 return err
}
func getVideoGames() ([]VideoGame, error) {
 //Declare an array because if there's error, we return it empty
 videoGames := []VideoGame{}
 bd, err := getDB()
 if err != nil {
  return videoGames, err
 }
 // Get rows so we can iterate them
 rows, err := bd.Query("SELECT id, name, genre, year FROM video_games")
 if err != nil {
  return videoGames, err
 }
 // Iterate rows...
 for rows.Next() {
  // In each step, scan one row
  var videoGame VideoGame
  err = rows.Scan(&videoGame.Id, &videoGame.Name, &videoGame.Genre, &videoGame.Year)
  if err != nil {
   return videoGames, err
  }
  // and append it to the array
  videoGames = append(videoGames, videoGame)
 }
 return videoGames, nil
}

func getVideoGameById(id int64) (VideoGame, error) {
 var videoGame VideoGame
 bd, err := getDB()
 if err != nil {
  return videoGame, err
 }
 row := bd.QueryRow("SELECT id, name, genre, year FROM video_games WHERE id = ?", id)
 err = row.Scan(&videoGame.Id, &videoGame.Name, &videoGame.Genre, &videoGame.Year)
 if err != nil {
  return videoGame, err
 }
 // Success!
 return videoGame, nil
}

All functions return at least one argument that is of type error, so that possible failures found in the execution can be handled. Furthermore, we are avoiding SQL injections because if you notice we use placeholders instead of concatenating strings.

By the way, all functions receive or return the VideoGame data type that we saw earlier. The functions are:

• createVideoGame – Insert a video game into the MySQL database
• deleteVideoGame – Delete a video game by its id using Go
• updateVideoGame – Update a video game in the database
• getVideoGames – Get all existing video games as an array of type VideoGame
• getVideoGameById – Get a video game by id

These functions will be exposed with the router, that’s where we are going to connect to MySQL with Go to expose or fetch the data.

Defining API routes with Go

We finally get to the point where we are going to expose our controller and database through an API using Go. We define them within a function that the router receives, in this way we make things simpler and separate concepts.

package main

import (
 "encoding/json"
 "github.com/gorilla/mux"
 "net/http"
)

func setupRoutesForVideoGames(router *mux.Router) {
 // First enable CORS. If you don't need cors, comment the next line
 enableCORS(router)

 router.HandleFunc("/videogames", func(w http.ResponseWriter, r *http.Request) {
  videoGames, err := getVideoGames()
  if err == nil {
   respondWithSuccess(videoGames, w)
  } else {
   respondWithError(err, w)
  }
 }).Methods(http.MethodGet)
 router.HandleFunc("/videogame/{id}", func(w http.ResponseWriter, r *http.Request) {
  idAsString := mux.Vars(r)["id"]
  id, err := stringToInt64(idAsString)
  if err != nil {
   respondWithError(err, w)
   // We return, so we stop the function flow
   return
  }
  videogame, err := getVideoGameById(id)
  if err != nil {
   respondWithError(err, w)
  } else {
   respondWithSuccess(videogame, w)
  }
 }).Methods(http.MethodGet)

 router.HandleFunc("/videogame", func(w http.ResponseWriter, r *http.Request) {
  // Declare a var so we can decode json into it
  var videoGame VideoGame
  err := json.NewDecoder(r.Body).Decode(&videoGame)
  if err != nil {
   respondWithError(err, w)
  } else {
   err := createVideoGame(videoGame)
   if err != nil {
    respondWithError(err, w)
   } else {
    respondWithSuccess(true, w)
   }
  }
 }).Methods(http.MethodPost)

 router.HandleFunc("/videogame", func(w http.ResponseWriter, r *http.Request) {
  // Declare a var so we can decode json into it
  var videoGame VideoGame
  err := json.NewDecoder(r.Body).Decode(&videoGame)
  if err != nil {
   respondWithError(err, w)
  } else {
   err := updateVideoGame(videoGame)
   if err != nil {
    respondWithError(err, w)
   } else {
    respondWithSuccess(true, w)
   }
  }
 }).Methods(http.MethodPut)
 router.HandleFunc("/videogame/{id}", func(w http.ResponseWriter, r *http.Request) {
  idAsString := mux.Vars(r)["id"]
  id, err := stringToInt64(idAsString)
  if err != nil {
   respondWithError(err, w)
   // We return, so we stop the function flow
   return
  }
  err = deleteVideoGame(id)
  if err != nil {
   respondWithError(err, w)
  } else {
   respondWithSuccess(true, w)
  }
 }).Methods(http.MethodDelete)
}

Basically it is to configure the endpoints of the API created with Go, invoking the controller functions. Let’s look at the enableCORS, respondWithSuccess, and respondWithError functions below.

An important thing is the part where the whole body of the request is decoded from JSON to the struct, which you can see in line 40, we read the request from r.Body and then we decode it inside the variable created earlier.

We do the same (decode JSON of the request) to do the update, that in line 56.

CORS middleware

CORS allows you to share resources in different domains. That is, it allows you to consume localhost: 8000 from localhost: 80 or similar; something like if it was possible to consume facebook.com from google.com

If you won’t be using this feature, just comment out the code that uses it on the router. What we are going to do is add headers that will allow CORS for the domain previously configured in the variables.

To achieve this, we are going to intercept all requests to add these headers.

func enableCORS(router *mux.Router) {
 router.PathPrefix("/").HandlerFunc(func(w http.ResponseWriter, req *http.Request) {
  w.Header().Set("Access-Control-Allow-Origin", AllowedCORSDomain)
 }).Methods(http.MethodOptions)
 router.Use(middlewareCors)
}
func middlewareCors(next http.Handler) http.Handler {
 return http.HandlerFunc(
  func(w http.ResponseWriter, req *http.Request) {
   // Just put some headers to allow CORS...
   w.Header().Set("Access-Control-Allow-Origin", AllowedCORSDomain)
   w.Header().Set("Access-Control-Allow-Credentials", "true")
   w.Header().Set("Access-Control-Allow-Methods", "POST, GET, OPTIONS, PUT, DELETE")
   w.Header().Set("Access-Control-Allow-Headers", "Accept, Content-Type, Content-Length, Accept-Encoding, X-CSRF-Token, Authorization")
   // and call next handler!
   next.ServeHTTP(w, req)
  })
}

Answering to the HTTP client

We also have two functions that will help us respond to the client that consumes the API; one is to respond successfully (HTTP code 200) and another is to respond with error (code 500).

The one that responds with error receives the error and displays it as a string. The function that responds successfully receives a parameter of type interface, which is something like a generic data type in Go. Both functions encode the response as JSON.

// Helper functions for respond with 200 or 500 code
func respondWithError(err error, w http.ResponseWriter) {
 w.WriteHeader(http.StatusInternalServerError)
 json.NewEncoder(w).Encode(err.Error())
}

func respondWithSuccess(data interface{}, w http.ResponseWriter) {

 w.WriteHeader(http.StatusOK)
 json.NewEncoder(w).Encode(data)
}

Putting it all together

To finish this tutorial, let’s see what the main method looks like. This method is the entry point of the programs in Go, therefore in this function is where we ping the database, configure the routes and turn on the server on the specified port.

package main

import (
 "github.com/gorilla/mux"
 "log"
 "net/http"
 "time"
)

func main() {
 // Ping database
 bd, err := getDB()
 if err != nil {
  log.Printf("Error with database" + err.Error())
  return
 } else {
  err = bd.Ping()
  if err != nil {
   log.Printf("Error making connection to DB. Please check credentials. The error is: " + err.Error())
   return
  }
 }
 // Define routes
 router := mux.NewRouter()
 setupRoutesForVideoGames(router)
 // .. here you can define more routes
 // ...
 // for example setupRoutesForGenres(router)

 // Setup and start server
 port := ":8000"

 server := &http.Server{
  Handler: router,
  Addr:    port,
  // timeouts so the server never waits forever...
  WriteTimeout: 15 * time.Second,
  ReadTimeout:  15 * time.Second,
 }
 log.Printf("Server started at %s", port)
 log.Fatal(server.ListenAndServe())
}

Remember that to compile you must have installed Go. After that, in the directory where the code is, run go build and run the file that appears. If the firewall asks for permissions, give them. In the same way I have written some documentation in the readme of the project.

You can test the API from any programming language or using Postman as I have done in my video (in spanish):

Here is the complete code, as always, on my GitHub. I also invite you to read more about Go on my blog.