Build a REST API in Golang with MySQL, GORM and Gorilla Mux

Dec 30, 2019

In a previous post - Go REST API, we saw how to build a simple REST service in Golang. For the sake of simplicity, we did not use any database in that tutorial. In this post, we will be building a REST service that makes use of a MySQL database for persistence and the popular GORM framework for object-relational mapping.

Initial Setup

The first pre-requisite for getting this working is to have a working environment setup for Go. As long as you have installed Go, and validated your setup , we should be good to go. Also take a look at the code organization guidelines, so that you have a clear idea about the Go workspace and directory structure.

The second requirement is to have MySQL database installed. The official MySQL documentation is the best resource in the regard, with detailed OS-specific steps for Linux, Mac and Windows.

The next step is to download and install the dependent packages - namely, Gorilla Mux, Gorm, and Go-sql-driver for MySql . Run the following commands from the terminal:

go get - u github . com / gorilla / mux go get - u github . com / jinzhu / gorm go get - u github . com / go - sql - driver / mysql

Imports

Now that we have our environment setup, it’s time to write some code and get our hands dirty. Let’s start by importing the set of packages mentioned in the code snippet below:

import ( "encoding/json" "fmt" "log" "net/http" "strconv" "time" "github.com/gorilla/mux" "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/mysql" )

encoding/json - This package contains methods that are used to convert Go types to JSON and vice-versa (This conversion is called as encode/decode in Go, serialization/de-serialization or marshall/unmarshall in other languages).

fmt - This package implements formatted I/O functions similar to scanf and printf in C

log - Has methods for formatting and printing log messages.

net/http - Contains methods for performing operations over HTTP. It provides HTTP server and client implementations and has abstractions for HTTP request, response, headers, etc.

strconv - Contains methods that convert string from/to other datatypes.

time - Provides methods for handling(storing/displaying/manipulating) time values.

github.com/gorilla/mux - Provides methods to route incoming http requests to their respective handler methods

github.com/jinzhu/gorm - Gorm is the most widely-used framework for object-relational mapping in Go

github.com/jinzhu/gorm/dialects/mysql - GORM has wrapped some drivers to make it easier to remember the import path . This internally refers the github.com/go-sql-driver/mysql driver we installed earlier.

Defining our Models

The next step is to create a representation of an order for our service. For the purpose of this tutorial, let’s just have the basic set of fields needed for an order.

Just a quick note about GORM naming conventions before we dive into our models:

Table name is the pluralized version of struct name. (If the model struct is named Order , the table name will be orders )

Column names will be the field’s name is lower snake case. (If the struct field is named CustomerName , the column name will be customer_name )

Now that we got the naming conventions out of the way, lets look at the model structs starting with Order .

// Order represents the model for an order // Default table name will be `orders` type Order struct { // gorm.Model OrderID uint `json:"orderId" gorm:"primary_key"` CustomerName string `json:"customerName"` OrderedAt time . Time `json:"orderedAt"` Items [] Item `json:"items" gorm:"foreignkey:OrderID"` }

An order has the following fields defined inside the Order struct.

OrderId - id for each order

CustomerName - name of the customer

OrderedAt - the date/time at which it was placed

Items - list of items in the order. An order can have one or more items, and this is an example of one-to-many relationship/association between entities

Similarly, each individual item in an order has the following fields declared in the Item struct.

type Item struct { // gorm.Model LineItemID uint `json:"lineItemId" gorm:"primary_key"` ItemCode string `json:"itemCode"` Description string `json:"description"` Quantity uint `json:"quantity"` OrderID uint `json:"-"` }

ItemCode - code for each item

Description - Description of the product/item

Quantity - Number of quantities of the item in the order

As you can see above, each field consists of a tag(The string within the backticks at the end of each field declaration). Tags are used to specify metadata information about a specific field. For instance, the tag json:"customerName" means that, during encoding(converting Go struct to Json), the field CustomerName in the struct will be mapped to the customerName attribute in the JSON. Similarly, during decoding (Converting JSON to a Go struct), the attribute named customerName in the JSON will be mapped to the CustomerName field in the struct.

As you can see, the field OrderID has the gorm:"primary_key" tag - This means that the order_id column will be the primary key for the orders table once created. Integer fields with primary_key tag are auto_increment by default. - This fits in well for our use-case and we don’t have to worry about generating a unique id for each order.

The Items field has the gorm:"foreignKey:OrderID" tag - This means that the items table will have an order_id column that references the order_id column in the orders table.

Also, the LineItemID field is marked as the primary key for the Items model. Hence, the line_item_id column will serve as the unique identifier for records in the items table.

I chose this schema with a one-to-many relationship since it would help us understand how to deal with foreign keys , associations between models, eager loading - ideas which are essential to understand while dealing with object -relational mapping.

Initial DB setup and configuration

For the purpose of this tutorial, it would be preferable to create the database and tables with GORM.

var db * gorm . DB func initDB () { var err error dataSourceName := "root:@tcp(localhost:3306)/?parseTime=True" db , err = gorm . Open ( "mysql" , dataSourceName ) if err != nil { fmt . Println ( err ) panic ( "failed to connect database" ) } // Create the database. This is a one-time step. // Comment out if running multiple times - You may see an error otherwise db . Exec ( "CREATE DATABASE orders_db" ) db . Exec ( "USE orders_db" ) // Migration to create tables for Order and Item schema db . AutoMigrate ( & Order {}, & Item {}) }

If you have already created the database outside this program, the code to initialize the DB connection will be slightly different. Please use the following code as reference if that case:

dataSourceName := "your_username:your_password@tcp(localhost:3306)/your_database_name?parseTime=True" /* Commenting out, so that order_db is not used, and your_database_name is used instead db.Exec("CREATE DATABASE orders_db") db.Exec("USE orders_db") */

Since multiple methods in our code will require database access ( createOrder , getOrder , etc as we will see shortly), it is a good idea to have a global variable for the database connectivity and a separate function that initializes during application startup.

Note: AutoMigrate will ONLY create tables, missing columns and missing indexes, and WON’T change existing column’s type or delete unused columns to protect your data.

If you are playing around with the Order or Item model, by changing the datatype of fields or by removing fields , it will not reflected in the DB tables. In that case, it’s better to delete the tables manually, and run the migration again to create tables with the updated model.

Routes Definition

Let’s get started by defining the routes for our APIs inside the main function.

func main () { router := mux . NewRouter () // Create router . HandleFunc ( "/orders" , createOrder ). Methods ( "POST" ) // Read router . HandleFunc ( "/orders/{orderId}" , getOrder ). Methods ( "GET" ) // Read-all router . HandleFunc ( "/orders" , getOrders ). Methods ( "GET" ) // Update router . HandleFunc ( "/orders/{orderId}" , updateOrder ). Methods ( "PUT" ) // Delete router . HandleFunc ( "/orders/{orderId}" , deleteOrder ). Methods ( "DELETE" ) // Initialize db connection initDB () log . Fatal ( http . ListenAndServe ( ":8080" , router )) }

The first line creates a new mux Router. Before we proceed further, What exactly is a route? It is a way of specifying which function handles a certain API request.We can consider a route as a mapping between an API and the function that handles the API request. With the Gorilla Mux router, routes are defined using the HandleFunc method - The first argument is the API path, and the second argument is the name of the method that should be executed for that API. The Method function at the end specifies the HTTP method to be matched (GET, POST, PUT, etc). For example, with the above code snippet, POST API requests to the /orders URL is routed to the createOrder method (which we will define shortly).

Once the routes are defined, the Mux router directs incoming requests to their respective handler methods. Mux also supports more advanced use-cases like matching based on headers, query params, etc but we won’t need them for our example. Here, we register 5 routes (create, read, read-all, update and delete), mapping URL paths to their respective handler methods.

The ListenAndServe method starts an HTTP server listening at the 8080 port, and the wrapping log.Fatal ensures that errors are captured if the HTTP server fails.

1. Create API

Now, let’s code the API for creating an order.

func createOrder ( w http . ResponseWriter , r * http . Request ) { var order Order json . NewDecoder ( r . Body ). Decode ( & order ) // Creates new order by inserting records in the `orders` and `items` table db . Create ( & order ) w . Header (). Set ( "Content-Type" , "application/json" ) json . NewEncoder ( w ). Encode ( order ) }

The func keyword indicates we are defining a function named createOrder , which accepts 2 arguments - http.ResponseWriter , which contains the response details(headers, payload) - http.Request , which contains the incoming request details

json.NewDecoder(r.Body) converts the body of the incoming HTTP request and populates the appropriate fields in the order variable we have defined. The db.Create(&order) creates a new order by inserting records in the orders and items table.

The next line sets the Content-Type header to application/json which signifies that the function/API returns a JSON content as response.

Normally, the response of create API(POST) contains the representation of the resource that was created. In our case , the order variable has the picture of the order we just created. All we are doing in the last line of the code snippet is creating a new Encoder that encodes/converts the order variable to a JSON which is sent to the caller as the response.

2. Read and Read-all API

In this section, we are going to create APIs for reading/getting the orders we create through the createOrder API we discussed above. We are going to code 2 variations of this API - One to get all orders, and the other to get an order corresponding to a given orderId .

func getOrders ( w http . ResponseWriter , r * http . Request ) { w . Header (). Set ( "Content-Type" , "application/json" ) var orders [] Order db . Preload ( "Items" ). Find ( & orders ) json . NewEncoder ( w ). Encode ( orders ) }

The getOrders function is relatively simple - First, we set the Content-Type header similar to the createOrder function. We then use the db.Find() method to fetch all the orders, which is then encoded as a JSON and returned as a response. The db.Preload() method ensures that associations are preloaded while using the Find() method. In our case, we would like to see the item details we well, when we lookup the order. Hence we use the Preload () method to fetch the associated items, in addition to the order details.

As shown below, the items attribute in the json will be null, if we had not used the Preload() method for loading the associated items.

Without Preloading:

{ "orderId" : 1 , "customerName" : "Tom Jerry" , "orderedAt" : "2019-11-09T21:21:46Z" , "items" : null }

With Preloading:

{ "orderId" : 1 , "customerName" : "Tom Jerry" , "orderedAt" : "2019-11-09T21:21:46Z" , "items" : [ { "itemCode" : "123" , "description" : "IPhone 10X" , "quantity" : 1 } ] }

The getOrder function is supposed to return the order details for a specific orderId which is provided as a path param to the API. So, how do we get the orderId passed in the request? The mux.Vars(r) function returns the path params as a map. In Line4, we retrieve the orderId from the params map, and assign it to the inputOrderID variable. The db.First() method finds and returns the record whose primary key is inputOrderId .

func getOrder ( w http . ResponseWriter , r * http . Request ) { w . Header (). Set ( "Content-Type" , "application/json" ) params := mux . Vars ( r ) inputOrderID := params [ "orderId" ] var order Order db . Preload ( "Items" ). First ( & order , inputOrderID ) json . NewEncoder ( w ). Encode ( order ) }

Next, we are going to see how to update the details of an order. The first step is to decode the updated order JSON into the updatedOrder struct. Once we have the updated order details in the struct, we can use the db.Save () method to update the records in the DB.

By default, the save command will update the associated entities having a primary key. In our case, since the associated Items model has the LineItemId as primary key, the db.Save() command will automatically update the related records in the items table as well.

func updateOrder ( w http . ResponseWriter , r * http . Request ) { var updatedOrder Order json . NewDecoder ( r . Body ). Decode ( & updatedOrder ) db . Save ( & updatedOrder ) w . Header (). Set ( "Content-Type" , "application/json" ) json . NewEncoder ( w ). Encode ( updatedOrder ) }

4. Delete API

Finally, we have the delete API. Similar to the GetOrder API, we read the orderId path param from the request. To facilitate comparison to the OrderId field which is a uint , the inputOrderId string needs to be converted to a uint as well.

We then use the db.Delete() method to delete the corresponding records from the orders table and the items table. In the below code, we end up calling the Delete() method twice - once for deleting the items, and the next for deleting the order. From what I understand, Gorm tag for cascading deletes is still being worked on and I could not find a simpler approach for deleting associated objects from the database.

(Some DELETE implementations respond with a 200 OK and the deleted resource as the response body) My preferred option for any DELETE API is to not return a response body and use the 204 No Content as the HTTP status code. The last line in the method does exactly this.

func deleteOrder ( w http . ResponseWriter , r * http . Request ) { params := mux . Vars ( r ) inputOrderID := params [ "orderId" ] // Convert `orderId` string param to uint64 id64 , _ := strconv . ParseUint ( inputOrderID , 10 , 64 ) // Convert uint64 to uint idToDelete := uint ( id64 ) db . Where ( "order_id = ?" , idToDelete ). Delete ( & Item {}) db . Where ( "order_id = ?" , idToDelete ). Delete ( & Order {}) w . WriteHeader ( http . StatusNoContent ) }

Running & Testing the App

Finally, we are done with all the APIs, and it’s time take them for a spin. To run the app, navigate to your project directory, and run the following commands:

go build go - orders - api . / go - orders - api

Create Order

curl -H 'Content-Type: application/json' -d '{"orderedAt":"2019-11-09T21:21:46+00:00","customerName":"Tom Jerry","items":[{"itemCode":"123","description":"IPhone 10X","quantity":1}]}' -X POST http://localhost:8080/orders

Get Orders

curl http://localhost:8080/orders

Update Order

curl -H 'Content-Type: application/json' -d '{"orderId":1,"customerName":"Spike Tyke","orderedAt":"2019-11-09T21:21:46Z","items":[{"lineItemId":1,"itemCode":"123","description":"IPhone 10X","quantity":10}]}' -X PUT http://localhost:8080/orders/1

Delete Order

curl -X DELETE http://localhost:8080/orders/1

Do take a look at this post on curl for different options to use with the curl command (verbose, pretty print, etc). You can also use tools like Postman to try these requests out.

Conclusion

In this post, we saw how to build a simple REST API with MySQL using GORM as the ORM framework. I hope you found this post useful, and hope you go on to build much cooler/bigger/better APIs in Golang. You can checkout the complete code for this API from Github. Please do comment below if you have any questions/issues with the code - I’ll be glad to help out.

See Also