Prerequisites

1. Go installed on your machine (latest version).
2. PostgreSQL installed and running.
3. Postman for testing APIs.

Step 1: Set Up Your Go Project

1. Create a new directory for your project and navigate into it:

   mkdir go-crud-api-postgresql cd go-crud-api-postgresql 

2. Initialize a new Go module:

   go mod init go-crud-api-postgresql 

Step 2: Install Required Packages

Install the necessary packages for handling PostgreSQL database and HTTP requests:

go get github.com/lib/pq go get github.com/gorilla/mux 

Step 3: Set Up PostgreSQL Database

1. Start PostgreSQL and create a new database:

   CREATE DATABASE go_crud_api; 

2. Create a new table:

   \c go_crud_api; CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 

Step 4: Create the Main Application File

Create a new file main.go and set up the basic structure:

package main import (	"database/sql"	"encoding/json"	"fmt"	"log"	"net/http"	"time"	"github.com/gorilla/mux"	_ "github.com/lib/pq" ) // User represents the model for our resource type User struct {	ID int `json:"id"`	Name string `json:"name"`	Email string `json:"email"`	CreatedAt time.Time `json:"created_at"` } var db *sql.DB func main() {	// Initialize PostgreSQL database connection	var err error	connStr := "user=yourusername password=yourpassword dbname=go_crud_api sslmode=disable"	db, err = sql.Open("postgres", connStr)	if err != nil {	log.Fatal(err)	}	defer db.Close()	// Create a new router	router := mux.NewRouter()	// Define endpoints	router.HandleFunc("/users", getUsers).Methods("GET")	router.HandleFunc("/user/{id}", getUser).Methods("GET")	router.HandleFunc("/user", createUser).Methods("POST")	router.HandleFunc("/user/{id}", updateUser).Methods("PUT")	router.HandleFunc("/user/{id}", deleteUser).Methods("DELETE")	// Start server	log.Fatal(http.ListenAndServe(":8000", router)) } // getUsers fetches all users from the database func getUsers(w http.ResponseWriter, r *http.Request) {	var users []User	rows, err := db.Query("SELECT id, name, email, created_at FROM users")	if err != nil {	http.Error(w, err.Error(), http.StatusInternalServerError)	return	}	defer rows.Close()	for rows.Next() {	var user User	if err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt); err != nil {	http.Error(w, err.Error(), http.StatusInternalServerError)	return	}	users = append(users, user)	}	json.NewEncoder(w).Encode(users) } // getUser fetches a single user by ID from the database func getUser(w http.ResponseWriter, r *http.Request) {	params := mux.Vars(r)	id := params["id"]	var user User	err := db.QueryRow("SELECT id, name, email, created_at FROM users WHERE id = $1", id).Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt)	if err != nil {	if err == sql.ErrNoRows {	http.NotFound(w, r)	} else {	http.Error(w, err.Error(), http.StatusInternalServerError)	}	return	}	json.NewEncoder(w).Encode(user) } // createUser creates a new user in the database func createUser(w http.ResponseWriter, r *http.Request) {	var user User	err := json.NewDecoder(r.Body).Decode(&user)	if err != nil {	http.Error(w, err.Error(), http.StatusBadRequest)	return	}	err = db.QueryRow(	"INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, created_at",	user.Name, user.Email).Scan(&user.ID, &user.CreatedAt)	if err != nil {	http.Error(w, err.Error(), http.StatusInternalServerError)	return	}	json.NewEncoder(w).Encode(user) } // updateUser updates an existing user in the database func updateUser(w http.ResponseWriter, r *http.Request) {	params := mux.Vars(r)	id := params["id"]	var user User	err := json.NewDecoder(r.Body).Decode(&user)	if err != nil {	http.Error(w, err.Error(), http.StatusBadRequest)	return	}	_, err = db.Exec("UPDATE users SET name = $1, email = $2 WHERE id = $3", user.Name, user.Email, id)	if err != nil {	http.Error(w, err.Error(), http.StatusInternalServerError)	return	}	user.ID = int(id)	json.NewEncoder(w).Encode(user) } // deleteUser deletes a user from the database func deleteUser(w http.ResponseWriter, r *http.Request) {	params := mux.Vars(r)	id := params["id"]	_, err := db.Exec("DELETE FROM users WHERE id = $1", id)	if err != nil {	http.Error(w, err.Error(), http.StatusInternalServerError)	return	}	w.WriteHeader(http.StatusNoContent) } 

Explanation of the Code and REST APIs

1. Package and Imports

   • The main package is used, and necessary packages are imported.
   • github.com/lib/pq is the PostgreSQL driver.
   • github.com/gorilla/mux is used for routing.

2. User Struct

   • Represents the User model with JSON tags for marshalling/unmarshalling.

3. Global DB Variable

   • db is a global variable to hold the database connection.

4. Main Function

   • Initializes the PostgreSQL database connection.
   • Creates a new router using mux.NewRouter().
   • Defines the REST endpoints and associates them with handler functions.
   • Starts the HTTP server on port 8000.

5. getUsers Function

   • Retrieves all users from the users table.
   • Encodes the result as JSON and writes it to the response.

6. getUser Function

   • Retrieves a single user by ID.
   • Returns a 404 error if the user is not found.

7. createUser Function

   • Decodes the JSON request body to a User struct.
   • Inserts the new user into the users table.
   • Returns the created user as JSON.

8. updateUser Function

   • Decodes the JSON request body to a User struct.
   • Updates the user with the specified ID in the users table.
   • Returns the updated user as JSON.

9. deleteUser Function

   • Deletes the user with the specified ID from the users table.
   • Returns a 204 No Content status on successful deletion.

Testing with Postman

1. Get All Users

   • Method: GET
   • URL: http://localhost:8000/users
   • Response: JSON array of users.

2. Get a Single User

   • Method: GET
   • URL: http://localhost:8000/user/{id}
   • Response: JSON object of the user.

3. Create a New User

   • Method: POST
   • URL: http://localhost:8000/user
   • Body:

     { "name": "John Doe", "email": "john@example.com" } 

   • Response: JSON object of the created user.

4. Update an Existing User

   • Method: PUT
   • URL: http://localhost:8000/user/{id}
   • Body:

     { "name": "Jane Doe", "email": "jane@example.com" } 

   • Response: JSON object of the updated user.

5. Delete a User

   • Method: DELETE
   • URL: http://localhost:8000/user/{id}
   • Response: 204 No Content status.