Golang CRUD REST API MySQL Tutorial

Introduction

        I was searching online for a complete tutorial on Golang CRUD REST API integration with MySQL database with all the CRUD operations and I found many confusing posts. Some post did not have all the CRUD operations, some gave complicated approaches and some had other general code issues. Hence I have come up with this post to give you a comprehensive overview of all the CRUD operations. Please feel free to use my application as a base to build your own application.

Golang

        Go or Golang, as it is called, from their website, is an open source programming language that makes it easy to build simple, reliable, and efficient software. It is gaining popularity due to the following features:
  1. Fast: Golang is a compiled language, the code written is directly translated into formats that any processor understands. 
  2. Lightweight and minimalist language: The formal Go language specification is only a few pages
  3. Memory safety: The compiler manages memory safety by introducing “Bounds Check” points in the code that guarantees safe access to the memory.
  4. Native Garbage collection
  5. Native concurrency: The Go language has Goroutines, that are functions that can run independently and simultaneously.
  6. Structural typing: Go is strongly and statically typed with no implicit conversions. It has simple type inference in assign­ments together with untyped numeric constants.
 Docker and Kubernetes are built using Go. 

Requirements to Run the Application:
  1. Golang
  2. Visual Studio Code or any IDE that supports working on Golang code.
  3. MySQL Database
Golang should be installed in your machine. To setup and test if Golang is installed fine, please refer to my post on: Golang Setup.

MySQL Database should be setup and running in your machine. To setup, run and test if the MySQL Database is working fine, please refer to my post on: MySQL Database Setup.

I prefer using Visual Studio Code for working on the Golang code. It can be downloaded from Microsoft Visual Studio Code Website.
To get to know more about Visual Studio Code, you can read: Getting Started on Visual Studio Code. 
If you prefer using any other tool for working on the Golang code, you can go ahead and use it.

Once you have the setup mentioned above ready, you would be able to write and execute Golang code. To keep things simple, I will be writing the entire code for this app in a single file. You will be surprised that the entire code for this app is just 76 lines.

Here are the steps to build this application.

Step 1: Create the file: go.mod

The first step in writing any application in Go is to create the file: go.mod. The following details need to be added to this file:
  1. Go module name or the application name
  2. Go version being used
  3. Dependencies required in the application
We will be using Gin and GORM in this application
Gin: It is a web framework written in Golang which we will use for routing and serving requests over HTTP. To learn more about it, please refer to Gin GitHub page. 
GORM: It is the Object Relational Mapping (ORM) library for Golang. To learn more about it, please refer to its website.

Here is the full file: go.mod: 
module go-rest-mysql

go 1.15

require (
	github.com/fatih/color v1.7.0 // indirect dependency
	github.com/gin-gonic/gin v1.4.0
	github.com/go-sql-driver/mysql v1.4.1
	github.com/jinzhu/gorm v1.9.10
	github.com/mattn/go-colorable v0.1.2 // indirect dependency
)

Step 2: Create the file main.go

As mentioned above to keep things simple, I will writing the code for the entire app in one file. Here is the full file: main.go: 
package main
import (
 "fmt"
 "github.com/gin-gonic/gin"
 _ "github.com/go-sql-driver/mysql" 
 "github.com/jinzhu/gorm"
)
var db *gorm.DB
var err error

type Employee struct {
	ID uint `json:"id"`
	Name string `json:"name"`
	Department string `json:"department"`
	Salary int `json:"salary"`
   }

func main() {
 db, _ = gorm.Open("mysql", "root:@tcp(127.0.0.1:3306)/softwaredevelopercentral?charset=utf8&parseTime=True&loc=Local")
 if err != nil {
    fmt.Println(err)
 }
 defer db.Close()
 db.AutoMigrate(&Employee{})
 r := gin.Default()
 r.GET("/employee/", GetAllEmployees)
 r.GET("/employee/:id", GetEmployee)
 r.POST("/employee", CreateEmployee)
 r.PUT("/employee/:id", UpdateEmployee)
 r.DELETE("/employee/:id", DeleteEmployee)
 r.Run(":8080")
}

func GetAllEmployees(c *gin.Context) {
   var employee []Employee
   if err := db.Find(&employee).Error; err != nil {
      c.AbortWithStatus(404)
      fmt.Println(err)
   } else {
      c.JSON(200, employee)
   }
  }
