Connecting to PostgreSQL using GORM

This article builds up on the last part where we built a simple REST API with Go. Now, let's connect our API with a real database! Here, we will use PostgreSQL and GORM to accomplish this.

All the code from this article is available here

I've also created a video if you will like to follow along!

Setup

Before we start working with the code, we will need postgres database for development. Easiest way to do this is by using Docker. So make sure you have docker installed.

Now let's define our docker-compose.yml file that will make it even easier to work with the database.



version: "3.8"

services:
  database:
    container_name: database
    image: postgres:12.8
    restart: always
    environment:
      - POSTGRES_USER=pg
      - POSTGRES_PASSWORD=pass
      - POSTGRES_DB=crud
    ports:
      - 5432:5432
    volumes:
      - db:/var/lib/postgresql/data 

volumes:
  db:

Let's start our Postgres container!



$ docker compose up -d

Connecting to our database

Once we have our database running, we can connect to it via any database management tool such as pgAdmin. Here, I'll use TablePlus and create a connection of type PostgreSQL.

Let's add all the details same as we did in docker-compose.yml. So, our user is pg, password is pass and database is crud. After that, we can click on test to verify that we can reach our database. Then click connect.

Now we should be able to see our tables.

Setting up GORM

Let's install GORM and Postgres driver.



$ go get -u gorm.io/gorm
$ go get -u gorm.io/driver/postgres

Update Models

Let's go to pkg/models/book.go and declare our Id as primaryKey in our struct tags.



package models

type Book struct {
    Id     int    `json:"id" gorm:"primaryKey"`
    Title  string `json:"title"`
    Author string `json:"author"`
    Desc   string `json:"desc"`
}

DB package

Now, let's create a db package in pkg/db/db.go. In our Init function we'll simply open a connection to our Postgres database using the dbURL.

Also, we can use db.AutoMigrate to migrate our models.

Note: We can also store the dbURL as an environment variable, but to keep things simple we'll just add it right here.



package db

import (
    "log"

    "github.com/tutorials/go/crud/pkg/models"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

func Init() *gorm.DB {
    dbURL := "postgres://pg:pass@localhost:5432/crud"

    db, err := gorm.Open(postgres.Open(dbURL), &gorm.Config{})

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

    db.AutoMigrate(&models.Book{})

    return db
}

Dependency Injection

Now that we have db setup, let's think how we'll use that in our handlers. We cannot call Init in each handler as this will create lots of database connections. So we will initialize it once and pass it to our handlers. For that, we can make use of dependency injection. Here, let's declare handler struct.



package handlers

import "gorm.io/gorm"

type handler struct {
    DB *gorm.DB
}

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

Now, let's update our handlers to be receiver functions.

pkg/handlers/GetAllBooks.go



func (h handler) GetAllBooks(w http.ResponseWriter, r *http.Request) {}

pkg/handlers/GetBook.go



func (h handler) GetBook(w http.ResponseWriter, r *http.Request) {}

pkg/handlers/AddBook.go



func (h handler) AddBook(w http.ResponseWriter, r *http.Request) {}

pkg/handlers/UpdateBook.go



func (h handler) UpdateBook(w http.ResponseWriter, r *http.Request) {}

pkg/handlers/DeleteBook.go



func (h handler) DeleteBook(w http.ResponseWriter, r *http.Request) {}

Update main

Putting it all together, let's initialize our database and handlers like below.



package main

import (
    "log"
    "net/http"

    "github.com/gorilla/mux"
    "github.com/tutorials/go/crud/pkg/db"
    "github.com/tutorials/go/crud/pkg/handlers"
)

func main() {
    DB := db.Init()
    h := handlers.New(DB)
    router := mux.NewRouter()

    router.HandleFunc("/books", h.GetAllBooks).Methods(http.MethodGet)
    router.HandleFunc("/books/{id}", h.GetBook).Methods(http.MethodGet)
    router.HandleFunc("/books", h.AddBook).Methods(http.MethodPost)
    router.HandleFunc("/books/{id}", h.UpdateBook).Methods(http.MethodPut)
    router.HandleFunc("/books/{id}", h.DeleteBook).Methods(http.MethodDelete)

    log.Println("API is running!")
    http.ListenAndServe(":4000", router)
}

Let's now run our API.



$ go run cmd/main.go

Update handlers

Now, let's update our handlers by replacing mock implementation with GORM.

AddBook

Here, we can use h.DB.Create to create our book.



package handlers

import (
    "encoding/json"
    "fmt"
    "io/ioutil"
    "log"
    "net/http"

    "github.com/tutorials/go/crud/pkg/models"
)

func (h handler) AddBook(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)
    }

    var book models.Book
    json.Unmarshal(body, &book)

    // Append to the Books table
    if result := h.DB.Create(&book); result.Error != nil {
        fmt.Println(result.Error)
    }

    // Send a 201 created response
    w.Header().Add("Content-Type", "application/json")
    w.WriteHeader(http.StatusCreated)
    json.NewEncoder(w).Encode("Created")
}

