Golang CRUD REST API with MySQL

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.

Learn the complete Go programming: Learn Go (Golang) Programming.

Check out 100+ Golang Source Code Examples.

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:

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

Let’s create a function that retrieves all users from the database and returns them in JSON format.

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

Let’s create a function that retrieves a single user by their ID and returns it in JSON format. If no user is found, it returns a 404 error.

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

Let’s create a function that decodes the JSON request body and inserts a new user into the database. It returns the created user in JSON format.

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

Let’s create a function that updates an existing user in the database by their ID, returning the updated user as JSON.

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

Let’s create a function that deletes a user from the database by their ID, returning a 204 No Content status upon success.

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:

Get All Users

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

Get a Single User

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

Create a New User

• Method: POST
• URL: http://localhost:8000/user
• Body (JSON):
• { "name": "Ramesh Fadatare", "email": "ramesh.fadatare@example.com" }

Update a User

• Method: PUT
• URL: http://localhost:8000/user/{id}
• Body (JSON):
• { "name": "Ramesh Fadatare",
"email": "ramesh@example.com" }

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.

Original Tutorial:

Golang CRUD REST API with MySQL

References:

Learn Go Programming - Ramesh Fadatare

Golang Source Code Examples