func GetEmployee(c *gin.Context) {
   id := c.Params.ByName("id")
   var employee Employee
   if err := db.Where("id = ?", id).First(&employee).Error; err != nil {
      c.AbortWithStatus(404)
      fmt.Println(err)
   } else {
      c.JSON(200, employee)
   }
  }
func CreateEmployee(c *gin.Context) {
   var employee Employee
   c.BindJSON(&employee)
   db.Create(&employee)
   c.JSON(200, employee)
  }
func UpdateEmployee(c *gin.Context) {
 var employee Employee
 id := c.Params.ByName("id")
 if err := db.Where("id = ?", id).First(&employee).Error; err != nil {
    c.AbortWithStatus(404)
    fmt.Println(err)
 }
 c.BindJSON(&employee)
 db.Save(&employee)
 c.JSON(200, employee)
}
func DeleteEmployee(c *gin.Context) {
   id := c.Params.ByName("id")
   var employee Employee
   d := db.Where("id = ?", id).Delete(&employee)
   fmt.Println(d)
   c.JSON(200, gin.H{"Employee with ID# " + id: "is deleted"})
  }
Here is the explanation for the code:
Import Block is as below: 
package main
import (
 "fmt"
 "github.com/gin-gonic/gin"
 _ "github.com/go-sql-driver/mysql" 
 "github.com/jinzhu/gorm"
)
In this app, I am using Employee model. In Golang model is defined using struct. Here is the code for this: 
type Employee struct {
	ID uint `json:"id"`
	Name string `json:"name"`
	Department string `json:"department"`
	Salary int `json:"salary"`
   }
main function is to be created with the logic to connect to database, auto create table if not present and creating all the APIs used in the application. We also define the port for running the app. I am using the database named softwaredevelopercentral and I am using root user without password to connect to it. DB URL that I have used for this is: 
  root:@tcp(127.0.0.1:3306)/softwaredevelopercentral?charset=utf8&parseTime=True&loc=Local
Here is the code for main function: 
func main() {
 db, _ = gorm.Open("mysql", "root:@tcp(127.0.0.1:3306)/softwaredevelopercentral?charset=utf8&parseTime=True&loc=Local")
 if err != nil {
    fmt.Println(err)
 }
 defer db.Close()
 db.AutoMigrate(&Employee{})
 r := gin.Default()
 r.GET("/employee/", GetAllEmployees)
 r.GET("/employee/:id", GetEmployee)
 r.POST("/employee", CreateEmployee)
 r.PUT("/employee/:id", UpdateEmployee)
 r.DELETE("/employee/:id", DeleteEmployee)
 r.Run(":8080")
}
Other functions which define the logic for the CRUD operations. Here is the code for this: 
func GetAllEmployees(c *gin.Context) {
   var employee []Employee
   if err := db.Find(&employee).Error; err != nil {
      c.AbortWithStatus(404)
      fmt.Println(err)
   } else {
      c.JSON(200, employee)
   }
  }
func GetEmployee(c *gin.Context) {
   id := c.Params.ByName("id")
   var employee Employee
   if err := db.Where("id = ?", id).First(&employee).Error; err != nil {
      c.AbortWithStatus(404)
      fmt.Println(err)
   } else {
      c.JSON(200, employee)
   }
  }
func CreateEmployee(c *gin.Context) {
   var employee Employee
   c.BindJSON(&employee)
   db.Create(&employee)
   c.JSON(200, employee)
  }
func UpdateEmployee(c *gin.Context) {
 var employee Employee
 id := c.Params.ByName("id")
 if err := db.Where("id = ?", id).First(&employee).Error; err != nil {
    c.AbortWithStatus(404)
    fmt.Println(err)
 }
 c.BindJSON(&employee)
 db.Save(&employee)
 c.JSON(200, employee)
}
func DeleteEmployee(c *gin.Context) {
   id := c.Params.ByName("id")
   var employee Employee
   d := db.Where("id = ?", id).Delete(&employee)
   fmt.Println(d)
   c.JSON(200, gin.H{"Employee with ID# " + id: "is deleted"})
  }

Step 3: Build the application

Build the application using the command below: 
$ go build
If the build is successful, a file named go-rest-mysql.exe is created. If build fails, then the failure reasons are mentioned.

