In this tutorial, you'll learn how to build a CRUD (Create, Read, Update, Delete) REST API using Golang and MySQL. We'll guide you step-by-step through the process of setting up a Go project, interacting with a MySQL database, and testing all REST APIs using Postman. Whether you're a beginner or have experience with Golang, this tutorial will provide a solid foundation for building robust web APIs.

Prerequisites

Before we get started, ensure that the following tools and software are installed on your machine:

1. Go (Golang) – Make sure you have the latest version of Go installed.
2. MySQL – Ensure that MySQL is installed, running, and accessible.
3. Postman – This tool will help us test the APIs.

Step 1: Set Up Your Go Project

Start by creating a directory for your Go project and initializing it:

mkdir go-crud-api cd go-crud-api go mod init go-crud-api 

This will set up a new Go module and allow you to manage dependencies for your project.

Step 2: Install Required Packages

Next, install the required packages for handling HTTP requests and interacting with MySQL.

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

• github.com/go-sql-driver/mysql: MySQL driver for Go.
• github.com/gorilla/mux: A powerful routing library for building web APIs.

Step 3: Set Up MySQL Database

3.1 Create a Database

Start MySQL and create a new database for our project:

CREATE DATABASE go_crud_api; 

3.2 Create the users Table

Now, create the users table within the go_crud_api database:

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 ); 

This table will store user data, including an auto-incrementing ID, name, email, and timestamp.

Step 4: Create the Main Application File

Create a file called main.go and set up the basic structure of the Go web application.

package main import ( "database/sql" "encoding/json" "fmt" "log" "net/http" "github.com/gorilla/mux" _ "github.com/go-sql-driver/mysql" ) // User represents the user model for our CRUD operations 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() // Initialize router router := mux.NewRouter() // Define API routes router.HandleFunc("/users", getUsers).Methods("GET") // Fetch all users router.HandleFunc("/user/{id}", getUser).Methods("GET") // Fetch a user by ID router.HandleFunc("/user", createUser).Methods("POST") // Create a new user router.HandleFunc("/user/{id}", updateUser).Methods("PUT") // Update a user by ID router.HandleFunc("/user/{id}", deleteUser).Methods("DELETE") // Delete a user by ID // Start server on port 8000 log.Fatal(http.ListenAndServe(":8000", router)) } 

Explanation of the Code

1. MySQL Connection: We establish a connection to the MySQL database using sql.Open.
2. Routing: The Gorilla Mux router is used to define routes and handle API requests.
3. Server Setup: The HTTP server listens on port 8000 for incoming requests.

Step 5: Implement CRUD Operations

5.1 Fetch All Users

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) }

5.2 Fetch a Single User by ID

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) }

5.3 Create a New User

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 json.NewEncoder(w).Encode(user) }

5.4 Update an Existing User

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) }

5.5 Delete a User

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) }

Testing the CRUD API with Postman

You can test the endpoints using Postman by performing the following actions:

1. Get All Users

   • Method: GET
   • URL: http://localhost:8000/users

2. Get a Single User

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

3. Create a New User

   • Method: POST
   • URL: http://localhost:8000/user
   • Body (JSON):

   { "name": "Ramesh Fadatare", "email": "ramesh.fadatare@example.com" } 

4. Update a User

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

   { "name": "Ramesh Fadatare",
    "email": "ramesh@example.com" } 

5. Delete a User

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

Best Practices

1. Error Handling: Always handle errors gracefully and return meaningful HTTP status codes.
2. Input Validation: Validate user input to ensure data integrity before interacting with the database. 
3. Logging: Implement logging to monitor your application and catch issues early. 
4. Environment Variables: Use environment variables for sensitive information like database credentials.

Conclusion

In this tutorial, we covered creating a CRUD REST API using Golang and MySQL. We set up the project, created a MySQL database, and built the necessary API endpoints to manage users. You can extend this example by adding features like authentication, input validation, and more complex querying.