Golang CRUD RESTful API with SQLC and PostgreSQL

This article will teach you how to build a CRUD RESTful API in Golang that runs on a Gonic Gonic HTTP server and uses a PostgreSQL database. You will also learn how to generate the CRUD functions with SQLC and run the database migration with the Golang migrate library.

What is CRUD? The acronym CRUD (Create, Read, Update, and Delete) refers to the four basic operations that can be performed on database applications.

Many programming languages have their own equivalent of CRUD, often with slight variations in the naming of the functions and their implementations. For example, SQL calls the four functions Insert, Select, Update, and Delete. In REST architecture, CRUD also maps to the major HTTP methods.

1. API with Golang, PostgreSQL, SQLC & Gin Gonic: Project Setup
2. Golang, SQLC, and PostgreSQL: JWT Access & Refresh Tokens
3. Golang CRUD RESTful API with SQLC and PostgreSQL

What the course will cover

1. How to set up a Postgres server with Docker compose
2. How to set up SQLC with Golang
3. How to connect the Golang application to the PostgreSQL server
4. How to perform database migration with Golang migrate
5. How to generate Golang code and database CRUD functions with SQLC
6. How to build a CRUD RESTful API with the generated database CRUD functions and Gin Gonic

Prerequisites

Before continuing with this tutorial, you should have:

• Windows Subsystem for Linux, also commonly referred to as WSL installed on your Windows machine. This installation is only for Windows users.
• The latest LTS version of Ubuntu installed on the WSL. This step is for only Windows users.
• Homebrew installed on the WSL (Windows Subsystem for Linux), Mac, or Linux.
• Visual Studio Code (VS Code) installed on your system
• Windows users who have WSL should also install the VS Code Remote – WSL extension
  
• Docker installed on your machine
• Basic knowledge of Golang and SQL (Structured Query Language)
• Some understanding of CRUD and RESTful APIs will be highly beneficial.

Run the Golang + SQLC CRUD API Locally

• For Windows users, ensure you have installed WSL, the latest LTS version of Ubuntu, and Homebrew.
• For other users (Linux, and Mac), ensure you have installed Homebrew
• Install the latest version of Golang in the WSL, Linux, or Mac
• Enter into the Golang working environment and download or clone the SQLC project code from https://github.com/wpcodevo/golang-postgresql-api
• Windows users should reopen the project folder in WSL using the VS Code Remote – WSL extension.
• Duplicate the example.env file and rename the duplicated one to app.env.
• Start the PostgreSQL Docker container by running docker-compose up -d . You need to update the Postgres credentials in the app.env file and the migration script provided in the Makefile file if you decide to use your local Postgres server.
• Install the Golang migrate package with Homebrew. Windows users who installed WSL and Homebrew should also use the brew command.
• Run the migration script to push the schema to the Postgres database with the command below:

migrate -path db/migrations -database "postgresql://admin:password123@localhost:6500/golang_postgres?sslmode=disable" -verbose up

• Start the Golang HTTP server by running go run main.go in the terminal
• Test the Golang + SQLC CRUD API with any API testing tool like Postman or Insomnia

Step 1 – Setup and Run PostgreSQL with Docker

First things first, create the project folder and open it with VS Code or any IDE you are comfortable with. You can name the folder golang-postgresql-api .

Next, initialize the Golang project with this command:

go mod init github.com/<GitHub username>/<project folder name>

Having Docker installed was one of the prerequisites of this tutorial. Create a docker-compose.yml file in the root project folder and add the following configurations:

docker-compose.yml

version: '3'
services:
  postgres:
    image: postgres:latest
    container_name: postgres
    ports:
      - '6500:5432'
    volumes:
      - progresDB:/var/lib/postgresql/data
    env_file:
      - ./app.env
volumes:
  progresDB:

The above Docker-compose configurations will pull the latest Postgres image from Docker Hub, build the Postgres Docker container, run it and map port 6500 to the default port of the running Postgres server.

Since we added an env_file property in the compose file, create an app.env file to contain the credentials the Postgres image will need when building the Postgres container.

app.env

SERVER_PORT=8000
CLIENT_PORT=8080
NODE_ENV=development

POSTGRES_DRIVER=postgres
POSTGRES_SOURCE=postgresql://admin:password123@localhost:6500/golang_postgres?sslmode=disable

