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.