Build a CRUD application in Golang with PostgreSQL

Posted on: February 29, 2020 by Shubham

In this tutorial, we are going to build a CRUD application in Golang.
We are going to use gorilla/mux library for the apis and PostgreSQL DB to store the data.

So, without wasting any time, roll up your sleeves. This tutorial is a complete hands-on.

Prerequisites

Install golang v1.11 or above.
Basic understanding of the golang syntax.
Basic understanding of SQL query.
Code Editor (I recommend to use VS Code with Go extension by Microsoft installed)
Postman for calling APIs

Don’t panic if you are not comfortable in golang or SQL query. Just install the golang, I’ll cover most of the topics. ?

Setup the project

Create a new project directory go-postgres outside the $GOPATH.

Open the terminal inside the go-postgres project. Instantiate the go modules.

go mod init go-postgres

go modules is the dependency manager. It is similar to the package.json in nodejs.

This will create a file by name go.mod.

Install dependencies

There are 3 packages we are going to use in this project:
Open the terminal inside the go-postgres project.

1. gorilla/mux router

Package gorilla/mux implements a request router and dispatcher for matching incoming requests to their respective handler.

go get -u github.com/gorilla/mux

2. lib/pq driver

A pure Go postgres driver for Go’s database/sql package.

go get github.com/lib/pq

We are going to use godotenv package to read the .env file. The .env file is used to save the environment variables. Environment variables is used to keep the sensitive data safe. Learn more about how to use environment variables in golang.

go get github.com/joho/godotenv

Now, open go.mod and check. All the installed dependencies are list out there with there installed version.

Similar to this, version can be different.

module go-postgres

require (
    github.com/gorilla/mux v1.7.4
    github.com/joho/godotenv v1.3.0
    github.com/lib/pq v1.3.0
)

Postgres Setup

PostgreSQL ? is a powerful, open-source object-relational database system. It is known for reliability, feature robustness, and performance.

There are following methods to use a postgreSQL:

Local Setup
Cloud Based: ElephantSQL, Azure, AWS, GCP
Docker Image

We are going to use ElephantSQL ?. Compared to all other setups, this is much easier to set up, it is cloud-based and it comes with a free plan. ?

ElephantSQL is a PostgreSQL database hosting service.

Register for an account and create a tiny turtle ? instance. Tiny Turtle is free and doesn’t require any credit card.

Follow this elephantsql documentation.

Once your instance is created. Now, we have to create a table.

Go to the Browser tab in the ElephantSQL and paste the below create table query and execute it.

We are using SERIAL type for userid. SERIAL auto increment with each insert operation.

CREATE TABLE users (
    userid SERIAL PRIMARY KEY,
    name TEXT,
    age INT,
    location TEXT
);

This will create a users table.

Project Development

This project is divided into 4 parts to keep the code modular and clean.

The directory structure is:

|- go-postgres
    |- middleware
        |- handlers.go
    |- models
        |- models.go
    |- router
        |- router.go
    |- .env
    |- main.go

There are other files like go.mod, go.sum and .gitignore.

a. models

The models package will store the database schema. We will use struct type to represent or map the database schema in golang.

Create a new folder models in the go-postgres project.
Create a new file models.go in the models and paste the below code.

package models

// User schema of the user table
type User struct {
    ID       int64  `json:"id"`
    Name     string `json:"name"`
    Location string `json:"location"`
    Age      int64  `json:"age"`
}

The User struct is a representation of users table which we created above.
To learn more how to handle JSON in golang, check out this tutorial.

Note: User is in Uppercase. It means it is public or exported. It can be accessed by the other packages.

b. middleware

The middleware package is the bridge between APIs and Database. This package will handle all the db operations like Insert, Select, Update, and Delete (CRUD).

Create a new folder middleware and create a new file handlers.go inside it.
Paste the below code.

package middleware

import (
    "database/sql"
    "encoding/json" // package to encode and decode the json into struct and vice versa
    "fmt"
    "go-postgres/models" // models package where User schema is defined
    "log"
    "net/http" // used to access the request and response object of the api
    "os"       // used to read the environment variable
    "strconv"  // package used to covert string into int type

    "github.com/gorilla/mux" // used to get the params from the route

    "github.com/joho/godotenv" // package used to read the .env file
    _ "github.com/lib/pq"      // postgres golang driver
)

// response format
type response struct {
    ID      int64  `json:"id,omitempty"`
    Message string `json:"message,omitempty"`
}