POSTGRES_HOST=127.0.0.1
POSTGRES_PORT=6500
POSTGRES_USER=admin
POSTGRES_PASSWORD=password123
POSTGRES_DB=golang_postgres

ORIGIN=http://localhost:3000

Now that we have an app.env file that will contain sensitive information, create a .gitignore file and add the app.env to exclude it from your Git commits. This will eventually prevent you from accidentally pushing it to GitHub.

With that out of the way, run the PostgreSQL container by running:

docker-compose up -d

Feel free to stop the running container with this command:

docker-compose down

Step 2 – Setup and Run Database Migration in Golang

What is database migration? Database migration is a technique used to track incremental and reversible changes in database schemas. It’s comparable to how we use the Git version control system to track changes in source codes.

In this section, you will learn how to use the golang-migrate library to run database migration in Golang.

We will be using PostgreSQL as our database but the steps can be adjusted to work with any database server supported by the golang-migrate library.

Now open the Golang migrate CLI documentation page and install it with the brew command. Windows users having WSL should also use the brew installation option.

Run migrate in the terminal to see if the installation was successful.

Below are some of the essential commands you should take note of:

• create – For creating new migration files
• goto V – For changing the migrate schema to a specific version
• up – For running the up migration files based on the sequential order of their prefix version.
• down – For running the down migration files based on the sequential reverse order of their prefix version.

With the above explanation, create a db/migrations folder in the root project directory.

mkdir db && cd db && mkdir migrations

After creating the db/migrations folder, run the command below to create the up/down migration files.

migrate create -ext sql -dir db/migrations -seq init_schema

• -ext – is the extension of the up/down migration files
• -dir – the directory where the generated files will be placed
• -seq – tells the golang-migrate library to generate a sequential version number for the migration files.

Before we can run the database migration, we need to add the SQL code to the up migration file generated by the Golang migrate library. Open the up migration file, in my case it’s db/migrations/000001_init_schema.up.sql , and add the following SQL code.

db/migrations/000001_init_schema.up.sql

CREATE TABLE
    "posts" (
        "id" UUID NOT NULL DEFAULT (uuid_generate_v4()),
        "title" VARCHAR NOT NULL,
        "category" VARCHAR NOT NULL,
        "content" VARCHAR NOT NULL,
        "image" VARCHAR NOT NULL,
        "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
        "updated_at" TIMESTAMP(3) NOT NULL,
        CONSTRAINT "posts_pkey" PRIMARY KEY ("id")
    );

CREATE UNIQUE INDEX "posts_title_key" ON "posts"("title");

The above SQL code will do the following:

• Create a posts table and make the ID the primary column
• Add a unique constraint on the ID column to ensure that no two records end up with the same IDs.
• Add an index and a unique constraint on the title column.

PostgreSQL supports the UUID data type by default, but since we used the uuid_generate_v4() function as a default value on the ID column, we need to install the uuid-ossp module in the running Postgres server.

Before installing the uuid-ossp module, let’s create the down migration script to do the opposite of the up script.

Open the down migration file, in my case it’s db/migrations/000001_init_schema.down.sql , and add the following SQL code

db/migrations/000001_init_schema.down.sql

DROP TABLE IF EXISTS posts;

We are now ready to install the Postgres uuid-ossp plugin. Enter into the bash shell of the running Postgres container with the following code docker exec -it <container name> bash:

docker exec -it postgres bash

To install the uuid-ossp extension, follow these steps:

Step 1: Log into the Postgres server with psql -U admin <database name> :

psql -U admin golang_postgres

Step 2: List all the available extensions

select * from pg_available_extensions;

Press the enter key repeatedly to scroll down the list. You will notice that the uuid-ossp extension available but it’s not installed.

Step 3: Install the uuid-ossp extension

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Now exit the Postgres shell with \q and the Docker shell with exit .

With the Postgres uuid-ossp extension installed, we are ready to run the migration script to push the schema to the database.

Run the following command to execute the up migration script:

migrate -path db/migrations -database "postgresql://admin:password123@localhost:6500/golang_postgres?sslmode=disable" -verbose up

Once the migration is successful, open any Postgres GUI client and log in with the credentials provided in the app.env file to see the SQL table added by the Golang migrate library.

Connect to the running Postgres container:

