MySQL Tutorial: Connecting to MySQL and Creating a DB using Go
Welcome to tutorial no. 1 in our MySQL tutorial series. In this tutorial, we will connect to MySQL and create a database. We will also ping the DB to ensure that the connection is established properly.
MySQL Series Index
Connecting to MySQL and creating a Database
Creating a Table and Inserting Rows
Selecting single and multiple rows
Prepared statements - WIP
Updating rows - WIP
Deleting rows - WIP
Importing the MySQL driver
The first step in creating the MySQL database is to download the MySQL driver package and import it into our application.
Let’s create a folder for our app and then download the MySQL package.
I have created a folder in the Documents directory. Please feel free to create it wherever you like.
mkdir ~/Documents/mysqltutorial
cd ~/Documents/mysqltutorial
After creating the directory, let’s initialize a go module for the project.
go mod init github.com/golangbot/mysqltutorial
The above command initializes a module named github.com/golangbot/mysqltutorial
The next step is to download the MySql driver. Run the following command to download the MySQL driver package.
go get github.com/go-sql-driver/mysql
Let’s write a program to import the MySQL driver we just downloaded.
Create a file named main.go with the following contents.
1package main
2
3import (
4 "database/sql"
5
6 _ "github.com/go-sql-driver/mysql"
7)
Use of blank identifier _ when importing the driver
The "database/sql" package provides interfaces for accessing the MySQL database. It contains the types needed to manage the MySQL DB.
In the next line, we import _ "github.com/go-sql-driver/mysql" prefixed with an underscore (called as a blank identifier). What does this mean? This means we are importing the MySQL driver package for its side effect and we will not use it explicitly anywhere in our code. When a package is imported prefixed with a blank identifier, the init function of the package will be called. Also, the Go compiler will not complain if the package is not used anywhere in the code.
That’s all fine, but why is this needed?
The reason is any SQL driver must be registered by calling the Register function before it can be used. If we take a look at the source code of the MySQL driver, in line https://github.com/go-sql-driver/mysql/blob/7cf548287682c36ebce3b7966f2693d58094bd5a/driver.go#L93 we can see the following init function
1func init() {
2 sql.Register("mysql", &MySQLDriver{})
3}
The above function registers the MySQL driver with the name mysql. When we import the package prefixed with the blank identifier _ "github.com/go-sql-driver/mysql", the above init function is called and the driver is available for use. Perfect 😃. Just what we wanted.
Connecting and Creating the Database
Now that we have registered the driver successfully, the next step is to connect to MySQL and create the database.
Let’s define constants for our DB credentials. We must never define passwords in plain text for security reasons but for the sake of this tutorial, we will define them in plain text.
1const (
2 username = "root"
3 password = "password"
4 hostname = "127.0.0.1:3306"
5 dbname = "ecommerce"
6)
Please replace the above values with your root credentials to access MySQL. The dbname above is the name of the database that will be created.
The DB can be opened by using Open function of the sql package. This function takes two parameters, the driver name, and the data source name(DSN). As we have already discussed, the driver name is the first parameter passed to sql.Register function. In our case, it is mysql . The DSN is of the following format
username:password@protocol(address)/dbname?param=value
Let’s write a small function that will return us this DSN when the database name is passed as a parameter.
1func dsn(dbName string) string {
2 return fmt.Sprintf("%s:%s@tcp(%s)/%s", username, password, hostname, dbName)
3}
The above function returns a DSN for the dbName passed. For example, if ecommerce is passed, it will return root:password@tcp(127.0.0.1:3306)/ecommerce. The dbName is optional and it can be empty.
Since we are actually creating the DB here and do not want to connect an existing DB, an empty dbName will be passed to the dsn function.
1func main() {
2 db, err := sql.Open("mysql", dsn(""))
3 if err != nil {
4 log.Printf("Error %s when opening DB\n", err)
5 return
6 }
7}
Please ensure that the user has rights to create the DB. The above lines of code open and return a connection to the database.
After establishing a connection to the DB, the next step is to create the DB. The following code does that.
1ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
2defer cancelfunc()
3res, err := db.ExecContext(ctx, "CREATE DATABASE IF NOT EXISTS "+dbname)
4if err != nil {
5 log.Printf("Error %s when creating DB\n", err)
6 return
7}
8no, err := res.RowsAffected()
9if err != nil {
10 log.Printf("Error %s when fetching rows", err)
11 return
12}
13log.Printf("rows affected: %d\n", no)
After opening the database, we use the ExecContext method to create the database. This method is used to execute a query without returning any rows. The database creation query doesn’t return any rows and hence ExecContext can be used to create the database.
Being a responsible developer, we pass a context with a timeout of 5 seconds to ensure that the program doesn’t get stuck when creating the DB in case there is any network error or any other error in the DB. cancelfunc is only needed when we want to cancel the context before it times out. There is no use of it here, hence we just defer the cancelfunc call.
The ExecContext call returns a result type and an error. We can check the number of rows affected by the query by calling the RowsAffected() method. The above code creates a database named ecommerce.
Understanding Connection Pool
The next step after creating the DB is to connect to it and start executing queries. In other programming languages, you might do this by running the use ecommerce command to select the database and start executing queries. This can be done in Go by using the code db.ExecContext(ctx, "USE ecommerce").
While this might seem to be a logical way to proceed, this leads to unexpected runtime errors in Go. Let’s understand the reason behind this.
When we first executed sql.Open("mysql", dsn("")), the DB returned is actually a pool of underlying DB connections. The sql package takes care of maintaining the pool, creating and freeing connections automatically. This DB is also safe to be concurrently accessed by multiple Goroutines.
Since DB is a connection pool, if we execute use ecommerce on DB, it will be run on only one of the DB connections in the pool. When we execute another query on DB, we might end up running the query on some other connection in the pool on which use ecommerce was not executed. This will lead to the error Error Code: 1046. No database selected. The solution to this problem is simple. We close the existing connection to the mysql which we created without specifying a DB name and open a new connection with the DB name ecommerce which was just created.
1db.Close()
2db, err = sql.Open("mysql", dsn(dbname))
3if err != nil {
4 log.Printf("Error %s when opening DB", err)
5 return
6}
7defer db.Close()
In the above lines, we close the existing connection and open a new connection to the DB. This time we specify the DB name ecommerce in line no. 2 when opening a connection to the database. Now we have a connection pool connected to the ecommerce DB 😃.
Connection Pool Options
There are few important connection pool options to be set to ensure that network partitions and other runtime errors that may occur with our DB connections are handled properly.
SetMaxOpenConns
This option is used to set the maximum number of open connections that are allowed from our application. It’s better to set this to ensure that our application doesn’t utilize all available connections to MySQL and starve other applications.
The maximum number of client connections for a MySQL Server can be determined by running the following query
show variables like 'max_connections';
It returns the following output for me
151 is the default maximum connections allowed and this can be changed if needed. 151 is the maximum number of connections allowed for this entire MySQL server. This includes connections to all databases present in this MySQL server.
Ensure that you set a value lower than max_connections so that other applications and databases are not starved. I am using 20. Please feel free to change it according to your requirement.
db.SetMaxOpenConns(20)
SetMaxIdleConns
This option limits the maximum idle connections. The number of idle connections in the connection pool is controlled by this setting.
db.SetMaxIdleConns(20)
SetConnMaxLifetime
It’s quite common for connections to become unusable because of a number of reasons. For instance, there might be a firewall or middleware that terminates idle connections. This option ensures that the driver closes the idle connection properly before it is terminated by a firewall or middleware.
db.SetConnMaxLifetime(time.Minute * 5)
Please feel free to change the above options based on your requirement.
Pinging the DB
The Open function call doesn’t make an actual connection to the DB. It just validates whether the DSN is correct. The PingContext() method must be called to verify the actual connection to the database. It pings the DB and verifies the connection.
1ctx, cancelfunc = context.WithTimeout(context.Background(), 5*time.Second)
2defer cancelfunc()
3err = db.PingContext(ctx)
4if err != nil {
5 log.Printf("Errors %s pinging DB", err)
6 return
7}
8log.Printf("Connected to DB %s successfully\n", dbname)
We create a context with a 5 second timeout to ensure that the program doesn’t get stuck when pinging the DB in case there is a network error or any other error.
The full code is provided below.
1package main
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "log"
8 "time"
9
10 _ "github.com/go-sql-driver/mysql"
11)
12
13const (
14 username = "root"
15 password = "password"
16 hostname = "127.0.0.1:3306"
17 dbname = "ecommerce"
18)
19
20func dsn(dbName string) string {
21 return fmt.Sprintf("%s:%s@tcp(%s)/%s", username, password, hostname, dbName)
22}
23
24func main() {
25 db, err := sql.Open("mysql", dsn(""))
26 if err != nil {
27 log.Printf("Error %s when opening DB\n", err)
28 return
29 }
30
31 ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
32 defer cancelfunc()
33 res, err := db.ExecContext(ctx, "CREATE DATABASE IF NOT EXISTS "+dbname)
34 if err != nil {
35 log.Printf("Error %s when creating DB\n", err)
36 return
37 }
38 no, err := res.RowsAffected()
39 if err != nil {
40 log.Printf("Error %s when fetching rows", err)
41 return
42 }
43 log.Printf("rows affected: %d\n", no)
44 db.Close()
45
46 db, err = sql.Open("mysql", dsn(dbname))
47 if err != nil {
48 log.Printf("Error %s when opening DB", err)
49 return
50 }
51 defer db.Close()
52
53 db.SetMaxOpenConns(20)
54 db.SetMaxIdleConns(20)
55 db.SetConnMaxLifetime(time.Minute * 5)
56
57 ctx, cancelfunc = context.WithTimeout(context.Background(), 5*time.Second)
58 defer cancelfunc()
59 err = db.PingContext(ctx)
60 if err != nil {
61 log.Printf("Errors %s pinging DB", err)
62 return
63 }
64 log.Printf("Connected to DB %s successfully\n", dbname)
65}
Running the above code will print
2023/08/21 21:23:54 rows affected: 1
2023/08/21 21:23:54 Connected to DB ecommerce successfully
This brings us to an end of this tutorial.
I hope you liked this tutorial. Please leave your feedback and comments. Please consider sharing this tutorial on twitter or LinkedIn. Have a good day.
If you would like to advertise on this website, hire me, or if you have any other software development needs please email me at naveen[at]golangbot[dot]com.
Next tutorial - Creating a Table and Inserting Rows
