In this article, we will explore the ways to establish a connection between Go and MySQL which enables us to leverage the full potential of both technologies. We will cover the installation of the MySQL driver, establish a connection, execute queries, and perform some operations. Before we embark on our journey of connecting Go with MySQL, let’s ensure that we have the necessary prerequisites in place.
First, ensure that Go is installed on the machine and is properly configured. Second, have access to a working MySQL server or to a MySQL database-as-a-service platform.
Install MySQL Driver in Golang to Connect with MySQL Database
We need to install a MySQL driver to facilitate a communication between Go and MySQL. Fortunately, Go provides a robust ecosystem of database drivers including an official MySQL driver.
To install the MySQL driver in Go (Golang) to connect to a MySQL database, we need to use the Go “get” command to download and install the driver package. For this, we must open the “cmd” prompt and run the subsequent command:
The command fetches the MySQL driver package and installs it in the Go workspace. The driver and its dependencies are automatically downloaded and installed after the installation is complete. The MySQL driver can now be imported in the Go code, and we can use the driver to connect to a MySQL database.
Let’s dive into implementing the examples for this article. We first establish the connection by providing the valid connection string. After that, we illustrate the creation of the table of MySQL within Go. Then, we insert the values into that table using the MySQL INSERT statement in the Go file.
Example 1: Establish a Connection in Golang with MySQL
First, we need to establish a connection in MySQL with a Go (Golang) application. For this, we can make use of the database/sql package along with the appropriate MySQL driver. Consider the following program which establishes the connection with MySQL.
import (
"fmt"
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func main() {
dbIs, errIs := sql.Open("mysql",
"username:password@tcp(127.0.0.1:3306)/test")
if errIs != nil {
panic(errIs.Error())
}
fmt.Println("Connected to MySQL Database")
defer dbIs.Close()
}
Here, we import the database/sql package which works with databases. Then, the “github.com/go-sql-driver/mysql” import is used to register the MySQL driver without directly referencing it in the code.
After that, we implement the sqlOpen() function which creates a connection to the database that is stored in MySQL. The first argument specifies the driver to use which is “mysql” in this case. The second argument is the connection string that contains the necessary credentials and database information. We connect to a “test “ database that runs on the local machine (127.0.0.1) on port 3306. After the connection is established, we check for the errors that occurrs during the connection attempt.
Next, the defer is called to the dbIs.Close() function which is made to close the database connection when the main function completes the execution.
Hence, the connection with MySQL is successfully established within the Golang file as shown in the output of the Go console:
Example 2: Create a Table in Golang with MySQL
Moving ahead, we create a table in MySQL using Go (Golang). We utilize the database/sql package along with the MySQL driver to establish a connection and execute the table creation query. We can see the implementation in the Go file to create the table.
import (
"fmt"
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "root@tcp(127.0.0.1:3306)/test")
if err != nil {
panic(err.Error())
}
fmt.Println("Connected to MySQL Database")
defer db.Close()
createTableQuery := `
CREATE TABLE IF NOT EXISTS test (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
);
`
_, err = db.Exec(createTableQuery)
if err != nil {
fmt.Println("Failed to create table:", err)
return
}
fmt.Println("Table created successfully!")
}
Here, we first establish the connection with the MySQL database within Golang as accomplished in the previous example. After that, we define the “createTableQuery” variable that holds a multi-line string that contains the SQL query to create a table named “test” if it does not yet exist. The table has three columns: id (INT), name (VARCHAR), and age (INT). The “CREATE TABLE IF NOT EXISTS” statement checks if the table already exists and creates it only if it doesn’t exist. Next, we call the db.Exec() function that executes the SQL query that is stored in “createTableQuery” whenever there is a failure during execution.
Thus, the output displays the message that the table is created successfully without an exception in the program upon the execution of the GoLang file:
Example 3: Insert Rows into the Table in Golang with MySQL
However, we can also insert the rows into the table after establishing the connection and creating the table. We can execute the “SQL INSERT” statement to add the data to the created table. The query to insert the data in the MySQL table within the Go file is illustrated in the following:
import (
"fmt"
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "root@tcp(127.0.0.1:3306)/test")
if err != nil {
panic(err.Error())
}
defer db.Close()
insert, err := db.Query("INSERT INTO test VALUES ( 1, 'Alex', 23 )")
if err != nil {
panic(err.Error())
}
defer insert.Close()
}
Here, we made the connection with the MySQL database. We then proceed with inserting the rows into the test table that we created in the prior example. We use the db.Query() function to carry out the “INSERT SQL” statement that adds a row, accordingly. The statement is provided as a string directly in the code.
In this case, it inserts the values of “1”, “Alex”, and “23” into the table. The function returns the “Rows” interface and an error. In this case, we don’t need the rows, so we use the blank identifier “_” to discard it. Next, we deploy the defer insert.Close() statement to make sure that, whether any error occurs or not, the result of the query is eliminated before the program ends. Irrespective of whether an error arises or not, the defer db.Close() command is called to assure sure that the database access is terminated before the program ends.
Finally, the values are inserted into the MySQL database table as the output generates the following message without any error:
Conclusion
We explored the methods to build the connection in Go with MySQL and performed the basic database operations. We covered the installation of the MySQL driver, established a database connection, and executed the queries. With this knowledge, we can build the robust and efficient applications that interact with MySQL databases using the Go programming language. Now that we followed the examples and grasped the fundamentals, we can confidently incorporate Go and MySQL into the applications.
