Golang CRUD application using MySql

As the title reads, In this tutorial, we will implement a basic Golang CRUD application using, of course, Golang as a server-side script and MySql as a Database. We will implement a Rest API in Golang that will perform Create, Read, Update and Delete operations. We will use gorilla/mux package to create rest APIs in the application.

In the previous article, we understood how to connect and use MySql with Golang. If you are not familiar with the basics of GoLang then I urge you to read that tutorial and understand the basic stuff. Once you understand how to connect MySql with GoLang then you can go ahead start implementing this application.

 Download

To demonstrate Golang CRUD application, we will make a list of user’s and we will create functionality to Update/ Remove the user from the list of users. Here we will focus on GoLang and MySql side, hence we won’t cover the Client side Javascript into this article. Though, when you will download the source code, you will get a full code including client-side AngularJs as well.

1. Understanding the project structure

1.As I said earlier, here we will create an application, which will implement a list of user’s. Here we will give a very little bit of styling to our web application just to make it look presentable. Since we are using plain Javascript and there is not much on the client side to understand, hence we will focus on the Backend part only.

2.Inside the client folder, we will write down the Javascript scripts and inside view folder, we will write down the MARKUP.

2. Creating a GoLang Server

1.Create aserver.goin the root of the project, which will be our entry point for the project. Here we will make a connection with the MySql database and we will define our application routes.

=>Inside themain()function, First we are printing some information.

=>Then usingconnectDatabse()function, we will make a MySql connection.

=>In the next line, we will createroutevariable, which will hold Route instance.

=>ThenaddApproutes()function register application routes.

=>And at the end, usinghttp.ListenAndServe()we will start our GO server.

server.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
*/
package main

import (
"fmt"
"log"
"net/http"

"github.com/gorilla/mux"
)

func main() {

fmt.Println("Server will start at http://localhost:8000/")

connectDatabse()

route := mux.NewRouter()

addApproutes(route)

log.Fatal(http.ListenAndServe(":8000", route))
}

3. Connecting GoLang to MySql

1.Create adb.goin the root of the project, Here we will connect our GoLang app with MySql database.

=>In the below code, first we have included thedatabase/sqlpackage and thengo-mysql-driver.

=>Then we have created variabledb, which will hold the MySql connection. Thisdbvariable will be available inside all the files under themainpackage.

=>Inside theconnectDatabse()function we will create MySql connection as shown below,

db.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
*/
package main

