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) {
vars := mux.Vars(r)
userID := vars["id"]
_, err := getUserByID(userID)
if err != nil {
w.WriteHeader(http.StatusNotFound)
fmt.Fprintf(w, "User with ID %s not found", userID)
return
}
err = deleteUserByID(userID)
if err != nil {
w.WriteHeader(http.StatusInternalServerError)
fmt.Fprint(w, "Error deleting user")
return
}
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.
