Simple Golang API + MYSQL Database tutorial
In this article I am going to show to set up a simple API that can interact with a SQL database using the golang language. I will also show how a SQL database can be set up locally. Is should be said that much of the code in this project was inspired from the following repo.
The full code used in the project can be found here, the flow of the project in terms of functionality looks as follows: main -> routes -> controller <-> model <-> config <-> utils .
MariaDB + Dependencies
To start with we need to have a database set up so that we can later interact with it. I will be using a MariaDB (AKA MySQL) here, if you do not have MySQL installed on your machine then you will need to install it first. There are many ways to install MySQL depending on the OS you are using, I found the guide here on installation very helpful. I am using the debian linux enviroment on my chromebook, and found the simplest way to install a MySQL server was using APT in my terminal. APT works by using a database of available packages, most mainstream programs are available to install by way of APT. Run the following commands in terminal to get your MariaDB (MySQL) database up and running locally.
sudo apt update
Run an update of the apt repo using super user privileges, this will ensure you have the most up to date package versions available.
sudo apt-get install mysql-server
Run this command to install the MySQL server. Note: you will be asked to set a password for the your root user, you can give one if you like (just make sure to remember it) otherwise leave this blank and press enter.
mysql -u root -p
Log into the MySQL server using the root user with the password created earlier.
CREATE DATABASE database_name;
Run this command to create a database, if successful an output saying “query OK, 1 row affected” should be seen.
With the database set up, we need to get our package dependencies. Create a project folder (I called it go-bookstore) located in the go/src folder. Navigate to the project folder from the terminal and run the following commands.
go mod init github.com/bh1995/go-bookstore
go get “github.com/jinzhu/gorm”
go get “github.com/jinzhu/gorm/dialects/mysql”
go get “github.com/gorilla/mux”
Main
We initialize the project from the main script in the terminal. We are using the package mux which has functionality for a request router and dispatcher. Here we register a route mapping to the RegisterBookStoreRoutes function which further maps the URL to handlers depending on the matching path. We could also just put all the handler functionality directly into the main function but we instead point it to a different script to keep the code more organised.
Routes
The routes script is where the logic of the handlers lives. The job of the handlers is to take in the incoming HTTP request from the mux router and find the appropriate handler based on the request path (the required function will determine the path).
Controller
The controller script will hold the functions to allow user interaction with the database. When the user wants to retrieve information, they will send a request and the database will send a response holding the requested information in the form of a JSON. When the user wants to alter data in the database, again a request will be sent and a function will be triggered to allowing the database to intemperate the information sent by the user.
The controller will be used to perform three user called functions, namely, to insert a data record, retrieve data records, delete a data record. We also allow for updating a data record, which technically is a combination of deleting a record and then inserting a new version of that record. We also allow for retrieving a record by it’s ID which is technically retrieving a subset of all records.
Model
The model script holds all functions performing the SQL quires which get or alter data in the database. The gorm package is used for the actual SQL logic, which simplifies everything as all we need to then define is the gorm database object and the variables which we want to alter or retrieve.
Config
This above is the config information used for connecting to the MySQL database. We need to insert the admin name and password as well as the database name (database is called “simplerest” above).
Interacting with the API
To run the project locally run in the terminal the main.go file. If all works you should be able to access the API from the browser via localhost:9010/book/. A testing tool like postman can be used to try out some of the functionality.
Further Ideas
In this blog a simple API was constructed using a local SQL database. The API was used for book management, but this API could be used for nearly anything. An example of a real world use case could be a logistics company using this API to post updates of GPS coordinates and time information every second. This API could be run on a cloud based VM, a micro processor in a moving vehicle could then send requests to post every second to the server the API is running on. The small MySQL database on the VM may once a day insert all records into a larger cloud based database and thereafter truncate all of it’s own data. This could for example be done using a task scheduler on the VM which runs a simple stored procedure. The larger database could then be used as a source for all master data pertaining to vehicle movement.