Let's test it with Postman and create a book. And also check how it reflects in our database.

Looks like we added a new book!

DeleteBook

Here, we can first query the book by using h.DB.First and then simply delete the book we found using h.DB.Delete.



package handlers

import (
    "encoding/json"
    "fmt"
    "net/http"
    "strconv"

    "github.com/gorilla/mux"
    "github.com/tutorials/go/crud/pkg/models"
)

func (h handler) DeleteBook(w http.ResponseWriter, r *http.Request) {
    // Read the dynamic id parameter
    vars := mux.Vars(r)
    id, _ := strconv.Atoi(vars["id"])

    // Find the book by Id

    var book models.Book

    if result := h.DB.First(&book, id); result.Error != nil {
        fmt.Println(result.Error)
    }

    // Delete that book
    h.DB.Delete(&book)

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

Let's send a delete request using Postman.

And if we check the database we'll see that the book was deleted.

GetAllBooks

Here, we can simply fetch all the books using h.DB.Find.



package handlers

import (
    "encoding/json"
    "fmt"
    "net/http"

    "github.com/tutorials/go/crud/pkg/models"
)

func (h handler) GetAllBooks(w http.ResponseWriter, r *http.Request) {
    var books []models.Book

    if result := h.DB.Find(&books); result.Error != nil {
        fmt.Println(result.Error)
    }

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

GetBook

Here, we will use h.DB.Find with book id from request body to query for the book by id.



package handlers

import (
    "encoding/json"
    "fmt"
    "net/http"
    "strconv"

    "github.com/gorilla/mux"
    "github.com/tutorials/go/crud/pkg/models"
)

func (h handler) GetBook(w http.ResponseWriter, r *http.Request) {
    // Read dynamic id parameter
    vars := mux.Vars(r)
    id, _ := strconv.Atoi(vars["id"])

    // Find book by Id
    var book models.Book

    if result := h.DB.First(&book, id); result.Error != nil {
        fmt.Println(result.Error)
    }

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

Let's try this with Postman.

UpdateBook

Finally, we will first find our book by id similar to GetBook handler and update the fields with the request body we received. Then we can simply save it.



package handlers

import (
    "encoding/json"
    "fmt"
    "io/ioutil"
    "log"
    "net/http"
    "strconv"

    "github.com/gorilla/mux"
    "github.com/tutorials/go/crud/pkg/models"
)

func (h handler) UpdateBook(w http.ResponseWriter, r *http.Request) {
    // Read dynamic id parameter
    vars := mux.Vars(r)
    id, _ := strconv.Atoi(vars["id"])

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

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

    var updatedBook models.Book
    json.Unmarshal(body, &updatedBook)

    var book models.Book

    if result := h.DB.First(&book, id); result.Error != nil {
        fmt.Println(result.Error)
    }

    book.Title = updatedBook.Title
    book.Author = updatedBook.Author
    book.Desc = updatedBook.Desc

    h.DB.Save(&book)

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

Let's do a quick request from Postman and check our books table.

And sure enough, the book was updated.

Cleanup

Finally, let's delete pkg/mocks and our project structure should look like this.



├── cmd

│   └── main.go

├── pkg

│    ├── handlers

│    │   ├── handler.go

│    │   ├── AddBook.go

│    │   ├── DeleteBook.go

│    │   ├── GetAllBooks.go

│    │   ├── GetBook.go

│    │   └── UpdateBook.go

│    ├── db

│    │   └── db.go

│    └── models

│        └── book.go

├── docker-compose.yml

├── go.sum

└── go.mod

Next steps

So, we finally connected our CRUD API with PostgreSQL! Our next step could be to adding tests and doing some refactoring, which we will do in the next part!

I hope this was helpful, as always feel free to reach out if you face any issues.

Have a great day!

Blog

Connecting to PostgreSQL using GORM

Karan Pratap Singh

Setup

Setting up GORM

Update handlers

Next steps

Join Our Newsletter. No Spam, Only the good stuff.

Related