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:
- gorilla/mux router: Implements a request router and dispatcher for matching incoming requests to their respective handler.
go get -u github.com/gorilla/mux
- 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 theinsertUser
pass theuser
as an argument. TheinsertUser
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 theid
passed as a param in the route.
Usingmux
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
- 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}
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
{
"name": "golang gopher",
"age":24,
"location":"Hyderabad, India"
}
Delete a user (DELETE) URL: http://localhost:8080/api/deleteuser/1
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.