Go with MySQL
In this article we are going into the process of establishing a connection to a MySQL database in the Go programming language, using the capabilities of the Gorm package.
Using MySQL Server with Docker
Docker simplifies the process of setting up and managing databases, including MySQL. Follow the below steps to use MySQL server with Docker:
- Pull the MySQL Docker Image
Open a terminal and run the following command to pull the official MySQL Docker image and check that the image is downloaded successfully.
> docker pull mysql/mysql-server
> docker images
2. Run MySQL Container
Start a MySQL container using the pulled image. Replace your-mysql-container with your desired container name and your-mysql-password with your preferred MySQL root password.
> docker run -d --name your-mysql-container -e MYSQL_ROOT_PASSWORD=your-mysql-password -e MYSQL_ROOT_HOST='%' -p 3306:3306 mysql/mysql-server:latest
> docker ps
3. Access MySQL Container
You can access the MySQL container using a MySQL client or a tool like MySQL Workbench. Make sure to use the correct credentials (root username and the password you set). Replace your-container-name with your mySQL container name.
> docker exec -it your-mysql-container-name mysql -u root -p
4. Check for Permissions of the root user
You have already set MYSQL_ROOT_HOST='%', which allows connections from any host; but check it to make sure that the root user has allowed the connections from any host by running the below command.
SELECT host, user FROM mysql.user;
5. Create a Database
To create a database in MySQL use the below command and replace the your_database_name with your preferred database name.
CREATE DATABASE your_database_name;
SHOW DATABASES;
Go Environment Setup
Let’s explore how to connect to a MySQL database using Go, with the help of two fantastic tools; the Gorm package for easy database handling and the Viper package to manage environment variables smoothly.
Firstly, check out an article explaining Viper, a package that makes handling environment variables easy.
Now, let’s dive into writing some code! We’ll use the Gorm package to interact with our MySQL database. Gorm is great because it makes working with databases in Go super easy. Make sure you have both Gorm, Gorm MySQL dialects and Viper installed in your Go project workspace.
// Initialize Go project workspace
> go mod init <go-project-name>
// Install Gorm package and mySQL dialects
> go get -u github.com/jinzhu/gorm
> go get -u github.com/jinzhu/gorm/dialects/mysql
// Install Viper package
> go get -u github.com/spf13/viperProject Structure
Our project structure will be as below, a structure separates concerns, making it organized and easy to navigate.
- main.go — The main entry point for your Go application.
- app.env — The file containing environment variables and configurations.
- models — A directory where you can store your database model files (e.g.,
your_model.go,another_model.go). - database — This directory holds files related to database operations.
- connection.go — File to handle actual database connection setup and initialization.
- utils.go — File to handle the initialization of tables and other utility functions. - config — A directory to manage configuration-related files.
- config.go — A file that handles the configuration setup using the Viper package.
Storing Environment Variables
Initially, we’ll store configuration-related environment variables, particularly those associated with the database. To accomplish this, we’ll create an app.env file to house the configurations. Additionally, we’ll craft a new config.go file to efficiently read these environment variables.
Create Database Connection
Now, let’s proceed to write the database connection code in the connection.go file within the database directory. This file will utilize the configurations obtained from the config.go file.
The NewConnectionToMySQL function is used to establish a connection to a MySQL database using the provided configuration object (DBConfig). This object encapsulates details such as user credentials, password, URL, port, and database name. In the event of a successful database connection, the function returns the db object and a nil error object, as error handling is internally managed within the function.
The CloseDB function is utilized to terminate the connection between the application and the database. To accomplish this, the function requires the database object as a parameter, and it proceeds to close the connection with the database.
Creating Model Classes
In this section, we’re implementing a user.go model class to execute queries using Gorm for MySQL. The user.go file in a Go application plays an important role in organizing and defining how user data is structured and managed.
It acts as a blueprint for a user, detailing their attributes like first name, last name, email, and age, while also incorporating database-specific configurations using Gorm tags.
This file goes beyond just describing the user; it contains functions for fundamental operations like creating, reading, updating, and deleting user data in a MySQL database. By encapsulating these operations in one file, the code becomes modular, making it easier to maintain, expand, and ensure consistency in how user-related features are implemented throughout the application.
In essence, user.go serves as the go-to place for anything related to users, offering a clean and organized solution for handling user data interactions with the database. The simplicity and modularity of this file make it a valuable asset, providing a clear structure for user-related logic and facilitating seamless integration into various parts of the application.
Utill File to Initialize the Database Tables
The util.go file in the database package serves a specific purpose in initializing the database. Here, it contains a function named InitializeDatabase that utilizes Gorm's AutoMigrate feature to create or update database tables based on the provided model, in this case, models.User.
Finalizing the Application
In the Go main the function below, we initialize environment variables, establish a database connection, and execute queries to explore the capabilities of the Go language with the Gorm package in handling MySQL databases.
In the main.go file, The first step involves initializing environment configurations using configs.InitEnvConfigs(). This function, located in the configs package reads environment variables and gets essential settings such as database connection details. This initialization ensures that the application is equipped with the necessary environmental information.
Following this, a databaseConfig object is created to encapsulate the database connection parameters fetched from the environment configurations. This object is vital for configuring the subsequent connection to the MySQL database.
The next set of initializations focuses on the database interaction. The application establishes a connection to the MySQL database using database.NewConnectionToMySQL(&databaseConfig). Subsequently, the database.InitializeDatabase(db) function is invoked to initialize the database schema.
Lastly, the closure of the database connection is deferred until the end of the main function using defer database.CloseDB(db). This ensures that the connection is properly closed after executing all queries, preventing resource leaks and promoting effective resource management.
After trying out the queries we will get the below responses and our database user table will look like below.
This is how in Go language can create a database connection to MySQL database using the Gorm package. It is pretty straightforward but need to think more about how to handle it efficiently.
