Simple CRUD API with Go, Gin and PostgreSQL ORM

The Go programming language is simple and easy to learn yet powerful, A lot of new powerful applications like Docker are built with Go. Channels and goroutines help's us to achieve concurrency easier than ever before.

I'm assuming that you have already installed GO in your machine and set up the $GOPATH and $GOROOT.

In this project we are going to do CRUD operation on a user object, We will be using Gin framework to build the REST api services and PostgreSQL DB to save the user data, also we will be using the Beego ORM for making queries to the PostgreSQL DB

Create a new folder inside your $GOPATH/src which will be our project folder, Lets first start making the connections to the PostgreSQL database

Install the following dependencies.

go get github.com/astaxie/beego/orm
go get github.com/lib/pq

Create a new folder inside your project folder and name it as models, Create a new file db.go

package models

import (
    "github.com/astaxie/beego/orm"
    _ "github.com/lib/pq" //PostgreSQL Driver
)

var ormObject orm.Ormer

// ConnectToDb - Initializes the ORM and Connection to the postgres DB
func ConnectToDb() {
    orm.RegisterDriver("postgres", orm.DRPostgres)
    orm.RegisterDataBase("default", "postgres", "user=dbUser password=yourPassword dbname=dbName host=dbHost sslmode=disable")
    orm.RegisterModel(new(Users))
    ormObject = orm.NewOrm()
}

// GetOrmObject - Getter function for the ORM object with which we can query the database
func GetOrmObject() orm.Ormer {
    return ormObject
}

and another new file named users.go

package models

// Users - Model for the uses table
type Users struct {
    UserId   int    `json:"user_id" orm:"auto"`
    Email    string `json:"email" orm:"size(128)"`
    Password string `json:"password" orm:"size(64)"`
    UserName string `json:"user_name" orm:"size(32)"`
}

In the db.go file we are creating a global orm object by registering the PostgreSQL DB and the driver and we have a getter function GetOrmObject() which just returns the created orm object.

The users.go defines the model of the user object over which we are going to do the CRUD operation, orm:"size(128)" defines as which data type it is going to be stored in the DB, orm:"auto" defines that user_id is auto increment in the DB and json:"user_id" defines how this property will be named in the JSON object.

Next, we have to initialize our gin app and define the API endpoints through which will be doing the CRUD operations. Use this command to install Gin go get github.com/gin-gonic/gin

Create a new file main.go

package main

import (
    "github.com/astaxie/beego/orm"
)
var ORM orm.Ormer

func init() {
    models.ConnectToDb()
    ORM = models.GetOrmObject()
}

The above code makes a new connection the DB and gets the ORM object and stores it in the global variable ORM, The init function executes before the main function is executed, Now we have to initialize the gin app inside the main function.

    "./models"
    "net/http"

import also the above packages to the main.go file

func main() {
    router := gin.Default()
    router.POST("/createUser", createUser)
    router.GET("/readUsers", readUsers)
    router.PUT("/updateUser", updateUser)
    router.DELETE("/deleteUser", deleteUser)
    router.Run(":3000")
}

In the above code, we are creating a new gin router object and creating new API routes to create, read, update and delete a user and register the handler functions for the same and start the gin app at port 3000. Next, we will create the handler functions for each route.

func createUser(c *gin.Context) {
    var newUser models.Users
    c.BindJSON(&newUser)
    _, err := ORM.Insert(&newUser)
    if err == nil {
        c.JSON(http.StatusOK, gin.H{
            "status": http.StatusOK, 
            "email": newUser.Email,
            "user_name": newUser.UserName, 
            "user_id": newUser.UserId})
    } else {
        c.JSON(http.StatusInternalServerError, 
            gin.H{"status": http.StatusInternalServerError, "error": "Failed to create the user"})
    } 
}

In the above function, we are creating a variable of type user and binding that with the gin context so that we can parse the user information which we add to the body of the API request, we are using ORM.insert to insert the user details to the DB and if there is no error we return success message and the user object, else we return internal server error.

To start the application run go run main.go

Postman Response :

func readUsers(c *gin.Context) {
    var user []models.Users
    fmt.Println(ORM)
    _, err := ORM.QueryTable("users").All(&user)
    if(err == nil) {
        c.JSON(http.StatusOK, gin.H{"status": http.StatusOK, "users": &user})
    } else {
        c.JSON(http.StatusInternalServerError, 
            gin.H{"status": http.StatusInternalServerError, "error": "Failed to read the users"})
    }
}

In the above function, we are querying the table users for the DB using ORM.QueryTable("users") and All(&user) returns all the rows without filtering and saves it to the local user array, If the query was executed successfully we return the users array in the response.

Postman response:

func updateUser(c *gin.Context) {
    var updateUser models.Users
    c.BindJSON(&updateUser)
    _, err := ORM.QueryTable("users").Filter("email", updateUser.Email).Update(
        orm.Params{"user_name": updateUser.UserName, 
            "password": updateUser.Password,})
    if(err == nil) {
        c.JSON(http.StatusOK, gin.H{"status": http.StatusOK})
    } else {
        c.JSON(http.StatusInternalServerError, 
            gin.H{"status": http.StatusInternalServerError, "error": "Failed to update the users"})
    }
}

In the above function we use the user's email in the API request body and query the users table,Filter("email", updateUser.Email) filters the rows which have the email id received from the API request, Update method replaces the username and password of those rows in the DB with what we have received in the API request and we return success

Postman response :

func deleteUser(c *gin.Context) {
    var delUser models.Users
    c.BindJSON(&delUser)
    _, err := ORM.QueryTable("users").Filter("email", delUser.Email).Delete()
    if(err == nil) {
        c.JSON(http.StatusOK, gin.H{"status": http.StatusOK})
    } else {
        c.JSON(http.StatusInternalServerError, 
            gin.H{"status": http.StatusInternalServerError, "error": "Failed to delete the users"})
    }
}

In the above function, we query the users table and filter with respect to the user email in the API request body and .Delete() method deletes the filtered rows from the DB and returns success.

Postman response :

The full code can be found at this GitHub Repo, The API documentation for the Beego ORM can be found here