Connect REST API to database with Go

In previous tutorial we learned how to create a REST API with Go

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

Create database

To create a database we are going to use a Docker image. Make sure you have docker installed.

*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

Connect to database

Now that the database is created let's connect to it. In my case I will be using TablePlus, but you can use other database management.

We need to create a new connection with details defined in docker-compose.yml.

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

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

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")

}

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")

}

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

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