DEV Community

Cover image for Connecting to PostgreSQL using GORM
Karan Pratap Singh
Karan Pratap Singh

Posted on

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

Let's start our Postgres container!

$ docker compose up -d
Enter fullscreen mode Exit fullscreen mode

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.

table-plus

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.

table-plus-connection

Now we should be able to see our tables.
table-plus-tables

Setting up GORM

Let's install GORM and Postgres driver.

$ go get -u gorm.io/gorm
$ go get -u gorm.io/driver/postgres
Enter fullscreen mode Exit fullscreen mode

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

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

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

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

pkg/handlers/GetAllBooks.go

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

pkg/handlers/GetBook.go

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

pkg/handlers/AddBook.go

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

pkg/handlers/UpdateBook.go

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

pkg/handlers/DeleteBook.go

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

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

Let's now run our API.

$ go run cmd/main.go
Enter fullscreen mode Exit fullscreen mode

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

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

addbook-postman

Looks like we added a new book!

addbook-table-plus

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

Let's send a delete request using Postman.

deletebook-postman

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

deletebook-table-plus

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

getallbooks-postman
getallbooks-table-plus

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

Let's try this with Postman.
getbook-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")
}
Enter fullscreen mode Exit fullscreen mode

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

updatebook-postman

And sure enough, the book was updated.

updatebook-table-plus

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

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!

Discussion (2)

Collapse
nsavelyeva profile image
Natallia Savelyeva

Hello Karan, thanks for the tutorial!
I am looking forward to your next post. In particular, I am very interested in writing unit tests for the database layer. With GORM V1 github.com/jinzhu/gorm I used go-mocket github.com/selvatico/go-mocket, and now I am looking for a way to migrate to GORM V2 and update unit tests.

Collapse
karanpratapsingh profile image
Karan Pratap Singh Author

Thank you Natallia, means a lot!