Building REST APIs in Golang go-gin with persistence database Postgres

#go #restapis #webdev #tutorial

Let's build REST APIs in Go using Gin web framework.

I will adding authentication, docker image and sample deployment in Google cloud in next coming articles.

We are going to build APIs around Albums that I took from go.dev but I will extend by using the Postgres database. So, this will have persistent data storage.

Usually when we are working with web frameworks, they usually contains all the required components to build full application without importing any other extra library. See here

We will be having routing, JSON/XML parsers (we could say famous data interchange format parsers), validations, sanitizations of input data, HTTP clients to make call to external APIs, database drivers, things that makes response writing easy to server, ORMs (object relational mapper), Security measures such as CSRF, XSS, SQL Injections, logging etc.. which are required to build application easily.

Just imagine, we do not have fully tested router functionality and in case if want to build router from scratch, it is really hard for normal developer to build and maintain all the times. All the libraries, frameworks and SDKs provide abstraction for us to build application easily. We only need to worry about what is our business logic. In fact there are even Go libraries that generated REST APIs skelton for us. Awesome right ?

Yes, it is, it is happening and it will continue to happen that we do not bother to write code for common web application items such as authentication, authorization, input validations, response generation, unit testing, integration testing, logging, middleware, take any functionality you wanted to have, there will be multiple libraries and APIs (this could be SaaS companies which provide enterprise level things) which makes things easy for us.

I think we are going beyond our current topic, all I am sharing here is basic example of building REST APIs which has persistent memory with Postgres Server.

Let's initialize go module to create to rest apis.

go mod init example.com/web-services

First of all we need to connect to database, I am keeping all the files in same main package as this is simple one but usually we should divide into packages, there are no official project layout for Go but usually developers follow things like handlers, config, services, apis, domain, etc like that, but there are like golang project layout

postgressql.sql


CREATE TABLE IF NOT EXISTS artist (
    id SERIAL PRIMARY KEY,
    name VARCHAR ( 50 ) NOT NULL --this is name of the Author,
    title VARCHAR ( 50 ) UNIQUE NOT NULL,
    price VARCHAR ( 255 ) NOT NULL
);

SELECT id,name,title,price FROM artist LIMIT 5 OFFSET 1;

UPDATE artist 
SET name='nae56',title='tle56', price=66.7667
WHERE id=8;

SELECT * FROM artist;

SELECT * FROM artist WHERE id=8;

DELETE FROM artist WHERE id=1;

INSERT INTO artist(name,title,price) VALUES('name3','title3','price3') ON CONFLICT DO NOTHING;

database.go


package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

// Env holds database connection to Postgres
type Env struct {
    DB *sql.DB
}

// database variables
// usually we should get them from env like os.Getenv("variableName")
const (
    host     = "localhost"
    port     = 5439
    user     = "postgres"
    password = "root"
    dbname   = "artists"
)

// ConnectDB tries to connect DB and on succcesful it returns
// DB connection string and nil error, otherwise return empty DB and the corresponding error.
func ConnectDB() (*sql.DB, error) {
    connString := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname = %s sslmode=disable", host, port, user, password, dbname)
    db, err := sql.Open("postgres", connString)
    if err != nil {
        log.Printf("failed to connect to database: %v", err)
        return &sql.DB{}, err
    }
    return db, nil
}

main.go

package main

import (
    "log"

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

func main() {
    // connect to DB first
    env := new(Env)
    var err error
    env.DB, err = ConnectDB()
    if err != nil {
        log.Fatalf("failed to start the server: %v", err)
    }

    router := gin.Default()
    router.GET("/albums/:id", env.GetAlbumByID)
    router.GET("/albums", env.GetAlbums)
    router.POST("/albums", env.PostAlbum)
    router.PUT("/albums", env.UpdateAlbum)
    router.DELETE("/albums/:id", env.DeleteAlbumByID)

    router.Run("localhost:8080")
}

handler.go

package main

// This is to ensure we do not overload the server when we have millions of rows
var recordFetchLimit = 100 // testing purpose

album_get.go

package main

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

    "github.com/gin-gonic/gin"

    _ "github.com/lib/pq"
)