The SQL table created by the Golang migrate library:

You can revert the changes made by the up migration script by running the down migration script:

migrate -path db/migrations -database "postgresql://admin:password123@localhost:6500/golang_postgres?sslmode=disable" -verbose down

Step 3 – Generate the CRUD Functions with SQLC

Now that we have successfully migrated the schema to the database, let’s generate the database CRUD (Create/Read/Update/Delete) functions and structs with SQLC.

Navigate to the SQLC installation page and use the brew installation option to install SQLC on your system. This option will also work for Windows users who have Homebrew installed on the WSL.

After the installation, run sqlc in the terminal to see if the installation was successful.

Now run the sqlc init command from the terminal in the root directory to generate an empty sqlc.yaml settings file.

Replace the content of the sqlc.yaml file with the following:

sqlc.yaml

version: '1'
packages:
  - name: 'db'
    path: './db/sqlc'
    queries: './db/query'
    schema: './db/migrations'
    engine: 'postgresql'
    emit_prepared_queries: true
    emit_interface: false
    emit_exact_table_names: false
    emit_json_tags: true

• name – specifies the name of the generated Golang package.
• path – specifies the path to the directory where the generated files will be stored.
• queries – specifies the location of the query files.
• schema – specifies the path to the directory where the SQL migration files are stored
• engine – specifies the database engine. Could be either PostgreSQL or MySQL.
• emit_prepared_queries – Tells SQLC to enable or disable support for prepared queries.
• emit_interface – Tells SQLC to include or exclude the Querier interface in the generated package. This becomes relevant when mocking the database to test higher-level functions.
• emit_json_tag – Tells SQLC to add JSON tags to the generated structs.
• emit_exact_table_names – If false, SQLC will attempt to singularize plural table names. Otherwise, struct names will mirror table names.

With that out of the way, create two folders: db/sqlc and db/query. After that create a db/query/post.sql file and add the following SQLC queries:

db/query/post.sql

-- name: CreatePost :one
INSERT INTO posts (
  title,
  content,
  category,
  image,
  created_at,
  updated_at
) VALUES (
  $1, $2, $3, $4, $5, $6
)
RETURNING *;

-- name: GetPostById :one
SELECT * FROM posts
WHERE id = $1 LIMIT 1;

-- name: ListPosts :many
SELECT * FROM posts
ORDER BY id
LIMIT $1
OFFSET $2;

-- name: UpdatePost :one
UPDATE posts
set 
title = coalesce(sqlc.narg('title'), title), 
category = coalesce(sqlc.narg('category'), category), 
content = coalesce(sqlc.narg('content'), content) ,
image = coalesce(sqlc.narg('image'), image), 
updated_at = coalesce(sqlc.narg('updated_at '), updated_at ) 
WHERE id = sqlc.arg('id')
RETURNING *;

-- name: DeletePost :exec
DELETE FROM posts
WHERE id = $1;

Quite a lot is going on in the above code, let’s break it down. We created five SQL queries to represent the low-level CRUD operations.

Each query has a specific comment annotation that will instruct SQL on how to generate the Golang code. The -- name: CreatePost :one annotation will tell SQLC to generate a Golang function called CreatePost and it should only return a single row.

The query with the :many annotation indicates that the generated CRUD method will return multiple rows.

Since we will be using an HTTP PATCH method for the update endpoint, we used the sqlc.narg() method to ensure that the user has the ability to update any column in the row.

With the above explanation, run the sqlc generate command to generate the Golang CRUD code.

After the generation is successful, open the db/sqlc folder and you should see three newly generated Golang files.

• models.go – contains the struct definitions for the Post model.
• post.sql.go – contains the Golang CRUD functions and structs.
• db.go – contains the DBTX interface having four common methods that both SQL db and SQL tx object has.

Run go mod tidy to install all the dependencies added by SQLC.

Step 4 – Load the Environment Variables with Viper

Golang comes with an OS package that we can use to access the environment variables without installing any third-party package but we will be using the Viper package because of the flexibility it provides.

Viper supports many file formats like SON, TOML, YAML, HCL, envfile, and Java properties config files. However, in our example, we will be loading the environment variables from an envfile.

So install Viper with this command:

go get github.com/spf13/viper

Next, create a config/default.go file in the root project folder and add the following code:

config/default.go

package config

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

