Category: Misc

Welcome again! After learning some Golang, my next experiment was to build APIs. So, here’s a blog-post for someone interested in trying it out. The only prerequisite to building RESTful APIs is to know the basics of Golang along with some SQL. Link to my blog-post series on Introduction to Golang may come handy.

The code can be broadly divided into two files:

• migrate.go: Connects to MySQL and creates the necessary tables.
• base.go: Contains all the API handlers.

Step 1: Establishing Database Connection

We will now be working on the migrate.go file. Certain packages are to be imported before we can start over.

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

The package sql provide an interface for the SQL database. In other words, it equips Golang to work with database and create a connection, executing queries and perform other SQL operations. The sql package works along with a database driver. We have used Go’s mysql driver in this case: go-sql-driver/mysql.

db, err := sql.Open("mysql","root:password@(127.0.0.1:3306)/gotest")
if err != nil {
fmt.Println(err.Error())
} else {
fmt.Println("Connection established successfully")
}
defer db.Close()


sql.Open() opens a database connection to MySQL. This function accepts two parameters – the database used (mysql in this case) and the connection details in the format: username: password@hostname/database_name. Here ‘gotest‘ is the database created to run this project. Appropriate Error message is shown when the connection fails. Defer is used to wait until all the surrounding functions return. Only then is the connection  to the database closed.

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


In case the machine is not reachable at all,  sql.Open() does not throw an error. Hence we need to explicitly ping the machine in order to verify the same.

STEP 2: Creating Tables

stmt, err := db.Prepare("CREATE TABLE emp (id int NOT NULL AUTO_INCREMENT, first_name varchar(40), last_name varchar(40), PRIMARY KEY(id));")
if err != nil {
fmt.Println(err.Error())
}
_,err = stmt.Exec()
if err != nil {
fmt.Println(err.Error())
} else {
fmt.Println("emp table migration successful")
}


Now let’s create the table ‘emp‘ using db.Prepare(). Just to recap, ‘db’ is the handler we obtained after connecting to the database. The SQL create statement is provided as parameter. It returns a handler which is then executed using Exec() function. It also returns an error if the function fails. Error handling is coded accordingly in the last few lines.

STEP 3: Building the APIs

We now have everything in-place to start building the APIs. Create the file base.go. We will be importing some new libraries apart from those we have already come across. “Gin” is a HTTP web framework written in golang. The package “bytes” implements functions for manipulating byte slices. “net/http” provides implementations for Client Server Communication.

import (
"fmt"
"database/sql"
"bytes"
"net/http"
"encoding/json"
"github.com/gin-gonic/gin"
_ "github.com/go-sql-driver/mysql"
)


Next, create a database connection using sql.Open() along with implementation of  error handling.

func main() {
db, err := sql.Open("mysql","root:pwd@(127.0.0.1:3306)/gotest")
if err != nil {
fmt.Println(err.Error())
} else {
fmt.Println("Connection established successfully")
}
defer db.Close()
//Checking for connection:
err = db.Ping()
if err != nil {
fmt.Println(err.Error())
}


Create a golang type called Emp which will hold each row from the database and help in processing them. The type is a carbon copy for a typical row in the database containing “id”, “first_name” and “last_name” as its variables.

type Emp struct {
id int
first_name string
last_name string
}


Step 4: The GET request

A simple GET request can be created with the help of Gin which creates a handler to place a http GET request. It takes as input the URL string which is to be matched and function to handle the data returned.

router.GET("/emp/:id", func(c *gin.Context) {
var (
emp Emp
result gin.H
)
id := c.Param("id")
row := db.QueryRow("select id,first_name,last_name from emp where id = ?;",id)
err := row.Scan(&emp.id,&emp.first_name,&emp.last_name)
if err != nil {
//if no results found send Nill
result = gin.H{
"result": nil,
"count": 0,
}} else {
result = gin.H{
"result": gin.H{
"id": emp.id,
"first_name": emp.first_name,
"last_name": emp.last_name,
},
"count": 1,
}}
c.JSON(http.StatusOK, result)
})


 

Here the handler will match /emp/1/ but will not match /emp. The function stores the id, first name and last name into the type Emp by querying the database with the id obtained from the user in the GET request. The result string is constructed and passed as a JSON which serves as the output.

Similarly,

• A GET request can be constructed to obtain all the employee data in the database.
• POST request can be used to insert a new record or
• PUT can modify an existing record.
• You can similarly delete one or more records using the DELETE request.

Code for each of the above APIs can be found on my github.

Finally, let us see the above APIs in Action.

Here’s an example of a PUT request. We are here updating the first & last name of employee #2. The port in use is 3000. Note the message shown on successful execution.

Let’s now verify if the name of employee #2 was successfully modified as claimed by the above output. Here’s the GET request:

The entire code can be found on: Github/RESTful-API-with-Golang-and-MySql