Building CRUD API Using Golang and PostgreSQL

3 min readOct 30, 2020

In this post we will build simple food ordering API using Go and persist it with PostgreSQL. Before start, we assumed you had installed Golang and Postgresql on your system.

We use Gin framework for building API as it is fast and lightweight, and then to connect with postgresql we use GORM as ORM library for Golang. First we do following step to install required packages.

go get github.com/gin-gonic/gin
go get github.com/jinzhu/gorm
go get github.com/lib/pq

After installation is succeed, we need to setup project directory. Our project have the following structure:

├── README.md
├── controllers 
│ └── order.go (All function to handle API)
├── database.env (environment variable)
├── main.go (project entry)
├── models  
│ ├── order.go (order and query class structure)
│ └── dbsetup.go (setup database)

models/order.go

In order golang to connect with database and also to receipt JSON formatted API request, we need to setup our order struct. For the purpose of this post, we will create API to submit food ordering. Our API need to get information from the user about what good is it ordered with additional user information. Our list of food will be saved on Menu field and having json data type, so we can save more than one food to order. For user information, we have phone, name and address field.

Also we setup query struct for our API to receive request showing history of user order on specific date.

models/dbsetup.go

To connect to Postgresql using ORM, we use bellow code. After connection success, MPosGORM will be our variable to use throughout code to query database. Please fill username, password and database name according your own setup.

controllers/order.go

On our simple food ordering system API, we implemented CRUD to submit, list, update and delete food information. We separate and put our API business logic into controllers/order.go. Here is list of our food ordering system business logic:

OrderAdd, submit food to be ordered with user phone, name and address information
OrderDelete, delete order by order id
OrderEdit, update ongoing ordered food with new food
OrderShowByDate, list food order history by specific date
OrderShowByPhone, list food order history by user phone number
OrderShowByID, list food order history by specific order id

main.go

Here in main.go is our starting point execution of our application. It start with print current date and time based on local timezone, calling connection to postgre using Gorm adapter, setup api router with Gin, define 6 api endpoint to implement food ordering business logic, and last run api server on port 4000.

Run food ordering API

Time for test our API. After we run our main.go, open new terminal window and run following CURL command.

Submit food order:

$ curl -i -X POST -H "Content-Type: application/json" -d '{"phone":"0811987905", "name":"jhony", "address": "cibubur", "menu": ["mie ayam", "es jeruk"], "total_item": 2, "pay": 80000}' http://localhost:4000/api/order/addHTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Type: application/json
Date: Fri, 30 Oct 2020 07:09:20 GMT
Content-Length: 56{"order_id":"OD-20201030140920","phone":"0811987905"}

List order by specific date and phone number:

$ curl -i -X POST -H "Content-Type: application/json" -d '{"phone":"0811987905", "date": "30-Oct-2020"}' http://localhost:4000/api/order/showHTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Type: application/json
Date: Fri, 30 Oct 2020 07:11:57 GMT
Content-Length: 415[{"id":"3","create_dtm":"2020-10-30T09:38:35.001806Z","order_id":"OD-2020103093835","phone":"0811987905","name":"jhony","address":"cibubur","menu":["nasi goreng","teh tawar"],"total_item":2,"pay":50000},{"id":"4","create_dtm":"2020-10-30T10:57:05.028345Z","order_id":"OD-20201030105705","phone":"0811987905","name":"jhony","address":"cibubur","menu":["kupat tahu","es jeruk","kerupuk"],"total_item":2,"pay":80000}]

List order by specific user phone number:

$ curl -i "http://localhost:4000/api/order/show?phone=0811987905"HTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Type: application/json
Date: Fri, 30 Oct 2020 07:13:42 GMT
Content-Length: 415[{"id":"3","create_dtm":"2020-10-30T09:38:35.001806Z","order_id":"OD-2020103093835","phone":"0811987905","name":"jhony","address":"cibubur","menu":["nasi goreng","teh tawar"],"total_item":2,"pay":50000},{"id":"4","create_dtm":"2020-10-30T10:57:05.028345Z","order_id":"OD-20201030105705","phone":"0811987905","name":"jhony","address":"cibubur","menu":["kupat tahu","es jeruk","kerupuk"],"total_item":2,"pay":80000}]

List order by specific order id:

$ curl -i "http://localhost:4000/api/order/id/OD-20201030105705"HTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Type: application/json
Date: Fri, 30 Oct 2020 07:14:35 GMT
Content-Length: 415[{"id":"3","create_dtm":"2020-10-30T09:38:35.001806Z","order_id":"OD-2020103093835","phone":"0811987905","name":"jhony","address":"cibubur","menu":["nasi goreng","teh tawar"],"total_item":2,"pay":50000},{"id":"4","create_dtm":"2020-10-30T10:57:05.028345Z","order_id":"OD-20201030105705","phone":"0811987905","name":"jhony","address":"cibubur","menu":["kupat tahu","es jeruk","kerupuk"],"total_item":2,"pay":80000}]

Summary

Writing CRUD restful APIs using Go can be easy and fast, and with the help of Gorm adapter, our api data which is food order in this post can be saved to our database and retrieved it back easily. Thank You.