Building Your First CRUD App in Go: A Hands-On Tutorial

Developing RESTful applications represents a widely adopted approach for constructing web services due to their straightforward and scalable architecture. CRUD, an acronym for Create, Read, Update, and Delete, constitutes a foundational task in software development. The capability to execute CRUD operations is intrinsic to virtually any application development endeavor. In this tutorial, we will delve into the construction of a basic CRUD application using Go in conjunction with MySQL.

Go, renowned for its efficiency and simplicity, serves as our programming language of choice, while MySQL functions as the robust relational database management system supporting our project. Our journey commences with the establishment of the development environment and the creation of the database schema. Subsequently, we will craft the API endpoints essential for executing CRUD operations.

Getting Your Environment Ready

To kick off our project, let's prepare our development environment, create the database, and establish the essential connection between our MySQL database and the Golang application.

So ensure you have a version of Go installed and a relational database , for the purpose of this demo we will be using MySQL.

Initializing Your Golang Project

To begin our journey, let's set up the development environment and initiate a Golang project for constructing our CRUD application. Here's a step-by-step guide:

• Create a Project Directory: After successfully setting up the repository, create a dedicated directory for your project and navigate into it using the following commands:

mkdir DirectoryName && cd DirectoryName        

• Initialize the Golang Project: Now that you're inside your project directory, you can initialize a Golang project with the repository link you created earlier using the following command:

go mod init DirectoryName        

This command generates a go.mod file containing the URL you specified along with the Golang version you're using.

• Create a Golang File: Next, create a file where you'll write all your Golang code. You can do this with the following command for the purpose of keeping it simple let's call it main.go:

package main

func main (){
// code here
}        

Now that you've completed these initial steps, you're ready to edit the Golang file and start building the package, including the CRUD functions.

Imports & dependencies

Before going any further , we will need to imports some package from the standard library and some 3rd party ones .

as for routing , we will be using the Chi router. while the Chi middleware is not required , it's a valuable as it will log out informations about each route or endpoint you use .

 go get -u github.com/go-sql-driver/mysql
 go get -u github.com/go-chi/chi/v5        

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"html/template"
	"log"
	"net/http"
	"strconv"

	"github.com/go-chi/chi/middleware"
	"github.com/go-chi/chi/v5"
	_ "github.com/go-sql-driver/mysql"
)

func main (){
// your database connection information ideally , this should be in a .env file and kept secret 
dbHost := "mysql"
dbUser := "root"
dbPwd  := "admin"
dbName := "DatabaseName"

// ....
}        

Preparing the database

Setting up your database and it's schema is a crucial step. Our application's purpose? To securely store and seamlessly retrieve user information from a MySQL database. This information comprises the user's name and email—a fundamental component of our web application.

Now, let's dive into the creation of the database :

Step 1: Accessing MySQL

To do so, execute the following command in your terminal. It's worth noting that MySQL should be installed on your machine and it's service running for this command to work:

mysql -u root -p        

Step 2: Creating the Database

With MySQL at our fingertips, let's sculpt our database. Using SQL commands or a preferred MySQL management tool, we'll create a database :

CREATE DATABASE yourDatabaseName;        

Step 3: Selecting the Database

Having created our database, we pivot to it, ready to perform operations within its confines:

USE yourDatabaseName;        

Step 4: Forming the Users Table

Now, the pièce de résistance—creating the users table. This table will house our user data, with three key columns: id, name, and email. The id column, standing tall as the primary key, auto-increments with each new user entry, ensuring a unique identifier for each record:

CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), email VARCHAR(100) );        

Mapping the Application's Routes

Creating a router with middleware :

func main() {
	// Create a new router
	router := chi.NewRouter()
	router.Use(middleware.Logger)
......
}        

Defining CRUD Routes

Our application's core functionality revolves around the four fundamental CRUD operations: Create, Read, Update, and Delete. As such, we diligently define four routes, each tailored to a specific operation:

• POST requests are captured by the handleCreate function, responsible for creating a user.
• GET requests are skillfully handled by the handleGet function, retrieving user data.
• PUT requests find their way to the handleUpdate function, facilitating user updates.
• DELETE requests find closure in the handleDelete function, gracefully deleting user records.

