Building CRUD REST APIs using Golang and PostgreSQL

Introduction

In this tutorial, we will learn how to build CRUD (Create, Read, Update, Delete) REST APIs using Golang and a PostgreSQL database. We'll cover everything from setting up the environment to creating and testing the APIs. By the end of this tutorial, you will have a working CRUD API that can interact with a PostgreSQL database.

Installation

Prerequisites

Golang (latest version): Download and install Go
PostgreSQL (latest version): Download and install PostgreSQL
Postman or curl for testing the APIs: Download Postman

Setting Up the Environment

Install Golang: Download and install Go
Install PostgreSQL: Follow the instructions for your operating system to install PostgreSQL.

Create a database and table:

CREATE DATABASE go_crud_api;
\c go_crud_api;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    age INT NOT NULL
);

Steps to Build the REST APIs

Step 1: Initialize the Go Module

mkdir go-crud-api
cd go-crud-api
go mod init go-crud-api

Explanation: This initializes a new Go module in the go-crud-api directory. This is important for managing dependencies.

Step 2: Install Required Packages

go get -u github.com/gin-gonic/gin
go get -u github.com/lib/pq

Explanation: The gin-gonic/gin package is a web framework for building APIs in Go and lib/pq is a driver for PostgreSQL.

Step 3: Create the Main Application File

Create a file named main.go and add the following code:

package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"

    "github.com/gin-gonic/gin"
    _ "github.com/lib/pq"
)

// Declare a global variable to hold the database connection pool
var db *sql.DB

// initDB initializes the database connection
func initDB() {
    var err error
    // Data Source Name (DSN) format: user:password@host:port/dbname
    dsn := "user=postgres password=password dbname=go_crud_api sslmode=disable"
    db, err = sql.Open("postgres", dsn)
    if err != nil {
        log.Fatal(err)
    }

    // Verify the connection to the database
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
}

func main() {
    // Initialize the database connection
    initDB()
    defer db.Close()

    // Create a Gin router instance
    router := gin.Default()

    // Define the API endpoints and their handlers
    router.POST("/users", createUser)
    router.GET("/users", getUsers)
    router.GET("/users/:id", getUserByID)
    router.PUT("/users/:id", updateUser)
    router.DELETE("/users/:id", deleteUser)

    // Start the server on port 8080
    router.Run(":8080")
}

Explanation: This code initializes the database connection, sets up the Gin router, and defines the API endpoints and their handlers.

Step 4: Define the User Model and Handlers

Define the User Model

Create a file named models.go and add the following code:

package main

// User represents the user model
type User struct {
    ID    int    `json:"id"`
    Name  string `json:"name"`
    Email string `json:"email"`
    Age   int    `json:"age"`
}

Explanation: This defines the User struct, which represents the structure of our user data.

Implement the Handlers

Update main.go with the following code:

// createUser handles the creation of a new user
func createUser(c *gin.Context) {
    var user User
    // Bind the incoming JSON payload to the user struct
    if err := c.ShouldBindJSON(&user); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    // Insert the user into the database
    result, err := db.Exec("INSERT INTO users (name, email, age) VALUES ($1, $2, $3)", user.Name, user.Email, user.Age)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    // Retrieve the last inserted ID and set it to the user
    id, _ := result.LastInsertId()
    user.ID = int(id)
    c.JSON(http.StatusCreated, user)
}

// getUsers handles the retrieval of all users
func getUsers(c *gin.Context) {
    rows, err := db.Query("SELECT id, name, email, age FROM users")
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    defer rows.Close()

    // Iterate over the rows and append them to the users slice
    users := []User{}
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.Age); err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
            return
        }
        users = append(users, user)
    }

    c.JSON(http.StatusOK, users)
}

// getUserByID handles the retrieval of a user by ID
func getUserByID(c *gin.Context) {
    id := c.Param("id")

    var user User
    // Query the user by ID
    err := db.QueryRow("SELECT id, name, email, age FROM users WHERE id = $1", id).Scan(&user.ID, &user.Name, &user.Email, &user.Age)
    if err != nil {
        if err == sql.ErrNoRows {
            c.JSON(http.StatusNotFound, gin.H{"error": "User not found"})
        } else {
            c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        }
        return
    }

    c.JSON(http.StatusOK, user)
}

// updateUser handles the update of an existing user
func updateUser(c *gin.Context) {
    id := c.Param("id")
    var user User
    // Bind the incoming JSON payload to the user struct
    if err := c.ShouldBindJSON(&user); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    // Update the user in the database
    _, err := db.Exec("UPDATE users SET name = $1, email = $2, age = $3 WHERE id = $4", user.Name, user.Email, user.Age, id)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    user.ID = int(id)
    c.JSON(http.StatusOK, user)
}

// deleteUser handles the deletion of a user by ID
func deleteUser(c *gin.Context) {
    id := c.Param("id")

    // Delete the user from the database
    _, err := db.Exec("DELETE FROM users WHERE id = $1", id)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, gin.H{"message": "User deleted"})
}

Explanation of Each REST API

Create User

Method: POST
URL: /users
Description: This endpoint creates a new user in the database. The user information is sent as a JSON payload in the request body.
Handler Function: createUser
- Binds the incoming JSON payload to the User struct.
- Inserts the user into the database.
- Returns the created user with a status code of 201 (Created).

Get All Users

Method: GET
URL: /users
Description: This endpoint retrieves all users from the database.
Handler Function: getUsers
- Queries the database for all users.
- Appends each user to a slice of users.
- Returns the list of users with a status code of 200 (OK).

Get User by ID

Method: GET
URL: /users/:id
Description: This endpoint retrieves a user by their ID.
Handler Function: getUserByID
- Extracts the user ID from the URL parameter.
- Queries the database for the user by ID.
- Returns the user with a status code of 200 (OK) if found, or 404 (Not Found) if the user does not exist.

Update User

Method: PUT
URL: /users/:id
Description: This endpoint updates an existing user's information.
Handler Function: updateUser
- Extracts the user ID from the URL parameter.
- Binds the incoming JSON payload to the User struct.
- Updates the user in the database.
- Returns the updated user with a status code of 200 (OK).

Delete User

Method: DELETE
URL: /users/:id
Description: This endpoint deletes a user by their ID.
Handler Function: deleteUser
- Extracts the user ID from the URL parameter.
- Deletes the user from the database.
- Returns a confirmation message with a status code of 200 (OK).

Test REST APIs using Postman or curl

Create User

Method: POST
URL: http://localhost:8080/users

Body:

{
    "name": "Ramesh Fadatare",
    "email": "ramesh.fadatare@example.com",
    "age": 30
}

Get All Users

Method: GET
URL: http://localhost:8080/users

Get User by ID

Method: GET
URL: http://localhost:8080/users/{id}

Update User

Method: PUT
URL: http://localhost:8080/users/{id}

Body:

{
    "name": "Ram Jadhav",
    "email": "ram.jadhav@example.com",
    "age": 25
}

Delete User

Method: DELETE
URL: http://localhost:8080/users/{id}

Conclusion

In this tutorial, we built a set of CRUD REST APIs using Golang and a PostgreSQL database. We covered the entire process, from setting up the environment, creating the necessary models and handlers, and testing the APIs. Following this guide, you should have a solid foundation for creating and managing RESTful services using Go and PostgreSQL. This knowledge can be extended to create more complex and feature-rich APIs for various applications.

Java Guides

Search This Blog