CRUD Operations on PostgreSQL with a Golang REST API 2024

In this article, you will learn how to create a CRUD REST API in Golang using Fiber as the web framework and PostgreSQL as the database. By the end of this tutorial, you will be able to create, read, update, and delete feedback entries in the PostgreSQL database using HTTP requests.

We will start from scratch, covering everything from setting up the Go project and spinning up a PostgreSQL server using Docker, to connecting the Go application to the PostgreSQL database and creating route controllers and corresponding routes.

More practice:

CRUD Operations on PostgreSQL with a Golang REST API

Run and Test the REST API

To get the Go API project up and running on your machine, follow these simple steps:

Download or clone the Golang REST API project from its GitHub repository: https://github.com/wpcodevo/go-postgres-crud-rest-api. Afterwards, open the project in your preferred IDE or text editor.
Make sure Docker is running on your computer, then open the terminal in the project’s root directory and run docker-compose up -d to start the PostgreSQL server.
Next, run the command go run main.go. This will install all necessary packages, migrate the GORM schema to the PostgreSQL database, and start the Fiber HTTP server.
First, import the Feedback App.postman_collection.json file from the root directory into Postman or the Thunder Client extension in VS Code. This collection includes predefined CRUD HTTP requests, sample request data, and other settings to streamline testing the CRUD endpoints.
With the collection file now imported into Postman, you can test the various CRUD endpoints by sending requests to the Go server.

Set Up the Golang Project

To start, we need to initialize the Go project. Navigate to your desktop or any preferred location where you’d like to store the source code, and create a new directory named go-postgres-crud-rest-api. You can name the project folder anything you like.

Once the directory is created, open it in your IDE or text editor. In the integrated terminal, run the following command to initialize the folder as a Go project. Be sure to replace <github_username> with your GitHub username:


go mod init github.com//go-postgres-crud-rest-api

Next, install the dependencies required for building the REST API. This tutorial will use the Fiber framework to handle HTTP requests and GORM to interact with a PostgreSQL database. Run the following commands to add these dependencies:


go get github.com/gofiber/fiber/v2
go get github.com/google/uuid
go get github.com/go-playground/validator/v10
go get -u gorm.io/gorm
go get gorm.io/driver/postgres
go get github.com/spf13/viper

fiber – A web framework inspired by Express.js. It provides routing, middleware, and other tools for creating REST APIs efficiently in Go.
uuid – A package for generating universally unique identifiers (UUIDs) in Go.
validator – A package that provides a set of tools for validating struct fields.
gorm – An ORM (Object-Relational Mapping) library for Go.
postgres – A PostgreSQL driver for GORM.
viper – A configuration management package that loads configuration values from different sources, like environment variables or config files.

With the dependencies installed, let’s set up a basic Fiber HTTP server to get us started before diving into the actual CRUD implementation. We’ll create a health check endpoint that returns a simple message in a JSON object.

In the root directory of your project, create a main.go file and add the following code:

main.go


package main

import (
	"log"

	"github.com/gofiber/fiber/v2"
)

func main() {

	app := fiber.New()

	app.Get("/api/healthchecker", func(c *fiber.Ctx) error {
		return c.Status(200).JSON(fiber.Map{
			"status":  "success",
			"message": "CRUD Operations on PostgreSQL using Golang REST API",
		})
	})

	log.Fatal(app.Listen(":8000"))
}

Next, start the Fiber HTTP server by running:

go run main.go

Within a few seconds, the server should be listening on port 8000. To automatically restart the server whenever you make changes, install the Air binary with the following:

go install github.com/air-verse/air@latest

After installing Air, stop the currently running server and run:

air

This will start the server with automatic reloading enabled. Now, open your browser and go to http://localhost:8000/api/healthchecker. You should see a JSON response indicating that the Go server is running correctly.

testing the health checker endpoint of the fiber HTTP server

Set Up PostgreSQL with Docker

Next, let’s set up a PostgreSQL database using Docker. You can skip this step if you plan to use a cloud-based PostgreSQL database instead.

In the root directory of your project, create a file named docker-compose.yml and add the following configuration:

docker-compose.yml


services:
  postgres:
    image: postgres:latest
    container_name: postgres
    ports:
      - '6500:5432'
    volumes:
      - progresDB:/var/lib/postgresql/data
    env_file:
      - ./app.env
  pgAdmin:
    image: dpage/pgadmin4
    container_name: pgAdmin
    env_file:
      - ./app.env
    ports:
      - '5050:80'
volumes:
  progresDB:

In addition to the Postgres service, we have included a pgAdmin server, which provides a web-based tool for managing and administering PostgreSQL databases.

In the Docker Compose configuration, both the Postgres and pgAdmin services retrieve their secrets from an app.env file. To ensure these credentials are available to Docker Compose, create an app.env file in your root directory and include the following environment variables:

app.env


POSTGRES_HOST=127.0.0.1
POSTGRES_USER=postgres
POSTGRES_PASSWORD=password123
POSTGRES_DB=golang-gorm
POSTGRES_PORT=6500

PORT=8000

PGADMIN_DEFAULT_EMAIL=admin@admin.com
PGADMIN_DEFAULT_PASSWORD=password123

With the environment variables set, run the command docker-compose up -d to start the Postgres and pgAdmin servers in their respective Docker containers.

both the pgAdmin and PostgreSQL servers running in Docker Containers

Load the Environment Variables

Next, let’s use the Viper package to load the environment variables from the app.env file so that we can access them within our application.

To do this, create a new directory called initializers at the root level of your project. Within the initializers directory, create a file named env.go and add the following code:

