Dead Simple Chat

 | Blog
Rest API with GO Lang and PostgreSQL
Rest APIs with GO Lang and PostgreSQL

Rest API with GO Lang and PostgreSQL

Dead Simple Chat Team

Table of Contents

Golang is the language developed by google and is extremely fast and efficient. Due to its huge performance and scalability. It is an ideal choice for large scale web development projects

It is a compiled language. The compiler complies the code to an executable which can then run on any computer or server

In this article we will be creating REST API in GO programming language and adding the PostgreSQL database to it.

Dead Simple Chat offers powerful Javascript Chat SDK and APIs that you can use to easily add Chat functionality to your React Applications.

Step 1: Pre-requisites

In this article we are going to create a REST API server in GO lang and using a PostgreSQL database along with it

we are going to be creating a database and API that will receive and store album data such as title, artist name and price

It is going to be a simple application for learning purposes

To go along with this article you need to know

  1. GO lang
  2. Basics of REST API
  3. PostgreSQL  

Step 2: Importing the dependencies

initialize a new project in go and create a file name main.go

then open this file in you code editor and write the below code there

package main

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

	"github.com/gin-gonic/gin"
	_ "github.com/lib/pq"
)
importing required packages

here we have imported the package main and after that we are importing packages like

database/sql log and net/http form the go standard library we are also importing the gin library which is the web framework that we will be using in our app

and we are importing the pq library which is the postgres library for golang

_ before the pq means that we want to execute the package init funtion.

Step 3: connecting to database server

Let us define a variable named db that is going to be of type *sql.DB

now let us create our main func and connect to the database. write the below code in your main.go file

func main() {

	var err error
	db, err = sql.Open("postgres", "postgres://postgres:postgres@localhost/mydb?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
main.go file

Connect to the database using the sql.connect and pass the connection string to it. We are assuming you already have an sql server running in your system.

If not you can easily create one in docker container or in the cloud and connect to it using your credentials

we are also logging the err if any to the console

create a database in your postgreSQL server called mydb then

In your database create a table called as albums with the following fields

  1. id
  2. title
  3. artist
  4. price

you can use the below code to initialize the server 

create database mydb

CREATE TABLE albums (
	id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    artist VARCHAR(255),
    price DECIMAL(10, 2)
    );
    
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
create database mydb and table called as albums

you can check whether the table has been created by using the below code

mydb=# select * from albums

//it returns
 id | title | artist | price
----+-------+--------+-------
(0 rows)
check table has been created

Step 4 : Creating routes and starting the server

Now that we have established the connection to the SQL server it is time to start our go server

paste the below code in your main func below the code connecting to the database like

func main() {

	var err error
	db, err = sql.Open("postgres", "postgres://postgres:postgres@localhost/mydb?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	router := gin.Default()
	router.GET("/albums", getAlbums)
	router.POST("/albums", createAlbum)

	router.Run("localhost:8080")
}
starting the go server

here we are starting the gin web service and we have created two routes one is GET and another is POST

When a req comes to these routes the getAlbums and createAlbum functions are called

we are running the server on localhost://8080

Step 5 : Creating the getAlbums function

The getAlbums function will be called when a GET request is received at the /albums endpoint

the getAlbums will retrieve the data that is the list of all albums present in the database and send it as a response to the GET request

create the getAlbums function as follows

func getAlbums(c *gin.Context) {
	c.Header("Content-Type", "application/json")

	rows, err := db.Query("SELECT id, title, artist, price FROM albums")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	var albums []album
	for rows.Next() {
		var a album
		err := rows.Scan(&a.ID, &a.Title, &a.Artist, &a.Price)
		if err != nil {
			log.Fatal(err)
		}
		albums = append(albums, a)
	}
	err = rows.Err()
	if err != nil {
		log.Fatal(err)
	}

	c.IndentedJSON(http.StatusOK, albums)
}
getAlbums function

What are we doing here

the (c *gin.Context) parameter provides our function with the req and res  objects and c.Header("Content-Type", "application/json") specifies that the response is going to be in JSON format

rows, err := db.Query("SELECT id, title, artist, price FROM albums")
if err != nil {
log.Fatal(err)
}
defer rows.Close()

Here we are sending an SQL query to the database server to fetch all the rows of the album table and return the rows in the rows variable

If there is any err log the error to the console as well

	var albums []album
	for rows.Next() {
		var a album
		err := rows.Scan(&a.ID, &a.Title, &a.Artist, &a.Price)
		if err != nil {
			log.Fatal(err)
		}
		albums = append(albums, a)
	}
	err = rows.Err()
	if err != nil {
		log.Fatal(err)
	}

	c.IndentedJSON(http.StatusOK, albums)

Next we are creating a variable named albums and initializing it to be an array of type album

Next we are creating a for loop and running it on the data returned by the database server

we are iterating each row and appending it to the albums array.

If there is any error we are logging the error to the console

We are converting the albums array into JSON format and returning it in the HTTP response.

now that we have started the server, created the routes, connected to the database server and initialized the database and the tables, we have also function to retrieve data from the server

let us now try out out API for real. I have put in some demo data in the server that we can try to retrieve

(you can also try and populate the database table albums with fake data and that will be returned by this API)

If you don't want to populate data manually in the database, don't worry next we are going to create the createAlbums func that will populate the data in the postgreSQL database

If we go to the http://localhost:8080/albums url we can see the data in the JSON format returned by the server

[
    {
        "id": "1",
        "title": "Blue Train",
        "artist": "John Coltrane",
        "price": 56.99
    }
]
JSON returned by the server

We have created a lot of code till now

  1. we have imported all the dependencies
  2. We have connected to the database server
  3. We have created routes and started the server
  4. We have retrieved the data from the server

Here is the code we have written till now

package main

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

	"github.com/gin-gonic/gin"
	_ "github.com/lib/pq"
)

var db *sql.DB

func main() {

	var err error
	db, err = sql.Open("postgres", 		   "postgres://postgres:postgres@localhost/mydb?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	router := gin.Default()
	router.GET("/albums", getAlbums)
	router.POST("/albums", createAlbum)

	router.Run("localhost:8080")
}

//returns a list of albums from the database
func getAlbums(c *gin.Context) {
	c.Header("Content-Type", "application/json")

	rows, err := db.Query("SELECT id, title, artist, price FROM albums")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	var albums []album
	for rows.Next() {
		var a album
		err := rows.Scan(&a.ID, &a.Title, &a.Artist, &a.Price)
		if err != nil {
			log.Fatal(err)
		}
		albums = append(albums, a)
	}
	err = rows.Err()
	if err != nil {
		log.Fatal(err)
	}

	c.IndentedJSON(http.StatusOK, albums)
}
completed code till now

Step 6: Creating the createAlbum function

Now let us create the createAlbum function this func will be called when a HTTP POST request arrives at the albums endpoint

The createAlbum function will get the data from the HTTP req.body and save that data in the postgreSQL database

Paste the below code after the getAlbums function

type album struct {
	ID     string  `json:"id"`
	Title  string  `json:"title"`
	Artist string  `json:"artist"`
	Price  float64 `json:"price"`
}

func createAlbum(c *gin.Context) {

	var awesomeAlbum album
	if err := c.BindJSON(&awesomeAlbum); err != nil {
		c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"error": "Invalid request payload"})
		return
	}

	stmt, err := db.Prepare("INSERT INTO albums (id, title, artist, price) VALUES ($1, $2, $3, $4)")
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()

	if _, err := stmt.Exec(awesomeAlbum.ID, awesomeAlbum.Title, awesomeAlbum.Artist, awesomeAlbum.Price); err != nil {
		log.Fatal(err)
	}

	c.JSON(http.StatusCreated, awesomeAlbum)
}
createAlbum function

first we are declaring a awesomeAlbum struct called the album. This struct will come in handy when storing the HTTP data received by the API for the time being for checking that the payload data is valid or not

Next in the createAlbum function we are creating a var called awesomeAlbum and assigning it the type of struct album

next we are trying to parse the payload as JSON and bind the data to the awessomeAlbum struct. If successful we move on to the  next code else we return error with invalid request payload

Next we send the data to the SQL server and return that we are successful or else we log error to the console

here is what the complete code looks like

package main

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

	"github.com/gin-gonic/gin"
	_ "github.com/lib/pq"
)


