Deploying a Golang RESTful API with Gin, SQLC and PostgreSQL
Tags: go, sql, backend, webdev
Published: 2023-11-15 08:36:22

REST which stands for Representative State Transfer is an architectural style for designing APIs(Application Programming Interface). An API is a set of rules through which programs communicate with each other. The API is normally created on the server side and allows the client to talk to it. 

REST has a set of rules on how APIs should be designed and most of these rules follow the CRUD(Create, Read, Update, and Delete) pattern where you request a resource and data is sent back which is called a Response. The request anatomy comprises four sections namely the endpoint which is the request URL, the method, the headers, and the data/body.
 
In this article, you will implement a CRUD RESTful API in Golang with Gin HTTP web framework, SQLC for creating CRUD database functions and PostgreSQL as the database engine of choice for this project. You will also learn to use the Golang migrate library for database schema migrations. 

## Table of Contents

- [Prerequisites](#prerequisites)
- [Setting up the Golang project](#setting-up-the-golang-project)
- [Setting up PostgreSQL with Docker Compose](#setting-up-postgresql-with-docker-compose)
- [Performing database migration with Golang migrate](#performing-database-migration-with-golang-migrate)
- [Using SQLC to generate Go code from SQL queries](#using-sqlc-to-generate-go-code-from-sql-queries)
- [Implementing CRUD in Golang Rest API](#implementing-crud-in-golang-rest-api)
- [Route Creation for the Contact Handlers](#route-creation-for-the-contact-handlers)
- [Testing the Golang RESTful API with REST Client](#testing-the-golang-restful-api-with-rest-client)
- [Conclusion](#conclusion)

## Prerequisites
- Before getting started ensure you have [Go installed](https://go.dev/dl/) installed on your machine. At the time of writing this guide 1.21.3 is the latest stable version of Go. 
- You should also have [Vscode installed](https://code.visualstudio.com/docs/setup/setup-overview) as it is the IDE of choice for this exercise but feel free to use any other IDE of your liking. Install [Go extension](https://code.visualstudio.com/docs/languages/go) for vscode to make it easy to get features like intellisense, code navigation, testing, debugging and many more features that will guide you in your Golang development journey.
- Ensure [Docker](https://docs.docker.com/engine/install/) is also installed on your machine.
- Some basic knowledge of Golang and SQL(Structured Query Language) will also come in handy to follow along this article.

## Setting up the Golang project
To start this project navigate to your preferred directory on your machine and  open the terminal. From the terminal create a new folder with the project name, navigate to the project folder, initialize the Golang project and  open VsCode editor. Use the following commands to set up the project.

```
mkdir golang-crudsqlc-rest
cd golang-crudsqlc-rest/
go mod init golang-crudsqlc-rest
code .
```
Once the above setup is done, install the relevant packages for the project by running the following commands.

```
go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest
go install github.com/cosmtrek/air@latest
go get github.com/lib/pq
go get github.com/spf13/viper
go get -u github.com/gin-gonic/gin
```
The migrate package is installed slightly differently from the rest of the packages. Since I am using Ubuntu the below commands will be run at the terminal. If you are using macOS or Windows operating system refer to [migration installation page](https://github.com/golang-migrate/migrate/tree/master/cmd/migrate) to see how to install migrate CLI.

```
curl -L https://packagecloud.io/golang-migrate/migrate/gpgkey | apt-key add -
echo "deb https://packagecloud.io/golang-migrate/migrate/ubuntu/ $(lsb_release -sc) main" > /etc/apt/sources.list.d/migrate.list
apt-get update
apt-get install -y migrate
```
Let's have a brief overview of what each package does.

- `sqlc`: Is a library that is used to generate Golang code 
   that has type safe interfaces from SQL queries.
- `air`: Is a live reloading utility that is used to observe 
   changes in Go applications. Run  the air command in the 
   root of your project and it will take care of 
   listening to changing events in your code without the need 
   to restart the application.
- `pq`: It's GO Postgres driver used for GO database/sql. You 
   have to import this driver for you to use the database/sql 
   if you are using the Postgresql engine.
- `viper`: Is a configuration package solution used to read 
   configurations of all types ranging from environment 
 variables, 
   buffer files, command line flags to files written in JSON, 
   YAML and TOML to name just but a few.
- `gin`: Is a web framework written in Go and it comes with a 
   lot of prebuilt in features ranging from routes grouping, 
   JSON validation, middleware support and error management.
- `migrate`: This library helps with database migration 
   either through incremental changes in the schema or 
   reversal changes made in the database schema.

## Setting up PostgreSQL with Docker Compose
The database of choice used for this project is PostgreSQL. The database engine will be run as a container. To pull the image from docker ensure docker is installed on your machine. Remember this is one of the prerequisites stated earlier on.
At the root of the project create a `docker-compose.yaml` file by typing `touch docker-compose.yaml`. This file will have the following configurations.

```
version: "3.9"
services:
  postgres:
    image: postgres:latest
    container_name: postgres
    ports:
      - "5432:5432"
    volumes:
      - db:/var/lib/postgresql/data
    env_file:
      - ./app.env

volumes:
  db:
```

When the above file is run using the required docker-compose command it will pull the latest Postgres image from [docker hub](https://hub.docker.com/_/postgres) and run it as a Postgres container. The container name is `postgres` which will be mapped to port `5432` from where the postgres server will listen to. The `env_file` property will be mapped to an external file named `app.env` which will store sensitive information related to your postgres database. 
At the root of your project create an `app.env` file with the following credentials. Feel free to use credentials of your choice.

### app.env

```
DB_DRIVER=postgres
DB_SOURCE=postgresql://root:secret@localhost:5432/contact_db?sslmode=disable
POSTGRES_USER=root
POSTGRES_PASSWORD=secret
POSTGRES_DB=contact_db
SERVER_ADDRESS=8000
```

After setting  the above run the PostgreSQL container with the following command.

```
docker-compose up -d
```

The container will be run in a detached mode and will pull the image from docker hub. Once the image has been downloaded and container is up and running you can type `docker ps` command to see if postgres name of the container will be displayed at the terminal. The result will be something similar to the one below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fttwekoz0ba8e12dtq0j.png)

To stop the container type the following command.

```
docker-compose down
```

## Performing database migration with Golang migrate
Database migration is one of the most important tasks when it comes to version tracking of data. It involves tracking increments and decrements of version changes in the populated database and this helps to ensure that there are no dirty schema migrations.
In this section you will go through how to write database migration using [golang-migrate library](https://github.com/golang-migrate/migrate/tree/master/cmd/migrate). Remember you have already installed golang migrate library in the project setup. If not sure you can always refer the project setup section. To confirm that migrate library is installed type `migrate` at the terminal and an output similar to the following will be displayed.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gytjjczbccmg8oq0tlw4.png)

Let's describe the commonly used commands for the above output:

- `create` - Is used to create new migration files.
- `up` - For migrating files of the schema to a newer 
   version based on the prefix sequence.
- `down` - Used to migrate files of the schema in a reverse 
   manner based on the prefix sequence.

With migration package in place You can now create a folder `db/migration` where the migration files will reside. Run the following command to create the folder.

```
mkdir -p db/migration
```
You can now create the up/down migration files where your SQL code will be placed with the following command.

```
migrate create -ext sql -dir db/migration -seq init_schema
```

- The `-ext` flag refers to the extension of the file which 
  is SQL and the  `-dir` flag refers to the directory where 
  SQL files will reside.
- The -seq flag will generate a sequential version number for 
  the migration files.

If you `cd` to the `db/migration` folder you will notice two migration files have been generated and both have version 1 in the file names prefix. The suffix of the files however is different with one showing `up.sql` and another showing `down.sql`.
Before any database migration operation is done you need to have a `SQL` table in the up migration file and a  reverse operation for removing this table in the down migration file. Add the following SQL code in the folder with the file name `db/migration/000001_init_schema.up.sql`.

```
CREATE TABLE "contacts"(
    "contact_id" UUID NOT NULL DEFAULT (uuid_generate_v4()),
    "first_name" VARCHAR NOT NULL,
    "last_name" VARCHAR NOT NULL,
    "phone_number" VARCHAR NOT NULL,
    "street" VARCHAR NOT NULL,
    "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL,
    CONSTRAINT "contacts_pkey" PRIMARY KEY("contact_id")
);

-- CREATE UNIQUE INDEX ON "contacts" ("phone_number");
CREATE UNIQUE INDEX "contacts_phone_number_key" ON "contacts"("phone_number");
```
The SQL code above does the following:

- Creates a table with the name `contacts` and the primary 
 key column is `contact_id`. The primary key column uses 
 `uuid` data type which helps in ensuring singularity of 
 values with no duplicates.
- A unique constraint on the`phone_number` column is also 
  created which ensures the values of this column will be 
  unique when keyed in the database.

PostgreSQL enables you to store and compare `UUID` values but it doesn't have default functions for creating `UUID` values by default. It depends on third-party modules that offer algorithms to generate  `UUIDS`. You will install `uuid-ossp` module which has some functions that contain algorithms to generate `UUIDS`.
Before going further let's do one operation in the migration down file. In the `db/migration/000001_init_schema.down.sql` file add the following code.

```
DROP TABLE IF EXISTS contacts;
```
The above SQL statement will drop the `contacts` table . 
Now you are ready to install Postgres `uuid-ossp` third-party module. Run the Postgres container with the following command at the terminal.

```
docker exec -it postgres bash
```
Proceed to log in as a user and populate the database name you earlier set on the environment variables. Command similar to this one is used `psql -U <user> <database name>`

```
psql -U root contact_db
```
You can now view all the available extensions in Postgres by running the following SQL command.

```
select * from pg_available_extensions;
```
An output similar to the following will be displayed.  Scroll down repeatedly in the list until you reach the `uuid-ossp` extension as highlighted below in white.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ojuw2v40ilroube5mvpw.png)

Having located the `uuid-ossp` extension on the list, you can now install the extension as shown below.

```
CREATE EXTENSION IF NOT EXISTS  "uuid-ossp";
```
With Postgres `uuid-ossp` extension installed you can  exit the Postgres shell and Docker shell using the `exit` command.
It's time to run the migration script and push this table to the database. 
Run the following command to execute the migration up script

```
migrate -path db/migration -database  "postgresql://root:secret@localhost:5432/contact_db?sslmode=disable" -verbose up
```
If the migration is successful, proceed to open any Postgres graphical user interface(GUI) client and login with the database credentials specified in `app.env` file. The GUI client used in this article is [TablePlus] (https://tableplus.com/) but there are other GUI clients you can still use to connect to the database like [PgAdmin](https://www.pgadmin.org/download/) and [Beekeper Studio](https://www.beekeeperstudio.io/). Connect to the Postgres server container by typing the credentials similar to the following.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v8jq14ddm5m6mxqnt2g6.png)

If the connection is successful the contacts table created by   [Golang migrate](https://github.com/golang-migrate/migrate/tree/master/cmd/migrate) will be created with a schema definition similar to the following.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l84j2y23um3izxd2wkcd.png)

The schema migrations table stores the latest migration version which in this scenario is version 1 since there is a single migration that has been migrated. To revert back to the changes made by the up migration script run the following command.

```
migrate -path db/migration -database "postgresql://root:secret@localhost:5432/contact_db?sslmode=disable" -verbose down
```

## Using SQLC to generate Go code from SQL queries

Having successfully migrated scripts to the database schema you can go ahead to generate Golang CRUD code with `sqlc`. Ensure you have `sqlc` installed. Refer to the project setup section to see how to install `sqlc`. 
Run `sqlc` at the terminal and if the output is similar to the following you have successfully installed `sqlc` in your machine.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jt2633jxb7gk4vps4bqa.png)

With sqlc in place run the following command to generate `sqlc.yaml` at the root of your project.

```
sqlc init
```
A sqlc.yaml file will be generated at the root of the project. Replace the generated content with the following.

**sqlc.yaml**

```
version: "1"
packages:
  - name: "db"
    path: "./db/sqlc"
    queries: "./db/query/"
    schema: "./db/migration/"
    engine: "postgresql"
    emit_json_tags: true
    emit_prepared_queries: true
    emit_interface: false
    emit_exact_table_names: false
    emit_empty_slices: true
```

- The `name` option is the name of the Go package generated. 
  In this case `db` is the package name.
- Next, you specify the folder path where the generated 
  Golang files will be generated. Create a new folder `sqlc` 
  inside the `db` so that the `path` references the path with 
  string `./db/sqlc`.
- `queries` tells  sqlc where to look for `SQL` query files. 
   Create a new folder  `query` inside the `db` folder so 
   that `queries` references the path with string `./db/query`.
- The `schema` points to where our migration files are 
  stored. In your case, it's `./db/migration`.
- `engine` section tells sqlc which database engine to use. 
   In this scenario, the database engine is `PostgreSQL`.
- `emit_json_tags` if set to true directs sqlc to add `JSON 
   tags` on the generated structs.
- `emit_prepared_queries` tells sqlc to generate code that 
   supports prepared queries. This is useful in the 
   performance optimization of code.
- `emit_interface` if set to true, outputs a Querier 
   interface in the generated package. The querier interface 
   generated is useful if you want to mock the db higher- 
   level functions.
-  `emit_exact_table_names` instructs sqlc to create struct 
    names that will be the same as table names. The generated 
    name of the struct will mirror the table name in 
    singularity i.e. contact table will have a Contact 
    struct.

Before generating queries with `sqlc generate`  populate the SQL queries in folder `db/query/contact.sql` with the following code.

##### db/query/contact.sql

```
-- name: CreateContact :one
INSERT INTO contacts(
    first_name,
    last_name,
    phone_number,
    street,
    created_at,
    updated_at
) VALUES (
    $1, $2, $3, $4, $5, $6
) RETURNING *;

-- name: GetContactById :one
SELECT * FROM contacts
WHERE contact_id = $1 LIMIT 1;

-- name: ListContacts :many
SELECT * FROM contacts
ORDER BY contact_id
LIMIT $1
OFFSET $2;

-- name: UpdateContact :one
UPDATE contacts
SET
first_name = coalesce(sqlc.narg('first_name'), first_name),
last_name = coalesce(sqlc.narg('last_name'), last_name),
phone_number = coalesce(sqlc.narg('phone_number'), phone_number),
street = coalesce(sqlc.narg('street'), street),
updated_at = coalesce(sqlc.narg('updated_at'), updated_at)
WHERE contact_id = sqlc.arg('contact_id')
RETURNING *;

-- name: DeleteContact :exec
DELETE FROM contacts
WHERE contact_id = $1;
```
Below is a breakdown of what the above code does. Five `SQL` queries that represent `CRUD` operations have been created for create, insert, update, get and delete operations.
Each query has some annotations namely `--name`, `:one`, `:many` and `:exec`. The `--name` annotation will generate the function name to be used by the Golang function. For instance `-- name: CreateContact :one` annotation tells sqlc to generate Golang function that will have one record.
`:many` annotation instructs sqlc generated query to generate multiple row records during the database-specific `CRUD` operation. The `:exec` annotation label instructs postgres not to return the deleted record.
For nullable parameters `sqlc.narg()` is used. It tells sqlc to ignore the nullability it has inferred and replace it with a generated nullable parameter instead. Simply put you can update each field independently without affecting the nullability of fields.
With the SQL queries above explained you can now generate Golang code with `sqlc generate`. Once the Golang crud has been generated by sqlc generate, open the `db/sqlc` folder and three new Golang files will be generated.

- `contact.sql.go` - contains Golang crud functions and struct definitions.
- `db.tx` - contains the DBTX interface with the four common methods of ExecContext, PrepareContext, QueryContext and QueryRowContext that  SQL db and SQL tx object both have.
- `models.go` contains the struct definition for the Contact table.

If you see red line in visual studio code just run `go mod tidy` so that dependencies added by `SQLC` are installed.

### Loading Environment Variables with Viper
[Viper](https://github.com/spf13/viper) is a Golang package for configuration management of various file formats. Viper finds, loads, and unmarshal configuration files in JSON, TOML, YAML, HCL, INI, envfile or Java properties formats. In this project, viper is used to find values stored in the environment file `app.env` which stores sensitive information like database passwords and other secret information relevant to the project.
Ensure you have viper installed and this was done earlier in the project setup section.  Create `util/config.go` file in the root of the project with the following code.

##### util/config.go

```
package util

import "github.com/spf13/viper"

type Config struct {
	DbDriver         string `mapstructure:"DB_DRIVER"`
	DbSource         string `mapstructure:"DB_SOURCE"`
	PostgresUser     string `mapstructure:"POSTGRES_USER"`
	PostgresPassword string `mapstructure:"POSTGRES_PASSWORD"`
	PostgresDb       string `mapstructure:"POSTGRES_DB"`
	ServerAddress    string `mapstructure:"SERVER_ADDRESS"`
}

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

	viper.AutomaticEnv()

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

	err = viper.Unmarshal(&config)
	return
}

```
With this in place `Viper` will now load the environment variables from the `util/config.go`.

### Creating Request Validation Structs
SQLC has already generated structs from  `SQl` queries which can be passed to  Gin web framework for validation of the request body during request of the respective resource endpoints. However since more validation bindings will be required, you can go ahead and create your custom structs.

Create a `schemas/contacts.schema.go` file and add the following Golang structs.

##### schemas/contacts.schema.go

```
package schemas

type CreateContact struct {
	FirstName   string `json:"first_name" binding:"required"`
	LastName    string `json:"last_name" binding:"required"`
	PhoneNumber string `json:"phone_number" binding:"required"`
	Street      string `json:"street" binding:"required"`
}

type UpdateContact struct {
	FirstName   string `json:"first_name"`
	LastName    string `json:"last_name"`
	PhoneNumber string `json:"phone_number"`
	Street      string `json:"street"`
}

```

- `CreateContact` struct will be validated by Gin Gonic when 
   creating contacts request payload records in the database.
- `UpdateContact` struct will be validated by Gin Gonic when updating the contacts payload records in the database.

### Route Controller Creation
In this section, you will perform the CRUD operations by creating five functions. These functions will perform the CRUD operations by using the generated `sqlc` CRUD methods that were generated from the SQL queries.

The five functions to be used are defined below:

- `CreateContact` - Adds new contact records to the 
   database.
- `UpdateContact` - Will edit the contact records 
   already stored in the database.
- `GetContactById` - Retrieve a single record in the 
   database.
- `GetAllContacts` - Returns all the selected contacts 
   from the database.
-  `DeleteContactById` - Removes an instance of a record 
   from the database.

Ensure you have `Gin Gonic Http` library installed and this has already been covered in the project setup section.
Create `controllers/contact.controllers.go` file where all your `CRUD` methods will be stored and add the following code to begin with.

##### controllers/contact.controllers.go

```
package controllers

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

	db "github.com/Geoff89/sqlccrud/db/sqlc"
	"github.com/Geoff89/sqlccrud/schemas"

	"github.com/gin-gonic/gin"
	"github.com/google/uuid"
)

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

func NewContactController(db *db.Queries, ctx context.Context) *ContactController {
	return &ContactController{db, ctx}
}
```

### Implementing CRUD in Golang Rest API
##### Creating a post route handler for contact creation.
This route handler will be called when a `POST` request is made to the endpoint `http://localhost:8000/api/contacts`.   In the file `controllers/contact.controller.go` add the following code.

```
// Create contact  handler
func (cc *ContactController) CreateContact(ctx *gin.Context) {
	var payload *schemas.CreateContact

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

	now := time.Now()
	args := &db.CreateContactParams{
		FirstName:   payload.FirstName,
		LastName:    payload.LastName,
		PhoneNumber: payload.PhoneNumber,
		Street:      payload.Street,
		CreatedAt:   now,
		UpdatedAt:   now,
	}

	contact, err := cc.db.CreateContact(ctx, *args)

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

	ctx.JSON(http.StatusOK, gin.H{"status": "successfully created contact", "contact": contact})
}

```
`schemas.CreateContact` struct is bound to  `ctx.ShouldBindJSON()` method to validate the request body against the json tags validation rules listed in the struct. Afterwards, initialize the `&db.CreateContactParams{}` struct with the required fields provided in the request payload. `db.CreateContact()` CRUD method is called afterward to insert a new record in the database and if successful `http.StatusOK` will be shown.

##### Adding an update route handler for updating contacts.
This is the second route endpoint created to edit contact records in the database at endpoint `http://localhost:8000/api/contacts/:contactId` when `PATCH` request is used.

##### controllers/contact.controller.go

```
// Update contact handler
func (cc *ContactController) UpdateContact(ctx *gin.Context) {
	var payload *schemas.UpdateContact
	contactId := ctx.Param("contactId")

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

	now := time.Now()
	args := &db.UpdateContactParams{
		ContactID:   uuid.MustParse(contactId),
		FirstName:   sql.NullString{String: payload.FirstName, Valid: payload.FirstName != ""},
		LastName:    sql.NullString{String: payload.LastName, Valid: payload.LastName != ""},
		PhoneNumber: sql.NullString{String: payload.PhoneNumber, Valid: payload.PhoneNumber != ""},
		Street:      sql.NullString{String: payload.PhoneNumber, Valid: payload.Street != ""},
		UpdatedAt:   sql.NullTime{Time: now, Valid: true},
	}

	contact, err := cc.db.UpdateContact(ctx, *args)

	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "failed", "message": "Failed to retrieve contact with this ID"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "Failed retrieving contact", "error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "successfully updated contact", "contact": contact})
}

```
`schemas.UpdateContact` struct is bound to  `ctx.ShouldBindJSON()` method to validate the request body against validation rules listed in the struct.  Next, the ID of the record to be updated is retrieved from the request parameter and `db.UpdateContactParams{}` struct initialized with the required updated fields. `db.UpdateContact()` CRUD method is called afterwards to update the specified record in the database. 

##### Retrieving a single contact record handler
This endpoint `http://localhost:8000/api/contacts/:contactId` will get a specific contact record when `GET` request is called . The specified ID parameter will get the specific record in the database and call `db.GetContactById()` method wich will retrieve the record that matches the parameter ID.

##### controllers/contact.controller.go

```
// Get a single handler
func (cc *ContactController) GetContactById(ctx *gin.Context) {
	contactId := ctx.Param("contactId")

	contact, err := cc.db.GetContactById(ctx, uuid.MustParse(contactId))
	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "failed", "message": "Failed to retrieve contact with this ID"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "Failed retrieving contact", "error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "Successfully retrived id", "contact": contact})
}
```

##### Getting all records handler
This handler will retrieve a specific number of records from the database when a request is made from endpoint `http://localhost:8000/api/contacts?page=1&limit=10`. 

##### controllers/contact.controllers.go

```
// Retrieve all records handlers
func (cc *ContactController) GetAllContacts(ctx *gin.Context) {
	var page = ctx.DefaultQuery("page", "1")
	var limit = ctx.DefaultQuery("limit", "10")

	reqPageID, _ := strconv.Atoi(page)
	reqLimit, _ := strconv.Atoi(limit)
	offset := (reqPageID - 1) * reqLimit

	args := &db.ListContactsParams{
		Limit:  int32(reqLimit),
		Offset: int32(offset),
	}

	contacts, err := cc.db.ListContacts(ctx, *args)
	if err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "Failed to retrieve contacts", "error": err.Error()})
		return
	}

	if contacts == nil {
		contacts = []db.Contact{}
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "Successfully retrieved all contacts", "size": len(contacts), "contacts": contacts})
}

```
The `.DefaultQuery()` is used to specify query parameters `page` and `limit`. These query parameters are useful for pagination. The `limit` query sets the number of rows to be retrieved from the database and the `page` query parameter is useful for pagination of your records. Next, the `&db.ListContactsParams{}` is initialized with the specific query parameters before calling `db.ListContacts()` method to get all records from the database.

##### Deleting record route handler
This endpoint will delete a specific record from the database as specified by the `contactID` parameter when 
`http://localhost:8000/api/contacts/:contactId` endpoint  `DELETE` request is made. The `db.GetContactById()` is used to confirm whether the specific record to be deleted exists in the database after which `db.DeleteContact()` deletes the specific record from the database.

##### controllers/contact.controllers.go

```
// Deleting contact handlers
func (cc *ContactController) DeleteContactById(ctx *gin.Context) {
	contactId := ctx.Param("contactId")

	_, err := cc.db.GetContactById(ctx, uuid.MustParse(contactId))
	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "failed", "message": "Failed to retrieve contact with this ID"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "Failed retrieving contact", "error": err.Error()})
		return
	}

	err = cc.db.DeleteContact(ctx, uuid.MustParse(contactId))
	if err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "failed", "error": err.Error()})
		return
	}

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

}
```
The complete source code for the route handlers will be as follows:
##### controllers/contact.controllers.go

```
package controllers

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

	db "github.com/Geoff89/sqlccrud/db/sqlc"
	"github.com/Geoff89/sqlccrud/schemas"

	"github.com/gin-gonic/gin"
	"github.com/google/uuid"
)

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

func NewContactController(db *db.Queries, ctx context.Context) *ContactController {
	return &ContactController{db, ctx}
}

// Create contact  handler
func (cc *ContactController) CreateContact(ctx *gin.Context) {
	var payload *schemas.CreateContact

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

	now := time.Now()
	args := &db.CreateContactParams{
		FirstName:   payload.FirstName,
		LastName:    payload.LastName,
		PhoneNumber: payload.PhoneNumber,
		Street:      payload.Street,
		CreatedAt:   now,
		UpdatedAt:   now,
	}

	contact, err := cc.db.CreateContact(ctx, *args)

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

	ctx.JSON(http.StatusOK, gin.H{"status": "successfully created contact", "contact": contact})
}

// Update contact handler
func (cc *ContactController) UpdateContact(ctx *gin.Context) {
	var payload *schemas.UpdateContact
	contactId := ctx.Param("contactId")

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

	now := time.Now()
	args := &db.UpdateContactParams{
		ContactID:   uuid.MustParse(contactId),
		FirstName:   sql.NullString{String: payload.FirstName, Valid: payload.FirstName != ""},
		LastName:    sql.NullString{String: payload.LastName, Valid: payload.LastName != ""},
		PhoneNumber: sql.NullString{String: payload.PhoneNumber, Valid: payload.PhoneNumber != ""},
		Street:      sql.NullString{String: payload.PhoneNumber, Valid: payload.Street != ""},
		UpdatedAt:   sql.NullTime{Time: now, Valid: true},
	}

	contact, err := cc.db.UpdateContact(ctx, *args)

	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "failed", "message": "Failed to retrieve contact with this ID"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "Failed retrieving contact", "error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "successfully updated contact", "contact": contact})
}

// Get a single handler
func (cc *ContactController) GetContactById(ctx *gin.Context) {
	contactId := ctx.Param("contactId")

	contact, err := cc.db.GetContactById(ctx, uuid.MustParse(contactId))
	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "failed", "message": "Failed to retrieve contact with this ID"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "Failed retrieving contact", "error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "Successfully retrived id", "contact": contact})
}

// Retrieve all records handlers
func (cc *ContactController) GetAllContacts(ctx *gin.Context) {
	var page = ctx.DefaultQuery("page", "1")
	var limit = ctx.DefaultQuery("limit", "10")

	reqPageID, _ := strconv.Atoi(page)
	reqLimit, _ := strconv.Atoi(limit)
	offset := (reqPageID - 1) * reqLimit

	args := &db.ListContactsParams{
		Limit:  int32(reqLimit),
		Offset: int32(offset),
	}

	contacts, err := cc.db.ListContacts(ctx, *args)
	if err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "Failed to retrieve contacts", "error": err.Error()})
		return
	}

	if contacts == nil {
		contacts = []db.Contact{}
	}

	ctx.JSON(http.StatusOK, gin.H{"status": "Successfully retrieved all contacts", "size": len(contacts), "contacts": contacts})
}

// Deleting contact handlers
func (cc *ContactController) DeleteContactById(ctx *gin.Context) {
	contactId := ctx.Param("contactId")

	_, err := cc.db.GetContactById(ctx, uuid.MustParse(contactId))
	if err != nil {
		if err == sql.ErrNoRows {
			ctx.JSON(http.StatusNotFound, gin.H{"status": "failed", "message": "Failed to retrieve contact with this ID"})
			return
		}
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "Failed retrieving contact", "error": err.Error()})
		return
	}

	err = cc.db.DeleteContact(ctx, uuid.MustParse(contactId))
	if err != nil {
		ctx.JSON(http.StatusBadGateway, gin.H{"status": "failed", "error": err.Error()})
		return
	}

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

}

```

### Route Creation for the Contact Handlers

With the specific route controllers in place, it's time to create Gin Gonic router where CRUD endpoints for the controller handlers will be invoked.
Create a `route/contact.route.go` file with the following code

##### routes/contact.route.go

```
package routes

import (
	"github.com/Geoff89/sqlccrud/controllers"
	"github.com/gin-gonic/gin"
)

type ContactRoutes struct {
	contactController controllers.ContactController
}

func NewRouteContact(contactController controllers.ContactController) ContactRoutes {
	return ContactRoutes{contactController}
}

func (cr *ContactRoutes) ContactRoute(rg *gin.RouterGroup) {

	router := rg.Group("contacts")
	router.POST("/", cr.contactController.CreateContact)
	router.GET("/", cr.contactController.GetAllContacts)
	router.PATCH("/:contactId", cr.contactController.UpdateContact)
	router.GET("/:contactId", cr.contactController.GetContactById)
	router.DELETE("/:contactId", cr.contactController.DeleteContactById)
}

```
The final step to do before testing your endpoints is to create the `main.go` file which is the main entry point to your API project when the GIN server is started and connection to the database is made. Add the following code to the main.go file.

```
package main

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

	"github.com/Geoff89/sqlccrud/controllers"
	dbCon "github.com/Geoff89/sqlccrud/db/sqlc"
	"github.com/Geoff89/sqlccrud/routes"
	"github.com/Geoff89/sqlccrud/util"
	"github.com/gin-gonic/gin"
	_ "github.com/lib/pq"
)

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

	ContactController controllers.ContactController
	ContactRoutes     routes.ContactRoutes
)

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

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

	conn, err := sql.Open(config.DbDriver, config.DbSource)
	if err != nil {
		log.Fatalf("Could not connect to database: %v", err)
	}

	db = dbCon.New(conn)

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

	ContactController = *controllers.NewContactController(db, ctx)
	ContactRoutes = routes.NewRouteContact(ContactController)

	server = gin.Default()
}

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

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

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

	router.GET("/healthcheck", func(ctx *gin.Context) {
		ctx.JSON(http.StatusOK, gin.H{"message": "The contact APi is working fine"})
	})

	ContactRoutes.ContactRoute(router)

	server.NoRoute(func(ctx *gin.Context) {
		ctx.JSON(http.StatusNotFound, gin.H{"status": "failed", "message": fmt.Sprintf("The specified route %s not found", ctx.Request.URL)})
	})

	log.Fatal(server.Run(":" + config.ServerAddress))
}

```

Going through the above code an `init` function loads the environment variables from the app.env file, connects to the database and starts an instance of Gin Gonic engine. In Golang the init function is usually executed first before the `main` function. For tasks that you want to run before firing the server such as database connections use `init` function.

Finally, in the main function, you create the API routes and start the GIN server to listen to the port provided. 
Now it is time to test the RESTful api endpoints. Type `air` at the terminal in the root of the project. It will automatically start the server for you, and show the endpoints you will make requests to. When run successfully at the terminal air will display output similar to the one below.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b4lo73c27zlywghczmss.png)

## Testing the Golang RESTful API with REST Client

The Golang API server is now running together with SQLC for running database operations. Here you will use [REST Client extension for Visual Studio]
(https://marketplace.visualstudio.com/items?itemName=humao.rest-client) to run the API tests. For advanced testing of APIs with more features you can use [Postman](https://www.postman.com/downloads/).

#### HTTP POST Request: This will create records.
This request will create a new record in the database. A HTTP POST request will be made to the endpoint `http://localhost:8000/api/contacts` with a json object in the request body. Gin Gonic server will then validate the request body with the given `Golang` CreateContact struct instructions and finally invoke `.CreateContact()` method to insert a new contact record in the database. Add a few more records following the screenshot guide below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dthdlwlgvgrd4g18mzz5.png)

#### HTTP GET Request: This will retrieve all records
To retrieve all records in the database make a `GET` request to the endpoint `http://localhost:8000/api/contacts`. For pagination purposes, this request will retrieve the first 10 results.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/25i6byrssmrdd3lpaour.png)

#### HTTP GET Request: Retrieving a single record.
To get a single record from the database,  send a `GET` request to the `http://localhost:8000/api/contacts/:contactId` endpoint. The server will query the database and retrieve the record that matches the params contactId requested by client.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9jfwkcrrn4jlcsuakwi6.png)

#### HTTP PATCH Request: will update a specified record
For updating a record in the database make a `PATCH` HTTP request to the `http://localhost:8000/api/contacts/:contactId` endpoint with a specific ID from the database record as the value of the URL parameter. In the body of the JSON object change values of the columns you want to update. Proceed to make a request and Gin Gonic Server will only update the fields requested to be updated and return an updated record to the client.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5er8y8e8ki8cwrijhfag.png)

#### HTTP DELETE Request:  This will remove a record
To remove a record from the database make a request at the 
`http://localhost:8000/api/contacts/:contactId` endpoint by specifying the ID from the database as the URL parameter.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9u75p2ccgpt8h3st5lbc.png)

## Conclusion

In this guide, you learned the principles of RESTful APIs. You started by generating Golang CRUD code with SQLC and later on ran database migration using the Golang migrate library. You also built a RESTful CRUD API that runs on Gin Gonic HTTP web framework and uses PostgreSQL as the database of choice.

You can find the code for this project in this [Github Repo](https://github.com/Geoff89/golang-sqlc-gorm-rest).