type Config struct {
	PostgreDriver  string `mapstructure:"POSTGRES_DRIVER"`
	PostgresSource string `mapstructure:"POSTGRES_SOURCE"`
	ServerPort     string `mapstructure:"SERVER_PORT"`
	ClientPort     string `mapstructure:"CLIENT_PORT"`
	Origin         string `mapstructure:"ORIGIN"`
}

func LoadConfig(path string) (config Config, err error) {
	viper.AddConfigPath(path)
	viper.SetConfigType("env")
	viper.SetConfigName("app")

	viper.AutomaticEnv()

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

	err = viper.Unmarshal(&config)
	return
}

Step 5 – Create the Request Validation Structs

SQLC has already generated structs that we can pass to the Gin Gonic framework to validate the request body but because we want to add more validation bindings we have to create our own custom structs.

Create a schemas/post.schema.go file and add these two Golang structs:

schemas/post.schema.go

package schemas

type CreatePost struct {
	Title    string `json:"title" binding:"required"`
	Category string `json:"category" binding:"required"`
	Content  string `json:"content" binding:"required"`
	Image    string `json:"image" binding:"required"`
}

type UpdatePost struct {
	Title    string `json:"title"`
	Category string `json:"category"`
	Content  string `json:"content"`
	Image    string `json:"image"`
}

• CreatePost – This struct will be used by Gin Gonic to validate the request payload when adding new records to the database.
• UpdatePost – This struct will be used by Gin Gonic to validate the request payload when updating records in the database.

Step 6 – Create the Route Controllers

In this section, you will create five higher-level functions to perform the CRUD operations. Each function will call the underlying lower-level CRUD method generated by SQLC to query or mutate the database.

These are the five CRUD functions:

• CreatePost – For adding new records to the database
• UpdatePost – Edits a record in the database
• GetPostById – Returns a single record in the database
• GetAllPosts – Returns a selected number of the records
• DeletePostById – Removes a record from the database

Install the Gin Gonic library:

go get -u github.com/gin-gonic/gin

To begin, create a controllers/post.controller.go file and add the following imports and Golang code.

controllers/post.controller.go

package controllers

import (
	"context"
	"database/sql"
	"net/http"
	"strconv"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/google/uuid"
	db "github.com/wpcodevo/golang-postgresql-api/db/sqlc"
	"github.com/wpcodevo/golang-postgresql-api/schemas"
)

type PostController struct {
	db  *db.Queries
	ctx context.Context
}

func NewPostController(db *db.Queries, ctx context.Context) *PostController {
	return &PostController{db, ctx}
}

Add a new record route handler

The first route handler will be called when a POST request is made to the http://localhost:8000/api/posts endpoint.

controllers/post.controller.go

// [...] Create post handler
func (ac *PostController) CreatePost(ctx *gin.Context) {
	var payload *schemas.CreatePost

	if err := ctx.ShouldBindJSON(&payload); err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"status": "fail", "message": err.Error()})
		return
	}

	now := time.Now()
	args := &db.CreatePostParams{
		Title:     payload.Title,
		Category:  payload.Category,
		Content:   payload.Content,
		Image:     payload.Image,
		CreatedAt: now,
		UpdatedAt: now,
	}

	post, err := ac.db.CreatePost(ctx, *args)

	if err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	ctx.JSON(http.StatusCreated, gin.H{"status": "success", "post": post})
}

We provided the schemas.CreatePost struct to the .ShouldBindJSON() method to validate the request body against the validation rules listed in the struct.

Next, we initialized the db.CreatePostParams{} struct with the fields provided in the request payload and called the db.CreatePost() CRUD method to insert the new record into the database.

Update record route handler

The second route controller will be evoked to edit the record in the database when a PATCH request is made to the http://localhost:8000/api/posts/:postId endpoint.

controllers/post.controller.go

// [...] Create post handler

