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.


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:

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:

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:

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:

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.

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.

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:

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.

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.

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.

Putting it all together

API REST with Go and MySQL encoding with JSON

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.

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.

I am available for hiring if you need help! I can help you with your project or homework feel free to contact me.
If you liked the post, show your appreciation by sharing it, or making a donation

Leave a Comment

Your email address will not be published. Required fields are marked *