Building a CRUD REST API using Postgresql DB in GO

Introduction to CRUD REST API Framework

CRUD is a common abbreviation when it comes to getting started with any framework or programming language. CRUD basically stands for Create, Read, Update and Delete. These are operations that can be performed on resources in your application. Go is not opinionated on how to create these CRUD applications, and therefore it is up to the developer to decide what he/she wants to work with.

In this article, we are going to learn how to develop a simple CRUD application that uses Go Gin framework to route HTTP requests to handlers that correspond to the CRUD operations. We are going to use the PostgreSQL database as our datastore. Beside that, we are also going to incorporate a developer friendly ORM called Gorm.

We are going to build a book API that allows users to perform all the CRUD operations.

Prerequisites

1. Go runtime . Install Go if you haven't already
2. Code Editor of your choice, in this articles I will use Visual Studio Code
3. Postman application for performing CRUD operation

 

Application structure

The application structure that we will use is quite simple. It is made of two modules namely:

1. database module: For all database related operations such as connection to the database
2. controllers module: For CRUD operations.

At the root level we will have the main.go file that will host code for initializing the database, and HTTP routing and running the server.

Follow the following steps to setup the application structure

Create application directory

mkdir go-crud-api && cd go-crud-api

Create database and controller directory

mkdir database controller

Create the main.go file

touch main.go

Initialize  go module

go mod init example.com/go-crud-api

Install dependencies

go get -u github.com/gin-gonic/gin
go get -u gorm.io/gorm
go get -u gorm.io/driver/postgres

 

Database setup

It is a good idea to start with the database setup and the resources that we are going to perform the CRUD operations on. In order to communicate with the database, we need to create a database client that we will use to call Create, Update, Find and Delete methods against the database. This database client will be exported and used in other parts of our code like in the controller module.

In the database module, create a postgres.go file and add the below code.

database/postgres.go

package database
 
import (
   "fmt"
   "log"
 
   "gorm.io/driver/postgres"
   "gorm.io/gorm"
)
 
var DB *gorm.DB
var err error
 
type Book struct {
   gorm.Model
   Title  string `json:"title"`
   Author string `json:"author"`
}
 
func DatabaseConnection() {
   host := "localhost"
   port := "5432"
   dbName := "postgres"
   dbUser := "postgres"
   password := "pass1234"
   dsn := fmt.Sprintf("host=%s port=%s user=%s dbname=%s password=%s sslmode=disable",
       host,
       port,
       dbUser,
       dbName,
       password,
   )
 
   DB, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})
   DB.AutoMigrate(Book{})
   if err != nil {
       log.Fatal("Error connecting to the database...", err)
   }
   fmt.Println("Database connection successful...")
}

We start by importing fmt and log packages to print messages in the terminal. We also import the ORM package to help us with high level interaction with the database. The gorm ORM can be used with many databases(dialect/drivers) like MySQL , PostgreSQL. In our case we are using postgres as our dialect that is why we import the driver with the gorm.io/driver/postgres.

Next thing to do is initialize a DB variable that will be assigned to a postgreSQL client. We also initialize an err variable that will be assigned an error if one occurs.

Next we define our Book resource using the type Book struct statement. Inside the struct we have the Title and Author fields that each book will have. We also have the gorm.Model fields that will add ID, CreatedAt, UpdatedAt and DeletedAt fields after successfuling creating a resource. The Book struct will create a table in our database called Book as we will see later.

After defining a Book struct, we define a DatabaseConnection function that

1. Creates a database client i.e DB
2. Create a Book table in the database

A successful connection to the database requires a host, port, database name, database user and a password. Without these variables, there will be no connection to the database. Please note that these variables need to be put in a configuration and a secret file. For example, that database port and and host can be put in a different configuration file and the password should be put in a secret file like the .env file. Never should you hard code these variables in your code in production.  This is not recommended as hackers can easily access these databases and manipulate the system.

