Go language + MySQL REST API: CRUD example.

Maheshwar Ligade's photo
Maheshwar Ligade
·

5 min read

Table of contents

In this article, we will learn how to create a RESTful API using Go language and MySQL database. We will implement CRUD (Create, Read, Update, Delete) operations for a simple "users" table in the MySQL database.

Prerequisites:

  • Go language installed on your machine

  • MySQL server and client installed on your machine

  • A text editor or IDE of your choice

Step 1:

Create a MySQL database and table

First, we need to create a MySQL database and table to store our user data. You can use the following SQL commands to create the database and table:

CREATE DATABASE users_db;
USE users_db;

CREATE TABLE users (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  email VARCHAR(50) NOT NULL,
  age INT(3) NOT NULL
);

Step 2:

Set up a Go project

Next, we need to set up a Go project. Create a new directory for your project and initialize a new Go module inside it:

$ mkdir go-mysql-api
$ cd go-mysql-api
$ go mod init github.com/your-username/go-mysql-api

Step 3:

Install required packages

We will be using the following packages in our project:

Install these packages using the following command:

$ go get github.com/gorilla/mux github.com/go-sql-driver/mysql github.com/joho/godotenv

Step 4:

Set up environment variables

Create a new file called ".env" in the root of your project directory and add the following environment variables:

DB_USERNAME=root
DB_PASSWORD=password
DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=users_db

Replace the values with your own MySQL database credentials.

Step 5:

Connect to MySQL database

In your Go code, import the required packages:

import (
  "database/sql"
  "fmt"
  "log"
  "net/http"
  "os"

  "github.com/gorilla/mux"
  _ "github.com/go-sql-driver/mysql"
  "github.com/joho/godotenv"
   "encoding/json"
)

Load the environment variables from the ".env" file:

err := godotenv.Load()
if err != nil {
  log.Fatal("Error loading .env file")
}

Create a new MySQL database connection:

dbUsername := os.Getenv("DB_USERNAME")
dbPassword := os.Getenv("DB_PASSWORD")
dbHost := os.Getenv("DB_HOST")
dbPort := os.Getenv("DB_PORT")
dbName := os.Getenv("DB_NAME")

dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", dbUsername, dbPassword, dbHost, dbPort, dbName)

db, err := sql.Open("mysql", dataSourceName)
if err != nil {
  log.Fatal("Error connecting to database")
}
defer db.Close()

Step 6:

Define CRUD operations

We will define the following CRUD operations:

  • GET /users - retrieve all users

  • GET /users/{id} - retrieve a specific user by ID

  • POST /users - create a new user

  • PUT /users/{id} - update an existing user by ID

  • DELETE /users/{id} - delete a user by ID

Here is a code snippet for each of the CRUD operations you mentioned using the Gorilla Mux router and MySQL database.

First, we need to create a User struct to hold the data for each user:

type User struct {
    ID        int    `json:"id"`
    FirstName string `json:"first_name"`
    LastName  string `json:"last_name"`
    Email     string `json:"email"`
}

If any of database column values are null, then the struct should have sql.NullString instead of string type.

Next, we create a MySQL database connection:

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

Now we can start building our API endpoints using Gorilla Mux. Here's the code for each endpoint:

GET /users - retrieve all users

func getUsers(w http.ResponseWriter, r *http.Request) {
    var users []User
    rows, err := db.Query("SELECT id, first_name, last_name, email FROM users")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var user User
        err := rows.Scan(&user.ID, &user.FirstName, &user.LastName, &user.Email)
        if err != nil {
            log.Fatal(err)
        }
        users = append(users, user)
    }

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(users)
}

GET /users/{id} - retrieve a specific 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, first_name, last_name, email FROM users WHERE id = ?", id).Scan(&user.ID, &user.FirstName, &user.LastName, &user.Email)
    if err != nil {
        log.Fatal(err)
    }

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(user)
}

POST /users - create a new user

func createUser(w http.ResponseWriter, r *http.Request) {
    var user User
    json.NewDecoder(r.Body).Decode(&user)

    result, err := db.Exec("INSERT INTO users (first_name, last_name, email) VALUES (?, ?, ?)", user.FirstName, user.LastName, user.Email)
    if err != nil {
        log.Fatal(err)
    }

    lastInsertID, err := result.LastInsertId()
    if err != nil {
        log.Fatal(err)
    }

    user.ID = int(lastInsertID)

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(user)
}

PUT /users/{id} - update an existing user by ID

func updateUser(w http.ResponseWriter, r *http.Request) {
    params := mux.Vars(r)
    id := params["id"]

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

    _, err := db.Exec("UPDATE users SET first_name = ?, last_name = ?, email = ? WHERE id = ?", user.FirstName, user.LastName, user.Email, id)
    if err != nil {
        log.Fatal(err)
    }

    user.ID, _ = strconv.Atoi(id)

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(user)
}

DELETE /users/{id} - delete a user by ID

the deleteUser function for the /users/{id} endpoint could be implemented in Go:

func deleteUser(w http.ResponseWriter, r *http.Request) {
    // Get the user ID from the request URL path
    vars := mux.Vars(r)
    userID := vars["id"]

    // Check if the user with the given ID exists
    _, err := getUserByID(userID)
    if err != nil {
        // If the user doesn't exist, return a 404 Not Found error
        w.WriteHeader(http.StatusNotFound)
        fmt.Fprintf(w, "User with ID %s not found", userID)
        return
    }

    // Delete the user with the given ID
    err = deleteUserByID(userID)
    if err != nil {
        // If there was an error deleting the user, return a 500 Internal Server Error
        w.WriteHeader(http.StatusInternalServerError)
        fmt.Fprint(w, "Error deleting user")
        return
    }

    // If the user was deleted successfully, return a 204 No Content response
    w.WriteHeader(http.StatusNoContent)
}

In this example, we first extract the user ID from the request URL path using the mux.Vars function from the Gorilla mux package. We then call the getUserByID function (not shown here) to check if a user with the given ID exists. If the user doesn't exist, we return a 404 Not Found error.

Conclusion:

We covered a wide range of topics related to Go language development, including logging, command line arguments, environment variables, base64 encoding, packages, maps, REST APIs, JWT tokens, MySQL database integration, and HTTP request handling.

Go language has become a popular choice for building robust and scalable backend systems and microservices. With its simple syntax, built-in concurrency support, and powerful standard library, Go offers developers a fast and efficient way to build high-performance applications.

I hope this helps, you!!

More such articles:

https://medium.com/techwasti

https://www.youtube.com/channel/UCiTaHm1AYqMS4F4L9zyO7qA

https://www.techwasti.com/

\==========================**=========================

If this article adds any value to you then please clap and comment.

Let’s connect onStackoverflow,LinkedIn, &Twitter.

Did you find this article valuable?

Support techwasti by becoming a sponsor. Any amount is appreciated!

Go LanguageREST APIMicroservicesProgramming Tipsprogramming languages