Golang CRUD REST API with MySQL

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

Prerequisites

  1. Go installed on your machine (latest version).
  2. MySQL 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
cd go-crud-api

  2. Initialize a new Go module:

    go mod init go-crud-api

Step 2: Install Required Packages

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

go get -u github.com/go-sql-driver/mysql
go get -u github.com/gorilla/mux

Step 3: Set Up MySQL Database

  1. Start MySQL and create a new database:

    CREATE DATABASE go_crud_api;

  2. Create a new table:

    USE go_crud_api;

CREATE TABLE users (
  id INT AUTO_INCREMENT 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"

	"github.com/gorilla/mux"
	_ "github.com/go-sql-driver/mysql"
)

// User represents the model for our resource
type User struct {
	ID        int    `json:"id"`
	Name      string `json:"name"`
	Email     string `json:"email"`
	CreatedAt string `json:"created_at"`
}

var db *sql.DB

func main() {
	// Initialize database connection
	var err error
	db, err = sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/go_crud_api")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Create a new router
	router := mux.NewRouter()

	// Define endpoints
	router.HandleFunc("/users", getUsers).Methods("GET")          // Get all users
	router.HandleFunc("/user/{id}", getUser).Methods("GET")       // Get user by ID
	router.HandleFunc("/user", createUser).Methods("POST")        // Create new user
	router.HandleFunc("/user/{id}", updateUser).Methods("PUT")    // Update user by ID
	router.HandleFunc("/user/{id}", deleteUser).Methods("DELETE") // Delete user by ID

	// 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 = ?", 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
	}

	result, err := db.Exec("INSERT INTO users (name, email) VALUES (?, ?)", user.Name, user.Email)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	id, err := result.LastInsertId()
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	user.ID = int(id)
	user.CreatedAt = "now" // Placeholder, in real scenario fetch from DB or use time package
	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 = ?, email = ? WHERE id = ?", user.Name, user.Email, id)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	user.ID = int(id)
	user.CreatedAt = "now" // Placeholder
	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 = ?", 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/gorilla/mux is used for routing.
    • github.com/go-sql-driver/mysql is the MySQL driver.

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

Best Practices

  1. Error Handling: Properly handle errors and return meaningful HTTP status codes.
  2. Validation: Add input validation to ensure data integrity.
  3. Logging: Implement logging to help with debugging and monitoring.
  4. Environment Variables: Use environment variables for sensitive data like database credentials.

Conclusion

This tutorial covered the basics of creating a CRUD API in Golang with MySQL, following best practices. You can expand on this by adding features like authentication, more complex validation, and advanced querying capabilities.


Comments

Post a Comment