The dns variable concatenates the host, port, dbUser, dbName and password into a single string that our database requires to connect with our application. To open a new connection , we use the DB, err := gorm.Open() statement. If an error occurs while opening the database we log the error and stop the application completely using the log.Fatal() syntax.

If w opening  the database is successful, we migrate our Book struct into the database using the DB.AutoMigrate(Book{}).

Now that connection to the database code is done, we now move to the controller module. Navigate into the controller module, create a gin.go file into the controller module.

Configure CRUD Operation on the Controller

controllers/gin.go

This file hosts all the code for the CRUD operations. These handler functions route HTTP requests from the client and perform database operations accordingly.

Imports

package controllers
 
import (
   "errors"
   "net/http"
 
   "example.com/go-crud-api/database"
   "github.com/gin-gonic/gin"
)

We start off the code by importing the database module in order to access the DB client and the Book struct. We also import gin packages to make use of the gin context to return responses to the client with different status codes.

 

Create

func CreateBook(c *gin.Context) {
   var book *database.Book
   err := c.ShouldBind(&book)
   if err != nil {
       c.JSON(http.StatusBadRequest, gin.H{
           "error": err,
       })
       return
   }
   res := database.DB.Create(book)
   if res.RowsAffected == 0 {
       c.JSON(http.StatusBadRequest, gin.H{
           "error": "error creating a book",
       })
       return
   }
   c.JSON(http.StatusOK, gin.H{
       "book": book,
   })
   return
}

We first off all declare the book variable  using the var book *database.Book statement. This book variable is used to validate the incoming data from the client using the c.ShouldBind() function from the gin context. In case the binding is not successful, we return a response to the client using the c.JSON() method.If the binding is successful, we add the new book into the database using the res := database.DB.Create(&book) statement. If an error occurs when creating a new book, we return a response with a StatusBadRequest status code. On the other hand, we return the newly created book to the client.

 

Read a single book

func ReadBook(c *gin.Context) {
   var book database.Book
   id := c.Param("id")
   res := database.DB.Find(&book, id)
   if res.RowsAffected == 0 {
       c.JSON(http.StatusNotFound, gin.H{
           "message": "book not found",
       })
       return
   }
   c.JSON(http.StatusOK, gin.H{
       "book": book,
   })
   return
}

To read a book, we first of all get the id from the gin context using the id := c.Param(“id”) statement. We then query the database using the id using the res:= database.DB.Find(&book, id). When a book is not found we return a response to the client stating that the book was not found with a 404 status code. If the book with the id is found it is returned to the client with a 200 status code to the client.

 

Read all books

func ReadBooks(c *gin.Context) {
   var books []database.Book
   res := database.DB.Find(&books)
   if res.Error != nil {
       c.JSON(http.StatusNotFound, gin.H{
           "error": errors.New("authors not found"),
       })
       return
   }
   c.JSON(http.StatusOK, gin.H{
       "books": books,
   })
   return
}

Reading all books from the database is almost the same as reading a single book, the difference being that you do not require to pass any id filter to the database. To get all books, the res := database.DB.Find(&book) statement is used. This returns all the books from the database.

 

Update

func UpdateBook(c *gin.Context) {
   var book database.Book
   id := c.Param("id")
   err := c.ShouldBind(&book)
  
   if err != nil {
       c.JSON(http.StatusBadRequest, gin.H{
           "error": err,
       })
       return
   }
 
   var updateBook database.Book
   res := database.DB.Model(&updateBook).Where("id = ?", id).Updates(book)
 
   if res.RowsAffected == 0 {
       c.JSON(http.StatusBadRequest, gin.H{
           "error": "book not updated",
       })
       return
   }
   c.JSON(http.StatusOK, gin.H{
       "book": book,
   })
   return
}