// create connection with postgres db
func createConnection() *sql.DB {
    // load .env file
    err := godotenv.Load(".env")

    if err != nil {
        log.Fatalf("Error loading .env file")
    }

    // Open the connection
    db, err := sql.Open("postgres", os.Getenv("POSTGRES_URL"))

    if err != nil {
        panic(err)
    }

    // check the connection
    err = db.Ping()

    if err != nil {
        panic(err)
    }

    fmt.Println("Successfully connected!")
    // return the connection
    return db
}

// CreateUser create a user in the postgres db
func CreateUser(w http.ResponseWriter, r *http.Request) {
    // set the header to content type x-www-form-urlencoded
    // Allow all origin to handle cors issue
    w.Header().Set("Context-Type", "application/x-www-form-urlencoded")
    w.Header().Set("Access-Control-Allow-Origin", "*")
    w.Header().Set("Access-Control-Allow-Methods", "POST")
    w.Header().Set("Access-Control-Allow-Headers", "Content-Type")

    // create an empty user of type models.User
    var user models.User

    // decode the json request to user
    err := json.NewDecoder(r.Body).Decode(&user)

    if err != nil {
        log.Fatalf("Unable to decode the request body.  %v", err)
    }

    // call insert user function and pass the user
    insertID := insertUser(user)

    // format a response object
    res := response{
        ID:      insertID,
        Message: "User created successfully",
    }

    // send the response
    json.NewEncoder(w).Encode(res)
}

// GetUser will return a single user by its id
func GetUser(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Context-Type", "application/x-www-form-urlencoded")
    w.Header().Set("Access-Control-Allow-Origin", "*")
    // get the userid from the request params, key is "id"
    params := mux.Vars(r)

    // convert the id type from string to int
    id, err := strconv.Atoi(params["id"])

    if err != nil {
        log.Fatalf("Unable to convert the string into int.  %v", err)
    }

    // call the getUser function with user id to retrieve a single user
    user, err := getUser(int64(id))

    if err != nil {
        log.Fatalf("Unable to get user. %v", err)
    }

    // send the response
    json.NewEncoder(w).Encode(user)
}

// GetAllUser will return all the users
func GetAllUser(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Context-Type", "application/x-www-form-urlencoded")
    w.Header().Set("Access-Control-Allow-Origin", "*")
    // get all the users in the db
    users, err := getAllUsers()

    if err != nil {
        log.Fatalf("Unable to get all user. %v", err)
    }

    // send all the users as response
    json.NewEncoder(w).Encode(users)
}

// UpdateUser update user's detail in the postgres db
func UpdateUser(w http.ResponseWriter, r *http.Request) {

    w.Header().Set("Content-Type", "application/x-www-form-urlencoded")
    w.Header().Set("Access-Control-Allow-Origin", "*")
    w.Header().Set("Access-Control-Allow-Methods", "PUT")
    w.Header().Set("Access-Control-Allow-Headers", "Content-Type")

    // get the userid from the request params, key is "id"
    params := mux.Vars(r)

    // convert the id type from string to int
    id, err := strconv.Atoi(params["id"])

    if err != nil {
        log.Fatalf("Unable to convert the string into int.  %v", err)
    }

    // create an empty user of type models.User
    var user models.User

    // decode the json request to user
    err = json.NewDecoder(r.Body).Decode(&user)

    if err != nil {
        log.Fatalf("Unable to decode the request body.  %v", err)
    }

    // call update user to update the user
    updatedRows := updateUser(int64(id), user)

    // format the message string
    msg := fmt.Sprintf("User updated successfully. Total rows/record affected %v", updatedRows)

    // format the response message
    res := response{
        ID:      int64(id),
        Message: msg,
    }

    // send the response
    json.NewEncoder(w).Encode(res)
}

// DeleteUser delete user's detail in the postgres db
func DeleteUser(w http.ResponseWriter, r *http.Request) {

    w.Header().Set("Context-Type", "application/x-www-form-urlencoded")
    w.Header().Set("Access-Control-Allow-Origin", "*")
    w.Header().Set("Access-Control-Allow-Methods", "DELETE")
    w.Header().Set("Access-Control-Allow-Headers", "Content-Type")

    // get the userid from the request params, key is "id"
    params := mux.Vars(r)

    // convert the id in string to int
    id, err := strconv.Atoi(params["id"])

    if err != nil {
        log.Fatalf("Unable to convert the string into int.  %v", err)
    }

    // call the deleteUser, convert the int to int64
    deletedRows := deleteUser(int64(id))

    // format the message string
    msg := fmt.Sprintf("User updated successfully. Total rows/record affected %v", deletedRows)

    // format the reponse message
    res := response{
        ID:      int64(id),
        Message: msg,
    }

    // send the response
    json.NewEncoder(w).Encode(res)
}

