DEV Community

Janire Fernandez
Janire Fernandez

Posted on

Connect REST API to database with Go

#go #rest #api #postgres

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
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

We can stop it by doing:

docker-compose down
Enter fullscreen mode Exit fullscreen mode

Database Container

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.
Database connection details

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
Enter fullscreen mode Exit fullscreen mode

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()
}
Enter fullscreen mode Exit fullscreen mode
  • 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}
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Lets include the dependency in the handlers:

  • GetAllArticles 
func (h handler) GetAllArticles(w http.ResponseWriter, r *http.Request) { ...
Enter fullscreen mode Exit fullscreen mode
  • Get Article By Id 
func (h handler) GetArticle(w http.ResponseWriter, r *http.Request) { ...
Enter fullscreen mode Exit fullscreen mode
  • Create new Article 
func (h handler) AddArticle(w http.ResponseWriter, r *http.Request) { ...
Enter fullscreen mode Exit fullscreen mode
  • Update Article by id 
func (h handler) UpdateArticle(w http.ResponseWriter, r *http.Request) { ...
Enter fullscreen mode Exit fullscreen mode
  • Delete Article by id 
func (h handler) DeleteArticle(w http.ResponseWriter, r *http.Request) {
Enter fullscreen mode Exit fullscreen mode

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)
}
Enter fullscreen mode Exit fullscreen mode

If you now run the API you should see message :

% go run cmd/main.go
Successfully connected to db!
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode
  • To know if table 'articles' already exists: 
SELECT EXISTS (SELECT FROM pg_tables WHERE  schemaname = 'public' AND tablename = 'articles' );
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode
  • 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');
Enter fullscreen mode Exit fullscreen mode

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

}
Enter fullscreen mode Exit fullscreen mode

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

func main() {
    DB := db.Connect()
    db.CreateTable(DB)
    handleRequests(DB)
    db.CloseConnection(DB)
}
Enter fullscreen mode Exit fullscreen mode

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

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)
}
Enter fullscreen mode Exit fullscreen mode

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

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)
}
Enter fullscreen mode Exit fullscreen mode

Get Article Paw

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

}
Enter fullscreen mode Exit fullscreen mode

Add article Paw

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

}
Enter fullscreen mode Exit fullscreen mode

Update Article Paw

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")
}
Enter fullscreen mode Exit fullscreen mode

Delete Article Paw

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

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

Top comments (1)

Collapse
 
chabgood profile image
Chris Habgood •

I have a Postgres locally on my macbook. when I try to select * from table, I keep getting this error:
failed to execute query pq: relation TABLE does not exist.

Read next

kittipat1413 profile image

Checking if a Type Satisfies an Interface in Go

Kittipat.po -

stringintech profile image

Optimizing PostgreSQL Mass Deletions with Table Partitioning

stringintech -

ehab7osam profile image

LeetCode in Golang: Parsing A Boolean Expression

Ehab Hosam -

mspilari profile image

Fullstack app: Deploying the Fullstack Application on Render with Docker

Matheus Bernardes Spilari -