type album struct {
	ID     string  `json:"id"`
	Title  string  `json:"title"`
	Artist string  `json:"artist"`
	Price  float64 `json:"price"`
}

var db *sql.DB

func main() {

	var err error
	db, err = sql.Open("postgres", "postgres://postgres:postgres@localhost/mydb?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	router := gin.Default()
	router.GET("/albums", getAlbums)
	router.POST("/albums", createAlbum)

	router.Run("localhost:8080")
}


func getAlbums(c *gin.Context) {
	c.Header("Content-Type", "application/json")

	rows, err := db.Query("SELECT id, title, artist, price FROM albums")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	var albums []album
	for rows.Next() {
		var a album
		err := rows.Scan(&a.ID, &a.Title, &a.Artist, &a.Price)
		if err != nil {
			log.Fatal(err)
		}
		albums = append(albums, a)
	}
	err = rows.Err()
	if err != nil {
		log.Fatal(err)
	}

	c.IndentedJSON(http.StatusOK, albums)
}


type album struct {
	ID     string  `json:"id"`
	Title  string  `json:"title"`
	Artist string  `json:"artist"`
	Price  float64 `json:"price"`
}


func createAlbum(c *gin.Context) {

	var awesomeAlbum album
	if err := c.BindJSON(&awesomeAlbum); err != nil {
		c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"error": "Invalid request payload"})
		return
	}

	stmt, err := db.Prepare("INSERT INTO albums (id, title, artist, price) VALUES ($1, $2, $3, $4)")
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()

	if _, err := stmt.Exec(awesomeAlbum.ID, awesomeAlbum.Title, awesomeAlbum.Artist, awesomeAlbum.Price); err != nil {
		log.Fatal(err)
	}

	c.JSON(http.StatusCreated, awesomeAlbum)
}
complete code for the go REST API with PostgreSQL database

You might be interested in some of our other articles

Conclusion

In this article we have learnt how to create a go server with rest api and save and retrieve data from a PostgreSQL database server

I hope you liked the article and it helped you

Thank you reading