//------------------------- handler functions ----------------
// insert one user in the DB
func insertUser(user models.User) int64 {

    // create the postgres db connection
    db := createConnection()

    // close the db connection
    defer db.Close()

    // create the insert sql query
    // returning userid will return the id of the inserted user
    sqlStatement := `INSERT INTO users (name, location, age) VALUES ($1, $2, $3) RETURNING userid`

    // the inserted id will store in this id
    var id int64

    // execute the sql statement
    // Scan function will save the insert id in the id
    err := db.QueryRow(sqlStatement, user.Name, user.Location, user.Age).Scan(&id)

    if err != nil {
        log.Fatalf("Unable to execute the query. %v", err)
    }

    fmt.Printf("Inserted a single record %v", id)

    // return the inserted id
    return id
}

// get one user from the DB by its userid
func getUser(id int64) (models.User, error) {
    // create the postgres db connection
    db := createConnection()

    // close the db connection
    defer db.Close()

    // create a user of models.User type
    var user models.User

    // create the select sql query
    sqlStatement := `SELECT * FROM users WHERE userid=$1`

    // execute the sql statement
    row := db.QueryRow(sqlStatement, id)

    // unmarshal the row object to user
    err := row.Scan(&user.ID, &user.Name, &user.Age, &user.Location)

    switch err {
    case sql.ErrNoRows:
        fmt.Println("No rows were returned!")
        return user, nil
    case nil:
        return user, nil
    default:
        log.Fatalf("Unable to scan the row. %v", err)
    }

    // return empty user on error
    return user, err
}

// get one user from the DB by its userid
func getAllUsers() ([]models.User, error) {
    // create the postgres db connection
    db := createConnection()

    // close the db connection
    defer db.Close()

    var users []models.User

    // create the select sql query
    sqlStatement := `SELECT * FROM users`

    // execute the sql statement
    rows, err := db.Query(sqlStatement)

    if err != nil {
        log.Fatalf("Unable to execute the query. %v", err)
    }

    // close the statement
    defer rows.Close()

    // iterate over the rows
    for rows.Next() {
        var user models.User

        // unmarshal the row object to user
        err = rows.Scan(&user.ID, &user.Name, &user.Age, &user.Location)

        if err != nil {
            log.Fatalf("Unable to scan the row. %v", err)
        }

        // append the user in the users slice
        users = append(users, user)

    }

    // return empty user on error
    return users, err
}

// update user in the DB
func updateUser(id int64, user models.User) int64 {

    // create the postgres db connection
    db := createConnection()

    // close the db connection
    defer db.Close()

    // create the update sql query
    sqlStatement := `UPDATE users SET name=$2, location=$3, age=$4 WHERE userid=$1`

    // execute the sql statement
    res, err := db.Exec(sqlStatement, id, user.Name, user.Location, user.Age)

    if err != nil {
        log.Fatalf("Unable to execute the query. %v", err)
    }

    // check how many rows affected
    rowsAffected, err := res.RowsAffected()

    if err != nil {
        log.Fatalf("Error while checking the affected rows. %v", err)
    }

    fmt.Printf("Total rows/record affected %v", rowsAffected)

    return rowsAffected
}

// delete user in the DB
func deleteUser(id int64) int64 {

    // create the postgres db connection
    db := createConnection()

    // close the db connection
    defer db.Close()

    // create the delete sql query
    sqlStatement := `DELETE FROM users WHERE userid=$1`

    // execute the sql statement
    res, err := db.Exec(sqlStatement, id)

    if err != nil {
        log.Fatalf("Unable to execute the query. %v", err)
    }

    // check how many rows affected
    rowsAffected, err := res.RowsAffected()

    if err != nil {
        log.Fatalf("Error while checking the affected rows. %v", err)
    }

    fmt.Printf("Total rows/record affected %v", rowsAffected)

    return rowsAffected
}

Let’s break down the functionalities:

createConnection: This function will create connection with the postgreSQL DB and return the db connection.

Check the code in the function

// use godotenv to load the .env file
err := godotenv.Load(".env")

// Read the POSTGRES_URL from the .env and connect to the db.
db, err := sql.Open("postgres", os.Getenv("POSTGRES_URL"))

Create a new file .env in the go-postgres.

POSTGRES_URL="Postgres connection string"

