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 theGolang 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 functionsInsert, 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 withGolang migrate
5. How to generate Golang code and database CRUD functions withSQLC
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 asWSL installed on your Windows machine. This installation is only forWindows users.
• Thelatest 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 theVS 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 installedWSL, thelatest LTS version of Ubuntu, andHomebrew.
• For other users (Linux, and Mac), ensure you have installedHomebrew
• 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 fromhttps://github.com/wpcodevo/golang-postgresql-api
• Windows users should reopen the project folder in WSL using theVS Code Remote – WSL extension.
• Duplicate theexample.env file and rename the duplicated one toapp.env.
• Start the PostgreSQL Docker container by runningdocker-compose up -d . You need to update the Postgres credentials in theapp.env file and the migration script provided in theMakefile file if you decide to use your local Postgres server.
• Install theGolang migrate package with Homebrew. Windows users who installed WSL and Homebrew should also use thebrew 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 runninggo run main.go in the terminal
• Test the Golang + SQLC CRUD API with any API testing tool likePostman orInsomnia

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 foldergolang-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 adocker-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.envvolumes:  progresDB:

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

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

app.env

SERVER_PORT=8000CLIENT_PORT=8080NODE_ENV=developmentPOSTGRES_DRIVER=postgresPOSTGRES_SOURCE=postgresql://admin:password123@localhost:6500/golang_postgres?sslmode=disablePOSTGRES_HOST=127.0.0.1POSTGRES_PORT=6500POSTGRES_USER=adminPOSTGRES_PASSWORD=password123POSTGRES_DB=golang_postgresORIGIN=http://localhost:3000

Now that we have anapp.env file that will contain sensitive information, create a.gitignore file and add theapp.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 theGit version control system to track changes in source codes.

In this section, you will learn how to use thegolang-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 thegolang-migrate library.

Now open theGolang migrate CLI documentation page and install it with thebrew command. Windows users havingWSL should also use thebrew installation option.

Runmigrate 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 adb/migrations folder in the root project directory.

mkdir db && cd db && mkdir migrations

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

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

• -ext – is the extension of theup/down migration files
• -dir – the directory where the generated files will be placed
• -seq – tells thegolang-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 theup migration file generated by theGolang migrate library. Open the up migration file, in my case it’sdb/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 aposts 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 theUUID data type by default, but since we used theuuid_generate_v4() function as a default value on the ID column, we need to install theuuid-ossp module in the running Postgres server.

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

Open the down migration file, in my case it’sdb/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 Postgresuuid-ossp plugin. Enter into the bash shell of the running Postgres container with the following codedocker exec -it <container name> bash:

docker exec -it postgres bash

To install theuuid-ossp extension, follow these steps:

Step 1: Log into the Postgres server withpsql -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 theuuid-ossp extension available but it’s not installed.

Step 3: Install theuuid-ossp extension

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

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

With the Postgresuuid-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 theapp.env file to see the SQL table added by theGolang migrate library.

Connect to the running Postgres container:

The SQL table created by theGolang 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 theSQLC installation page and use thebrew 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, runsqlc in the terminal to see if the installation was successful.

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

Replace the content of thesqlc.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 theQuerier 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 – Iffalse, 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 anddb/query. After that create adb/query/post.sql file and add the following SQLC queries:

db/query/post.sql

-- name: CreatePost :oneINSERT INTO posts (  title,  content,  category,  image,  created_at,  updated_at) VALUES (  $1, $2, $3, $4, $5, $6)RETURNING *;-- name: GetPostById :oneSELECT * FROM postsWHERE id = $1 LIMIT 1;-- name: ListPosts :manySELECT * FROM postsORDER BY idLIMIT $1OFFSET $2;-- name: UpdatePost :oneUPDATE postsset 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 :execDELETE FROM postsWHERE 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 calledCreatePost 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 anHTTP PATCH method for the update endpoint, we used thesqlc.narg() method to ensure that the user has the ability to update any column in the row.

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

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

• models.go – contains the struct definitions for thePost model.
• post.sql.go – contains the Golang CRUD functions and structs.
• db.go – contains theDBTX interface having four common methods that both SQLdb and SQLtx object has.

Rungo mod tidy to install all the dependencies added by SQLC.

Step 4 – Load the Environment Variables with Viper

Golang comes with anOS package that we can use to access the environment variables without installing any third-party package but we will be using theViper 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 aconfig/default.go file in the root project folder and add the following code:

config/default.go

package configimport ("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 aschemas/post.schema.go file and add these two Golang structs:

schemas/post.schema.go

package schemastype 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 acontrollers/post.controller.go file and add the following imports and Golang code.

controllers/post.controller.go

package controllersimport ("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.Queriesctx 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 aPOST request is made to thehttp://localhost:8000/api/posts endpoint.

controllers/post.controller.go

// [...] Create post handlerfunc (ac *PostController) CreatePost(ctx *gin.Context) {var payload *schemas.CreatePostif 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 theschemas.CreatePost struct to the.ShouldBindJSON() method to validate the request body against the validation rules listed in the struct.

Next, we initialized thedb.CreatePostParams{} struct with the fields provided in the request payload and called thedb.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 aPATCH request is made to thehttp://localhost:8000/api/posts/:postId endpoint.

controllers/post.controller.go

// [...] Create post handler// [...] Update post handlerfunc (ac *PostController) UpdatePost(ctx *gin.Context) {var payload *schemas.UpdatePostpostId := 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 theschemas.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 thedb.UpdatePostParams{} struct before calling thedb.UpdatePost() method to update the record in the database.

Retrieve a single record handler

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

controllers/post.controller.go

// [...] Create post handler// [...] Update post handler// [...] Get a single post handlerfunc (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 thehttp://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 handlerfunc (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) * intLimitargs := &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 thedb.ListPostsParams{} struct and called thedb.ListPosts() method to retrieve a selected number of records in the database.

Delete a record route handler

This route handler will be called when aDELETE request is made to thehttp://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 thedb.GetPostById() method to check if a record with that ID exists.

Next, we will call thedb.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 handlerfunc (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 controllersimport ("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.Queriesctx 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.CreatePostif 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.UpdatePostpostId := 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) * intLimitargs := &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 aroutes/post.route.go file and add the following code:

routes/post.route.go

package routesimport ("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 theGin 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 amain.go file and add the following code snippets. Themain.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 mainimport ("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.Enginedb     *dbConn.Queriesctx    context.ContextPostController controllers.PostControllerPostRoutes     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 = trueserver.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 aninit function to load the environment variables from theapp.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, theinit function will be fired first when the file is executed before themain function will be called.

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

In themain 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 theGolang 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 theGolang air package, start the Golang Gin Gonic server by running theair 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 likePostman orInsomnia.

HTTP POST Request: Create new record

Let’s start with the first CRUD operation. Make an HTTPPOST request to thehttp://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 anHTTP PATCHrequest to thehttp://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 aGET request to thehttp://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 aGET to thehttp://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 aDELETE request to thehttp://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 thisGitHub repository.

Share Article:

Build a Complete Deno CRUD RESTful API with MongoDB

React Query and Axios: User Registration and Email Verification

How to Convert HTML to PDF in JavaScript – Example with Code

How to Use Shadcn UI and TanStack Router in React.js

CRUD Operations on PostgreSQL using Django REST Framework

Build a CRUD Django REST API using Django REST Framework