// [...] Update post handler
func (ac *PostController) UpdatePost(ctx *gin.Context) {
	var payload *schemas.UpdatePost
	postId := ctx.Param("postId")

	if err := ctx.ShouldBindJSON(&payload); err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	now := time.Now()
	args := &db.UpdatePostParams{
		ID:        uuid.MustParse(postId),
		Title:     sql.NullString{String: payload.Title, Valid: payload.Title != ""},
		Category:  sql.NullString{String: payload.Category, Valid: payload.Category != ""},
		Content:   sql.NullString{String: payload.Content, Valid: payload.Content != ""},
		Image:     sql.NullString{String: payload.Image, Valid: payload.Image != ""},
		UpdatedAt: sql.NullTime{Time: now, Valid: true},
	}

	post, err := ac.db.UpdatePost(ctx, *args)
	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "fail", "message": "No post with that ID exists"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "success", "post": post})
}

We provided the schemas.UpdatePost struct to the .ShouldBindJSON() method to ensure that the request payload is included in the request.

Next, we extracted the ID of the record to be updated from the request parameter and initialized the db.UpdatePostParams{} struct before calling the db.UpdatePost() method to update the record in the database.

Retrieve a single record handler

This route handler will be called when a GET request is made to the http://localhost:8000/api/posts/:postId endpoint. Here, we will extract the ID of the record to be selected from the database and call the db.GetPostById() method to retrieve the record that matches the ID.

controllers/post.controller.go

// [...] Create post handler

// [...] Update post handler

// [...] Get a single post handler
func (ac *PostController) GetPostById(ctx *gin.Context) {
	postId := ctx.Param("postId")

	post, err := ac.db.GetPostById(ctx, uuid.MustParse(postId))
	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "fail", "message": "No post with that ID exists"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "success", "post": post})
}

Retrieve all records handler

This route handler will be evoked to retrieve a selected number of records from the database when a request is issued to the http://localhost:8000/api/posts?page=1&limit=10 endpoint.

controllers/post.controller.go

// [...] Create post handler

// [...] Update post handler

// [...] Get a single post handler

// [...] Get all posts handler
func (ac *PostController) GetAllPosts(ctx *gin.Context) {
	var page = ctx.DefaultQuery("page", "1")
	var limit = ctx.DefaultQuery("limit", "10")

	intPage, _ := strconv.Atoi(page)
	intLimit, _ := strconv.Atoi(limit)
	offset := (intPage - 1) * intLimit

	args := &db.ListPostsParams{
		Limit:  int32(intLimit),
		Offset: int32(offset),
	}

	posts, err := ac.db.ListPosts(ctx, *args)
	if err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	if posts == nil {
		posts = []db.Post{}
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "success", "results": len(posts), "data": posts})
}

To make the query parameters optional, we used the .DefaultQuery() method to return the value of the query parameter when it exists, otherwise, it will return the specified default value.

Next, we initialized the db.ListPostsParams{} struct and called the db.ListPosts() method to retrieve a selected number of records in the database.

Delete a record route handler

This route handler will be called when a DELETE request is made to the http://localhost:8000/api/posts/:postId endpoint. Similar to getting a single record from the database, we will extract the ID of the record to be deleted from the request parameters and call the db.GetPostById() method to check if a record with that ID exists.

Next, we will call the db.DeletePost() CRUD method generated by SQLC to remove that record from the database.

controllers/post.controller.go

// [...] Create post handler

// [...] Update post handler

// [...] Get a single post handler

// [...] Get all posts handler

// [...] Delete a single post handler
func (ac *PostController) DeletePostById(ctx *gin.Context) {
	postId := ctx.Param("postId")

	_, err := ac.db.GetPostById(ctx, uuid.MustParse(postId))
	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "fail", "message": "No post with that ID exists"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	err = ac.db.DeletePost(ctx, uuid.MustParse(postId))
	if err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	ctx.JSON(http.StatusNoContent, gin.H{"status": "success"})
}

Complete code of the route handlers

controllers/post.controller.go

package controllers

import (
	"context"
	"database/sql"
	"net/http"
	"strconv"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/google/uuid"
	db "github.com/wpcodevo/golang-postgresql-api/db/sqlc"
	"github.com/wpcodevo/golang-postgresql-api/schemas"
)

type PostController struct {
	db  *db.Queries
	ctx context.Context
}

func NewPostController(db *db.Queries, ctx context.Context) *PostController {
	return &PostController{db, ctx}
}