// GetAlbumByID locates the album whose ID value matches the id
// parameter sent by the client, then returns that album as a response.
func (env Env) GetAlbumByID(c *gin.Context) {
    id, err := strconv.Atoi(c.Param("id"))
    if err != nil {
        e := fmt.Sprintf("received invalid id path param which is not string: %v", c.Param("id"))
        log.Println(e)
        makeGinResponse(c, http.StatusNotFound, e)
        return
    }

    var name, title string
    var price float64
    q := `SELECT * FROM artist WHERE id=$1;`
    row := env.DB.QueryRow(q, id)
    err = row.Scan(&id, &name, &title, &price)
    switch err {
    case sql.ErrNoRows:
        log.Printf("no rows are present for alubum with id: %d", id)
        makeGinResponse(c, http.StatusBadRequest, err.Error())
    case nil:
        log.Printf("we are able to fetch album with given id: %d", id)
        c.JSON(http.StatusOK, NewAlbum(id, title, name, price))
    default:
        e := fmt.Sprintf("error: %v occurred while reading the databse for Album record with id: %d", err, id)
        log.Println(e)
        makeGinResponse(c, http.StatusInternalServerError, err.Error())
    }
}

// GetAlbums responds with the list of all albums as JSON.
func (env Env) GetAlbums(c *gin.Context) {
    // Note:
    //
    // pagnination can be impleted in may ways, but I am following one of the way,
    // if you feel this is confusing, please read medium article that I have added below
    // For this page and perPage isseus, front end engineers can take care of it
    // by escaping and sanitization of query params.
    // Please see: https://www.enterprisedb.com/postgres-tutorials/how-use-limit-and-offset-postgresql
    // Please see: https://levelup.gitconnected.com/creating-a-data-pagination-function-in-postgresql-2a032084af54
    page := c.Query("page") // AKA limit in SQL terms
    if page == "" {
        e := "missing query param: page"
        log.Println(e)
        makeGinResponse(c, http.StatusNotFound, e)
        return
    }

    perPage := c.Query("perPage") // AKA limit in SQL terms
    if perPage == "" {
        e := "missing query param: perPage"
        log.Println(e)
        makeGinResponse(c, http.StatusNotFound, e)
        return
    }

    limit, err := strconv.Atoi(page)
    if err != nil {
        e := fmt.Sprintf("received invalid page query param which is not integer : %v", page)
        log.Println(e)
        makeGinResponse(c, http.StatusBadRequest, e)
        return
    }

    if limit > recordFetchLimit {
        // Seems some bad user or front end developer playing with query params!
        e := fmt.Sprintf("we agreed to fetch less than %d records but we received request for %d", recordFetchLimit, limit)
        log.Println(e)
        makeGinResponse(c, http.StatusBadRequest, e)
        return
    }

    offset, err := strconv.Atoi(perPage)
    if err != nil {
        e := fmt.Sprintf("received invalid offset query param which is not integer : %v", page)
        log.Println(e)
        makeGinResponse(c, http.StatusBadRequest, e)
        return
    }

    // anyway, let's check if offset is a negative value
    if offset < 0 {
        e := "offset query param cannot be negative"
        log.Println(e)
        makeGinResponse(c, http.StatusBadRequest, e)
        return
    }

    q := `SELECT id,name,title,price FROM artist LIMIT $1 OFFSET $2;`
    rows, err := env.DB.Query(q, limit, offset)
    switch err {
    case sql.ErrNoRows:
        defer rows.Close()
        e := "no rows records found in artist table to read"
        log.Println(e)
        makeGinResponse(c, http.StatusBadRequest, e)
    case nil:
        defer rows.Close()
        a := make([]Album, 0)
        var rowsReadErr bool
        for rows.Next() {
            var id int
            var name, title string
            var price float64
            err = rows.Scan(&id, &name, &title, &price)
            if err != nil {
                log.Printf("error occurred while reading the database rows: %v", err)
                rowsReadErr = true
                break
            }
            a = append(a, NewAlbum(id, title, name, price))
        }

        if rowsReadErr {
            log.Println("we are not able to fetch few records")
        }

        // let's return the read rows at least
        log.Printf("we are able to fetch albums for requested limit: %d and offest: %d", limit, offset)
        c.JSON(http.StatusOK, a)
    default:
        defer rows.Close()
        // this should not happen
        e := "some internal database server error"
        log.Println(e)
        makeGinResponse(c, http.StatusInternalServerError, e)
    }
}

album_post.go

package main

import (
    "log"
    "net/http"

    "github.com/gin-gonic/gin"

    _ "github.com/lib/pq"
)

