How to Build a CRUD App with Golang, Gin, and PostgreSQL

CRUD (Create, Read, Update, Delete) applications are common when working with databases. In this tutorial, I'll show you how to create a simple CRUD app using Golang, the Gin framework, and PostgreSQL. By the end, you'll have a basic app where you can manage data stored in a PostgreSQL database.

---

Table of Contents

1. Introduction
2. Prerequisites
3. Folder Structure
4. Setting Up the Project
5. Creating the Database and Table
6. Writing the CRUD Handlers
7. Testing the API
8. Conclusion

---

1. Introduction

In this blog post, we’ll use Gin, a lightweight web framework for Golang, to build our API endpoints. The app will connect to a PostgreSQL database, and we’ll use the pgx driver for database interaction.

This tutorial is beginner-friendly and assumes you have basic knowledge of Golang and REST APIs.

---

2. Prerequisites

Before we start, ensure you have the following installed on your machine:

• Golang (1.20 or higher)
• PostgreSQL (any version)
• Postman or another API testing tool
• A code editor (e.g., VS Code)

---

3. Folder Structure

Here’s the folder structure we’ll use for the project:

crud-app/
├── main.go            # Entry point of the application
├── config/
│   └── database.go    # Database connection setup
├── controllers/
│   └── item.go        # CRUD handlers for the "item" resource
├── models/
│   └── item.go        # Database model for "item"
├── routes/
│   └── routes.go      # API route definitions
├── go.mod             # Go module file
└── go.sum             # Dependency file

This structure is simple but scalable. You can add more resources (e.g., "users" or "orders") later without cluttering the project.

---

4. Setting Up the Project

First, create the project folder and initialize a new Go module:

mkdir crud-app
cd crud-app
go mod init github.com/yourusername/crud-app

Next, install the required packages:

go get github.com/gin-gonic/gin
go get github.com/jackc/pgx/v5

---

5. Creating the Database and Table

Create a PostgreSQL database and a table for our app. For example, we’ll use an "items" table:

CREATE DATABASE crud_app;

\c crud_app

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    price NUMERIC(10, 2)
);

---

6. Writing the CRUD Handlers

Setting Up the Database Connection

In config/database.go:

package config

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

    _ "github.com/jackc/pgx/v5/stdlib"
)

var DB *sql.DB

func ConnectDatabase() {
    var err error
    dsn := "postgres://username:password@localhost:5432/crud_app"
    DB, err = sql.Open("pgx", dsn)
    if err != nil {
        log.Fatalf("Could not connect to the database: %v", err)
    }

    fmt.Println("Database connected!")
}

Update the dsn string with your PostgreSQL username and password.

---

Writing the Model

In models/item.go:

package models

type Item struct {
    ID          int     `json:"id"`
    Name        string  `json:"name"`
    Description string  `json:"description"`
    Price       float64 `json:"price"`
}

---

Writing the Handlers

In controllers/item.go:

package controllers

import (
    "crud-app/config"
    "crud-app/models"
    "github.com/gin-gonic/gin"
    "net/http"
)

// Create a new item
func CreateItem(c *gin.Context) {
    var item models.Item
    if err := c.ShouldBindJSON(&item); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    query := "INSERT INTO items (name, description, price) VALUES ($1, $2, $3) RETURNING id"
    err := config.DB.QueryRow(query, item.Name, item.Description, item.Price).Scan(&item.ID)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to create item"})
        return
    }

    c.JSON(http.StatusCreated, item)
}

You can implement similar handlers for reading, updating, and deleting items.

---

Defining Routes

In routes/routes.go:

package routes

import (
    "crud-app/controllers"
    "github.com/gin-gonic/gin"
)

func SetupRoutes(router *gin.Engine) {
    router.POST("/items", controllers.CreateItem)
    // Add more routes for Read, Update, Delete
}

---

Main File

In main.go:

package main

import (
    "crud-app/config"
    "crud-app/routes"
    "github.com/gin-gonic/gin"
)

func main() {
    config.ConnectDatabase()

    r := gin.Default()
    routes.SetupRoutes(r)

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

---

7. Testing the API

Start the server:

go run main.go

Use Postman or cURL to test the endpoints. For example, to create an item:

POST http://localhost:8080/items

Request body:

{
  "name": "Laptop",
  "description": "A powerful laptop",
  "price": 1200.50
}

---

8. Conclusion

Congratulations! You’ve built a basic CRUD app with Golang, Gin, and PostgreSQL. This structure is easy to expand as your project grows. You can add more features like authentication, logging, or middleware.

Let me know if you’d like more detailed steps or additional features for your project!