Rest API with GO Lang and PostgreSQL
Tags: go, postgres, webdev, tutorial
Published: 2023-05-08 20:23:50

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***](https://deadsimplechat.com/javascript-chat-api-sdk) ***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

```go
package main

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

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

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 function.

```go
func main() {

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

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

you can use the below code to initialize the server

```sql
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".
```

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

```plaintext
mydb=# select * from albums

//it returns
 id | title | artist | price
----+-------+--------+-------
(0 rows)
```

## 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

```go
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")
}
```

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`](http://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

```go
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)
}
```

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

```go
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

```go
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 initialize 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 album 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 also have a function to retrieve data from the server

let us now try 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](http://localhost:8080/albums) url we can see the data in the JSON format returned by the server

```json
[
    {
        "id": "1",
        "title": "Blue Train",
        "artist": "John Coltrane",
        "price": 56.99
    }
]
```

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

```go
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)
}
```

## 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

```go
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)
}
```

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

```go
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)
}
```

## Conclusion

In this article, we have learned 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

This article was originally published on the DeadSimpleChat Website: [Rest API with GO Lang and PostgreSQL](https://deadsimplechat.com/blog/rest-api-with-golang-and-postgresql/)