// PostAlbums adds an album from JSON received in the request body.
func (env Env) PostAlbum(c *gin.Context) {
    // Call BindJSON to bind the received JSON to
    // newAlbum.
    var newAlbum Album
    if err := c.BindJSON(&newAlbum); err != nil {
        log.Printf("invalid JSON body: %v", err)
        makeGinResponse(c, http.StatusNotFound, err.Error())
        return
    }

    q := `INSERT INTO artist(name,title,price) VALUES($1,$2,$3) ON CONFLICT DO NOTHING`
    result, err := env.DB.Exec(q, newAlbum.Artist, newAlbum.Title, newAlbum.Price)
    if err != nil {
        log.Printf("error occurred while inserting new record into artist table: %v", err)
        makeGinResponse(c, http.StatusInternalServerError, err.Error())
        return
    }

    // checking the number of rows affected
    n, err := result.RowsAffected()
    if err != nil {
        log.Printf("error occurred while checking the returned result from database after insertion: %v", err)
        makeGinResponse(c, http.StatusInternalServerError, err.Error())
        return
    }

    // if no record was inserted, let us say client has failed
    if n == 0 {
        e := "could not insert the record, please try again after sometime"
        log.Println(e)
        makeGinResponse(c, http.StatusInternalServerError, e)
        return
    }

    // NOTE:
    //
    // Here I wanted to return the location for newly created Album but this
    // 'pq' library does not support, LastInsertionID functionality.
    m := "successfully created the record"
    log.Println(m)
    makeGinResponse(c, http.StatusOK, m)
}

album_put.go

package main

import (
    "fmt"
    "log"
    "net/http"

    "github.com/gin-gonic/gin"

    _ "github.com/lib/pq"
)

// UpdateAlbum updates the Album with the given details if record found.
func (env Env) UpdateAlbum(c *gin.Context) {
    // Call BindJSON to bind the received JSON to
    // toBeUpdatedAlbum.
    var toBeUpdatedAlbum Album
    if err := c.BindJSON(&toBeUpdatedAlbum); err != nil {
        e := fmt.Sprintf("invalid JSON body: %v", err)
        log.Println(e)
        makeGinResponse(c, http.StatusBadRequest, e)
        return
    }

    q := `UPDATE artist 
    SET name=$1,title=$2, price=$3
    WHERE id=$4;`
    result, err := env.DB.Exec(q, toBeUpdatedAlbum.Artist, toBeUpdatedAlbum.Title, toBeUpdatedAlbum.Price, toBeUpdatedAlbum.ID)
    if err != nil {
        e := fmt.Sprintf("error: %v occurred while updating artist record with id: %d", err, toBeUpdatedAlbum.ID)
        log.Println(e)
        makeGinResponse(c, http.StatusInternalServerError, e)
        return
    }

    // checking the number of rows affected
    n, err := result.RowsAffected()
    if err != nil {
        e := fmt.Sprintf("error occurred while checking the returned result from database after updation: %v", err)
        log.Println(e)
        makeGinResponse(c, http.StatusInternalServerError, e)
    }

    // if no record was updated, let us say client has failed
    if n == 0 {
        e := "could not update the record, please try again after sometime"
        log.Println(e)
        makeGinResponse(c, http.StatusInternalServerError, e)
        return
    }

    m := "successfully updated the record"
    log.Println(m)
    makeGinResponse(c, http.StatusOK, m)
}

album_delete.go

package main

import (
    "fmt"
    "log"
    "net/http"
    "strconv"

    "github.com/gin-gonic/gin"

    _ "github.com/lib/pq"
)

// DeleteAlbumByID locates the album whose ID value matches the id
// parameter sent by the client, then returns that corresponding message.
func (env Env) DeleteAlbumByID(c *gin.Context) {
    id, err := strconv.Atoi(c.Param("id"))
    if err != nil {
        e := fmt.Sprintf("received invalid id path param which is not string: %v", c.Param("id"))
        log.Println(e)
        makeGinResponse(c, http.StatusNotFound, e)
        return
    }

    q := `DELETE FROM artist WHERE id = $1;`
    result, err := env.DB.Exec(q, id)
    if err != nil {
        e := fmt.Sprintf("error occurred while deleting artist record with id: %d and error is: %v", id, err)
        log.Println(e)
        makeGinResponse(c, http.StatusInternalServerError, e)
        return
    }

    // checking the number of rows affected
    n, err := result.RowsAffected()
    if err != nil {
        e := fmt.Sprintf("error occurred while checking the returned result from database after deletion: %v", err)
        log.Println(e)
        makeGinResponse(c, http.StatusInternalServerError, e)
        return
    }

    // if no record was deleted, let us inform that there might be no
    // records to delete for this given album ID.
    if n == 0 {
        e := "could not delete the record, there might be no records for the given ID"
        log.Println(e)
        makeGinResponse(c, http.StatusBadRequest, e)
        return
    }

    m := "successfully deleted the record"
    log.Println(m)
    makeGinResponse(c, http.StatusOK, m)
}