Launching the HTTP Server

With routes set and handlers in place, we're ready to fire up the HTTP server. The line http.ListenAndServe(":port", r) sets the stage for our server to listen on port you define, ready to direct incoming requests to their designated handlers.

Creating User Data

To enable the creation of new users, we've crafted an HTTP endpoint and a dedicated function to handle incoming POST requests containing user information. In our main function, which boasts a finely-tuned router courtesy of the gorilla/mux package, we've laid the foundation for this user creation process. Now, let's delve into the handleCreate function:

// create a new user struct
type User struct {
	ID    int
	Name  string
	Email string
}

func handleCreate(w http.ResponseWriter, r *http.Request) {
    // Establish a database connection
    db, err := sql.Open(dbHost, dbUser+":"+dbPwd+"@/"+dbName)
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // Parse JSON data from the request body
    var user User
    json.NewDecoder(r.Body).Decode(&user)

    // Invoke the CreateUser function to execute the database operation
    CreateUser(db, user.Name, user.Email)
    if err != nil {
        http.Error(w, "Failed to create user", http.StatusInternalServerError)
        return
    }

    // Respond with a success status and message
    w.WriteHeader(http.StatusCreated)
    fmt.Fprintln(w, "User created successfully")
}        

Understanding handleCreate

The handleCreate function encapsulates the process of creating a new user. Here's a breakdown:

• We initiate a database connection via the sql.Open function, combining the database driver, username, password, and database name.
• JSON data sent in the HTTP request body is diligently parsed and decoded into a User struct.
• The CreateUser function is invoked to execute the crucial database operation. This function, which we'll explore shortly, performs the actual user insertion.
• Error handling is a crucial aspect. If an error surfaces during the operation, an HTTP error response is generated, signaling a server error (status code 500). This ensures graceful handling of any issues that may arise during user creation.
• Upon successful user creation, a positive HTTP response is crafted. A status code of 201 (Created) is sent, accompanied by the reassuring message, "User created successfully."
• Creating a User in the Database: Now, let's delve into the CreateUser function that orchestrates the database insertion:

func CreateUser(db *sql.DB, name, email string) error {
 query := "INSERT INTO users (name, email) VALUES (?, ?)" 
_, err := db.Exec(query, name, email) 
if err != nil { return err } 
return nil 
}        

Understanding CreateUser

The CreateUser function plays a pivotal role in inserting a new user into the database. Here's what it involves:

• It accepts three parameters:db: A reference to the database connection (of type *sql.DB), enabling interaction with the database.name: A string representing the name of the user to be inserted.email: A string representing the user's email address.
• The query variable holds the SQL command for inserting a user into the database.
• The db.Exec function executes the SQL query with the provided parameters. If any errors arise during this execution, they are gracefully handled and returned.
• Upon successful execution, the function returns nil, signifying that the user insertion has been accomplished without hitches.

Retrieving User Data

To retrieve user information, our trusty handleGet function steps into action when provided with a user's ID. Let's delve into the details of this function and its underlying mechanics:

func handleGet(w http.ResponseWriter, r *http.Request) {
	db, err := sql.Open(dbHost, dbUser+":"+dbPwd+"@/"+dbName)
	if err != nil {
	http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer db.Close()
        
        //retrieve the id parameter
	idParam := chi.URLParam(r, "id")

	userID, err := strconv.Atoi(idParam)
	if err != nil {
	http.Error(w, "Invalid user ID", http.StatusBadRequest)
	return
	}

	user, err := GetUser(db, userID)
	if err != nil {
	log.Print("user not found with id", userID)
	http.Error(w, "User not found ", http.StatusNotFound)
	return
	}
	// Convert the user object to JSON and send it 
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(user)
}        

Understanding handleGet

This function is responsible for handling requests to retrieve user data based on their ID. Here's a breakdown of its key components:

• We initiate a database connection using sql.Open, incorporating the database driver, username, password, and database name. As always, error handling is in place to gracefully manage any issues.
• The function extracts the 'id' parameter from the URL
• We convert the 'id' parameter from a string to an integer, ensuring compatibility with the database query.
• The GetUser function is summoned to fetch user data from the database, using the database connection and user ID as parameters. This is where the magic happens.
• Error handling is paramount. If the user is not found in the database, an HTTP error response is generated, indicating a "User not found" situation with a status code of 404 (Not Found).
• To complete the journey, we convert the user object into JSON format and send it as the response. The HTTP response header is appropriately set to indicate the content type as JSON.

Retrieving User Data from the Database

The crucial GetUser function conducts the actual database query to retrieve user data:

func GetUser(db *sql.DB, id int) (*User, error) {
    query := "SELECT * FROM users WHERE id = ?"
    row := db.QueryRow(query, id)

    user := &User{}
    err := row.Scan(&user.ID, &user.Name, &user.Email)
    if err != nil {
        return nil, err
    }
    return user, nil
}        

Understanding GetUser

This function forms the bridge between our application and the database. Here's a closer look:

• We define the SQL query used to select a user from the users table based on their ID.
• The db.QueryRow function executes the query, returning a single row result.
• We create an empty User struct using the & operator to obtain a pointer to the struct.
• The row.Scan function scans the result into the User struct, populating its fields with the retrieved data.
• Error handling ensures that any issues during the database interaction are properly handled. If an error occurs, it's returned, signifying that the user retrieval process encountered a hitch.

Updating User Data

To modify user information, our handleUpdate function takes center stage. It retrieves a user based on their ID and updates their name and email fields with the new values provided. Let's delve into the intricacies of this function:

func handleUpdate(w http.ResponseWriter, r *http.Request) {
    // Establish a database connection
    db, err := sql.Open(dbHost, dbUser+":"+dbPwd+"@/"+dbName)
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    // Get the 'id' parameter from the URL
    idParam := chi.URLParam(r, "id")

    // Convert 'id' to an integer
    userID, err := strconv.Atoi(idParam)
    if err != nil {
	http.Error(w, "Invalid user ID", http.StatusBadRequest)
	return
	}

    var user User
    err = json.NewDecoder(r.Body).Decode(&user)

    // Invoke the UpdateUser function to update the user data in the database
    UpdateUser(db, userID, user.Name, user.Email)
    if err != nil {
        http.Error(w, "User not found", http.StatusNotFound)
        return
    }

    fmt.Fprintln(w, "User updated successfully")
}        

Understanding handleUpdate

This function plays a pivotal role in updating user data. Here's a breakdown:

• We initiate a database connection using sql.Open, ensuring a seamless bridge between our application and the database. Error handling is in place to gracefully manage any issues.
• The function extracts the 'id' parameter from the URL, ensuring we know which user we're updating.
• We convert the 'id' parameter from a string to an integer, making it compatible with our database query.
• The json.NewDecoder decodes the JSON data provided in the request body, allowing us to extract the new user information.
• The UpdateUser function is summoned to execute the crucial database operation. This function, which we'll explore shortly, performs the actual user update.
• Error handling is, as always, a priority. If the user is not found in the database, an HTTP error response is generated, indicating a "User not found" situation with a status code of 404 (Not Found).
• Upon successful user update, a confirmation message is sent as the HTTP response, reassuring the client with "User updated successfully."

Updating User Data in the Database

Now, let's delve into the UpdateUser function that orchestrates the database update:

func UpdateUser(db *sql.DB, id int, name, email string) error {
    query := "UPDATE users SET name = ?, email = ? WHERE id = ?"
    _, err := db.Exec(query, name, email, id)
    if err != nil {
        return err
    }
    return nil
}        

Understanding UpdateUser

This function serves as the conduit for modifying user data in the database. Here's a closer look:

• We define the SQL query for updating a user's name and email in the users table, targeting the user with the specified ID.
• The db.Exec function executes the SQL query with the provided parameters. If any errors occur during this process, they are captured and gracefully returned.
• Upon successful execution, the function returns nil, indicating that the user update has been accomplished without a hitch.

Deleting User Data

To remove a user from our application's records, we employ the handleDelete function. This function is responsible for deleting a user from the users table in the database. Let's explore the details of this function:

func handleDelete(w http.ResponseWriter, r *http.Request) {
    // Establish a database connection
    db, err := sql.Open(dbHost, dbUser+":"+dbPwd+"@/"+dbName)
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

  	// Get the 'id' parameter from the URL
	idParam := chi.URLParam(r, "id")

	// Convert 'id' to an integer
	userID, err := strconv.Atoi(idParam)
	if err != nil {
	http.Error(w, "Invalid user ID", http.StatusBadRequest)
	return
	}
	user := DeleteUser(db, userID)
	if err != nil {
	http.Error(w, "User not found", http.StatusNotFound)
	return
	}

	fmt.Fprintln(w, "User deleted successfully")
	// Convert the user object to JSON and send it 
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(user)
}        

Understanding handleDelete

This function takes the helm when it comes to user deletion. Here's a breakdown of its inner workings:

• We establish a database connection using sql.Open, ensuring that we have access to the database to carry out the user deletion operation. Error handling is in place to gracefully manage any issues.
• The function extracts the 'id' parameter from the URL, allowing us to identify the user we want to delete.
• The DeleteUser function is invoked to perform the actual database operation for user deletion. We'll explore this function in detail shortly.
• Error handling remains a top priority. If the user is not found in the database, an HTTP error response is generated, indicating a "User not found" situation with a status code of 404 (Not Found).
• A success message, "User deleted successfully," is sent as the HTTP response, confirming the successful deletion.
• Additionally, we set the response header to indicate that the response content is in JSON format, although no JSON data is sent in this case.

Deleting User Data in the Database

Now, let's delve into the DeleteUser function, which handles the database operation for user deletion:

func DeleteUser(db *sql.DB, id int) error {
	query := "DELETE FROM users WHERE id = ?"
	_, err := db.Exec(query, id)
	if err != nil {
		return err
	}
	return nil
}        

Understanding DeleteUser

This function acts as the executor for removing a user from the database. Here's a closer look:

• We define the SQL query for deleting a user from the users table, targeting the user with the specified ID.
• The db.Exec function is employed to execute the SQL query, passing in the 'id' as a parameter. Any errors arising during this process are captured and gracefully returned.
• Upon successful execution, the function returns nil, indicating that the user has been deleted from the database without any issues.

Running the app and interacting with the database

To create a new user, you can use curl to make a POST request to the /user endpoint with the user's data. Here's the command:

For update and delete , just swap the POST Verb with the correct one : PUT - DELETE

curl -X POST -H "Content-Type: application/json" -d '{"name":"gopher","email":"[email protected]"}' https://localhost:portNumber/user        

• -X POST: This flag indicates that we're making a POST request.
• -H "Content-Type: application/json": This sets the request's content type to JSON.
• -d '{"name":"gopher","email":"[email protected]"}': This is the user data we're sending in JSON format.
• https://localhost:portNumber/user: This is the URL of your application's /user endpoint.

curl -X PUT -H "Content-Type: application/json" -d '{"name":"new name","email":"[email protected]"}' https://localhost:portNumber/user/{id}        

curl -X DELETE https://localhost:portNumber/user/{id}        

Retrieving a User by ID

To retrieve a user by their ID, you can make a GET request to the /user/{id} endpoint, where {id} is the ID of the user you want to fetch. Replace {id} with the actual user ID and visit : https://localhost:portNumber/user/{id}

Conclusion

In this beginner-friendly guide to building a CRUD (Create, Read, Update, Delete) application with GoLang (Golang) and MySQL, we've taken you through every essential step. From setting up your development environment to implementing CRUD operations, testing your application, and gaining a foundational understanding of how it all fits together, you're now equipped to start your journey in crafting simple but effective web applications. The ability to Create, Read, Update, and Delete data is a crucial skill in the world of software development, and with this knowledge, you're ready to dive deeper into the realm of web development. So, as you take your first steps, remember that every great app begins with these fundamental building blocks. Happy coding!