import (
"database/sql"
"fmt"

_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB
var err error

func connectDatabse() {
db, err = sql.Open("mysql", "root:root@/test")
fmt.Println("Database connected.")
}

4. Adding GoLang routes in the application and Rendering the Home Page

1.Create aroutes.goin the root of the project, in this file register application routes. We will usegorilla/muxpackage to register routes.

=>The functionsetStaticFolder()will tell the GoLang route that/publicfolder will contain all the static files.

=>ThenaddApproutes()function will register all the routes in the application.

routes.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
*/
package main

import (
"fmt"
"net/http"

"github.com/gorilla/mux"
)

func setStaticFolder(route *mux.Router) {
fs := http.FileServer(http.Dir("./public/"))
route.PathPrefix("/public/").Handler(http.StripPrefix("/public/", fs))
}

func addApproutes(route *mux.Router) {

setStaticFolder(route)

route.HandleFunc("/", renderHome)

}

=> If you notice we have called a renderHome method to display the home page on the / URL. This method is defined inside theroutes-handlers.go file.

=> This will handle all the request payload validation and send an appropriate response based on the request. Create fileroutes-handlers.go in the root of the project folder and write down the below code,

routes-handlers.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
*/
package main

import (
"encoding/json"
"net/http"

"github.com/gorilla/mux"
)

func renderHome(response http.ResponseWriter, request *http.Request) {
http.ServeFile(response, request, "views/index.html")
}

5. Getting the list of users (Creating a GET HTTP Request)

1. Registering GET HTTP route

1. As the above title reads, we will add the route to fetch a list of users in this section. Here we will fetch data from MySql table and we will return the response as a JSON object.

=>Open the routes.go file and add the below code.

=>We have added /user endpoint with GET verb, that’s it.

routes.go:

route.HandleFunc("/user", getUsers).Methods("GET")

2. Adding Route Handler

3. Now Add the getUsers() in route-handler.go file, Here you will validate the request and you will fetch the response from the database.

4. Once the response is pulled from the database, then you will send the response client as a json byte.

=> Open the route-handler.go file and add the below function.

=> First we have createdhttpError named variable with HTTP Error Code and Message, in case if we get any error then we will send httpError as a JSON response.

=>getUsersFromDB() function is defined in helper-methods.go file. All the quries are handled and executed in that file.

=> FunctiongetUsersFromDB() will return the JSON byte of users.

=> Inside the if-else, we have used returnErrorResponse() function, which is defined inside the route-handler.go. This function returns the error response.

=> After passing the if-else block, we will return the JSON byte of users.

route-handler.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
*/
func getUsers(response http.ResponseWriter, request *http.Request) {
var httpError = ErrorResponse{
Code: http.StatusInternalServerError, Message: "It's not you it's me.",
}
jsonResponse := getUsersFromDB()

if jsonResponse == nil {
returnErrorResponse(response, request, httpError)
} else {
response.Header().Set("Content-Type", "application/json")
response.Write(jsonResponse)
}
}

3. Pulling users from the Database using MySql

5. Now we will use the database connection we created db.go file. Using that db variable you can execute MySql queries.

6. Inside the helper-methods.go file, we will write functions that will perform CRUD operations on MySql table.

=> Open the helper-methods.go file add the below function. In the below function we have created two variables user and users having User and User[] interface respectively.

1. Theuservariable will hold the instance of the mysql user data inside for loop.
2. Theusersvariable will hold the array of users.

=>Then we have used MySqlSelectquery to fetch the record from the database.

=>Later we have appliedfor loopon the mysql records in order to fetch the result.

=>And rest of code is just for error handling.

helper-methods.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
 */
func getUsersFromDB() []byte {
var (
user  User
users []User
)
rows, err := db.Query("SELECT * FROM users")
if err != nil {
fmt.Println(err)
return nil
}
for rows.Next() {
rows.Scan(&user.ID, &user.Name, &user.Lname, &user.Country)
users = append(users, user)
}
defer rows.Close()
jsonResponse, jsonError := json.Marshal(users)
if jsonError != nil {
fmt.Println(jsonError)
return nil
}
return jsonResponse
}

6. Adding a New user (Creating aPOSTHTTP Request)

1. In this section, we will add new users to the application, users will have three properties Name, Last name, and country. Now you know, where to add the routes in the application isn’t it? So we will directly jump to the coding part.

=> Open the routes.go file and add the /user route with the POST VERB.

route.HandleFunc("/user", insertUser).Methods("POST")

2. Next, we have to handle the request and after validating the request parameters we will add the user into the database. So open the routes-handlers.go file add the below Function.

=>First we have createdhttpErrornamed variable with HTTP Error Code and Message, in case if we get any error then we will send httpError as a JSON response.

=> Then we haveuserDetails variable with type User.

=> Then we are decoding the request body and by passing the reference ofuserDetails variable.

=> After that, we have validations and based on that we will return the proper error message.

=> If everything goes smoothly without error, then we callinsertUserInDB() Function to insert a new user in the database.

=>insertUserInDB() Function will return JSON byte response and we will send that response to the client.

routes-handlers.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
 */
func insertUser(response http.ResponseWriter, request *http.Request) {
var httpError = ErrorResponse{
Code: http.StatusInternalServerError, Message: "It's not you it's me.",
}
var userDetails User
decoder := json.NewDecoder(request.Body)
err := decoder.Decode(&userDetails) 
defer request.Body.Close()
if err != nil {
returnErrorResponse(response, request, httpError)
} else {
httpError.Code = http.StatusBadRequest
if userDetails.Name == "" {
httpError.Message = "First Name can't be empty"
returnErrorResponse(response, request, httpError)
} else if userDetails.Lname == "" {
httpError.Message = "Last Name can't be empty"
returnErrorResponse(response, request, httpError)
} else if userDetails.Country == "" {
httpError.Message = "Country can't be empty"
returnErrorResponse(response, request, httpError)
} else {
isInserted := insertUserInDB(userDetails)
if isInserted {
getUsers(response, request)
} else {
returnErrorResponse(response, request, httpError)
}
}
}
}

3. As you know we execute all our queries in helper-methods.go file. So open the helper-methods.go file and add the below code,

=> In the below code we are executing MySql insert query along with some error handling.

helper-methods.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
 */
func insertUserInDB(userDetails User) bool {
stmt, err := db.Prepare("INSERT into users SET Name=?,Lname=?,Country=?")
if err != nil {
fmt.Println(err)
return false
}
_, queryError := stmt.Exec(userDetails.Name, userDetails.Lname, userDetails.Country)
if queryError != nil {
fmt.Println(queryError)
return false
}
return true
}

7. Deleting an existing user (Creating aDELETEHTTP Request)

1. Now we will delete a user from the list of users. This will require us to execute the MySql delete query and the rest of process will be identical.

2. So we will follow the same process again, open the routes.go and add the below code,

route.HandleFunc("/user/{id}", deleteUser).Methods("DELETE")

3. After this, we will write a function to handle the request and response. Open the routes-handler.go file and add the below code,

=> In this code, first we extract the user id from the URL. When using gorilla/mux package we can write something like this to extract the URL parameter,

userID := mux.Vars(request)["id"]

=> Then we use deleteUserFromDB() function to delete the user from the database.

routes-handler.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
 */
func deleteUser(response http.ResponseWriter, request *http.Request) {
var httpError = ErrorResponse{
Code: http.StatusInternalServerError, Message: "It's not you it's me.",
}
userID := mux.Vars(request)["id"]
if userID == "" {
httpError.Message = "User id can't be empty"
returnErrorResponse(response, request, httpError)
} else {
isdeleted := deleteUserFromDB(userID)
if isdeleted {
getUsers(response, request)
} else {
returnErrorResponse(response, request, httpError)
}
}
}

4. ThedeleteUserFromDB() function is defined inside the helper-method.go file. In this method, we will execute the MySql Delete query.

helper-method.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
 */
func deleteUserFromDB(userID string) bool {
stmt, err := db.Prepare("DELETE FROM users WHERE id=?")
if err != nil {
fmt.Println(err)
return false
}
_, queryError := stmt.Exec(userID)
if queryError != nil {
fmt.Println(queryError)
return false
}
return true
}

8. Updating an existing user (Creating aPUTHTTP Request)

1. This step is almost similar to the Adding new user except for MySql query. Here we will fire UPDATE MySql, instead of INSERT query.

2. Let’start off by adding a new route in routes.go file and then we will add route handler function. Open the routes.go and add the below route,

route.HandleFunc("/user", updateUser).Methods("PUT")

=> TheupdateUser() Function is defined inside the routes-handler.go file.

=> TheupdateUser() Function will validate the request and it will update the respective user.

=> After performing update operation it will return the updated list of users.

Open the route-handler.go file and add the below function,

/*
*Golang CRUD application
*@author Shashank Tiwari
*/
func updateUser(response http.ResponseWriter, request *http.Request) {
var httpError = ErrorResponse{
Code: http.StatusInternalServerError, Message: "It's not you it's me.",
}
var userDetails User
decoder := json.NewDecoder(request.Body)
err := decoder.Decode(&userDetails)
defer request.Body.Close()
if err != nil {
returnErrorResponse(response, request, httpError)
} else {
httpError.Code = http.StatusBadRequest
if userDetails.Name == "" {
httpError.Message = "First Name can't be empty"
returnErrorResponse(response, request, httpError)
} else if userDetails.ID == 0 {
httpError.Message = "user Id can't be empty"
returnErrorResponse(response, request, httpError)
} else if userDetails.Lname == "" {
httpError.Message = "Last Name can't be empty"
returnErrorResponse(response, request, httpError)
} else if userDetails.Country == "" {
httpError.Message = "Country can't be empty"
returnErrorResponse(response, request, httpError)
} else {
isUpdated := updateUserInDB(userDetails)
if isUpdated {
getUsers(response, request)
} else {
returnErrorResponse(response, request, httpError)
}
}
}
}

=> In the above code, we have usedupdateUserInDB() function; in which we will update the user details based on the parameter passed to it.

=> TheupdateUserInDB() function is defined inside the helper-methods.go. In this function, we will execute the UPDATE MySql query. After updating the user details, this function will return the boolean value indicating the end result of the query execution.

helper-methods.go:

/*
*Golang CRUD application
*@author Shashank Tiwari
*/
func updateUserInDB(userDetails User) bool {
stmt, err := db.Prepare("UPDATE users SET name=?,lname=?,country=? WHERE id=?")
if err != nil {
fmt.Println(err)
return false
}
_, queryError := stmt.Exec(userDetails.Name, userDetails.Lname, userDetails.Country, userDetails.ID)
if queryError != nil {
fmt.Println(queryError)
return false
}
return true
}

Conclusion

In this article, we understood, how to create rest API in GoLang and how to implement a basic GoLang CRUD application. If you have any questions or doubts let me know in below comment box, So that’s it for as of now.

Also, If you find this article useful then do share with others.