We can add business object in domain.go

package main

// Album holds of few important details about it.
type Album struct {
    ID     int     `json:"id,omitempty"`
    Title  string  `json:"title"`
    Artist string  `json:"artist"`
    Price  float64 `json:"price"`
}

// NewAlbum is Album constructor.
func NewAlbum(id int, title, artist string, price float64) Album {
    return Album{id, title, artist, price}
}

There are certain things in application we repeat again, may be we can make one function (DRY principal) add it in the utils.go

package main

import (
    "github.com/gin-gonic/gin"
)

func makeGinResponse(c *gin.Context, statusCode int, value string) {
    c.JSON(statusCode, gin.H{
        "message":    value,
        "statusCode": statusCode,
    })
}

In case if you want try to just take all the codes and copy it run go mod download or go mod tidy to get go.sum and go.mod files.

I have tested them fully, please find Postman collection

postmainCollection.json


{
    "info": {
        "_postman_id": "b7f050de-a273-408c-b11d-3ec27fb8c5d4",
        "name": "Learning01",
        "schema": "https://schema.getpostman.com/json/collection/v2.1.0/collection.json"
    },
    "item": [
        {
            "name": "Get Albums",
            "request": {
                "method": "GET",
                "header": [],
                "url": {
                    "raw": "localhost:8080/albums?page=100&perPage=78",
                    "host": [
                        "localhost"
                    ],
                    "port": "8080",
                    "path": [
                        "albums"
                    ],
                    "query": [
                        {
                            "key": "page",
                            "value": "100"
                        },
                        {
                            "key": "perPage",
                            "value": "78"
                        }
                    ]
                }
            },
            "response": []
        },
        {
            "name": "Get Albums By ID",
            "request": {
                "method": "GET",
                "header": [],
                "url": {
                    "raw": "localhost:8080/albums/10",
                    "host": [
                        "localhost"
                    ],
                    "port": "8080",
                    "path": [
                        "albums",
                        "10"
                    ]
                }
            },
            "response": []
        },
        {
            "name": "Post Album",
            "request": {
                "method": "POST",
                "header": [],
                "body": {
                    "mode": "raw",
                    "raw": "{\r\n    \"id\":6667778,\r\n    \"title\":\"newTitle\",\r\n    \"artist\":\"dfjkfdkj\",\r\n    \"price\":123434.49\r\n}",
                    "options": {
                        "raw": {
                            "language": "json"
                        }
                    }
                },
                "url": {
                    "raw": "localhost:8080/albums",
                    "host": [
                        "localhost"
                    ],
                    "port": "8080",
                    "path": [
                        "albums"
                    ]
                }
            },
            "response": []
        },
        {
            "name": "Update Album",
            "request": {
                "method": "PUT",
                "header": [],
                "body": {
                    "mode": "raw",
                    "raw": "{\r\n    \"id\":10,\r\n    \"title\":\"dddf\",\r\n    \"artist\":\"88888888\",\r\n    \"price\":888.88888\r\n}",
                    "options": {
                        "raw": {
                            "language": "json"
                        }
                    }
                },
                "url": {
                    "raw": "localhost:8080/albums",
                    "host": [
                        "localhost"
                    ],
                    "port": "8080",
                    "path": [
                        "albums"
                    ]
                }
            },
            "response": []
        },
        {
            "name": "Delete Album by ID",
            "request": {
                "method": "DELETE",
                "header": [],
                "url": {
                    "raw": "localhost:8080/albums/10",
                    "host": [
                        "localhost"
                    ],
                    "port": "8080",
                    "path": [
                        "albums",
                        "10"
                    ]
                }
            },
            "response": []
        }
    ]
}