Daily Blog Post: June 7th, 2023

June 7th, 2023

Welcome back to our Go programming series! In our previous post, we covered how to connect to a PostgreSQL database using the pgx library. Now, let's integrate pgx into your Gin web application and build a simple CRUD API.

Integrating `pgx` into Gin

To integrate pgx into Gin, we can define a middleware function that creates a new database connection for each incoming HTTP request. Here's an example:

package main

import (
    "log"
    "net/http"
    "github.com/gin-gonic/gin"
    "github.com/jackc/pgx/v4/pgxpool"
)

func main() {
    // create a new pgxpool pool
    dbpool, err := pgxpool.Connect(context.Background(), "postgres://user:password@localhost:5432/mydb")
    if err != nil {
        log.Fatal(err)
    }
    defer dbpool.Close()

    // create a new Gin router
    router := gin.Default()

    // define a middleware function that creates a new database connection for each incoming HTTP request
    router.Use(func(c *gin.Context) {
        tx, err := dbpool.Begin(c.Request.Context())
        if err != nil {
            c.AbortWithError(http.StatusInternalServerError, err)
            return
        }
        defer tx.Rollback()

        c.Set("db", tx)
        c.Next()

        if c.Writer.Status() >= http.StatusInternalServerError {
            tx.Rollback()
            return
        }

        tx.Commit()
    })

    // define endpoint handlers
    router.GET("/users", getUsers)
    router.GET("/users/:id", getUser)
    router.POST("/users", createUser)
    router.PUT("/users/:id", updateUser)
    router.DELETE("/users/:id", deleteUser)

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

In this example, we create a new pgxpool pool using the pgxpool.Connect method. We define a middleware function that creates a new database connection for each incoming HTTP request using the dbpool.Begin method.

We use the c.Set method to store the database connection in the Gin context for later use in the endpoint handlers.

CRUD API

Now that we have pgx integrated into our Gin web application, we can build a simple CRUD API for managing users in our PostgreSQL database.

Here are the endpoint handlers for our CRUD API:

func getUsers(c *gin.Context) {
    db, ok := c.Get("db")
    if !ok {
        c.AbortWithError(http.StatusInternalServerError, errors.New("no database connection found in Gin context"))
        return
    }

    rows, err := db.Query(context.Background(), "SELECT * FROM users")
    if err != nil {
        c.AbortWithError(http.StatusInternalServerError, err)
        return
    }
    defer rows.Close()

    users := make([]User, 0)
    for rows.Next() {
        var user User
        err := rows.Scan(&user.ID, &user.Name)
        if err != nil {
            c.AbortWithError(http.StatusInternalServerError, err)
            return
        }
        users = append(users, user)
    }

    c.JSON(http.StatusOK, gin.H{"users": users})
}

func getUser(c *gin.Context) {
    db, ok := c.Get("db")
    if !ok {
        c.AbortWithError(http.StatusInternalServerError, errors.New("no database connection found in Gin context"))
        return
    }

    id := c.Param("id")
    var user User
    err := db.QueryRow(context.Background(), "SELECT * FROM users WHERE id = $1", id).Scan(&user.ID, &user.Name)
    if err != nil {
        c.AbortWithError(http.StatusInternalServerError, err)
        return
    }

    c.JSON(http.StatusOK, gin.H{"user": user})
}

func createUser(c *gin.Context) {
    db, ok := c.Get("db")
    if !ok {
        c.AbortWithError(http.StatusInternalServerError, errors.New("no database connection found in Gin context"))
        return
    }

    var user User
    if err := c.ShouldBindJSON(&user); err != nil {
        c.AbortWithError(http.StatusBadRequest, err)
        return
    }

    err := db.QueryRow(context.Background(), "INSERT INTO users (name) VALUES ($1) RETURNING id", user.Name).Scan(&user.ID)
    if err != nil {
        c.AbortWithError(http.StatusInternalServerError, err)
        return
    }

    c.JSON(http.StatusCreated, gin.H{"user": user})
}

func updateUser(c *gin.Context) {
    db, ok := c.Get("db")
    if !ok {
        c.AbortWithError(http.StatusInternalServerError, errors.New("no database connection found in Gin context"))
        return
    }

    id := c.Param("id")
    var user User
    if err := c.ShouldBindJSON(&user); err != nil {
        c.AbortWithError(http.StatusBadRequest, err)
        return
    }

    _, err := db.Exec(context.Background(), "UPDATE users SET name = $1 WHERE id = $2", user.Name, id)
    if err != nil {
        c.AbortWithError(http.StatusInternalServerError, err)
        return
    }

    c.JSON(http.StatusOK, gin.H{"user": user})
}

func deleteUser(c *gin.Context) {
    db, ok := c.Get("db")
    if !ok {
        c.AbortWithError(http.StatusInternalServerError, errors.New("no database connection found in Gin context"))
        return
    }

    id := c.Param("id")
    _, err := db.Exec(context.Background(), "DELETE FROM users WHERE id = $1", id)
    if err != nil {
        c.AbortWithError(http.StatusInternalServerError, err)
        return
    }

    c.Status(http.StatusNoContent)
}

In these endpoint handlers, we use db to interact with our PostgreSQL database using pgx.

We use c.ShouldBindJSON to bind the JSON data from the request body to our User struct.

We return JSON responses using c.JSON.

Conclusion

In this post, we covered how to integrate pgx into Gin and build a simple CRUD API for managing users in our PostgreSQL database. In the next post, we'll wrap up this series by summarizing what we've learned and discussing some resources for further learning. Stay tuned!

June 7th, 2023

Integrating pgx into Gin

CRUD API

Conclusion

Integrating `pgx` into Gin