In this tutorial, I will walk you through the process of building a Golang CRUD (Create, Read, Update, Delete) application using the gorilla/mux library for APIs and PostgreSQL for data storage.

This beginner-friendly tutorial is perfect for those new to Golang or SQL, as we will cover the essential topics. Let’s dive in!

Prerequisites

Before we begin, please ensure you have the following:

  • Golang installed.
  • A basic understanding of Golang syntax.
  • A basic understanding of SQL queries.
  • A code editor (we recommend VS Code with the Go extension by Microsoft).
  • Postman for calling APIs.

If you’re not comfortable with Golang or SQL queries, simply install Golang and follow along as we cover most of the necessary topics.

Setting up the Golang CRUD Project

  • Create a new project directory called go-postgres outside the $GOPATH.
  • Open the terminal inside the go-postgres project and initiate go modules:
go mod init go-postgres

Go modules are the dependency manager, similar to package.json in Node.js. This command will create a go.mod file.

Installing Dependencies

We will use three packages in this project:

  1. gorilla/mux router: Implements a request router and dispatcher for matching incoming requests to their respective handler.
go get -u github.com/gorilla/mux
  1. lib/pq driver: A pure Go PostgreSQL driver for Go’s database/sql package.
go get github.com/lib/pq

joho/godotenv: We will use the godotenv package to read the .env file, which is used to save environment variables for keeping sensitive data safe.

go get github.com/joho/godotenv

After installing these dependencies, check the go.mod file to ensure they are listed with their installed versions.

Setting up PostgreSQL

PostgreSQL is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance. We will use ElephantSQL, a cloud-based PostgreSQL hosting service with a free plan, to simplify our setup.

  • Register for an ElephantSQL account and create a Tiny Turtle instance.
  • Follow the ElephantSQL documentation to set up your instance.
  • Once your instance is created, create a users table by pasting the following SQL query into the ElephantSQL Browser tab and executing it:
CREATE TABLE users (
    userid SERIAL PRIMARY KEY,
    name TEXT,
    age INT,
    location TEXT
);

Developing the Project

Our project is divided into four parts for modularity and clean code:

The directory structure is:

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

Models

The models package stores the database schema, using a struct type to represent or map the database schema in Golang. Create a models.go file in the models folder with the following 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"`
}

Middleware:

The middleware package serves as the bridge between APIs and the database, handling all CRUD operations. Create a handlers.go file in the middleware folder and add the necessary code to handle CRUD operations, such as establishing a connection to PostgreSQL, executing SQL queries, and handling API requests and responses.

First, create a new folder named middleware and inside it, create a new file called handlers.go. Paste the following code to start:

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "go-postgres/models"
    "log"
    "net/http"
    "os"
    "strconv"

    "github.com/gorilla/mux"
    "github.com/joho/godotenv"
    _ "github.com/lib/pq"
)

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

func createConnection() *sql.DB {
    err := godotenv.Load(".env")

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

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

    if err != nil {
        panic(err)
    }

    err = db.Ping()

    if err != nil {
        panic(err)
    }

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

This part of the code contains the package dependencies required to run the API, as well as the function to establish a connection with the PostgreSQL database.

API Endpoint Handlers

This part of the code contains the functions that handle the API endpoints. These functions include CreateUser, GetUser, GetAllUser, UpdateUser, and DeleteUser. Each function performs a specific operation, such as creating a new user, retrieving an existing user, or updating an existing user.

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

The third part of the code consists of a set of handler functions that define the behavior of the API endpoints. These functions handle incoming requests, perform the necessary operations on the database, and send the appropriate responses.

// 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 inserts successfully in the db returns 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 database by its ID and return the user.
  • getAllUser: This is a handler function that will return all the users in the database.
  • getAllUsers: Get all the users from the database and return them as an array of models.User type.
  • updateUser: This is a handler function that will first extract the ID from the parameters and then decode the updated user from the request. Then pass the user ID as ID and updated user to the updateUser function to update the user in the database.
  • updateUser: This function will update the user in the database and return the count of updated rows.
  • deleteUser: This is a handler function that will extract the ID from the parameters and then pass the ID to the deleteUser function to delete it from the database.
  • deleteUser: This function will delete the user from the database and return the count of deleted rows.

Router:

The router package defines all API endpoints. Create a router.go file in the router folder with the following code:

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 use the gorilla/mux package to create a router, and the Router function handles all endpoints and their respective middleware.

main.go

The main.go file serves as our server, starting on port 8080 and handling all routers. Create a main.go file with the following code:

package main

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

func main() {
    r := router.Router()
    fmt.Println("Starting server on the port 8080...")

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

Testing APIs with Postman

  1. Open the terminal in the go-postgres project and start the server. The server will listen on port 8080.
go run main.go

Open Postman and test the APIs by sending requests to the endpoints, such as creating a new user, retrieving a user, updating a user, and deleting a user.

Create a new user (POST) URL: http://localhost:8080/api/newuser

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

Get a user (GET) URL: http://localhost:8080/api/user/1, The user id is passed as a param in the URL.

/api/user/{id}
Golang CRUD
Testing the API

Get all user (GET)I have created an extra user to test. URL: http://localhost:8080/api/user

Golang CRUD
Testing the API

Update a user (PUT) URL: http://localhost:8080/api/user/1

{
    "name": "golang gopher",
    "age":24,
    "location":"Hyderabad, India"
}
Golang CRUD
Testing User API

Delete a user (DELETE) URL: http://localhost:8080/api/deleteuser/1

Testing Delete API

Conclusion

CRUD operations are essential for most web applications, and this project’s structure keeps modules independent, allowing you to easily replace the database without modifying the rest of the code. You can find the complete code for this project in this Github repository.

By following this beginner-friendly guide, you have learned how to build a Golang CRUD application using the gorilla/mux library and PostgreSQL as the database. This knowledge will serve as a solid foundation for creating more complex web applications in the future.

Further Reading