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

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

Setting Up the Environment

  1. Install Golang: Download and install Go

  2. Install PostgreSQL: Follow the instructions for your operating system to install PostgreSQL.

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

Comments