Connect REST API to database with Go
Tags: go, rest, api, postgres
Published: 2022-10-15 15:27:54

In previous tutorial we learned how to [create a REST API with Go](https://dev.to/janirefdez/create-a-rest-api-with-go-1j52)

Now we are going to connect this API to a database. We will use PostgreSQL.

If you want to check the whole project here you have the link: [ArticleRestApi](https://github.com/janirefdez/ArticleRestApi/tree/database-connection)

## Create database
To create a database we are going to use a Docker image. Make sure you have [docker installed](https://www.docker.com/get-started/).

*`A Docker image is a read-only template that contains a set of instructions for creating a container that can run on the Docker platform.` 

Create a file `docker-compose.yml`:

```
version: "3.8"
services:
  database:
    container_name: database
    image: postgres
    ports:
      - "5432:5432"
    volumes:
      - ./database:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=articlesDB
      - POSTGRES_USER=janire
      - POSTGRES_PASSWORD=password
```

- This will pull the postgres image from the Docker Hub.
- The container opens port `5432`.
- It sets the environment variables to use when connecting to the database.
- The volume is needed so the data persists inside the container at `/var/lib/postgresql/data`.

To start the docker container use command:
```
docker-compose up -d
```

We can stop it by doing: 
```
docker-compose down
```

![Database Container](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2xwhwvbrppv9saky2vit.png)


## Connect to database

Now that the database is created let's connect to it. In my case I will be using [TablePlus] (https://tableplus.com/), but you can use other database management. 

We need to create a new connection with details defined in `docker-compose.yml`.
![Database connection details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kcar9va95gq4m2ujs7v0.png)

## Connect to database with Go

Go’s standard library was not built to include any specific database drivers. So we need to install a third party package. In this case we are going to install [https://github.com/lib/pq](https://github.com/lib/pq). Run following command:

```
go get github.com/lib/pq
```

You should see that the dependency has been included in file called `go.mod` and `go.sum`. 

Now we are going to create `db.go` file in package `pkg/db`. 

```
package db

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

	"github.com/janirefdez/ArticleRestApi/pkg/mocks"
	_ "github.com/lib/pq"
)

const (
	host     = "localhost"
	port     = 5432
	user     = "janire"
	password = "password"
	dbname   = "articlesDB"
)

func Connect() *sql.DB {
	connInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)

	db, err := sql.Open("postgres", connInfo)
	if err != nil {
		log.Fatal(err)
	}
	err = db.Ping()
	if err != nil {
		panic(err)
	}
	fmt.Println("Successfully connected to db!")
	return db
}

func CloseConnection(db *sql.DB) {
	defer db.Close()
}
```
- Connection information is defined in our `docker-compose.yml` file.
- `sql.Open()` connects to the database.

Now that we have the database setup, we need to think where to start the connection. We cannot connect in each handler as this will create lots of database connections.

We will use dependency injection to pass only one database connection to all the handlers. 

Create file `handler.go` in `pkg/handlers`:

```
package handlers

import "database/sql"

type handler struct {
	DB *sql.DB
}

func New(db *sql.DB) handler {
	return handler{db}
}
```

The structure of the project in our example should now be like this: 
```
├── cmd
│   └── main.go
├── pkg
│    ├── db
│    │   └── db.go
│    ├── handlers
│    │   ├── AddArticle.go
│    │   ├── DeleteArticle.go
│    │   ├── GetAllArticles.go
│    │   ├── GetArticle.go
│    │   ├── UpdateArticle.go
│    │   └── Handler.go
│    ├── mocks
│    │   └── article.go
│    └── models
│        └── article.go
├── go.sum
└── go.mod

```

Lets include the dependency in the handlers:

- GetAllArticles
```
func (h handler) GetAllArticles(w http.ResponseWriter, r *http.Request) { ...
```

- Get Article By Id
```
func (h handler) GetArticle(w http.ResponseWriter, r *http.Request) { ...
```

- Create new Article
```
func (h handler) AddArticle(w http.ResponseWriter, r *http.Request) { ...
```

- Update Article by id
```
func (h handler) UpdateArticle(w http.ResponseWriter, r *http.Request) { ...
```

- Delete Article by id
```
func (h handler) DeleteArticle(w http.ResponseWriter, r *http.Request) {
```

Now let's change the `cmd/main.go` to start the database connection: 

```
func handleRequests(DB *sql.DB) {
	h := handlers.New(DB)
	// create a new instance of a mux router
	myRouter := mux.NewRouter().StrictSlash(true)
	myRouter.HandleFunc("/", homePage)
	myRouter.HandleFunc("/articles", h.GetAllArticles).Methods(http.MethodGet)
	myRouter.HandleFunc("/articles/{id}", h.GetArticle).Methods(http.MethodGet)
	myRouter.HandleFunc("/articles", h.AddArticle).Methods(http.MethodPost)
	myRouter.HandleFunc("/articles/{id}", h.UpdateArticle).Methods(http.MethodPut)
	myRouter.HandleFunc("/articles/{id}", h.DeleteArticle).Methods(http.MethodDelete)
	log.Fatal(http.ListenAndServe(":8080", myRouter))
}

func main() {
	DB := db.Connect()
	handleRequests(DB)
	db.CloseConnection(DB)
}
```

If you now run the API you should see message : 
```
% go run cmd/main.go
Successfully connected to db!
```

But we still have to update the functions of the handlers to use the database information instead of the mocks defined in `pkg/mocks/article.go` and we also have to create a table 'articles' in the database. So let's start with that.  

## Create table in database with Go

We can create a table directly with `TablePlus` or we can create it with the code. These are the SQL queries we will use: 

- To create the table 'articles':
```
CREATE TABLE articles (id VARCHAR(36) PRIMARY KEY, title VARCHAR(100) NOT NULL, description VARCHAR(50) NOT NULL, content VARCHAR(50) NOT NULL);
``` 
- To know if table 'articles' already exists:
```
SELECT EXISTS (SELECT FROM pg_tables WHERE  schemaname = 'public' AND tablename = 'articles' );
```
or when creating the table we can include `IF NOT EXISTS`
```
CREATE TABLE IF NOT EXISTS  articles  (id VARCHAR(36) PRIMARY KEY, title VARCHAR(100) NOT NULL, description VARCHAR(50) NOT NULL, content VARCHAR(50) NOT NULL);
```

- To include data in table 'articles':
```
INSERT INTO articles (id,title,description,content) VALUES ('8617bf49-39a9-4268-b113-7b6bcd189ba2', 'Article 1', 'Article Description 1', 'Article Content 1');
```

In this example we will create the table with Go so let's create a new function in `pkg/db/db.go`:

```
func CreateTable(db *sql.DB) {
	var exists bool
	if err := db.QueryRow("SELECT EXISTS (SELECT FROM pg_tables WHERE  schemaname = 'public' AND tablename = 'articles' );").Scan(&exists); err != nil {
		fmt.Println("failed to execute query", err)
		return
	}
	if !exists {
		results, err := db.Query("CREATE TABLE articles (id VARCHAR(36) PRIMARY KEY, title VARCHAR(100) NOT NULL, description VARCHAR(50) NOT NULL, content VARCHAR(50) NOT NULL);")
		if err != nil {
			fmt.Println("failed to execute query", err)
			return
		}
		fmt.Println("Table created successfully", results)

		for _, article := range mocks.Articles {
			queryStmt := `INSERT INTO articles (id,title,description,content) VALUES ($1, $2, $3, $4) RETURNING id;`

			err := db.QueryRow(queryStmt, &article.Id, &article.Title, &article.Desc, &article.Content).Scan(&article.Id)
			if err != nil {
				log.Println("failed to execute query", err)
				return
			}
		}
		fmt.Println("Mock Articles included in Table", results)
	} else {
		fmt.Println("Table 'articles' already exists ")
	}

}
```

We will call this function in `cmd/main.go`:

```
func main() {
	DB := db.Connect()
	db.CreateTable(DB)
	handleRequests(DB)
	db.CloseConnection(DB)
}
```

If you run the API it will first create the database and insert data into it. You should now be able to see content in your database:

![Table articles](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q9wq0rdp31uv747vqash.png)

## Update handlers

We already have a database with articles and we are able to create a connection with Go. Now its time to modify the handlers so our API works with the database instead of the mocks defined in `pkg/mocks/article.go`.

### Get All Articles
In `pkg/handlers/GetAllArticles.go` we need to use query `SELECT * FROM articles;`
```
func (h handler) GetAllArticles(w http.ResponseWriter, r *http.Request) {

	results, err := h.DB.Query("SELECT * FROM articles;")
	if err != nil {
		log.Println("failed to execute query", err)
		w.WriteHeader(500)
		return
	}

	var articles = make([]models.Article, 0)
	for results.Next() {
		var article models.Article
		err = results.Scan(&article.Id, &article.Title, &article.Desc, &article.Content)
		if err != nil {
			log.Println("failed to scan", err)
			w.WriteHeader(500)
			return
		}

		articles = append(articles, article)
	}

	w.Header().Add("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	json.NewEncoder(w).Encode(articles)
}
```

The method returns results, which is of type `sql.Rows`. This stores the result of a query, row by row. So we need to go thought the list and scan the content.

![Get All Articles Paw](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2vpjhj9qqriutec1hnzs.png)

### Get Article By Id

In `pkg/handlers/GetArticle.go` we need to use query `SELECT * FROM articles WHERE id = $1 ;`

```
func (h handler) GetArticle(w http.ResponseWriter, r *http.Request) {
	vars := mux.Vars(r)
	id := vars["id"]

	queryStmt := `SELECT * FROM articles WHERE id = $1 ;`
	results, err := h.DB.Query(queryStmt, id)
	if err != nil {
		log.Println("failed to execute query", err)
		w.WriteHeader(500)
		return
	}

	var article models.Article
	for results.Next() {
		err = results.Scan(&article.Id, &article.Title, &article.Desc, &article.Content)
		if err != nil {
			log.Println("failed to scan", err)
			w.WriteHeader(500)
			return
		}
	}

	w.Header().Add("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	json.NewEncoder(w).Encode(article)
}
```

![Get Article Paw](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ctgvy0z8ejg4ej7edef5.png)

### Create new Article
In `pkg/handlers/AddArticle.go` we need to use query `INSERT INTO articles (id,title,description,content) VALUES ($1, $2, $3, $4)`

```
func (h handler) AddArticle(w http.ResponseWriter, r *http.Request) {
	// Read to request body
	defer r.Body.Close()
	body, err := ioutil.ReadAll(r.Body)

	if err != nil {
		log.Fatalln(err)
		w.WriteHeader(500)
		return
	}
	var article models.Article
	json.Unmarshal(body, &article)

	article.Id = (uuid.New()).String()
	queryStmt := `INSERT INTO articles (id,title,description,content) VALUES ($1, $2, $3, $4) RETURNING id;`
	err = h.DB.QueryRow(queryStmt, &article.Id, &article.Title, &article.Desc, &article.Content).Scan(&article.Id)
	if err != nil {
		log.Println("failed to execute query", err)
		w.WriteHeader(500)
		return
	}

	w.Header().Add("Content-Type", "application/json")
	w.WriteHeader(http.StatusCreated)
	json.NewEncoder(w).Encode("Created")

}
```

![Add article Paw](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6uszqro1sik6axta8usq.png)


### Update Article by id

In `pkg/handlers/UpdateArticle.go` we need to use query `UPDATE articles SET title = $2, description = $3, content = $4 WHERE id = $1 RETURNING id;`

```
func (h handler) UpdateArticle(w http.ResponseWriter, r *http.Request) {
	vars := mux.Vars(r)
	id := vars["id"]

	// Read request body
	defer r.Body.Close()
	body, err := ioutil.ReadAll(r.Body)

	if err != nil {
		log.Fatalln(err)
	}

	var updatedArticle models.Article
	json.Unmarshal(body, &updatedArticle)

	queryStmt := `UPDATE articles SET title = $2, description = $3, content = $4 WHERE id = $1 RETURNING id;`
	err = h.DB.QueryRow(queryStmt, &id, &updatedArticle.Title, &updatedArticle.Desc, &updatedArticle.Content).Scan(&id)
	if err != nil {
		log.Println("failed to execute query", err)
		w.WriteHeader(500)
		return
	}

	w.Header().Add("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	json.NewEncoder(w).Encode("Updated")

}
```

![Update Article Paw](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yxnwlng2aqazoo6olx6k.png)

### Delete Article by id

In `pkg/handlers/DeleteArticle.go` we need to use query `DELETE FROM articles WHERE id = $1;`

```
func (h handler) DeleteArticle(w http.ResponseWriter, r *http.Request) {
	vars := mux.Vars(r)
	id := vars["id"]

	queryStmt := `DELETE FROM articles WHERE id = $1;`
	_, err := h.DB.Query(queryStmt, &id)
	if err != nil {
		log.Println("failed to execute query", err)
		w.WriteHeader(500)
		return
	}

	w.Header().Add("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	json.NewEncoder(w).Encode("Deleted")
}
```
![Delete Article Paw](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uuv4opbf6k3p8gdzytm3.png)

And that's all!! You have an API that connects to a database. Hope you enjoyed!!

## Improvements
- Include error response when an article is not found.
- Include tests.
- ...

If you want to check the whole project here you have the link: [ArticleRestApi](https://github.com/janirefdez/ArticleRestApi)

Don't forget to like and share! Thank you! :)