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.

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

go get -u github.com/gorilla/mux

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:

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.

: This is the handler function which can access the request and response object of the api. It will extract the request body in the . Then, it will call the pass the as an argument. The 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.

: 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.

: 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.

: Get all the users from the db return all the users as 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.

: This is a handler function and it will first extract the from the param and the then decode the updated user from the request. Then pass the userid as id and updated user to the to update the user in the db. updateUser : This will update the user in the db and return the count of updated rows.

: 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.

: This is a handler function and it will extract the from the param then pass the id to the 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}

Testing the API

Get all user (GET)

I have created an extra user to test.

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

Testing the API

Update a user (PUT)

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

Body: raw/json

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

Testing User API

Delete a user (DELETE)

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

Testing Delete API

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.