Designing a Modern API with Golang and MySQL 8 on Linux

Published on November 2, 2021•Updated on November 21, 2023

Introduction

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.

Prerequisites

Before you proceed with this Golang API tutorial, you need:

1. Initialize a Database and a User Account for the API

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.

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_unicode_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;                
```

2. Create a Project Directory for the API

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.

3. Create the Golang API Scripts

In this sample API, you'll separate the actions/methods of your application using different functions grouped in separate files.

Create the `main.go` Script

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.

Create a `request.go` Script

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(&params)

         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.

Create a `requesterror.go` File

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.

Create a `pagination.go` Script

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++
     } 

 ...
```

Create a `sorting.go` Script

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.

Create a `customfields.go` Script

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.

Create a `dbgateway.go` Script

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.

Create an `authentication.go` 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.

4. Create the Resource Files

After you've all the supportive scripts for your API, you can now create the actual resource files that you'll serve.

Create a `products.go` Resource File

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.

Create a `categories.go` Resource File

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.

5. Test the Golang API

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":

Designing a Modern API with Golang and MySQL 8 on Linux

Introduction

Prerequisites

1. Initialize a Database and a User Account for the API

2. Create a Project Directory for the API

3. Create the Golang API Scripts

Create the `main.go` Script

Create a `request.go` Script

Create a `requesterror.go` File

Create a `sorting.go` Script

Create a `customfields.go` Script

Create a `dbgateway.go` Script

Create an `authentication.go` Script

4. Create the Resource Files

Create a `products.go` Resource File

Create a `categories.go` Resource File

5. Test the Golang API

Products

Features

Marketplace

Resources

Company

Designing a Modern API with Golang and MySQL 8 on Linux

Introduction

Prerequisites

1. Initialize a Database and a User Account for the API

2. Create a Project Directory for the API

3. Create the Golang API Scripts

Create the main.go Script

Create a request.go Script

Create a requesterror.go File

Create a pagination.go Script

Create a sorting.go Script

Create a customfields.go Script

Create a dbgateway.go Script

Create an authentication.go Script

4. Create the Resource Files

Create a products.go Resource File

Create a categories.go Resource File

5. Test the Golang API

Create the `main.go` Script

Create a `request.go` Script

Create a `requesterror.go` File

Create a `pagination.go` Script

Create a `sorting.go` Script

Create a `customfields.go` Script

Create a `dbgateway.go` Script

Create an `authentication.go` Script

Create a `products.go` Resource File

Create a `categories.go` Resource File