Deploying a Golang RESTful API with Gin, SQLC, and PostgreSQL 🚀

Representative State Transfer (REST) is an architectural style for designing Application Programming Interfaces (APIs). An API acts as a set of rules facilitating communication between programs, typically created on the server side to enable client-server interaction.

REST follows the CRUD (Create, Read, Update, and Delete) pattern, where a client requests a resource, and data is sent back as a response. The request comprises an endpoint (URL), method, headers, and data/body.

In this extensive guide, we will delve into the implementation of a CRUD RESTful API using Golang, Gin HTTP web framework, SQLC for CRUD database functions, and PostgreSQL as the chosen database engine. Additionally, we’ll explore the Golang migrate library for database schema migrations.

Prerequisites
Setting up the Golang Project
Setting up PostgreSQL with Docker Compose
Performing Database Migration with Golang Migrate
Using SQLC to Generate Go Code from SQL Queries
Implementing CRUD in Golang Rest API

Route Creation for the Contact Handlers

7. Testing the Golang RESTful API with REST Client

8. Conclusion

Prerequisites

Before diving into the project, ensure you have the following:

Go installed (latest stable version at the time of writing: 1.21.3)
Vscode installed (recommended IDE for this exercise)
Docker installed
Basic knowledge of Golang and SQL (Structured Query Language)

Setting up the Golang Project

Navigate to your preferred directory and open the terminal.
Create a new folder for the project, navigate into it, initialize the Golang project, and open VsCode editor:

mkdir golang-crudsqlc-rest
cd golang-crudsqlc-rest/
go mod init golang-crudsqlc-rest
code .

Install relevant packages for the project:

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

Install the migrate package (commands may vary based on the operating system):

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

Setting up PostgreSQL with Docker Compose

PostgreSQL will be used as the database engine, running as a container. Create a docker-compose.yaml file at the project root:

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:

Create an app.env file at the project root with the necessary credentials:

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

Run PostgreSQL container:

docker-compose up -d

To stop the container:

docker-compose down

Performing Database Migration with Golang Migrate

Database migration is crucial for version tracking of data. Use the golang-migrate library to create migration files. Run the following commands:

mkdir -p db/migration
migrate create -ext sql -dir db/migration -seq init_schema

Add the following SQL code to 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 "contacts_phone_number_key" ON "contacts"("phone_number");

Add the following to db/migration/000001_init_schema.down.sql:

DROP TABLE IF EXISTS contacts;

Run the Postgres container and install the uuid-ossp extension:

docker exec -it postgres bash
psql -U root contact_db

Install the uuid-ossp extension:

CREATE EXTENSION IF NOT EXISTS  "uuid-ossp";

Exit the Postgres shell and Docker shell.

Run the migration script:

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

Verify the changes in a Postgres GUI client.

Using SQLC to Generate Go Code from SQL Queries

Generate Golang CRUD code with sqlc:

sqlc init

Replace sqlc.yaml content with:

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

Generate SQL queries in 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;

Generate Golang code with sqlc generate.

Loading Environment Variables with Viper

Viper is used to load environment variables. Create 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
}

Creating Request Validation Structs

Create 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"`
}

Route Controller Creation

Create 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})
}

This comprehensive guide provides step-by-step instructions for setting up a Golang RESTful API with Gin, SQLC, and PostgreSQL. From project setup to database migration, SQL code generation, and CRUD implementation, you now have the knowledge to build robust APIs using these technologies. Test your API using a REST client, and you’re ready to deploy a performant and scalable Golang API. Happy coding! 🚀

“Support Sarah Dev’s work and join her community of supporters. Contribute to her projects and enjoy exclusive perks by becoming a member! ☕💼 Join now: Support on Buy Me a Coffee”

SarahDev is Girl Dev

Thx 4 the support ❤️

bmc.link

“Access Sarah Dev’s premium content and resources on Gumroad. Elevate your skills and knowledge with her offerings. 🛍️💡 Explore her Gumroad store: Visit Gumroad”

https://sarahdev.gumroad.com