An Application Programming Interface (API) is an intermediary software that allows your different applications to talk to each other. Located on your server, an API forwards your frontend clients' requests to a data provider like MySQL and responds in a standard JavaScript Object Notation (JSON) format. The frontend client can be a mobile app, desktop application, or web-based software.
An API automates your business workflows and makes your development cycle less costly as different applications can reuse your backend. On the marketing side, an API makes the personalization and adaptation of your software easier because third-party applications can consume your services by writing just a few lines of code.
In this tutorial, you'll create a modern API with Golang and MySQL 8 database servers. You'll use the API to expose products and categories resources from a sample store. Your final application will accept all Create, Read, Update, and Delete (CRUD) operations using different HTTP methods. While you can create the API with other server-side scripting languages, Golang is fast, easy to learn, scalable, and ships with comprehensive development tools, including a built-in web server.
Before you proceed with this Golang API tutorial, you need:
In this sample application, you'll permanently store products and categories data on a MySQL database. Then, you'll write Golang scripts that will access the data using a dedicated MySQL user account. SSH to your server and execute the following steps to set up the database and user account.
Log in to your MySQL database server.
$ sudo mysql -u root -p
Enter your root password for the database server when prompted and press ENTER to proceed. Then, create a sample go_db database and a go_db_user account. Replace EXAMPLE_PASSWORD with a strong value.
mysql > CREATE DATABASE go_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'go_db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
GRANT ALL PRIVILEGES ON go_db.* TO 'go_db_user'@'localhost';
FLUSH PRIVILEGES;
Switch to the new go_db database.
mysql > USE go_db;
Create the products table. This table stores the products' information including the product_id(primary key), product_name, category_id, and retail_price. These are just a few mandatory columns you'll need when developing a point of sale or e-commerce software. In a production environment, you may add other fields (For instance, cost_price, stock_level, and more) depending on the complexity of your application.
mysql> CREATE TABLE products (
product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50),
category_id BIGINT,
retail_price DOUBLE
) ENGINE = InnoDB;
Insert sample data into the products table.
mysql> INSERT INTO products (product_name, category_id, retail_price) values ("WINTER JACKET", 1, "58.30");
INSERT INTO products (product_name, category_id, retail_price) values ("LEATHER BELT", 1, "14.95");
INSERT INTO products (product_name, category_id, retail_price) values ("COTTON VEST", 1, "2.95");
INSERT INTO products (product_name, category_id, retail_price) values ("WIRELESS MOUSE", 2, "19.45");
INSERT INTO products (product_name, category_id, retail_price) values ("FITNESS WATCH", 2, "49.60");
INSERT INTO products (product_name, category_id, retail_price) values ("DASHBOARD CLEANER", 3, "9.99");
INSERT INTO products (product_name, category_id, retail_price) values ("COMBINATION SPANNER", 3, "22.85");
INSERT INTO products (product_name, category_id, retail_price) values ("ENGINE DEGREASER", 3, "8.25");
Confirm the records from the products table.
mysql> SELECT
product_id,
product_name,
category_id,
retail_price
FROM products;
Output.
+------------+---------------------+-------------+--------------+
| product_id | product_name | category_id | retail_price |
+------------+---------------------+-------------+--------------+
| 1 | WINTER JACKET | 1 | 58.3 |
| 2 | LEATHER BELT | 1 | 14.95 |
| 3 | COTTON VEST | 1 | 2.95 |
| 4 | WIRELESS MOUSE | 2 | 19.45 |
| 5 | FITNESS WATCH | 2 | 49.6 |
| 6 | DASHBOARD CLEANER | 3 | 9.99 |
| 7 | COMBINATION SPANNER | 3 | 22.85 |
| 8 | ENGINE DEGREASER | 3 | 8.25 |
+------------+---------------------+-------------+--------------+
8 rows in set (0.01 sec)
Next, create the categories table. This table categorizes your inventory to help you navigate through your collection.
mysql> CREATE TABLE categories (
category_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(50),
description VARCHAR(50)
) ENGINE = InnoDB;
Enter sample data into the categories table.
mysql> INSERT INTO categories (category_name, description) values ("APPAREL", "Stores different clothing");
INSERT INTO categories (category_name, description) values ("ELECTRONICS", "Stores different electronics");
INSERT INTO categories (category_name, description) values ("CAR ACCESSORIES", "Stores car DIY items");
Query the categories table to confirm the records.
mysql> SELECT
category_id,
category_name,
description
FROM categories;
Output.
+-------------+-----------------+------------------------------+
| category_id | category_name | description |
+-------------+-----------------+------------------------------+
| 1 | APPAREL | Stores different clothing |
| 2 | ELECTRONICS | Stores different electronics |
| 3 | CAR ACCESSORIES | Stores car DIY items |
+-------------+-----------------+------------------------------+
3 rows in set (0.00 sec)
To authenticate users, you'll need a table to store the usernames and passwords, create the system_users table.
mysql > CREATE TABLE system_users (
user_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(255)
) ENGINE = InnoDB;
Insert a sample john_doe user account into the system_users table. The password of the user is EXAMPLE_PASSWORD already hashed with the bcrypt algorithm. You can use online tools like Bcrypt-Generator.com to hash any password that you want to use to test this guide. In a production environment, you may create a registration script to capture the usernames and password hashes and post them into the system_users table.
For now, execute the following statement to create a user account manually.
mysql> INSERT INTO system_users (username, password) VALUES ('john_doe', '$2a$12$JOe5OFLD9dFkI.KJ1k9TP.ixWX/YtYArB/Yv.A8XSeIcCBkIlPvoi');
Log out from the MySQL database server.
mysql> EXIT;
You'll store your API development files in a separate directory. This will make troubleshooting and debugging your application easier in case you encounter any errors.
Create a project directory.
$ mkdir project
Navigate to the new project directory.
$ cd project
You'll now add new source code files to the new directory.
In this sample API, you'll separate the actions/methods of your application using different functions grouped in separate files.
The first file you need to create is Golang's entry point. This file holds the main function that will be executed when your application runs.
Create the main.go file using nano.
$ nano main.go
Enter the following information into the file.
package main
import (
"net/http"
"encoding/json"
"fmt"
)
func main() {
http.HandleFunc("/api/v1/", requestHandler)
http.ListenAndServe(":8081", nil)
}
func requestHandler(w http.ResponseWriter, req *http.Request) {
w.Header().Set("Content-Type", "application/json")
var reqError error
responseBody := map[string]interface{}{}
requestData := map[string]interface{}{}
request, reqError := getRequest(req)
if reqError == nil {
authenticated, authErr := authenticateUser(req)
if authErr != nil || authenticated == false {
reqError = &requestError{
statusCode: 401,
message: "Authentication failed.",
errorCode: "401",
resource : "",
}
} else {
switch request.resource {
case "products":
requestData, reqError = newProducts(request)
case "categories":
requestData, reqError = newCategories(request)
default:
reqError = &requestError{
statusCode: 404,
message: "Resource not found.",
errorCode: "404",
resource : "",
}
}
}
}
if reqError != nil {
if requestError, ok := reqError.(*requestError); ok {
w.WriteHeader(requestError.statusCode)
err := map[string]interface{}{
"status_code": requestError.statusCode,
"message": requestError.message,
"error_code": requestError.errorCode,
"resource": requestError.resource,
}
responseBody["error"] = err
}
} else {
w.WriteHeader(request.statusCode)
responseBody = requestData
}
enc := json.NewEncoder(w)
enc.SetIndent("", " ")
if err := enc.Encode(responseBody); err != nil {
fmt.Println(err.Error())
}
}
Save and close the file.
In the above main.go file, the first statement, package main, initializes your package name. Then, you've created a main function func main() {...} that routes requests to the requestHandler(...){...} function.
Then, you're importing different packages that you'll use in this file, including the net/http, encoding/json, and fmt.
You're using the *http.Request variable to capture users' requests to your web application and then, you're writing a response back using the HTTP response writer http.ResponseWriter.
The line request, reqError := getRequest(req) routes the HTTP request to a request.go file, which structures the request with more custom information that your API understands.
Then, after you've retrieved the type of request, you're checking if the user is authenticated into the system by sending the HTTP request to an authentication.go file, which has an authenticateUser function. This function compares the user-supplied username and password with the values stored in the database to see if there is a matching user account.
After you establish the user is authenticated, you're using the switch request.resource {...} to establish the resource that the user is requesting. For this guide, you'll create only two resources. That is the products and the categories resources. In a real-life application, you can have tens or even hundreds of API endpoints.
Finally, you're encoding the response to a JSON format using the json.NewEncoder(w) library.
In your main.go file, you've written the line request, reqError := getRequest(req), this line talks to the request.go file.
Create the request.go file using nano.
$ nano request.go
Then, paste the following information into the file.
package main
import (
"net/http"
"encoding/json"
"strings"
"strconv"
"fmt"
)
type request struct {
method string
resource string
resourceId interface{}
params map[string]interface{}
statusCode int
page int
perPage int
fields string
sort string
}
func getRequest(req *http.Request) (request, error) {
var newRequest request
urlParts := strings.Split(req.URL.Path, "/")
params := map[string]interface{}{}
if req.Method == "GET" {
for k, v := range req.URL.Query() {
params[k] = v[0]
}
} else if req.Method == "POST" || req.Method == "PUT" {
err := json.NewDecoder(req.Body).Decode(¶ms)
if err != nil {
return newRequest, &requestError{
statusCode: 400,
message: err.Error(),
errorCode: "400",
resource : "request/getRequest",
}
}
} else if req.Method == "DELETE" {
//When using the DELETE HTTP, there is no request body
} else {
return newRequest, &requestError{
statusCode: 405,
message: "Method not supported.",
errorCode: "405",
resource : "",
}
}
currentPage := 1
page, err := strconv.Atoi(fmt.Sprint(params["page"]))
if err == nil {
if page >= 1 {
currentPage = page
}
}
pageSize := -1
perPage, err := strconv.Atoi(fmt.Sprint(params["per_page"]))
if err == nil {
if perPage >= 1 {
pageSize = perPage
}
}
fields := ""
sort := ""
if params["fields"] != nil {
fields = params["fields"].(string)
}
if params["sort"] != nil {
sort = params["sort"].(string)
}
if len(urlParts) >= 5 {
newRequest.resourceId = urlParts[4]
}
newRequest.method = req.Method
newRequest.params = params
newRequest.page = currentPage
newRequest.perPage = pageSize
newRequest.fields = fields
newRequest.sort = sort
newRequest.resource = urlParts[3]
if req.Method == "POST" {
newRequest.statusCode = 201
} else {
newRequest.statusCode = 200
}
return newRequest, nil
}
Save and close the file.
At the beginning of the request.go file, you're still defining package main and importing all the necessary libraries. Then, in this file, you're using the statement urlParts := strings.Split(req.URL.Path, "/") to extract the requested resource as well as the resourceId. For instance, if a user requests the resource http://host_name/api/v1/products/3, you're only interested in retrieving the resource(products) and the resourceId(3).
Then, in this function, you're storing any GET, POST, and PUT parameters to the map params that you've defined using the statement params := map[string]interface{}{}. You're not expecting any parameters for the HTTP DELETE method.
Towards the end of the file, you're defining a HTTP status code that your API users will receive when they get a response. For any POST request, your API will answer with status code 201 meaning a new resource has been created or status code 200 for GET, DELETE and PUT operations. Then, you're returning a new request with additional information about the request including the HTTP method, params, page, perPage, requested fields, sort parameters, resource, and the resourceId.
When designing an API, the errors you want to return to your clients should be descriptive enough and carry additional HTTP error status codes. In this guide, you'll create a separate file to handle this functionality.
Use Nano to create a requesterror.go file.
$ nano requesterror.go
Then, enter the information into the file.
package main
import (
"fmt"
)
type requestError struct {
statusCode int
message string
errorCode string
resource string
}
func (e *requestError) Error() string {
return fmt.Sprintf("statusCode %d: message %v: errorCode %v", e.statusCode , e.message, e.errorCode)
}
Save and close the file
In the above file, you're creating a custom requestError struct to extend errors into a more meaningful format. When returning the errors to the client, you'll include a statusCode of type integer corresponding to the actual HTTP status code. For instance, 400(bad request), 500(internal server error), or 404(resource not found), and more.
Then, you include a human-readable message and a custom errorCode. For this guide, you can repeat the HTTP statusCode as the errorCode but in more advanced software, you might come up with your personal errorCodes. Then, finally, include the actual resource that triggered the error to make troubleshooting easier.
When displaying data in your API, you must develop a paging algorithm to page data into manageable chunks to save bandwidth and avoid overloading the database server.
Luckily, in MySQL, you can use the LIMIT clause to achieve this functionality. To keep things simple, you'll create a separate script to handle pagination.
Use nano to create a pagination.go file.
$ nano pagination.go
Then, enter the following information below into the file.
package main
import (
"strconv"
)
func getLimitString (page int, perPage int) (string) {
limit := " "
if perPage != -1 {
limit = " limit " + strconv.Itoa((page - 1) * perPage) + ", " + strconv.Itoa(perPage)
}
return limit
}
func getMeta(dg dbGateway, queryString string, queryParams []interface{}) (map[string]int, error) {
db, err := openDb()
if err != nil {
return nil, err
}
stmt, err := db.Prepare("select count(*) as totalRecords from (" + queryString + ") tmp")
if err != nil {
return nil, err
}
defer stmt.Close()
totalRecords := 0
err = stmt.QueryRow(queryParams...).Scan(&totalRecords)
if err != nil {
return nil, err
}
totalPages := 0
if dg.perPage != -1 {
totalPages = totalRecords/dg.perPage
} else {
totalPages = 1
}
if totalRecords % dg.perPage > 0 {
totalPages++
}
meta := map[string]int {
"page": dg.page,
"per_page": dg.perPage,
"count": totalRecords,
"total_pages": totalPages,
}
if err != nil {
return nil, err
}
return meta, nil
}
Save and close the file
The above file has two functions. You're using the getLimitString function to examine the page and perPage variables to craft an offset and limit clause using the formula limit " + strconv.Itoa((page - 1) * perPage) + ", " + strconv.Itoa(perPage). For instance, if the user requests page 1 and wants to retrieve 25 records perPage, the limit clause will be as follows.
limitClause = limit " + strconv.Itoa((page - 1) * perPage) + ", " + strconv.Itoa(perPage)
limitClause = limit " + strconv.Itoa((1 - 1) * 25) + ", " + strconv.Itoa(25)
limitClause = limit " + 0 * 25 + ", " + 25
limitClause = limit " + 0 + ", " + 25
limitClause = limit " + 0 + ", " + 25
limitClause = limit 0 , 25
If the user wants page 2, your limitClause will be.
limitClause = limit " + strconv.Itoa((page - 1) * perPage) + ", " + strconv.Itoa(perPage)
limitClause = limit " + strconv.Itoa((2 - 1) * 25) + ", " + strconv.Itoa(25)
limitClause = limit " + 1 * 25 + ", " + 25
limitClause = limit " + 25 + ", " + 25
limitClause = limit " + 25 + ", " + 25
limitClause = limit 25 , 25
Then, you have the getMeta function which takes a queryString and uses the MySQL aggregate COUNT(*) function to determine the totalRecords and totalpages using the following formula. If the second expression below returns a fraction, you must add 1 page to the result because you can not have a page with fractions - the value of the totalPages must be an integer.
totalPages = totalRecords/dg.perPage
...
if totalRecords % dg.perPage > 0 {
totalPages++
}
...
When clients request data from your API, they should sort the data in ascending or descending order with the different fields depending on their use case. To achieve this functionality, you'll create a sorting algorithm that takes the user-supplied sort fields and turns them into a statement that your MySQL database can understand.
Create the sorting.go file.
$ nano sorting.go
Then, paste the following information into the file.
package main
import (
"strings"
)
func getSortFields(sortableFields string, sortParams string) (string, error) {
sortableFieldsSlice := strings.Split(sortableFields, ",")
sortParamsSlice := strings.Split(sortParams, ",")
i := 0
sort := ""
for _, field := range sortParamsSlice {
for _, allowList := range sortableFieldsSlice {
if strings.TrimSpace(strings.TrimPrefix(field, "-")) == strings.TrimSpace(allowList) {
if strings.HasPrefix(field, "-") == true {
sort = sort + strings.TrimPrefix(field, "-") + " desc" + ","
} else {
sort = sort + field + " asc" + ","
}
}
}
i++
}
sort = strings.TrimSuffix(sort, ",")
return sort, nil
}
Save and close the file.
The getSortFields function above accepts two variables, an allowlist of the sortableFields that you want to allow in your API, and a comma-separated string retrieved from the sort URL parameter.
You're looping through the user-supplied values to clean the sort parameters. In case a field is not in the allowlist, just drop and ignore it. Then, because you don't want users to include the asc or desc part when defining their custom sort fields, you'll treat any field prefixed with a minus sign - to mean descending order.
For instance, if a user enters the URL http://example.com/api/v1/products?sort=-product_name,retail_price, your script crafts a sort string similar to product_name desc, retail_price asc.
Different clients will consume your API. Some of them, like mobile apps, have resource constraints. Therefore, you should allow API consumers to define the fields that they want to be returned from a resource. For instance, in your products resource, your API users can retrieve only the product_name fields by requesting the URL http://example.com/api/v1/products?fields=product_name.
To achieve this functionality, create the customfields.go file.
$ nano customfields.go
Then, paste the following information into the file.
package main
import (
"strings"
"errors"
)
func cleanFields(defaultFields string, urlFields string) (string, error) {
fields := ""
if urlFields == "" {
fields = defaultFields
} else {
urlFieldsSlice := strings.Split(urlFields, ",")
defaultFieldsSlice := strings.Split(defaultFields , ",")
for _, x := range urlFieldsSlice {
for _, y := range defaultFieldsSlice {
if strings.TrimSpace(x) == strings.TrimSpace(y) {
fields = fields + x + ","
}
}
}
fields = strings.TrimSuffix(fields, ",")
}
if fields == "" {
err := errors.New("Invalid fields.")
return "", err
} else {
return fields, nil
}
}
Save and close the file
You're using the cleanFields() function above to accept the defaultFields that are returned in case the API consumer doesn't define any custom fields and the urlFields retrieved from the fields URL parameter.
Then, you're looping through the user-supplied fields and dropping any non-allowed values, and you're returning the clean fields to be used in a MySQL SELECT statement.
Your API will connect to the MySQL database that you defined at the beginning of the guide. Because you might have several files connecting to the database, it is conventional to create a single gateway file for connecting, querying, and executing SQL statements.
Create dbgateway.go file.
$ nano dbgateway.go
Then, paste the information below into the file.
package main
import (
_"github.com/go-sql-driver/mysql"
"database/sql"
)
type dbGateway struct {
page int
perPage int
sort string
resource string
}
func openDb() (*sql.DB, error) {
dbServer := "127.0.0.1"
dbPort := "3306"
dbUser := "go_db_user"
dbPassword := "EXAMPLE_PASSWORD"
dbName := "go_db"
conString := dbUser + ":" + dbPassword + "@tcp(" + dbServer + ":" + dbPort + ")/" + dbName
db, err := sql.Open("mysql", conString)
if err != nil {
return nil, err
}
pingErr := db.Ping()
if pingErr != nil {
return nil, &requestError{
statusCode: 500,
message: "Error opening database.",
errorCode: "500",
resource : "dbgateway/openDb",
}
}
return db, nil
}
func dbExecute(dg dbGateway, queryString string, paramValues []interface{}) (map[string]interface{}, error) {
db, err := openDb()
if err != nil {
return nil, err
}
stmt, err := db.Prepare(queryString)
if err != nil {
return nil, &requestError{
statusCode: 500,
message: "Error preparing execute query. " + err.Error(),
errorCode: "500",
resource : dg.resource,
}
}
defer stmt.Close()
result, err := stmt.Exec(paramValues...)
if err != nil {
return nil, &requestError{
statusCode: 500,
message: "Error executing statement",
errorCode: "500",
resource : dg.resource,
}
}
defer stmt.Close()
response := map[string]interface{}{}
LastInsertId, err := result.LastInsertId()
if err != nil {
return nil, &requestError{
statusCode: 500,
message: "Error retrieving last insert id.",
errorCode: "500",
resource : dg.resource,
}
} else {
response["LastInsertId"] = LastInsertId
return response, nil
}
}
func dbQuery(dg dbGateway, queryString string, paramValues []interface{}, resourceId interface{}) (map[string]interface{}, error) {
db, err := openDb()
if err != nil {
return nil, err
}
limit := getLimitString(dg.page, dg.perPage)
sort := " order by " + dg.sort
stmt, err := db.Prepare(queryString + sort + limit)
if err != nil {
return nil, &requestError{
statusCode: 500,
message: "Error preparing execute query. " + err.Error(),
errorCode: "500",
resource : dg.resource,
}
}
defer stmt.Close()
rows, err := stmt.Query(paramValues...)
if err != nil {
return nil, &requestError{
statusCode: 500,
message: "Error retrieving rows.",
errorCode: "500",
resource : dg.resource,
}
}
defer rows.Close()
columns, err := rows.Columns()
if err != nil {
return nil, &requestError{
statusCode: 500,
message: "Error retrieving columns",
errorCode: "500",
resource : dg.resource,
}
}
data := []map[string]interface{}{}
count := len(columns)
values := make([]interface{}, count)
scanArgs := make([]interface{}, count)
for i := range values {
scanArgs[i] = &values[i]
}
for rows.Next() {
err := rows.Scan(scanArgs...)
if err != nil {
return nil, &requestError{
statusCode: 500,
message: "Error scanning rows",
errorCode: "500",
resource : dg.resource,
}
}
tbRecord := map[string]interface{}{}
for i, col := range columns {
v := values[i]
b, ok := v.([]byte)
if (ok) {
tbRecord[col] = string(b)
} else {
tbRecord[col] = v
}
}
data = append(data, tbRecord)
}
response := map[string]interface{}{}
if resourceId == nil {
meta, err := getMeta(dg, queryString, paramValues)
if err != nil {
return nil, err
}
response["data"] = data
response["meta"] = meta
} else {
if len(data) < 1 {
return nil, &requestError{
statusCode: 404,
message: "Record not found",
errorCode: "404",
resource : dg.resource,
}
}
response["data"] = data[0]
}
return response, nil
}
Save and close the file
In the above file, you have defined a dbGateway struct which allows your calling files to pass the page, perPage, sort, and resource values to the dbQuery and dbExecute functions because you need these values to fulfill some business logic. Then, you're using the openDb() function to open your database using the user account you created earlier.
The dbQuery function executes parameterized SELECT queries and the dbExecute function handles parameterized INSERT, UPDATE, and DELETE statements. In the dbQuery function, you're using the functions in the pagination.go script to get the limit clause and the meta information. Finally, you're returning a map of type ...[string]interface{}... to the calling script.
To authenticate users into your API, you'll check their usernames and passwords and compare them against the database values. You'll do this in the main.go file which calls a function in an authentication.go file.
Create the authentication.go file
$ nano authentication.go
Then, paste the below information into the file.
package main
import (
"net/http"
"golang.org/x/crypto/bcrypt"
)
func authenticateUser(req *http.Request)(bool, error) {
reqUsername, reqPassword, ok := req.BasicAuth()
if ok {
db, err := openDb()
if err != nil {
return false, err
}
queryString := "select password from system_users where username = ?"
stmt, err := db.Prepare(queryString)
if err != nil {
return false, err
}
defer stmt.Close()
storedPassword := ""
err = stmt.QueryRow(reqUsername).Scan(&storedPassword)
if err != nil {
return false, err
}
if err := bcrypt.CompareHashAndPassword([]byte(storedPassword), []byte(reqPassword)); err != nil {
return false, err
} else {
return true, err
}
} else {
return false, nil
}
}
Save and close the file.
The authenticateUser function retrieves the user-supplied username and password, retrieves the appropriate record from the system_users table, and uses the bcrypt.CompareHashAndPassword(....) function to check if the password and hash match. This function returns a boolean true value if the user is authenticated or false if the user credentials do not exist in the database.
After you've all the supportive scripts for your API, you can now create the actual resource files that you'll serve.
This resource file will allow API consumers to create, update, delete, and retrieve items from the products table.
Create a products.go file.
$ nano products.go
Paste the information below into the file.
package main
import (
"strconv"
"fmt"
)
func newProducts(r request)(map[string]interface{}, error) {
action := r.method
var err error
data := map[string]interface{}{}
resourceId, _ := strconv.Atoi(fmt.Sprint(r.resourceId))
switch action {
case "POST":
data, err = createProduct(r.params)
case "PUT":
data, err = updateProduct(resourceId, r.params)
case "DELETE":
data, err = deleteProduct(resourceId)
case "GET":
data, err = getProducts(r)
}
if err != nil {
return nil, err
} else {
return data, nil
}
}
func validateProductData(params map[string]interface{}) string {
validationError := ""
if val, ok := params["product_name"]; ok {
if val.(string) == "" {
validationError = validationError + `Invalid product_name`
}
} else {
validationError = validationError + "\n" + `Field product_name is required.`
}
if val, ok := params["retail_price"]; ok {
retailPrice, err := strconv.ParseFloat(fmt.Sprint(val), 64)
if err != nil || retailPrice <= 0 {
validationError = validationError + "\n" + `Invalid retail_price`
}
} else {
validationError = validationError + "\n" + `Field retail_price is required.`
}
if val, ok := params["category_id"]; ok {
categoryId, err := strconv.Atoi(fmt.Sprint(val))
if err != nil || categoryId <= 0 {
validationError = validationError + "\n" + `Invalid category_id`
}
} else {
validationError = validationError + "\n" + `Field category_id is required.`
}
return validationError
}
func createProduct(params map[string]interface{}) (map[string]interface{}, error) {
validationError := validateProductData(params)
if validationError != "" {
return nil, &requestError{
statusCode: 400,
message: validationError,
errorCode: "400",
resource : "products",
}
}
var dg dbGateway
dg.resource = "products"
queryString := "insert into products(product_name, category_id, retail_price) values (?,?,?)"
paramValues := []interface{}{
params["product_name"],
params["retail_price"],
params["category_id"],
}
dbExecuteResponse, err:= dbExecute(dg, queryString, paramValues)
if err != nil {
return nil, err
}
result := map[string]interface{}{}
response := map[string]interface{}{
"product_id": dbExecuteResponse["LastInsertId"],
"product_name": params["product_name"],
"category_id": params["category_id"],
"retail_price": params["retail_price"],
}
result["data"] = response
return result, nil
}
func updateProduct(resourceId interface{}, params map[string]interface{}) (map[string]interface{}, error) {
validationError := validateProductData(params)
if validationError != "" {
return nil, &requestError{
statusCode: 400,
message: validationError,
errorCode: "400",
resource : "products",
}
}
var dg dbGateway
dg.resource = "products"
queryString := "update products set product_name = ?, category_id = ? ,retail_price = ? where product_id = ?"
paramValues := []interface{}{
params["product_name"],
params["category_id"],
params["retail_price"],
resourceId,
}
_, err:= dbExecute(dg, queryString, paramValues)
if err != nil {
return nil, err
}
result := map[string]interface{}{}
response := map[string]interface{}{
"product_id" : resourceId,
"product_name": params["product_name"],
"category_id": params["category_id"],
"retail_price": params["retail_price"],
}
result["data"] = response
return result, nil
}
func deleteProduct(resourceId interface{}) (map[string]interface{}, error) {
var dg dbGateway
dg.resource = "products"
queryString := "delete from products where product_id = ? limit 1"
paramValues := []interface{}{
resourceId,
}
_, err:= dbExecute(dg, queryString, paramValues)
if err != nil {
return nil, err
}
result := map[string]interface{}{}
result["data"] = "Success"
return result, nil
}
func getProducts(r request)(map[string]interface{}, error) {
var dg dbGateway
dg.page = r.page
dg.perPage = r.perPage
dg.resource = "products"
defaultFields := "product_id, product_name, category_id, retail_price"
var fields string
var err error
if r.fields != "" {
fields, err = cleanFields(defaultFields, r.fields)
} else {
fields = defaultFields
}
if err != nil {
return nil, err
}
defaultSortFields := "product_id asc, product_name asc, category_id asc"
sortableFields := "product_id, product_name, category_id"
sortFields := ""
if r.sort != "" {
sortFields, err = getSortFields(sortableFields, r.sort)
if err != nil {
return nil, err
}
} else {
sortFields = defaultSortFields
}
dg.sort = sortFields
queryString := ""
paramValues := []interface{}{}
if r.resourceId != nil {
queryString = `select ` +
fields +
` from products` +
` where product_id = ?`
paramValues = append(paramValues, r.resourceId)
} else {
filter := ""
if r.params["search"] != nil {
filter = "and product_name like ?"
paramValues = append(paramValues, r.params["search"].(string) + "%")
}
queryString = `select ` +
fields +
` from products` +
` where products.product_id > 0 ` +
filter
}
data, err := dbQuery(dg, queryString, paramValues, r.resourceId)
if err != nil {
return nil, err
}
return data, nil
}
Save and close the file.
In the above file, you've defined the entry function newProducts which will be called after the main.go function receives a request matching the products resource.
Then, you're using the Golang switch statement to route the request to the appropriate function depending on the request method as follows.
POST: createProduct function
PUT: updateProduct function
DELETE: deleteProduct function
GET: getProducts function
The validateProductData function validates data when executing a POST or a PUT operation.
The next resource that you're going to create is the categories file. This resource will return the different categories in your sample application. You can also use it to create, edit, and update records in the categories table.
Create the categories.go file.
$ nano categories.go
Then, paste the information below into the file.
package main
import (
"strconv"
"fmt"
)
func newCategories(r request)(map[string]interface{}, error) {
action := r.method
var err error
data := map[string]interface{}{}
resourceId, _ := strconv.Atoi(fmt.Sprint(r.resourceId))
switch action {
case "POST":
data, err = createCategory(r.params)
case "PUT":
data, err = updateCategory(resourceId, r.params)
case "DELETE":
data, err = deleteCategory(resourceId)
case "GET":
data, err = getCategories(r)
}
if err != nil {
return nil, err
} else {
return data, nil
}
}
func validateCategoryData(params map[string]interface{}) string {
validationError := ""
if val, ok := params["category_name"]; ok {
if val.(string) == "" {
validationError = validationError + `Invalid category_name`
}
} else {
validationError = validationError + "\n" + `Field category_name is required.`
}
if val, ok := params["description"]; ok {
if val.(string) == "" {
validationError = validationError + `Invalid description`
}
} else {
validationError = validationError + "\n" + `Field description is required.`
}
return validationError
}
func createCategory(params map[string]interface{}) (map[string]interface{}, error) {
validationError := validateCategoryData(params)
if validationError != "" {
return nil, &requestError{
statusCode: 400,
message: validationError,
errorCode: "400",
resource : "categories",
}
}
var dg dbGateway
dg.resource = "categories"
queryString := "insert into categories(category_name, description) values (?,?)"
paramValues := []interface{}{
params["category_name"],
params["description"],
}
dbExecuteResponse, err:= dbExecute(dg, queryString, paramValues)
if err != nil {
return nil, err
}
result := map[string]interface{}{}
response := map[string]interface{}{
"category_id": dbExecuteResponse["LastInsertId"],
"category_name": params["category_name"],
"description": params["description"],
}
result["data"] = response
return result, nil
}
func updateCategory(resourceId interface{}, params map[string]interface{}) (map[string]interface{}, error) {
validationError := validateCategoryData(params)
if validationError != "" {
return nil, &requestError{
statusCode: 400,
message: validationError,
errorCode: "400",
resource : "categories",
}
}
var dg dbGateway
dg.resource = "categories"
queryString := "update categories set category_name = ?, description = ? where category_id = ?"
paramValues := []interface{}{
params["category_name"],
params["description"],
resourceId,
}
_, err:= dbExecute(dg, queryString, paramValues)
if err != nil {
return nil, err
}
result := map[string]interface{}{}
response := map[string]interface{}{
"category_id" : resourceId,
"category_name": params["category_name"],
"description": params["description"] ,
}
result["data"] = response
return result, nil
}
func deleteCategory(resourceId interface{}) (map[string]interface{}, error) {
var dg dbGateway
dg.resource = "categories"
queryString := "delete from categories where category_id = ? limit 1"
paramValues := []interface{}{
resourceId,
}
_, err:= dbExecute(dg, queryString, paramValues)
if err != nil {
return nil, err
}
result := map[string]interface{}{}
result["data"] = "Success"
return result, nil
}
func getCategories(r request)(map[string]interface{}, error) {
var dg dbGateway
dg.resource = "categories"
dg.page = r.page
dg.perPage = r.perPage
defaultFields := "category_id, category_name, description"
var fields string
var err error
if r.fields != "" {
fields, err = cleanFields(defaultFields, r.fields)
} else {
fields = defaultFields
}
if err != nil {
return nil, err
}
defaultSortFields := "category_id asc, category_name asc"
sortableFields := "category_id, category_name"
sortFields := ""
if r.sort != "" {
sortFields, err = getSortFields(sortableFields, r.sort)
if err != nil {
return nil, err
}
} else {
sortFields = defaultSortFields
}
dg.sort = sortFields
queryString := ""
paramValues := []interface{}{}
if r.resourceId != nil {
queryString = "select " + fields + " from categories where category_id = ?"
paramValues = append(paramValues, r.resourceId)
} else {
filter := ""
if r.params["search"] != nil {
filter = "and category_name like ?"
paramValues = append(paramValues, r.params["search"].(string) + "%")
}
queryString = "select " + fields + " from categories where category_id > 0 " + filter
}
data, err := dbQuery(dg, queryString, paramValues, r.resourceId)
if err != nil {
return nil, err
}
return data, nil
}
Save and close the file.
Like in the products.go resource file, the newCategories function in the above categories.go file routes the different CRUD operations to the appropriate functions to create, update, delete, and retrieve categories.
After you've finished editing all the API source files, the next step is testing your application's different functions.
Run the program by executing the following function. Ensure you're still in the project directory.
$ go get github.com/go-sql-driver/mysql
$ go get golang.org/x/crypto/bcrypt
$ go run ./
The last command above has a blocking function that allows your API to listen on port 8081.
Open another terminal window and use curl to retrieve items from the products resource. The -i option allows you to retrieve the headers to make sure your API is returning the correct HTTP status codes.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/products"
Output.
{
"data": [
{
"category_id": 1,
"product_id": 1,
"product_name": "WINTER JACKET",
"retail_price": 58.3
},
{
"category_id": 1,
"product_id": 2,
"product_name": "LEATHER BELT",
"retail_price": 14.95
},
{
"category_id": 1,
"product_id": 3,
"product_name": "COTTON VEST",
"retail_price": 2.95
},
{
"category_id": 2,
"product_id": 4,
"product_name": "WIRELESS MOUSE",
"retail_price": 19.45
},
{
"category_id": 2,
"product_id": 5,
"product_name": "FITNESS WATCH",
"retail_price": 49.6
},
{
"category_id": 3,
"product_id": 6,
"product_name": "DASHBOARD CLEANER",
"retail_price": 9.99
},
{
"category_id": 3,
"product_id": 7,
"product_name": "COMBINATION SPANNER",
"retail_price": 22.85
},
{
"category_id": 3,
"product_id": 8,
"product_name": "ENGINE DEGREASER",
"retail_price": 8.25
}
],
"meta": {
"count": 8,
"page": 1,
"per_page": -1,
"total_pages": 1
}
}
Attempt to retrieve the first product with a product_id of 1.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/products/1"
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 10:55:23 GMT
Content-Length: 125
{
"data": {
"category_id": 1,
"product_id": 1,
"product_name": "WINTER JACKET",
"retail_price": 58.3
}
}
Experiment with custom fields. Retrieve only the product_id and product_name fields.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/products/1?fields=product_id,product_name"
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 10:56:29 GMT
Content-Length: 77
{
"data": {
"product_id": 1,
"product_name": "WINTER JACKET"
}
}
Experiment with pages. Retrieve page 1 from the products resource and specify a page size of 3 records.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/products?page=1&per_page=3"
Output
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 10:59:03 GMT
Content-Length: 483
{
"data": [
{
"category_id": 1,
"product_id": 1,
"product_name": "WINTER JACKET",
"retail_price": 58.3
},
{
"category_id": 1,
"product_id": 2,
"product_name": "LEATHER BELT",
"retail_price": 14.95
},
{
"category_id": 1,
"product_id": 3,
"product_name": "COTTON VEST",
"retail_price": 2.95
}
],
"meta": {
"count": 8,
"page": 1,
"per_page": 3,
"total_pages": 3
}
}
Retrieve page 1 of products sorted with product_name in descending order.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/products?page=1&per_page=3&sort=-product_name"
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:25:46 GMT
Content-Length: 487
{
"data": [
{
"category_id": 2,
"product_id": 4,
"product_name": "WIRELESS MOUSE",
"retail_price": 19.45
},
{
"category_id": 1,
"product_id": 1,
"product_name": "WINTER JACKET",
"retail_price": 58.3
},
{
"category_id": 1,
"product_id": 2,
"product_name": "LEATHER BELT",
"retail_price": 14.95
}
],
"meta": {
"count": 8,
"page": 1,
"per_page": 3,
"total_pages": 3
}
}
Add a new item into the products resource using the HTTP POST method.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X POST localhost:8081/api/v1/products -H "Content-Type: application/json" -d '{"product_name": "WIRELESS KEYBOARD", "category_id": 2, "retail_price": 55.69}'
Output.
HTTP/1.1 201 Created
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:33:20 GMT
Content-Length: 130
{
"data": {
"category_id": 2,
"product_id": 9,
"product_name": "WIRELESS KEYBOARD",
"retail_price": 55.69
}
}
Update the new record with a product_id of 9 using the HTTP PUT method.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X PUT localhost:8081/api/v1/products/9 -H "Content-Type: application/json" -d '{"product_name": "WIRELESS USB KEYBOARD", "category_id": 2, "retail_price": 50.99}'
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:34:45 GMT
Content-Length: 134
{
"data": {
"category_id": 2,
"product_id": 9,
"product_name": "WIRELESS USB KEYBOARD",
"retail_price": 50.99
}
}
Delete the new product using the HTTP DELETE method.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X DELETE "localhost:8081/api/v1/products/9"
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:36:14 GMT
Content-Length: 24
{
"data": "Success"
}
Attempt to retrieve the product you've just deleted.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/products/9"
Output.
HTTP/1.1 404 Not Found
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:37:16 GMT
Content-Length: 132
{
"error": {
"error_code": "404",
"message": "Record not found",
"resource": "products",
"status_code": 404
}
}
Retrieve all categories from the API.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/categories"
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:01:12 GMT
Content-Length: 478
{
"data": [
{
"category_id": 1,
"category_name": "APPAREL",
"description": "Stores different clothing"
},
{
"category_id": 2,
"category_name": "ELECTRONICS",
"description": "Stores different electronics"
},
{
"category_id": 3,
"category_name": "CAR ACCESSORIES",
"description": "Stores car DIY items"
}
],
"meta": {
"count": 3,
"page": 1,
"per_page": -1,
"total_pages": 1
}
}
Retrieve a single category with a category_id of 1.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/categories/1"
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:01:45 GMT
Content-Length: 121
{
"data": {
"category_id": 1,
"category_name": "APPAREL",
"description": "Stores different clothing"
}
}
Add a new record to the categories resource using the HTTP POST method.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X POST localhost:8081/api/v1/categories -H "Content-Type: application/json" -d '{"category_name": "FURNITURES", "description": "This category holds all furnitures in the store."}'
Output.
HTTP/1.1 201 Created
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:03:56 GMT
Content-Length: 147
{
"data": {
"category_id": 4,
"category_name": "FURNITURES",
"description": "This category holds all furnitures in the store."
}
}
Update the new category with a category_id of 4 using the PUT method.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X PUT localhost:8081/api/v1/categories/4 -H "Content-Type: application/json" -d '{"category_name": "FURNITURES AND HARDWARE ITEMS", "description": "This category holds all furnitures in the store."}'
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:06:31 GMT
Content-Length: 166
{
"data": {
"category_id": 4,
"category_name": "FURNITURES AND HARDWARE ITEMS",
"description": "This category holds all furnitures in the store."
}
}
Delete the new category using the HTTP DELETE method.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X DELETE "localhost:8081/api/v1/categories/4"
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:07:24 GMT
Content-Length: 24
{
"data": "Success"
}
Attempt requesting the category you've just deleted.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/categories/4"
Output.
HTTP/1.1 404 Not Found
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:08:19 GMT
Content-Length: 134
{
"error": {
"error_code": "404",
"message": "Record not found",
"resource": "categories",
"status_code": 404
}
}
Sort all categories in descending order by prefixing the sort parameter category_name with a minus sign -.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/categories?sort=-category_name"
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:21:14 GMT
Content-Length: 478
{
"data": [
{
"category_id": 2,
"category_name": "ELECTRONICS",
"description": "Stores different electronics"
},
{
"category_id": 3,
"category_name": "CAR ACCESSORIES",
"description": "Stores car DIY items"
},
{
"category_id": 1,
"category_name": "APPAREL",
"description": "Stores different clothing"
}
],
"meta": {
"count": 3,
"page": 1,
"per_page": -1,
"total_pages": 1
}
}
Page the categories data
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/categories?page=1&per_page=2"
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:11:19 GMT
Content-Length: 354
{
"data": [
{
"category_id": 1,
"category_name": "APPAREL",
"description": "Stores different clothing"
},
{
"category_id": 2,
"category_name": "ELECTRONICS",
"description": "Stores different electronics"
}
],
"meta": {
"count": 3,
"page": 1,
"per_page": 2,
"total_pages": 2
}
}
Retrieve only the category_name from a single record with a category_id of 3.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/categories/3?fields=category_name"
Output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:14:49 GMT
Content-Length: 59
{
"data": {
"category_name": "CAR ACCESSORIES"
}
}
Attempt to add a new category with an empty payload.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X POST localhost:8081/api/v1/categories -H "Content-Type: application/json" -d '{}'
Output.
HTTP/1.1 400 Bad Request
Content-Type: application/json
Date: Mon, 01 Nov 2021 13:22:02 GMT
Content-Length: 184
{
"error": {
"error_code": "400",
"message": "\nField category_name is required.\nField description is required.",
"resource": "categories",
"status_code": 400
}
}
Attempt connecting to the API using a wrong password.
$ curl -i -u john_doe:WRONG_PASSWORD -X GET "localhost:8081/api/v1/products"
Output.
HTTP/1.1 401 Unauthorized
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:39:01 GMT
Content-Length: 130
{
"error": {
"error_code": "401",
"message": "Authentication failed.",
"resource": "",
"status_code": 401
}
}
Your API is now running as expected. Next, stop the currently running application by pressing CTRL + C on the first terminal window and follow the steps below to create and run a new binary.
Create an executable file and copy it to the /usr/local/bin/ directory using the following commands. Although you're using test-api as the name of your final binary file in this guide, you may change the name to a more descriptive name depending on your application use case.
$ go build ./
$ sudo cp project /usr/local/bin/test-api
Then run the new executable.
$ test-api
Submit a sample request to the products or the categories endpoint on a new terminal window.
$ curl -i -u john_doe:EXAMPLE_PASSWORD -X GET "localhost:8081/api/v1/products/1?fields=product_id,product_name"
You should get the output.
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 01 Nov 2021 11:58:05 GMT
Content-Length: 77
{
"data": {
"product_id": 1,
"product_name": "WINTER JACKET"
}
}
Your API is now running as an executable binary, and everything is working as expected.
In this guide, you've created a fast, modern JSON API with Golang and MySQL 8 database on your Linux server. Use the code in this guide when developing your next API project.