Creating a CRUD application with Golang and MySQL

Building Restful applications is one of the popular ways to build web services as they follow simple and scalable architecture. CRUD, which stands for Create, Read, Update, and Delete is a common task in software development. The ability to perform CRUD operations is fundamental in any application development. In this tutorial, we will explore how to build a simple CRUD application using GoLang (Golang) and MySQL.

GoLang is a popular programming language known for its efficiency and simplicity, while MySQL is a widely used relational database management system. We will start by setting up the development environment and creating the database schema. Once we have the schema in place, we will make the API endpoints for performing CRUD operations. Finally, we will test the application and ensure that everything is working as expected.

So without further ado, let’s get to it.

Setting up Golang and MySQL

We will start the project by setting up our development environment, creating our database, and connecting the database to the Golang application.

Initialize Golang project

The first step is to set up the development environment and start a Golang project to build the CRUD application. Follow the steps below to set up a new Golang project.

Create a repository you would like to host your project and copy the link to the repository. The link should be in this format: github.com/USERNAME/REPO_NAME

After the repository has been properly set up, create a directory for the project and cd into the project folder by running the following command:

  mkdir GolangCRUD && cd GolangCrud

After that has been completed, you can open your computer terminal to initialize a Golang project with the repo link that was created earlier, using the command below:

  go mod init github.com/USERNAME/REPO_NAME

This should generate a go.mod file with the URL you just wrote and the Golang version you are using.

Finally, create a file where all your Golang code will be written

  touch GolangCrud.go

At the top of the new Golang file, indicate the package name of your library.

  package GolangCrud

With that, you can edit the Go file to create your package and start building out the CRUD functions

Creating the database schema

We are going to be building an application that stores a list of users in a MySQL database and then the application retrieves it when it is needed or requested. The database is going to take in both the user and their email.

Before we add the MySQL driver package to our Golang application, let’s create the database and database schema for the application.

To create the database and schema, you can follow the instructions below:

Log into your database:

  sudo mysql -u root -p

PS: MySQL should be installed on your computer before this command can be used.

Create the database using SQL commands or a MySQL management tool:

  CREATE DATABASE gocrud_app;

Select the newly created database:

  USE gocrud_app;

Create the users table with the desired columns:

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

These commands create a database named gocrud_app and a table named users with three columns: id, name, and email. The id is used as the primary key for this database table.

Once you have the database and schema set up, you can proceed with installing the MySQL driver package.

Go into your terminal, cd into your project directory, and install the SQL driver package using the following command.

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

This command downloads and installs the github.com/go-sql-driver/mysql package, which provides the MySQL driver for Go applications.

Defining the routes for the Golang application

The code below shows the main function, which is the entry point for the application. It begins by importing necessary packages like database/sql, encoding/json, fmt, log, net/http, strconv, and the github.com/go-sql-driver/mysql and github.com/gorilla/mux packages.

The main function creates a new router using the gorilla/mux package, which will handle incoming HTTP requests. It defines four routes corresponding to the CRUD operations: POST for creating a user, GET for retrieving a user, PUT for updating a user, and DELETE for deleting a user. Each route is associated with a specific route handler function (createUserHandler, getUserHandler, updateUserHandler, and deleteUserHandler).

After defining the routes, the code starts an HTTP server using http.ListenAndServe(":8090", r). This line of code instructs the server to listen on port 8090 and direct incoming requests to the appropriate route handler.

  package main

  import (
    "database/sql"
    "encoding/json"
    "fmt"
    "log"
    "net/http"
    "strconv"
    _ "github.com/go-sql-driver/mysql"
    "github.com/gorilla/mux"
)

  const (
    dbDriver = "mysql"
    dbUser   = "dbUser"
    dbPass   = "dbPass"
    dbName   = "gocrud_app"

  func main() {
    // Create a new router
    r := mux.NewRouter()

    // Define your HTTP routes using the router
    r.HandleFunc("/user", createUserHandler).Methods("POST")
    r.HandleFunc("/user/{id}", getUserHandler).Methods("GET")
    r.HandleFunc("/user/{id}", updateUserHandler).Methods("PUT")
    r.HandleFunc("/user/{id}", deleteUserHandler).Methods("DELETE")

    // Start the HTTP server on port 8090
    log.Println("Server listening on :8090")
    log.Fatal(http.ListenAndServe(":8090", r))
}

Implementing the CRUD operations

Now that we have created a database schema and initiated a connection to the MySQL database, let's implement the CRUD operations.

Creating data

To create a new user, we'll need an HTTP endpoint and the function to handle the incoming POST requests containing user data. In our main function, we've already set up a router using the gorilla/mux package. Now, let's add the createUserHandler function:

  func createUserHandler(w http.ResponseWriter, r *http.Request) {
    db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+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)

    CreateUser(db, user.Name, user.Email)
    if err != nil {
     http.Error(w, "Failed to create user", http.StatusInternalServerError)
     return
    }

    w.WriteHeader(http.StatusCreated)
    fmt.Fprintln(w, "User created successfully")
  }

The db.Exec() function is used to execute the SQL query with the given parameters. After the createUserHandler has been created, we can then go ahead to create the CreateUser function which performs the actual CRUD operation. The CreateUser function is written in the code block below:

  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
}

This code defines a function named CreateUser that is responsible for inserting a new user into the database. It takes in three parameters:

id: A reference to the database connection (of type *sql.DB), allowing the function to interact with the database.

name: This string represents the name of the user you want to insert.

email: This string represents the email address of the user.

The query defines the SQL command used to insert the user into the database. While the error handling function checks if the query results in an error. If an error occurred during the db.Exec() call, the code returns the error using the return err statement. If there was no error, the function returns nil, indicating that the insertion was successful.

N.B - This function should be written outside the main function.

Reading data

To retrieve a user, the getUserHandler function given below handles the retrival of the user data when provided with the ID of the user. Let's add the getUserHandler function to our existing code:

  type User struct {
    ID    int
    Name  string
    Email string
  }

  func getUserHandler(w http.ResponseWriter, r *http.Request) {
    db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
    if err != nil {
      panic(err.Error())
    }
    defer db.Close()

    // Get the 'id' parameter from the URL
    vars := mux.Vars(r)
    idStr := vars["id"]

    // Convert 'id' to an integer
    userID, err := strconv.Atoi(idStr)

    // Call the GetUser function to fetch the user data from the database
    user, err := GetUser(db, userID)
    if err != nil {
      http.Error(w, "User not found", http.StatusNotFound)
      return
    }

    // Convert the user object to JSON and send it in the response
    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(user)
 }

The GetUser function is used to extract the user details from the database using the user id. We create an empty User struct using the & operator to get a pointer to the struct.

It executes an SQL query, scans the result into a User struct, and returns the struct along with any error encountered during the process.

The GetUser function to run the database query which gets the user is given below:

  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
}

This function basically gets the id of the user and uses the id to run an SQL query which gets the user from the database.

N.B - This function should be written outside the main function.

Updating data

To update a user, the updateUserHandler is used to get the user and update it, with the new value that is being provided in the function. Add the updateUserHandler function to your existing code:

  func updateUserHandler(w http.ResponseWriter, r *http.Request) {
    db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
    if err != nil {
      panic(err.Error())
    }
    defer db.Close()

    // Get the 'id' parameter from the URL
    vars := mux.Vars(r)
    idStr := vars["id"]

    // Convert 'id' to an integer
    userID, err := strconv.Atoi(idStr)

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

    // Call the GetUser function to fetch the user data from 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")
}

This function updates the name and email fields of the user with the specified ID, which is provided in the function parameters.

Put in the UpdateUser function in your code as it is written below:

  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
}

This function takes in the email and the name of the user with the idthat was provided in the function.

N.B - This function should be written outside the main function.

Deleting data

The deleteUserHandler function deletes the user from the users table in the database. The below code contains the deleteUserHandler function. Add it to the existing code.

  func deleteUserHandler(w http.ResponseWriter, r *http.Request) {
    db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
    if err != nil {
      panic(err.Error())
    }

    defer db.Close()

    // Get the 'id' parameter from the URL
    vars := mux.Vars(r)
    idStr := vars["id"]

    // Convert 'id' to an integer
    userID, err := strconv.Atoi(idStr)
    if err != nil {
      http.Error(w, "Invalid 'id' parameter", 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 in the response
    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(user)
}

This function deletes the user with the specified ID from the table. The deleteUserHandler contains, the DeleteUser function which runs the SQL query to delete the user with the provided ID. The delete function(DeleteUser) is as it is written below:

  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
}

This function takes in only the idof the user and runs the SQL query to delete the user from the database.

N.B - This function should also be written outside the main function.

Testing the CRUD applications

Now that we have implemented all the CRUD operations, it's time to test our application. We can use the curl command-line tool to make HTTP requests to our API endpoints.

Creating a new user Let's use curl to create a new user by making a POST request to the /user endpoint. Open your terminal or command prompt and enter the following command:

  curl -X POST -H "Content-Type: application/json" -d '{"Name":"John Doe","Email":"john@example.com"}' http://localhost:8090/user

You should receive a response like:

  User created successfully

Retrieving a user by ID To retrieve the user we just created, we'll make a GET request to the /user/{id} endpoint, where {id} is the ID of the user we want to fetch. Replace {id} in the following command with the actual ID of the user:

  curl http://localhost:8090/user/{id}

You should receive a JSON response containing the user data, like:

{ "ID": 1, "Name": "John Doe", "Email": "john@example.com" }

Updating a user To update the user's information, we'll make a PUT request to the /user/{id} endpoint, providing the updated user data. Replace {id} in the following command with the actual ID of the user you want to update:

  curl -X PUT -H "Content-Type: application/json" -d '{"Name":"Jane Doe","Email":"jane@example.com"}' http://localhost:8090/user/{id}

You should receive a response like:

  User updated successfully

Deleting a user Finally, let's delete the user we just updated. We'll make a DELETE request to the /user/{id} endpoint, where {id} is the ID of the user we want to delete. Replace {id} in the following command with the actual ID of the user:

  curl -X DELETE http://localhost:8090/user/{id}

You should receive a response like:

  User deleted successfully

The curl command is used to run all the HTTP requests to test the endpoints in our application. This tutorial gives the the basics of building a CRUD application with Golang using MYSQL database as the Sequel database.

Conclusion

In this tutorial, we were able to learn how to create a simple CRUD application using GoLang and MySQL. We set up our development environment, implemented the CRUD operations, and tested the application using cURL. The ability to perform CRUD operations is crucial in building powerful and interactive applications. You can use this foundation to create more complex applications with additional features and functionalities.