func (ac *PostController) CreatePost(ctx *gin.Context) {
	var payload *schemas.CreatePost

	if err := ctx.ShouldBindJSON(&payload); err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"status": "fail", "message": err.Error()})
		return
	}

	now := time.Now()
	args := &db.CreatePostParams{
		Title:     payload.Title,
		Category:  payload.Category,
		Content:   payload.Content,
		Image:     payload.Image,
		CreatedAt: now,
		UpdatedAt: now,
	}

	post, err := ac.db.CreatePost(ctx, *args)

	if err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	ctx.JSON(http.StatusCreated, gin.H{"status": "success", "post": post})
}

func (ac *PostController) UpdatePost(ctx *gin.Context) {
	var payload *schemas.UpdatePost
	postId := ctx.Param("postId")

	if err := ctx.ShouldBindJSON(&payload); err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	now := time.Now()
	args := &db.UpdatePostParams{
		ID:        uuid.MustParse(postId),
		Title:     sql.NullString{String: payload.Title, Valid: payload.Title != ""},
		Category:  sql.NullString{String: payload.Category, Valid: payload.Category != ""},
		Content:   sql.NullString{String: payload.Content, Valid: payload.Content != ""},
		Image:     sql.NullString{String: payload.Image, Valid: payload.Image != ""},
		UpdatedAt: sql.NullTime{Time: now, Valid: true},
	}

	post, err := ac.db.UpdatePost(ctx, *args)
	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "fail", "message": "No post with that ID exists"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "success", "post": post})
}

func (ac *PostController) GetPostById(ctx *gin.Context) {
	postId := ctx.Param("postId")

	post, err := ac.db.GetPostById(ctx, uuid.MustParse(postId))
	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "fail", "message": "No post with that ID exists"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "success", "post": post})
}

func (ac *PostController) GetAllPosts(ctx *gin.Context) {
	var page = ctx.DefaultQuery("page", "1")
	var limit = ctx.DefaultQuery("limit", "10")

	intPage, _ := strconv.Atoi(page)
	intLimit, _ := strconv.Atoi(limit)
	offset := (intPage - 1) * intLimit

	args := &db.ListPostsParams{
		Limit:  int32(intLimit),
		Offset: int32(offset),
	}

	posts, err := ac.db.ListPosts(ctx, *args)
	if err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	if posts == nil {
		posts = []db.Post{}
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "success", "results": len(posts), "data": posts})
}

func (ac *PostController) DeletePostById(ctx *gin.Context) {
	postId := ctx.Param("postId")

	_, err := ac.db.GetPostById(ctx, uuid.MustParse(postId))
	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "fail", "message": "No post with that ID exists"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	err = ac.db.DeletePost(ctx, uuid.MustParse(postId))
	if err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "error", "message": err.Error()})
		return
	}

	ctx.JSON(http.StatusNoContent, gin.H{"status": "success"})
}

Step 7 – Create the Routes

Now that we have all the route controllers defined, let’s create a Gin Gonic router and add the CRUD endpoints to evoke the route handlers.

Create a routes/post.route.go file and add the following code:

routes/post.route.go

package routes

import (
	"github.com/gin-gonic/gin"
	"github.com/wpcodevo/golang-postgresql-api/controllers"
)

type PostRoutes struct {
	postController controllers.PostController
}

func NewRoutePost(postController controllers.PostController) PostRoutes {
	return PostRoutes{postController}
}

func (pc *PostRoutes) PostRoute(rg *gin.RouterGroup) {

	router := rg.Group("posts")
	router.POST("/", pc.postController.CreatePost)
	router.GET("/", pc.postController.GetAllPosts)
	router.PATCH("/:postId", pc.postController.UpdatePost)
	router.GET("/:postId", pc.postController.GetPostById)
	router.DELETE("/:postId", pc.postController.DeletePostById)
}

Step 8 – Register all the Routes

In this section, you will instantiate the Golang constructor functions and register all the routes to start accepting requests.

Before that, install the Gin CORS middleware package. The CORS package will configure the Gin HTTP server to accept requests from cross-origin domains.

go get github.com/gin-contrib/cors

With that out of the way, create a main.go file and add the following code snippets. The main.go file will serve as the entry point of the API project where the Gin server will be started and the connection to the database will be created.

main.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"net/http"

	"github.com/gin-contrib/cors"
	"github.com/gin-gonic/gin"
	"github.com/wpcodevo/golang-postgresql-api/config"
	"github.com/wpcodevo/golang-postgresql-api/controllers"
	dbConn "github.com/wpcodevo/golang-postgresql-api/db/sqlc"
	"github.com/wpcodevo/golang-postgresql-api/routes"

	_ "github.com/lib/pq"
)

