Building a Production REST API with PostgreSQL & Gin

A complete database-backed REST service supporting schema initialization, connection pooling, and JSON endpoints.

// Fundamentals learning: Building a database-backed API (Gin + PostgreSQL).
// To run:
// 1. Ensure Postgres is running locally.
// 2. Run the file:
//    go run basics_learning/4_postgres_db_api/main.go
// 3. Test:
//    POST: curl -X POST http://localhost:8080/tasks -H "Content-Type: application/json" -d '{"description": "Learn Go Database API"}'
//    GET:  curl http://localhost:8080/tasks

package main

import (
    "context"
    "database/sql"
    "log"
    "net/http"
    "os"
    "time"

    "github.com/gin-gonic/gin"
    _ "github.com/jackc/pgx/v5/stdlib"
)

type Task struct {
    ID          int       `json:"id"`
    Description string    `json:"description"`
    Done        bool      `json:"done"`
    CreatedAt   time.Time `json:"created_at"`
}

type CreateTaskReq struct {
    Description string `json:"description" binding:"required"`
}

func main() {
    // 1. Connect to PostgreSQL
    connStr := os.Getenv("DATABASE_URL")
    if connStr == "" {
        connStr = "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
    }

    db, err := sql.Open("pgx", connStr)
    if err != nil {
        log.Fatalf("Failed to open database: %v", err)
    }
    defer db.Close()

    // Verify connection
    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        log.Fatalf("Postgres not available: %v", err)
    }

    // 2. Initialize the Database Table
    createTableSQL := `
    CREATE TABLE IF NOT EXISTS learning_tasks (
        id SERIAL PRIMARY KEY,
        description TEXT NOT NULL,
        done BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );`
    if _, err := db.ExecContext(ctx, createTableSQL); err != nil {
        log.Fatalf("Failed to initialize database table: %v", err)
    }
    log.Println("Database initialized successfully.")

    // 3. Initialize Gin Router
    r := gin.Default()

    // 4. API Endpoint: Create a Task (POST /tasks)
    r.POST("/tasks", func(c *gin.Context) {
        var req CreateTaskReq
        if err := c.ShouldBindJSON(&req); err != nil {
            c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
            return
        }

        insertSQL := `
            INSERT INTO learning_tasks (description) 
            VALUES ($1) 
            RETURNING id, done, created_at;`

        var task Task
        task.Description = req.Description

        // Execute query and scan returning values into the task struct
        err := db.QueryRowContext(
            c.Request.Context(), // Use the request's context so the DB query cancels if the client disconnects!
            insertSQL,
            req.Description,
        ).Scan(&task.ID, &task.Done, &task.CreatedAt)

        if err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to save task: " + err.Error()})
            return
        }

        c.JSON(http.StatusCreated, task)
    })

    // 5. API Endpoint: List all Tasks (GET /tasks)
    r.GET("/tasks", func(c *gin.Context) {
        selectSQL := `SELECT id, description, done, created_at FROM learning_tasks ORDER BY id DESC;`

        rows, err := db.QueryContext(c.Request.Context(), selectSQL)
        if err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to fetch tasks: " + err.Error()})
            return
        }
        defer rows.Close()

        tasks := []Task{} // Initialize empty slice so it serializes as "[]" instead of "null" if empty
        for rows.Next() {
            var task Task
            err := rows.Scan(&task.ID, &task.Description, &task.Done, &task.CreatedAt)
            if err != nil {
                c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to parse task: " + err.Error()})
                return
            }
            tasks = append(tasks, task)
        }

        c.JSON(http.StatusOK, tasks)
    })

    // 6. Start Server
    log.Println("Starting Gin + Postgres server on :8080...")
    if err := r.Run(":8080"); err != nil {
        log.Fatalf("Server failed: %v", err)
    }
}