Build a products listing application with Golang and MySQL
Learn how to build a products listing application with Golang and a MySQL database.
In this tutorial, we will explore how to use a PlanetScale MySQL database with Golang to build a product listing application. We will start by setting up a PlanetScale database. Then, we will dive into the code and learn how to perform CRUD (Create, Read, Update, Delete) operations using Golang and MySQL.
If you're interested in building web applications with Golang and MySQL, this tutorial is a great place to start! All you need is the latest installation of Go and a PlanetScale account.
Set up the PlanetScale database
PlanetScale is a MySQL-compatible database that offers database scalability out of the box. Coupled with its database branching feature, non-blocking schema changes, deploy requests, and query-level analytics, PlanetScale offers scalability in a gift-wrapped box with top-notch developer experience.
With PlanetScale, you can create a new database either through the dashboard or the pscale CLI. We'll do everything in the dashboard for this article.
In the PlanetScale dashboard, click "New database" > "Create new database." A modal form will be displayed asking for the database name and region.
Give your database a name, and select the region closest to you or your application. For this tutorial, we will name the database products_db and use the default region. Click “Create database” to complete the creation process. You will be redirected to the database dashboard, as shown below.
The dashboard gives you an overview of your database and access to all the features highlighted earlier. For now, you need the database credentials. To get them, click the “Connect” button at the top right corner of the dashboard, and select "Go" from the dropdown. This shows you the database authentication credentials, as well as a sample code for connecting to your database for different programming languages.
Copy these details and keep them handy, as we will need them when we start building our application. For security, this page is only displayed once, and if you lose your credentials, you will need to regenerate a new set.
Now, we have everything we need to build our application and connect it to our database.
Connect to PlanetScale in Go
Create a new folder on your machine named product-listing-api. In that folder, set up your Go module using the go mod init command.
mkdir product-listing-api
cd product-listing-api
go mod init product-listing-api
This project uses the Gin framework. We also need to parse environment variables from a .env file. For this, we’ll use GoDotEnv. We also need fixed-point decimal numbers to represent product prices. For this, we’ll use the Decimal library. Finally, we’ll use GORM to wrap our database interactions. Add these dependencies to the project using the following command.
go get \
github.com/gin-gonic/gin \
github.com/joho/godotenv \
github.com/shopspring/decimal \
gorm.io/driver/mysql \
gorm.io/gorm
Next, open the folder in your preferred IDE or text editor.
Create a new file named .env.local. This file will hold the DSN for your database, which is the connection string value you copied from the PlanetScale dashboard in the previous section. Add the following to your file:
DSN=<YOUR_PLANETSCALE_DSN>
Using a .env.local file instead of .env is a best practice that protects your confidential keys from being exposed. This file is added to .gitignore and is not committed to the repository.
Next, create a new folder named database. In it, add the following code.
package database
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"os"
)
var Database *gorm.DB
func Connect() error {
var err error
dsn := fmt.Sprintf("%s&parseTime=True", os.Getenv("DSN"))
Database, err = gorm.Open(
mysql.Open(dsn),
)
if err == nil {
fmt.Println("Successfully connected to PlanetScale!")
}
return err
}
The Connect() function retrieves the environment variables required to set up a database connection, and then opens the connection using the GORM MySQL driver. By returning an error type, the function delegates error handling to the function caller.
Another key thing to note is that the PlanetScale DSN is appended with a parseTime parameter, which is set to True. This allows GORM to handle time.Time correctly. You can read more about that in their docs.
For now, our API has just one table for products. This will be generated via a GORM migration, but we need a struct to model a product. Create a new folder named model,and in it, a file named product.go. Add the following code to the file:
package model
import (
"github.com/shopspring/decimal"
"gorm.io/gorm"
)
type Product struct {
gorm.Model
Name string `gorm:"size:255;not null;" json:"name" binding:"required"`
SerialNumber string `gorm:"size:255;not null;unique" json:"serialNumber" binding:"required"`
Quantity uint `json:"quantity" binding:"required"`
Price decimal.Decimal `gorm:"type:numeric" json:"price" binding:"required"`
Description string `gorm:"type:text" json:"description" binding:"required"`
}
Here, we specify the fields of the Product struct which correspond to the columns in the corresponding table. Additionally, we specify the data types for GORM and provide JSON bindings for each field.
Next, in the top-level directory of the project, create a new file named main.go. This is the entry point of your application. Add the following code to main.go:
package main
import (
"errors"
"fmt"
"github.com/joho/godotenv"
"log"
"product-listing-api/database"
"product-listing-api/model"
)
func main() {
loadEnv()
loadDatabase()
}
func loadEnv() {
if err := godotenv.Load(".env.local"); err != nil {
handleError(errors.New("error loading .env file"))
}
}
func loadDatabase() {
if err := database.Connect(); err != nil {
handleError(err)
}
if err := database.Database.AutoMigrate(&model.Product{}); err != nil {
handleError(err)
}
fmt.Println("Migrations executed successfully")
}
func handleError(err error) {
log.Fatal(err)
}
When the application is run, the main() function in main.go is called. At the moment, this function does three things: It loads the environment variables from .env.local, sets up the database connection, and runs the necessary migrations.
With this in place, we can run the application using the following command:
go run main.go
The result of the command will be similar to the output below:
Successfully connected to PlanetScale!
2023/02/23 21:47:32 /Users/ybjozee/Documents/Projects/Go/product-listing-api/main.go:28 SLOW SQL >= 200ms
[306.520ms] [rows:-] SELECT DATABASE()
2023/02/23 21:47:34 /Users/ybjozee/Documents/Projects/Go/product-listing-api/main.go:28 SLOW SQL >= 200ms
[1227.643ms] [rows:1] SELECT SCHEMA_NAME from Information_schema.SCHEMATA where SCHEMA_NAME LIKE 'products_db%' ORDER BY SCHEMA_NAME='products_db' DESC,SCHEMA_NAME limit 1
2023/02/23 21:47:34 /Users/ybjozee/Documents/Projects/Go/product-listing-api/main.go:28 SLOW SQL >= 200ms
[614.647ms] [rows:-] SELECT count(*) FROM information_schema.tables WHERE table_schema = 'products_db' AND table_name = 'products' AND table_type = 'BASE TABLE'
2023/02/23 21:47:34 /Users/ybjozee/Documents/Projects/Go/product-listing-api/main.go:28 SLOW SQL >= 200ms
[334.473ms] [rows:0] CREATE TABLE `products` (`id` bigint unsigned AUTO_INCREMENT,`created_at` datetime(3) NULL,`updated_at` datetime(3) NULL,`deleted_at` datetime(3) NULL,`name` varchar(255) NOT NULL,`serial_number` varchar(255) NOT NULL,`quantity` bigint unsigned,`price` numeric,`description` text,PRIMARY KEY (`id`),INDEX `idx_products_deleted_at` (`deleted_at`))
Migrations executed successfully
You can see that a new table called products has been created. To confirm this, head back to your PlanetScale dashboard. In the “Branches” section, select the main branch, and then click the “Schema” tab. The displayed schema should match the image below.
Building the Golang API
Next, let's build the controller functions for the API. The API will have five endpoints to perform the following functions:
- Create a new product
- Get all products
- Get a product
- Update a product
- Delete a product
Create a new folder named controller. In this folder, create a new file named controller.go and add the following code to it:
package controller
import (
"github.com/gin-gonic/gin"
"net/http"
"product-listing-api/database"
"product-listing-api/model"
"strconv"
)
func CreateProduct(context *gin.Context) {
var product model.Product
if err := context.ShouldBindJSON(&product); err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
if err := database.Database.Create(&product).Error; err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
context.JSON(http.StatusCreated, gin.H{"data": product})
}
func GetAllProducts(context *gin.Context) {
var products []model.Product
if err := database.Database.Find(&products).Error; err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
context.JSON(http.StatusOK, gin.H{"data": products})
}
func GetProduct(context *gin.Context) {
var product model.Product
id, err := strconv.ParseInt(context.Param("id"), 10, 64)
if err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
if err := database.Database.Where("ID=?", id).Find(&product).Error; err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
context.JSON(http.StatusOK, gin.H{"data": product})
}
func UpdateProduct(context *gin.Context) {
id, err := strconv.ParseInt(context.Param("id"), 10, 64)
if err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
var input map[string]interface{}
if err := context.ShouldBindJSON(&input); err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
var product model.Product
err = database.Database.First(&product, id).Error
if err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
if err := database.Database.Model(&product).Updates(input).Error; err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
context.JSON(http.StatusOK, gin.H{"data": product})
}
func DeleteProduct(context *gin.Context) {
id, err := strconv.ParseInt(context.Param("id"), 10, 64)
if err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
if err := database.Database.Delete(&model.Product{}, id).Error; err != nil {
context.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
context.JSON(http.StatusNoContent, nil)
}
Next, set up a Gin router in main.go and serve the application. To do this, open main.go and add the following function:
func serveApplication() {
router := gin.Default()
userRoutes := router.Group("/product")
userRoutes.POST("", controller.CreateProduct)
userRoutes.GET("", controller.GetAllProducts)
userRoutes.GET("/:id", controller.GetProduct)
userRoutes.PATCH("/:id", controller.UpdateProduct)
userRoutes.DELETE("/:id", controller.DeleteProduct)
if err := router.Run(":8000"); err != nil {
handleError(err)
}
fmt.Println("Server running on port 8000")
}
Your import list should also be updated to match the following:
import (
"errors"
"fmt"
"github.com/gin-gonic/gin"
"github.com/joho/godotenv"
"log"
"product-listing-api/controller"
"product-listing-api/database"
"product-listing-api/model"
)
Finally, update the main() function as follows:
func main() {
loadEnv()
loadDatabase()
serveApplication()
}
The serveApplication() function sets up a Gin router and defines the routes for the API. It also serves the application on port 8000. Ensure that no other processes are running on this port, or update the port address to one which is not in use.
With that in place, your application is ready to go! Run the application.
go run main.go
You can test your endpoints by sending requests via Postman or cURL.
Conclusion
In this tutorial, we have seen how to set up a PlanetScale MySQL database and use it to build a product listing application with Golang. We explored how to connect to a PlanetScale database using GORM and how to perform CRUD operations on the database using the Gin framework. Armed with the knowledge gained in this tutorial, you can now confidently build your own applications using Golang and PlanetScale. I’m excited to see what you come up with!
The entire codebase for this tutorial is available on GitHub. Feel free to explore further. Happy coding!