🚀 Building a CRUD API in Go with PostgreSQL (Step-by-Step)

In the previous post, we built a simple CRUD API in Go using in-memory storage.

Now let’s make it real-world ready by connecting it to a database:

👉 PostgreSQL

---

⚙️ Step 1: Install PostgreSQL Driver

We’ll use the official driver:

go get github.com/lib/pq

---

🧱 Step 2: Setup Database Connection

package main

import (
    "database/sql"
    "log"

    _ "github.com/lib/pq"
)

var db *sql.DB

func initDB() {
    connStr := "user=postgres password=postgres dbname=testdb sslmode=disable"

    var err error
    db, err = sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }

    err = db.Ping()
    if err != nil {
        log.Fatal("DB not reachable:", err)
    }

    log.Println("Connected to PostgreSQL ✅")
}

---

🗄️ Step 3: Create Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

---

🧩 Step 4: Model

type User struct {
    ID   int    `json:"id"`
    Name string `json:"name"`
}

---

➕ Create User (INSERT)

func createUser(w http.ResponseWriter, r *http.Request) {
    var user User
    json.NewDecoder(r.Body).Decode(&user)

    err := db.QueryRow(
        "INSERT INTO users(name) VALUES($1) RETURNING id",
        user.Name,
    ).Scan(&user.ID)

    if err != nil {
        http.Error(w, err.Error(), 500)
        return
    }

    json.NewEncoder(w).Encode(user)
}

---

📥 Get Users (SELECT)

func getUsers(w http.ResponseWriter, r *http.Request) {
    rows, err := db.Query("SELECT id, name FROM users")
    if err != nil {
        http.Error(w, err.Error(), 500)
        return
    }
    defer rows.Close()

    var users []User

    for rows.Next() {
        var user User
        rows.Scan(&user.ID, &user.Name)
        users = append(users, user)
    }

    json.NewEncoder(w).Encode(users)
}

---

✏️ Update User

func updateUser(w http.ResponseWriter, r *http.Request) {
    idParam := r.URL.Query().Get("id")

    var user User
    json.NewDecoder(r.Body).Decode(&user)

    _, err := db.Exec(
        "UPDATE users SET name=$1 WHERE id=$2",
        user.Name, idParam,
    )

    if err != nil {
        http.Error(w, err.Error(), 500)
        return
    }

    json.NewEncoder(w).Encode("Updated")
}

---

❌ Delete User

func deleteUser(w http.ResponseWriter, r *http.Request) {
    idParam := r.URL.Query().Get("id")

    _, err := db.Exec("DELETE FROM users WHERE id=$1", idParam)
    if err != nil {
        http.Error(w, err.Error(), 500)
        return
    }

    json.NewEncoder(w).Encode("Deleted")
}

---

🌐 Step 5: Main Function

func main() {
    initDB()

    http.HandleFunc("/users", getUsers)
    http.HandleFunc("/create", createUser)
    http.HandleFunc("/update", updateUser)
    http.HandleFunc("/delete", deleteUser)

    log.Println("Server running on :8080")
    http.ListenAndServe(":8080", nil)
}

---

🧪 Test API

Same as before, but now data is stored in PostgreSQL 🎉

---

⚠️ Improvements You Should Do Next

This is still beginner-level. To make it production-ready:

• Use environment variables for DB config
• Add validation
• Use a router (Chi / Gorilla Mux)
• Structure code (controllers, services, repo)
• Add connection pooling tuning

---

🔥 What You Learned

• Connecting Go to PostgreSQL
• Executing SQL queries (INSERT, SELECT, UPDATE, DELETE)
• Handling real database-backed APIs

---

🚀 Coming Next

👉 Proper API structure in Go (like Laravel mindset)
👉 Using Docker with Go + PostgreSQL
👉 Adding authentication (JWT)

---

💬 Final Thought

Now you're no longer just learning Go…

👉 You're building backend systems.

---