Updating a book requires that you use an id to determine which book needs to be updated from the database. We first declare a book variable using the var book database.Book statement. We then use the c.ShouldBind() statement to bind the incoming data from the client with the book variable. After a successful binding, we use the id from the id from id := c.Param(“id”) statement to query a book from the database. We also declare a book variable called updateBook using the var updateBook statement. The updateBook variable stores the final results of the updated book.

 

Delete

func DeleteBook(c *gin.Context) {
   var book database.Book
   id := c.Param("id")
   res := database.DB.Find(&book, id)
   if res.RowsAffected == 0 {
       c.JSON(http.StatusNotFound, gin.H{
           "message": "book not found",
       })
       return
   }
   database.DB.Delete(&book)
   c.JSON(http.StatusOK, gin.H{
       "message": "book deleted successfully",
   })
   return
}

In order to delete a book, we need the id of the book to delete and then delete it from the database. The statement res := database.DB.Delete(&book, id) statement performs the delete operation.

 

main.go 

Now that the database and controller code is ready, we navigate to the main.go file and add the below code. We start by importing the code from the controllers and the database modules. We also import gin to help us set up a server to serve HTTP requests. We call the database.DatabaseConnection() from the database module to set the postgres database variable.

We then define our router using the r:=gin.Default() statement. Gin provided a clean way of writing routes.  Next we define routes that correspond to different CRUD operations. For example, all the POST methods correspond to Create operation, GET corresponds to Read operation, PUT corresponds to Update methods and DELETE corresponds to Delete operation.

Finally we use the r.Run(“:5000”) to run our server that listens on port 5000.

package main
 
import (
   "fmt"
 
   "example.com/go-crud-api/controllers"
   "example.com/go-crud-api/database"
   "github.com/gin-gonic/gin"
)
 
func main() {
   fmt.Println("Starting application ...")
   database.DatabaseConnection()
 
   r := gin.Default()
   r.GET("/books/:id", controllers.ReadBook)
   r.GET("/books", controllers.ReadBooks)
   r.POST("/books", controllers.CreateBook)
   r.PUT("/books/:id", controllers.UpdateBook)
   r.DELETE("/books/:id", controllers.DeleteBook)
   r.Run(":5000")
}

 

Starting the server

To run the server navigate to the root of your application in the terminal and issues the below command.

$ go run main.go
Starting application ...
Database connection successful...
[GIN-debug] [WARNING] Creating an Engine instance with the Logger and Recovery middleware already attached.
 
[GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production.
- using env:   export GIN_MODE=release
- using code:  gin.SetMode(gin.ReleaseMode)
 
[GIN-debug] GET    /books/:id                --> example.com/go-crud-api/controllers.ReadBook (3 handlers)
[GIN-debug] GET    /books                    --> example.com/go-crud-api/controllers.ReadBooks (3 handlers)
[GIN-debug] POST   /books                    --> example.com/go-crud-api/controllers.CreateBook (3 handlers)
[GIN-debug] PUT    /books/:id                --> example.com/go-crud-api/controllers.UpdateBook (3 handlers)
[GIN-debug] DELETE /books/:id                --> example.com/go-crud-api/controllers.DeleteBook (3 handlers)
[GIN-debug] [WARNING] You trusted all proxies, this is NOT safe. We recommend you to set a value.
Please check https://pkg.go.dev/github.com/gin-gonic/gin#readme-don-t-trust-all-proxies for details.
[GIN-debug] Listening and serving HTTP on :5000

Now our RESTful APi is ready for testing

 

Testing CRUD REST API Operation

In order to test these CRUD operations, open your Postman application and test as shown below.

Create book

---

Read book

---

Read books

---

Update book

---

Delete book

 

Summary

In this article, we learn how to get up and running with a simple CRUD application that uses PostgreSQL database and Go Gin web framework for routing  HTTP requests. CRUD operations match really well with HTTP methods such as POST, GET, PUT, and DELETE and therefore are easy to learn and set up a production grade ready application.

 

References

https://github.com/gin-gonic/gin
https://gorm.io/docs/index.html