Run Application

Run the application using the command below: 
$ ./go-rest-mysql
On the first run, the application will create a new table named as employees in the MySQL database.

API calls and results:

1. POST API to create an employee
    JSON Request Body:
  {
   "name": "Mary",
   "department": "HR",
   "salary": 15000
  }
2. GET API to get all employees:

3. GET API to get employee by ID

4. PUT  API to update an employee
    http://localhost:8080/employee/2
    JSON Request Body:
    {
        "name": "Jim",
        "department": "Accounts",
        "salary": 14000
    }


5. DELETE API to delete an employee by ID


Conclusion and GitHub link:

    This tutorial gives a comprehensive overview of creating CRUD REST APIs using Golang and integrating the application with MySQL Database. The code for the application used in this post is available on GitHub. 
    Learn the most popular and trending technologies like Blockchain, Cryptocurrency, Machine Learning, Chatbots, Internet of Things (IoT), Big Data Processing, Elastic Stack, React, Highcharts, Progressive Web Application (PWA), Angular 5, GraphQL, Akka HTTP, Play Framework, Dropwizard,   Docker, Netflix Eureka, Netflix Zuul, Spring Cloud, Spring Boot, Flask and RESTful Web Service integration with MongoDB, Kafka, Redis, Aerospike, MySQL DB in simple steps by reading my most popular blog posts at Software Developer Central.   
    If you like my post, please feel free to share it using the share button just below this paragraph or next to the heading of the post. You can also tweet with #SoftwareDeveloperCentral on Twitter. To get a notification on my latest posts or to keep the conversation going, you can follow me on Twitter or Instagram. Please leave a note below if you have any questions or comments.


Comments

Post a Comment

Popular Posts

Elasticsearch, Logstash, Kibana Tutorial: Load MySQL Data into Elasticsearch

Image
Elasticsearch, Logstash, Kibana Tutorial: Load MySQL Data into Elasticsearch Introduction           I was searching for a tutorial online on all the elements of the "Elastic Stack" (formerly the "ELK stack") and all I found was either a tutorial on Elasticsearch only or a tutorial on Logstash only or a tutorial on Kibana only or a data migrate tutorial using Logstash and Elaticsearch. Hence I came up with this tutorial which gives a comprehensive overview of all the elements in the Elastic Stack. The use case that I am using in this post is a data load from MySQL DB into Elasticsearch using Logstash and visualizing this data using Kibana. Elasticsearch         Elasticsearch is a distributed, RESTful search and analytics engine that is based on Apache Lucene . It provides a distributed, multitenant-capable full-text search engine with an HTTP web interface and schema-free JSON documents. For more details on Elasticsearch, ...
Keep reading

Dropwizard MySQL Integration Tutorial

Image
Dropwizard MySQL Integration Tutorial Introduction         Dropwizard is one of the most popular and most used frameworks for building microservices. I am assuming that you have a Basic Knowledge of Dropwizard and have a Basic Dropwizard Application running in your machine. If not, please check my blog on Basic Dropwizard Application by going to the link:  Dropwizard Tutorial . In this tutorial, we will be integrating a Dropwizard Application with MySQL Database, which is a relational database, to perform CRUD operations. The same steps can be followed to connect to any other relational database. Requirements to Run the Application: Java Maven MySQL Database IDE of your choice MySQL Database should be setup and running in your machine. To setup, run and test if the MySQL Database is working fine, please refer to my post on: MySQL Database Setup . In this post, I am using a simple Employee Table to perform CRUD operations. Here is the s...
Keep reading

Send Email in Java

Image
Send Email in Java Introduction         Sending an email is one of the most important and most frequently used functionalities in a technology organization. A software developer should know how this is done. This post will give you a comprehensive overview of sending emails using Java mail libraries. This post will give you an overview of the following: Send a plain text email in HTML format. Create an attachment and send an email. Use an existing file as an attachment and send an email.  As RESTful web services are commonly used today, I will be using Dropwizard for building out the email functionality as RESTful APIs. Hence here are what I will be using in this post: Java mail libraries: To connect to email service provider, compose and send emails Gmail: Email service provider Dropwizard: For developing Email functionalities as RESTful web services         Dropwizard is one of the most popular and most used frameworks ...
Keep reading