var (
	server *gin.Engine
	db     *dbConn.Queries
	ctx    context.Context

	PostController controllers.PostController
	PostRoutes     routes.PostRoutes
)

func init() {
	ctx = context.TODO()
	config, err := config.LoadConfig(".")

	if err != nil {
		log.Fatalf("could not load config: %v", err)
	}

	conn, err := sql.Open(config.PostgreDriver, config.PostgresSource)
	if err != nil {
		log.Fatalf("could not connect to postgres database: %v", err)
	}

	db = dbConn.New(conn)

	fmt.Println("PostgreSQL connected successfully...")

	PostController = *controllers.NewPostController(db, ctx)
	PostRoutes = routes.NewRoutePost(PostController)

	server = gin.Default()
}

func main() {
	config, err := config.LoadConfig(".")

	if err != nil {
		log.Fatalf("could not load config: %v", err)
	}

	corsConfig := cors.DefaultConfig()
	corsConfig.AllowOrigins = []string{config.Origin}
	corsConfig.AllowCredentials = true

	server.Use(cors.New(corsConfig))

	router := server.Group("/api")

	router.GET("/healthchecker", func(ctx *gin.Context) {
		ctx.JSON(http.StatusOK, gin.H{"status": "success", "message": "Welcome to Golang with PostgreSQL"})
	})

	PostRoutes.PostRoute(router)
	server.NoRoute(func(ctx *gin.Context) {
		ctx.JSON(http.StatusNotFound, gin.H{"status": "fail", "message": fmt.Sprintf("Route %s not found", ctx.Request.URL)})
	})
	log.Fatal(server.Run(":" + config.ServerPort))
}

In the above code, we created an init function to load the environment variables from the app.env file, connect the API server to the database, initialize the API constructor functions, and create an instance of the Gin Gonic engine.

If you are new to Golang, the init function will be fired first when the file is executed before the main function will be called.

So it makes a lot of sense to connect to the database in the init function before the main function is called to start the server.

In the main function, we added the CORS middleware to the middleware pipeline, created an alias for the API endpoints, registered the API routes, and started the Gin server to listen on the provided port.

Now install the Golang air package to help us hot-reload the Gin Gonic server upon every file change.

go install github.com/cosmtrek/air@latest

Once you have the Golang air package, start the Golang Gin Gonic server by running the air command in the terminal of the root project directory.

Step 9 – Test the SQLC + Golang CRUD API with Postman

Now that we have the SQLC + Golang API server up and running, let’s test the endpoints with an API testing software like Postman or Insomnia.

HTTP POST Request: Create new record

Let’s start with the first CRUD operation. Make an HTTP POST request to the http://localhost:8000/api/posts endpoint with the JSON object included in the request body.

The Gin Gonic server will then validate the request body against the provided Golang struct, and evoke the .CreatePost() method generated by SQLC to insert the new record into the database.

HTTP PATCH Request: Update existing record

To update a record in the database, make an HTTP PATCH request to the http://localhost:8000/api/posts/:postId endpoint with the ID of the record in the URL parameter. Also, include the values of the columns you want to update in the JSON object before making the request.

The Gin Gonic server will only update the fields provided in the request body and return the updated record to the client.

HTTP GET Request: Retrieve a single record

To retrieve a single record in the database, make a GET request to the http://localhost:8000/api/posts/:postId endpoint. The Gin Gonic server will then query the database to retrieve the record that matches the provided ID and return the found record to the client.

HTTP GET Request: Retrieve all records

To retrieve a selected number of records in the database, make a GET to the http://localhost:8000/api/posts endpoint. By default, the Golang API will return the first 10 results.

HTTP DELETE Request: Remove a record

To delete a record in the database, make a DELETE request to the http://localhost:8000/api/posts/:postId endpoint with the ID of the record you want to remove.

Conclusion

Congrats on reaching the end. In this article, you learned how to generate Golang CRUD code with SQLC and run database migration with the Golang migrate library. You also learned how to build a CRUD RESTful API that uses the PostgreSQL database and runs on a Gin Gonic HTTP server.

You can find the Golang + SQLC CRUD API project on this GitHub repository.