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

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

# webdev# programming# go# ai
🚀 Building a CRUD API in Go with PostgreSQL (Step-by-Step)Ahmed Raza Idrisi

In the previous post, we built a simple CRUD API in Go using in-memory storage. Now let’s make it...

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
Enter fullscreen mode Exit fullscreen mode

🧱 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 ✅")
}
Enter fullscreen mode Exit fullscreen mode

🗄️ Step 3: Create Table 

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

🧩 Step 4: Model 

type User struct {
    ID   int    `json:"id"`
    Name string `json:"name"`
}
Enter fullscreen mode Exit fullscreen mode

➕ 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)
}
Enter fullscreen mode Exit fullscreen mode

📥 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)
}
Enter fullscreen mode Exit fullscreen mode

✏️ 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")
}
Enter fullscreen mode Exit fullscreen mode

❌ 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")
}
Enter fullscreen mode Exit fullscreen mode

🌐 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)
}
Enter fullscreen mode Exit fullscreen mode

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