initializers/env.go


package initializers

import (
	"github.com/spf13/viper"
)

type Env struct {
	DBHost         string `mapstructure:"POSTGRES_HOST"`
	DBUserName     string `mapstructure:"POSTGRES_USER"`
	DBUserPassword string `mapstructure:"POSTGRES_PASSWORD"`
	DBName         string `mapstructure:"POSTGRES_DB"`
	DBPort         string `mapstructure:"POSTGRES_PORT"`
	ServerPort     string `mapstructure:"PORT"`
}

func LoadEnv(path string) (Env Env, err error) {
	viper.AddConfigPath(path)
	viper.SetConfigType("env")
	viper.SetConfigName("app")

	viper.AutomaticEnv()

	err = viper.ReadInConfig()
	if err != nil {
		return
	}

	err = viper.Unmarshal(&Env)
	return
}

Connect the App to the PostgreSQL Database

Now, let’s create the code that will enable our application to connect to the PostgreSQL server. We will also use the AutoMigrate function provided by GORM to apply our migrations to the Postgres schema whenever we start our Fiber server.

Navigate to the initializers directory and create a file named db.go, then insert the following code:

initializers/db.go


package initializers

import (
	"fmt"
	"log"

	"github.com/wpcodevo/go-postgres-crud-rest-api/models"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

var DB *gorm.DB

func ConnectDB(env *Env) {
	var err error
	dsn := fmt.Sprintf("host=%s user=%s password=%s dbname=%s port=%s sslmode=disable TimeZone=Asia/Shanghai", env.DBHost, env.DBUserName, env.DBUserPassword, env.DBName, env.DBPort)

	DB, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})
	if err != nil {
		log.Fatal("Failed to connect to the Database")
	}

	DB.Logger = logger.Default.LogMode(logger.Info)

	DB.AutoMigrate(&models.Feedback{})

	fmt.Println("🚀 Connected Successfully to the Database")
}

In the code above, we configured GORM to output logs related to database operations in the terminal by using the logger.Default.LogMode() method.

Create the Database Model and Request Schemas

Since we referenced the models.Feedback{} struct in the database connection code but haven’t created it yet, let’s proceed to define it.

To create our database model along with the validation logic, start by creating a new folder called models in the root directory of your project.

Inside the models directory, create a file named feedback.model.go and include the following code:

models/feedback.model.go


package models

import (
	"time"

	"github.com/go-playground/validator/v10"
	"github.com/google/uuid"
	"gorm.io/gorm"
)

type Feedback struct {
	ID        string    `gorm:"type:char(36);primary_key" json:"id,omitempty"`
	Name      string    `gorm:"not null" json:"name,omitempty"`
	Email     string    `gorm:"not null" json:"email,omitempty"`
	Feedback  string    `gorm:"uniqueIndex:idx_feedback;not null" json:"feedback,omitempty"`
	Rating    *float32  `gorm:"not null" json:"rating,omitempty"`
	Status    string    `json:"status,omitempty"`
	CreatedAt time.Time `gorm:"not null;default:'1970-01-01 00:00:01'" json:"createdAt,omitempty"`
	UpdatedAt time.Time `gorm:"not null;default:'1970-01-01 00:00:01';ON UPDATE CURRENT_TIMESTAMP" json:"updatedAt,omitempty"`
}

func (feedback *Feedback) BeforeCreate(tx *gorm.DB) (err error) {
	feedback.ID = uuid.New().String()
	return nil
}

var validate = validator.New()

type ErrorResponse struct {
	Field string `json:"field"`
	Tag   string `json:"tag"`
	Value string `json:"value,omitempty"`
}

func ValidateStruct[T any](payload T) []*ErrorResponse {
	var errors []*ErrorResponse
	err := validate.Struct(payload)
	if err != nil {
		for _, err := range err.(validator.ValidationErrors) {
			var element ErrorResponse
			element.Field = err.StructNamespace()
			element.Tag = err.Tag()
			element.Value = err.Param()
			errors = append(errors, &element)
		}
	}
	return errors
}

type CreateFeedbackSchema struct {
	Name     string   `json:"name" validate:"required"`
	Email    string   `json:"email" validate:"required"`
	Feedback string   `json:"feedback" validate:"required"`
	Rating   *float32 `json:"rating" validate:"required"`
	Status   string   `json:"status,omitempty"`
}

type UpdateFeedbackSchema struct {
	Name     string   `json:"name,omitempty"`
	Email    string   `json:"email,omitempty"`
	Feedback string   `json:"feedback,omitempty"`
	Rating   *float32 `json:"rating,omitempty"`
	Status   string   `json:"status,omitempty"`
}

ValidateStruct – This function allows us to validate the data fields included in the incoming request body.
CreateFeedbackSchema – This struct defines the expected format of the incoming data when creating new feedback.
UpdateFeedbackSchema – This struct defines the expected format of the incoming data when updating an existing feedback entry in the application.

Perform the CRUD Operations

Now that we have established a connection between our application and the running PostgreSQL database, we can proceed to implement the CRUD endpoint handlers that will perform the necessary operations on the database.

Handle the Create Operation

Let’s begin with the Create operation. We will start by extracting the data from the incoming request body. Next, we will use the ValidateStruct function to validate the data according to the defined validation rules.

Once the data passes validation, the DB.Create function will insert the new feedback into the database. If the operation is successful, a copy of the feedback will be returned to the user in a JSON response.

To implement this, create a controllers directory. Within this directory, create a feedback.controller.go file and include the following code: