Home About Contact

 

Build a simple REST API with Golang, Gin and MySQL

6 min read


It’s super easy to build a REST API with Go and its Gin web framework. Here comes a simple tutorial on how to build a REST API that retrieves a list of products stored in the MySQL database by GET. And also it allows the adding of product to the database by using POST.

Let’s define the API endpoints, as follows:

GET /products – Provide you a list of products, return as JSON.
GET /product/:code – Return a specific product as JSON specified by the product code

POST /products – Add a new product from request data sent as JSON

Let’s get started with coding

To begin creating a project with Go, just simply create a directory

mkdir simpleapi_go

cd into the project directory

cd simpleapi_go

Then run the go mod init simpleapi_go to create the go.mod file which contains the module’s property including the dependencies on other modules and versions of Go.

go mod init simpleapi_go
go: creating new go.mod: module simpleapi_go

Now let’s create the main.go file in the project directory, the main file with the API endpoints as follows:

package main

import (
	"simpleapi_go/models"
	"net/http"
	"github.com/gin-gonic/gin"
)

func main() {
	router := gin.Default()
	router.GET("/products", getProducts)
	router.GET("/product/:code", getProduct)
	router.POST("/products", addProduct)
	router.Run("localhost:8083")
}

So, in the above main.go, the first line is to declare its package “main”, which is always needed for a standalone program.

So, the next few lines (3 to 7) are to import the required packages which are the net/http, the remote module Gin and also our models which are stored in a local package or sub-directory called models.

So, in the main() function, we use the Gin framework to create a router and add a few of the service end points for getting list of products, getting a product by its code and add a product and map to each handler function respectively. And then have the router to run and listen to port 8083 or any other available port of your choice.

So, each of the respective handler functions in the main.go are as follows:

getProducts – the getProducts() calls the models.GetProducts function which will return an array of the Product struct from the MySQL db. If the returned result is nil, we will send a response of status not found 404, else we’ll use the super handy function Context.IndentedJSON which serializes the array of struct into JSON and add it to the response.

func getProducts(c *gin.Context) {
	products := models.GetProducts()

	if products == nil || len(products) == 0 {
		c.AbortWithStatus(http.StatusNotFound)
	} else {
		c.IndentedJSON(http.StatusOK, products)
	}
}

getProduct() – the getProduct() function returns a product which its code is specified in the request parameter “code” and it calls the respective models.GetProduct function which returns the Product struct when it’s found in the MySQL DB or nil when it’s not found or on error.

func getProduct(c *gin.Context) {
	code := c.Param("code")

	product := models.GetProduct(code)

	if product == nil {
		c.AbortWithStatus(http.StatusNotFound)
	} else {
		c.IndentedJSON(http.StatusOK, product)
	}
}

addProduct – the addProduct() function adds a product from JSON received in the request body. It uses the Context.BindJSON to bind the JSON data to the Product struct.

If there is error on binding the JSON, then it’ll just abort with a bad request, else it’ll proceed to call the models.AddProduct to insert the product to the MySQL database and then sends a response status code created (201) along with the Product as JSON, as follows:

func addProduct(c *gin.Context) {
	var prod models.Product

	if err := c.BindJSON(&prod); err != nil {
		c.AbortWithStatus(http.StatusBadRequest)
	} else {
		models.AddProduct(prod)
		c.IndentedJSON(http.StatusCreated, prod)
	}
}

The Model – Product struct – In order to keep our code clean and reusable for any future purposes, we create our Product struct in a separate package from the main. Here we have a sub-directory “models”, which is going to contain all the models and the necessary handlers for handling the persistency of the models etc.

The Product struct is contained in a file product.go as follows:

package models

type Product struct {
	Code string `json:"code"`
	Name string `json:"name"`
	Qty int `json:"qty"`
	LastUpdated string `json:"last_updated"`
}

So, the above Product struct is declared with a package models. Note that the tags such as json:”code”, json:”name” are used to specify the name of the field or property in JSON. If it’s not specified it’ll use the default struct’s capitalized property names.

Please also note we use capital letter for the Product struct and all its field names as we want all them to be exported and usable by other packages. In Go, a name that is exported must begin with a capital letter.

The Product struct corresponds to the database table structure as follows:

CREATE TABLE `product` (
  `code` varchar(20) NOT NULL DEFAULT 'x',
  `name` varchar(150) DEFAULT NULL,
  `qty` int(5) DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The Product Db handler , we also created a file productDb.go which contains the handler functions for handling retrieval and persistency of the product data in the MySQL database.

In productDb.go, we need to import the following packages:

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

We need the database/sql package, which is the generic interface for Go to deal with SQL database. And we import the the MySQL driver with an underscore in front of it, which means we import it for its side effect (intialization) i.e. registering the MySQL driver in the init() function only without importing any other functions.

The three functions in productDb.go for retrieving a list of products, inserting a new product and retrieving a product by its code are as follows:

The GetProducts function in the productDb.go, which returns an array of Product struct or nil if on error, as follows. Please take note the Go sql interface has a convenient function Scan, which allows the ease of populating the result into the Product struct.

func GetProducts() []Product {

	db, err := sql.Open("mysql", dbuser+":"+dbpass+"@tcp(127.0.0.1:3306)/"+dbname)

	// if there is an error opening the connection, handle it
	if err != nil {
		// simply print the error to the console
		fmt.Println("Err", err.Error())
		// returns nil on error
		return nil
	}

	defer db.Close()
	results, err := db.Query("SELECT * FROM product")

	if err != nil {
		fmt.Println("Err", err.Error())
		return nil
	}

	products := []Product{}
	for results.Next() {
		var prod Product
        // for each row, scan into the Product struct
		err = results.Scan(&prod.Code, &prod.Name, &prod.Qty, &prod.LastUpdated)
		if err != nil {
			panic(err.Error()) // proper error handling instead of panic in your app
		}
        // append the product into products array
		products = append(products, prod)
	}

	return products

}

The GetProduct function returns a the Product struct if it’s found or nil if its’ not found or on error.

func GetProduct(code string) *Product {

	db, err := sql.Open("mysql", dbuser+":"+dbpass+"@tcp(127.0.0.1:3306)/"+dbname)
	prod := &Product{}
	if err != nil {
		// simply print the error to the console
		fmt.Println("Err", err.Error())
		// returns nil on error
		return nil
	}
    
	defer db.Close()

	results, err := db.Query("SELECT * FROM product where code=?", code)

	if err != nil {
		fmt.Println("Err", err.Error())
		return nil
	}

	if results.Next() {
		err = results.Scan(&prod.Code, &prod.Name, &prod.Qty, &prod.LastUpdated)
		if err != nil {
			return nil
		}
	} else {

		return nil
	}

	return prod
}

The AddProduct function which takes a Product struct as its argument and insert the product into the database as follows:

func AddProduct(product Product) {

	db, err := sql.Open("mysql", dbuser+":"+dbpass+"@tcp(127.0.0.1:3306)/"+dbname)

	if err != nil {
		panic(err.Error())
	}

	// defer the close till after this function has finished
	// executing
	defer db.Close()

	insert, err := db.Query(
		"INSERT INTO product (code,name,qty,last_updated) VALUES (?,?,?, now())",
		product.Code, product.Name, product.Qty)

	// if there is an error inserting, handle it
	if err != nil {
		panic(err.Error())
	}

	defer insert.Close()

}

Please note the the Query function in Go database/sql creates a prepared statement for you. It’s similar to other programming languages, you just supply the Query function with an SQL statement with the question marks as the parameter markers and then supply the parameters accordingly, for example as follows:

db.Query("SELECT * from product WHERE code=? OR name=?", code, name)

In our code here, we use prepared statements mainly for the convenience, since there isn’t a need for additional work for checking if the parameters passed are correct for the protection of SQL injection.

Since each function above will have its connection closed after the function ends, therefore it’ll not be worth using prepared statements here. Or alternatively, you can use fmt.Sprintf to concatenate the SQL with the parameters and then pass to db.Query for example as follows:

sqlstm :=
fmt.Sprintf("INSERT INTO product (code,name,qty,last_updated)"+
" VALUES ('%s','%s',%d, now())",
product.Code, product.Name, product.Qty)

insert, err := db.Query(sqlstm)

Let’s get ready to run our REST API

So, we’ve got everything ready, we can run our Go REST API but first of all, you must run go get in your project directory as follows to install all the required packages in your imports.

go get .

And then run go run in your project directory to get your REST API running and listening for requests.

go run .

When it’s up and running you’ll see as follows:

Use cURL command to test your API

The cURL commands to test your API are as follows:

For testing GET /products

curl http://localhost:8083/products \
    --include \
    --header "Content-Type: application/json" \
    --request "GET"

For testing GET /product/:code

curl http://localhost:8083/product/P0111 \
    --include \
    --header "Content-Type: application/json" \
    --request "GET"

For testing POST /products

curl http://localhost:8083/products \
    --include \
    --header "Content-Type: application/json" \
    --request "POST" \
    --data '{"code": "P1114","name": "MacBook Air M1","qty": 10}'

That’s all for now. The complete source code of this tutorial is available on GitHub.

Spread the love
Posted on January 7, 2022 By Christopher Chee

Please leave us your comments below, if you find any errors or mistakes with this post. Or you have better idea to suggest for better result etc.

Your email address will not be published.


Our FB Twitter Our IG Copyright © 2022