# Go language + MySQL REST API: CRUD example.

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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1680348682874/b0aed3c8-be39-417c-a96e-3933411dfa8c.png align="center")

### 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:

```sql
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:

```bash
$ 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:

* "[github.com/gorilla/mux](http://github.com/gorilla/mux)" for routing
    
* "[github.com/go-sql-driver/mysql](http://github.com/go-sql-driver/mysql)" for MySQL database driver
    
* "[github.com/joho/godotenv](http://github.com/joho/godotenv)" for loading environment variables
    

Install these packages using the following command:

```go
$ 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:

```go
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:

```go
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:

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

Create a new MySQL database connection:

```go
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:

```sql
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:

```go
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**

```go
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**

```go
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**

```go
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**

```go
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:

```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](Link)

[https://www.youtube.com/channel/UCiTaHm1AYqMS4F4L9zyO7qA](Link)

[https://www.techwasti.com/](Link)

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

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

**Let’s connect on**[**Stackoverflow**](http://stackoverflow.com/users/3187349/maheshwar-ligade)**,**[**LinkedIn**](https://in.linkedin.com/in/maheshwar-ligade-14447841)**, &**[**Twitter**](https://twitter.com/MaheshwarLigade)**.**