Now, open the ElephantSQL details and copy the URL and paste to the POSTGRES_URL.

CreateUser: This is the handler function which can access the request and response object of the api. It will extract the request body in the user. Then, it will call the insertUser pass the user as an argument. The insertUser will return the insert id.
insertUser: This function will execute the insert query in the db.
First establish the db connection.

// create the postgres db connection
db := createConnection()

// close the db connection
defer db.Close()

defer statement run at the end of the function.

Create the SQL Query

sqlStatement := `INSERT INTO users (name, location, age) VALUES ($1, $2, $3) RETURNING userid`

We are not passing userid because userid is SERIAL type. Its range is 1 to 2,147,483,647.
With each insertion it will increment.

RETURNING userid means once it insert successfully in the db return the userid.

Execute the Insert query

var id int64
err := db.QueryRow(sqlStatement, user.Name, user.Location, user.Age).Scan(&id)

In QueryRow takes the sql query and arguments. In the sqlStatement, VALUES are passed as variable $1, $2, $3. The user.Name is the first argument, so it will replace the $1. Similarly, all the arguments will replace according to their position.
Using Scan the RETURNING userid will decode to id.

GetUser: This is a handler function and it will return the user by its id.
Get the id passed as a param in the route.
Using mux to get the param. Convert the param type from string to int.

params := mux.Vars(r)

// the id type from string to int
id, err := strconv.Atoi(params["id"])

getUser: Find the user in the db by its id and return the user.
GetAllUser: This is a handler function and it will return all the users in the db.
getAllUsers: Get all the users from the db return all the users as []models.User type.
UpdateUser: This is a handler function and it will first extract the id from the param and the then decode the updated user from the request. Then pass the userid as id and updated user to the updateUser to update the user in the db.
updateUser: This will update the user in the db and return the count of updated rows.
DeleteUser: This is a handler function and it will extract the id from the param then pass the id to the deleteUser function to delete it from the db.
deleteUser: This will delete the user in the db and return the count of deleted rows.

c. router

In the router package we will define all the api endpoints.

Create a folder router and a file router.go inside it.

package router

import (
    "go-postgres/middleware"

    "github.com/gorilla/mux"
)

// Router is exported and used in main.go
func Router() *mux.Router {

    router := mux.NewRouter()

    router.HandleFunc("/api/user/{id}", middleware.GetUser).Methods("GET", "OPTIONS")
    router.HandleFunc("/api/user", middleware.GetAllUser).Methods("GET", "OPTIONS")
    router.HandleFunc("/api/newuser", middleware.CreateUser).Methods("POST", "OPTIONS")
    router.HandleFunc("/api/user/{id}", middleware.UpdateUser).Methods("PUT", "OPTIONS")
    router.HandleFunc("/api/deleteuser/{id}", middleware.DeleteUser).Methods("DELETE", "OPTIONS")

    return router
}

We are using gorilla/mux to create the router. The Router function will handle all the endpoints and respective middleware.

d. main.go

The main.go is our server. It will start a server on 8080 port and serve all the Router.

Create a file main.go and paste the below code.

package main

import (
    "fmt"
    "go-postgres/router"
    "log"
    "net/http"
)

func main() {
    r := router.Router()
    // fs := http.FileServer(http.Dir("build"))
    // http.Handle("/", fs)
    fmt.Println("Starting server on the port 8080...")

    log.Fatal(http.ListenAndServe(":8080", r))
}

Test APIs with Postman

Open the terminal in the go-postgres and start the server.
Server will listen at 8080 port.

go run main.go

Open the Postman.

Create a new user (POST)

URL: http://localhost:8080/api/newuser
Body: raw/json

{
    "name": "gopher",
    "age":25,
    "location":"India"
}

Get a user (GET)

URL: http://localhost:8080/api/user/1

User id is passed as param in the URL.

/api/user/{id}

Get all user (GET)

I have created an extra user to test.
URL: http://localhost:8080/api/user

Update a user (PUT)

URL: http://localhost:8080/api/user/1
Body: raw/json

{
    "name": "golang gopher",
    "age":24,
    "location":"Hyderabad, India"
}

Delete a user (DELETE)

URL: http://localhost:8080/api/deleteuser/1

Conclusion

When you’re creating a microservice project the CRUD operations are ubiquitous. Almost all the web application uses CRUD operations.
This project is structured to keep all the modules independent of each other. So, if you want to use the different DB you just have update the middleware and rest of the code is reusable.

The complete code is available in the Github repo.