Golang CRUD REST API with PostgreSQL

This tutorial will guide you through creating a CRUD (Create, Read, Update, Delete) API using Golang and PostgreSQL. We'll test all the REST APIs using Postman.

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.

Comments

Post a Comment