How To Integrate Golang with MySQL Database

Introduction to Package Database

In Golang programming, by default it has a package called database. This package has a set of standard interfaces which provide standards for communication into the database so that we can immediately create the program that we will create using this package to access any type of database with the same code. But what will differ is that only the SQL query code is used according to the database used.

---
title: How to work database package
---
flowchart LR
    A[Application] --> B[Database \nInterface] --> C[Database\nDriver] --> D[Database]

---
title: How to work database package
---
flowchart LR
    A[Application] --> B[Database \nInterface] --> C[Database\nDriver] --> D[Database]

This time we will focus on integrating into the database that we use, namely MySQL as the Database Management System. So friends, try to understand or learn the basics of using MySQL first.

Added Driver Database

Make sure to create a project folder first using the steps below:

• Create a learn-golang-database-mysql folder
• Open the folder in Visual Studio Code or similar
• Open the terminal then type go mod init learn-golang-database-mysql to initialize the golang project with go mod.

Before we create a program using a database, we first need to add the driver database. Without a database driver, the Golang database package will not understand anything because this driver package is used to translate the contract interface in Golang. So we can visit https://golang.org/s/sqldrivers to see some of the drivers that already exist and support the Golang program.

In the MySQL driver there are 3 packages that we can use, namely:

• MySQL: https://github.com/go-sql-driver/mysql/ [*]
• MySQL: https://github.com/siddontang/go-mysql/ [**] (also handles replication)
• MySQL: https://github.com/ziutek/mymysql [*]

So, we can choose one of the three driver packages. Of course, according to the needs of which friends you want to use. Our suggestion is that we usually look at the popularity of the package by looking at the number of stars and forks on GitHub.

The next stage is to add the MySQL Database driver module to our project by typing like this

1go get -u github.com/go-sql-driver/mysql

1➜  integrasi-golang-database-mysql go get -u github.com/go-sql-driver/mysql        
2go: downloading github.com/go-sql-driver/mysql v1.7.1
3go: added github.com/go-sql-driver/mysql v1.7.1

Next, we will import the driver package into our program which will look like this

1import(
2    "database/sql"
3    _ "github.com/go-sql-driver/mysql"
4)

Create a Connection to the Database

The first thing we need to do when creating an application using a database is how to connect to the database. To connect to the database in Golang, we can create a sql.DB object using the sql.Open(driver, dataSourceName) function which we imported previously into the mysql driver. Meanwhile, dataSourceName usually contains writing a connection to the database. Below is the writing.

1    <username>:<password>@tcp(<host>:<port>)/<database_name>

• : username that has been created to connect to the program
• : password of the mysql connection
• : the destination database server/localhost that will be used
• : database port used
• <database_name> : the name of the database that we will connect to in the program

Let’s try directly creating a main function and type the code below.

 1package main
 2
 3import (
 4	"database/sql"
 5)
 6
 7func main() {
 8	db, err := sql.Open("mysql", "root:belajargolang@tcp(localhost:3306)/belajar-golang")
 9	if err != nil {
10		panic(err)
11	}
12
13	defer db.Close()
14}

Make sure you have MySQL Database available on your laptop or computer. Here’s what you need to prepare:

• The username root and password belajargolang can be adjusted if there is already an existing username and password that can be used.
• Create a learning-golang database in the MySQL Database which we will later use in this Golang integration.

When we run the program above, a panic error will occur where sql: unknown driver "mysql" is an error that appears when we have not added the mysql package. So don’t forget to add the package that was below at the beginning, namely adding the package _ "github.com/go-sql-driver/mysql".

Database Pooling

sql.Db in Golang is not a connection to a database but rather a pool to a database which is known as the Database Pooling concept. In this case, Golang will manage connections to databases automatically so we don’t need to manage connections manually. With this database pooling capability, we can also determine the minimum and maximum number of connections made by Golang so that we don’t flood connections to the database because each database has a maximum number of connections that access that database.

Some database config settings in sql.DB include the following: | Method | Description | |:—————————–|:—————– —| | SetMaxIdleConns(number) | setting the minimum number of connections to be made | | SetmaxOpenConns(number) | setting the maximum number of connections to make | | SetConnMaxIdleTime(duration) | settings for how long unused connections will be deleted | | SetConnMaxLifetime(duration) | setting how long the connection can be used |

For example, we will update the connection above, initially stored in the main function, we will separate it into one GetConnection() function like this

 1func GetConnection() *sql.DB {
 2	db, err := sql.Open("mysql", "root:belajargolang@tcp(localhost:3306)/belajar-golang")
 3	if err != nil {
 4		panic(err)
 5	}
 6
 7	db.SetMaxIdleConns(10)
 8	db.SetMaxOpenConns(100)
 9	db.SetConnMaxIdleTime(5 * time.Minute)
10	db.SetConnMaxLifetime(60 * time.Minute)
11	return db
12}

Execute SQL Commands

If we want to communicate with the database, then we make sure we can communicate with the database with SQL commands. Golang has provided a function that we can use to send SQL commands to the database using the DB function ExecContext(context,sql,params). When we send a SQL command, we also need to send the context that we studied previously so that we can also send a cancel signal when we want to cancel sending the SQL command.

Next, we try to create the Customer table below

1CREATE TABLE customer
2(
3    id VARCHAR(100) NOT NULL,
4    name VARCHAR(100) NOT NULL,
5    PRIMARY KEY (id)
6) ENGINE = InnoDB;

Send SQL Insert Command

After we create the customer table we will try to save the data into the database using the SQL Insert command, below is the function we will create

1func InsertIntoDB(ctx context.Context, db *sql.DB) {
2	_, err := db.ExecContext(ctx, "INSERT INTO customer(id,name) VALUES('santekno','Santekno');")
3	if err != nil {
4		panic(err)
5	}
6	fmt.Println("success insert data to database")
7}

So to call this function we need to update the main function to be like below

 1func main() {
 2    // initiate context
 3	ctx := context.Background()
 4
 5    // initiate get connection to db
 6	db := GetConnection()
 7	defer db.Close()
 8
 9    // get function insert into database
10	InsertIntoDB(ctx, db)
11}

Sending SQL Query Commands

SQL operations without requiring return results from the database, we can simply use the Exec or ExecContext command, but if we need the results of SQL commands such as SELECT then we can use a different function, namely QueryContext(context, sql, params).

OK, let’s just create a function to retrieve data from the database like the code below.

 1func GetDataCustomer(ctx context.Context, db *sql.DB) {
 2	rows, err := db.QueryContext(ctx, "SELECT id, name FROM customer")
 3	if err != nil {
 4		panic(err)
 5	}
 6	defer rows.Close()
 7
 8	for rows.Next() {
 9		var id, name string
10		err := rows.Scan(&id, &name)
11		if err != nil {
12			panic(err)
13		}
14		fmt.Println("Id : ", id)
15		fmt.Println("Name: ", name)
16	}
17}

If we see above the results of the function query are stored in the data struct sql.Rows. These rows are used to interact with the results of the query. We can also do this interaction by using the rows.Next() function to display the data. If the data returns false, it means there is no longer any data in the result. Then, if we want to read each piece of data we can use rows.Scan(column...) and don’t forget that we also need to close the rows using rows.Close().

To call the function to retrieve data from the database above, we need to call the function above in the main function.

 1func main() {
 2    // initiate context
 3	ctx := context.Background()
 4
 5    // initiate get connection to db
 6	db := GetConnection()
 7	defer db.Close()
 8
 9    // get function insert into database
10	// InsertIntoDB(ctx, db)
11
12    // get data customer
13    GetDataCustomer(ctx, db)
14}

We first comment out the data insert function call so we can try to retrieve it from the database. So if we run it the results will be like below

1✗ go run main.go
2Id :  santekno
3Name:  Santekno

Get to know the Column Data Type

We also need to understand more deeply about tables with certain column data types, currently this project only uses the varchar data type which we usually use for the string data type in Golang.

To find out more deeply, we will try adding several columns that we have created to the customer table. Below we add or try running in SQL terminal to change some additional columns.

1DELETE FROM customer;
2ALTER TABLE customer
3ADD COLUMN email VARCHAR(100),
4ADD COLUMN balance INTEGER DEFAULT 0,
5ADD COLUMN rating DOUBLE DEFAULT 0.0,
6ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
7ADD COLUMN birth_date DATE,
8ADD COLUMN married BOOLEAN DEFAULT false;

Next, we will also enter customer data so that we have sample data that we will use later.

1INSERT INTO customer(id, name, email, balance, rating, birth_date, married)
2VALUES('santekno','Santekno','santekno@gmail.com',100000,5.0,'2000-01-01', true);
3INSERT INTO customer(id, name, email, balance, rating, birth_date, married)
4VALUES('ihsan','Ihsan Arif','ihsanarifr@gmail.com',100000,5.0,'2001-01-01', true);

Let’s also look at the function that we have created in GetDataCustomer then change the query to something like the one below.

 1func GetDataCustomer(ctx context.Context, db *sql.DB) {
 2	rows, err := db.QueryContext(ctx, "SELECT id, name, email, balance, rating, birth_date, married, created_at FROM customer")
 3	if err != nil {
 4		panic(err)
 5	}
 6	defer rows.Close()
 7
 8	for rows.Next() {
 9		var id, name, email string
10		var balance int32
11		var rating float32
12		var birthDate, createdAt time.Time
13		var married bool
14		err := rows.Scan(&id, &name, &email, &balance, &rating, &birthDate, &married, &createdAt)
15		if err != nil {
16			panic(err)
17		}
18		fmt.Println("Id : ", id)
19		fmt.Println("Name: ", name)
20		fmt.Println("Email: ", email)
21		fmt.Println("Balance: ", balance)
22		fmt.Println("Rating: ", rating)
23		fmt.Println("Birth Date: ", birthDate)
24		fmt.Println("Married: ", married)
25		fmt.Println("Created At: ", createdAt)
26	}
27}

When we run the program, we will see a panic error in the column scan like this

1panic: sql: Scan error on column index 5, name "birth_date": unsupported Scan, storing driver.Value type []uint8 into type *time.Time
2
3goroutine 1 [running]:
4main.GetDataCustomer({0x1006302a8?, 0x14000086000?}, 0x60?)
5        /Users/ihsanarifr/Documents/ihsan/learn-golang-database-mysql/main.go:59 +0x528
6main.main()
7        /Users/ihsanarifr/Documents/ihsan/learn-golang-database-mysql/main.go:20 +0x70
8exit status 2

It turns out that the birth_date data type is not supported by Golang when translating the data type in the database with the data type in Golang. Then what should we do? This happens because the time format in the database is not translated into code. So we need an additional connection to the database like this.

1	db, err := sql.Open("mysql", "root:belajargolang@tcp(localhost:3306)/belajar-golang?parseTime=true")

By default, the MySQL Driver for Golang will query the DATE, DATETIME, TIMESTAMP data types to []byte or []uint8, which can be converted to strings by manually parsing them to time.Time. This conversion is a bit difficult when we have a lot of columns and in the future it is not scalable, so there is another way we can do it, namely by adding the parseTime=true parameter to the connection parameter.

Run the program again, it will be successful and output data as below.

 1✗ go run main.go
 2Id :  ihsan
 3Name:  Ihsan Arif
 4Email:  ihsanarifr@gmail.com
 5Balance:  100000
 6Rating:  5
 7Birth Date:  2001-01-01 00:00:00 +0000 UTC
 8Married:  true
 9Created At:  2023-07-30 10:40:20 +0000 UTC
10Id :  santekno
11Name:  Santekno
12Email:  santekno@gmail.com
13Balance:  100000
14Rating:  5
15Birth Date:  2000-01-01 00:00:00 +0000 UTC
16Married:  true
17Created At:  2023-07-30 10:40:11 +0000 UTC

Introduction to the Nullable Type Data Type

In Golang using the SQL Database driver it cannot understand the NULL data type in the database. So specifically columns that have NULL in the database will be a problem if we do a round Scan using the data type represented in Golang. Then what should we do? Don’t worry, there’s a way. Before going to the method, we will first add data that has NULL columns so that we can get a case study that is suitable for inserting null data in a database like this.

1INSERT INTO customer(id, name, email, balance, rating, birth_date, married)
2VALUES('arif','Arif',NULL,100000,5.0,NULL, true);

When we run our program, it will produce a scan error in the email column where the Arif data has a NULL email.

1✗ go run main.go
2panic: sql: Scan error on column index 2, name "email": converting NULL to string is unsupported
3
4goroutine 1 [running]:
5main.GetDataCustomer({0x104d182a8?, 0x14000014090?}, 0x60?)
6        /Users/ihsanarifr/Documents/ihsan/learn-golang-database-mysql/main.go:59 +0x528
7main.main()
8        /Users/ihsanarifr/Documents/ihsan/learn-golang-database-mysql/main.go:20 +0x70
9exit status 2

The solution is that we will automatically convert NULL which is not supported by the Golang Mysql Driver to a special type that can be NULL, which is in the sql package below.

So, we need to slightly change the variables that have NULL from the GetDataCustomer() function and check the data that returns NULL to be like this.

 1func GetDataCustomer(ctx context.Context, db *sql.DB) {
 2	rows, err := db.QueryContext(ctx, "SELECT id, name, email, balance, rating, birth_date, married, created_at FROM customer")
 3	if err != nil {
 4		panic(err)
 5	}
 6	defer rows.Close()
 7
 8	for rows.Next() {
 9		var id, name string
10		var email sql.NullString
11		var balance int32
12		var rating float32
13		var birthDate sql.NullTime
14		var createdAt time.Time
15		var married bool
16		err := rows.Scan(&id, &name, &email, &balance, &rating, &birthDate, &married, &createdAt)
17		if err != nil {
18			panic(err)
19		}
20		fmt.Println("Id : ", id)
21		fmt.Println("Name: ", name)
22		if email.Valid {
23			fmt.Println("Email: ", email)
24		}
25		fmt.Println("Balance: ", balance)
26		fmt.Println("Rating: ", rating)
27		if birthDate.Valid {
28			fmt.Println("Birth Date: ", birthDate)
29		}
30		fmt.Println("Married: ", married)
31		fmt.Println("Created At: ", createdAt)
32	}
33}

Get to know SQL Injection

When creating a query, we will likely ‘hardcode’ SQL commands in our Golang code which will usually receive data input from the user from parameters then add to the SQL command from the user input and send the command using the SQL query command.

So what is SQL Injection? SQL Injection is a technique that abuses a security gap that occurs in the database layer of a program. Usually done by sending input (usually parameters) from the user with the wrong command, causing the SQL results we create to be invalid. SQL Injection is very dangerous if we create the wrong SQL which could be unsafe for our program or application from hacker attacks.

Before carrying out a SQL Injection simulation, we first prepare the table that we will use later.

1CREATE TABLE user(
2    username VARCHAR(100) NOT NULL,
3    password VARCHAR(100) NOT NULL,
4    PRIMARY KEY (username)
5)ENGINE = InnoDB;
6
7# insert data admin that sample data
8INSERT INTO user(username,password) VALUES('admin','admin');

Now, we will create a function which will perform a SQL query to retrieve data based on username and password.

 1func Login(ctx context.Context, db *sql.DB, username, password string) bool {
 2	sqlQuery := "SELECT username FROM user WHERE username='" + username + "' AND password='" + password + "' LIMIT 1"
 3	rows, err := db.QueryContext(ctx, sqlQuery)
 4	if err != nil {
 5		panic(err)
 6	}
 7	defer rows.Close()
 8
 9	if rows.Next() {
10		var username string
11		rows.Scan(&username)
12		fmt.Println("Success Login ", username)
13		return true
14	} else {
15		fmt.Println("Failed Login")
16	}
17	return false
18}

Then we will run it by adding this command to the main function as follows.

 1func main() {
 2	// initiate context
 3	ctx := context.Background()
 4
 5	// initiate get connection to db
 6	db := GetConnection()
 7	defer db.Close()
 8
 9	// get function insert into database
10	// InsertIntoDB(ctx, db)
11
12	// get data customer
13	// GetDataCustomer(ctx, db)
14
15	username := "admin"
16	password := "admin"
17	Login(ctx, db, username, password)
18}

1✗ go run main.go 
2Success Login  admin

Then we try the password, enter wrong, then the login should fail.

1✗ go run main.go
2Failed Login

Next, we will immediately try to change the username and password which hackers usually use to perform SQL Injection.

1username := "admin'; #"
2password := "salah"

So, what will happen?

1go run main.go
2Success Login  admin

The result is that admin users can log in with the wrong password, this is an example of SQL Injection which is very dangerous when we use SQL command queries like the one below. So how do you solve it and what kind of solution?

• Do not manually create SQL by concatenating string directly
• If we need parameters in SQL commands then we can use the Execute or Query function with commands which we will discuss in the next chapter.

SQL with Parameters to handle SQL Injection

The Exec can Query function has additional parameters that we can use to substitute these parameters into the SQL query that we create. So SQL requires a parameter to change it, namely by using the ? (question mark) character.

For example, the example query below

• SELECT username FROM user WHERE username=? AND password=? LIMIT 1
• INSERT INTO user(username, password) VALUES(?,?)
• UPDATE USER SET password=? WHERE username=?

So, let’s try changing the Login query function to adapt the SQL command to the example above, so it will look like this.

1	sqlQuery := "SELECT username FROM user WHERE username=? AND password=? LIMIT 1"
2	rows, err := db.QueryContext(ctx, sqlQuery, username, password)
3    if err != nil {
4        panic(err)
5    }

Then when we run our program again with this username and password.

1username := "admin'; #"
2password := "salah"

Then the result will be an error and failure to log in.

1✗ go run main.go
2Failed Login

So now we have succeeded in dealing with the problem of SQL Injection which was carried out by several hackers.

We also try the Exec command by creating a new function to add user with the code below.

1func Register(ctx context.Context, db *sql.DB, username, password string) bool {
2	_, err := db.ExecContext(ctx, "INSERT INTO user(username, password) VALUE(?,?)", username, password)
3	if err != nil {
4		panic(err)
5	}
6
7	fmt.Println("success insert new user")
8	return true
9}

When we have added this function, we call the main function as below.

1  username := "santekno'; DROP TABLE user; #"
2	password := "santekno"
3	// Login(ctx, db, username, password)
4	Register(ctx, db, username, password)

The results of running the program above will look successful like this.

1✗ go run main.go
2success insert new user

In the username there is a SQL command to delete the table from the user but we look at the database and it turns out that if we use SQL parameters, entering the username will become a string which is stored directly as the username in the database username column so this is safe when executed and the command deletes the table too. not applicable.

Retrieve ID Data from Auto Increment

Sometimes, after adding data to the database, we also want to retrieve the resulting auto increment ID. In fact, we can re-query the database using SELECT LAST_INSERT_ID(), but in the Golang programming language it is easier, namely we just use the function LastInsertId() to get the last ID created by auto increment. Usually the return from the Exec function has an object in the form of Result, from Result we can call the LastInsertId() function to find out the last ID after we add data to the table.

To carry out the simulation, we need to add a table first with the command below.

1CREATE TABLE comments
2(
3    id INT NOT NULL AUTO_INCREMENT,
4    email VARCHAR(100) NOT NULL,
5    comment TEXT,
6    PRIMARY KEY (id)
7) ENGINE InnoDB;

After the table we created is available in the database, we continue by creating a function which adds data to the comments table by taking the last id that was added. Here is the function that we will create below.

 1func InsertComment(ctx context.Context, db *sql.DB, email, comment string) int64 {
 2	sqlQuery := "INSERT INTO comments(email, comment) VALUES(?, ?)"
 3	result, err := db.ExecContext(ctx, sqlQuery, email, comment)
 4	if err != nil {
 5		panic(err)
 6	}
 7
 8	insertID, err := result.LastInsertId()
 9	if err != nil {
10		panic(err)
11	}
12
13	return insertID
14}

After that, add the main function before running this program as follows.

1	email := "santekno@gmail.com"
2	comment := "hello world"
3	lastID := InsertComment(ctx, db, email, comment)
4	fmt.Println("Last Insert ID: ", lastID)

Then the results will display the last id when adding data automatically increment.

1✗ go run main.go
2Last Insert ID:  1

Preparing Statement

A prepare statement is a query or exec function that uses parameters which below use a prepare statement. The prepare statement is prepared first every time we execute a query, only the parameters are different, for example when we insert a lot of data directly. We can do prepare statements manually without having to use queries or exec parameters.

When we make a prepare statement it will automatically recognize the database connection used so that when we execute the prepare statement many times it will use the same connection and is more efficient because the prepare statement is only made once at the beginning.

If we use query and exec with parameters, we cannot guarantee that the connection used will be the same, therefore it is possible that the prepare statement will always be created many times even though we are using the same SQL. To create a prepare statement, we can use the Prepare(context,sql) function represented in the struct database/sql.Stmt. Like other SQL resources, sq.Stmt must also be Close() if it is no longer used.

OK, we will try to simulate it using the prepare statement below.

 1func PrepareStatement(ctx context.Context, db *sql.DB) {
 2	query := "INSERT INTO comments(email,comment) VALUE(?, ?)"
 3	statement, err := db.PrepareContext(ctx, query)
 4	if err != nil {
 5		panic(err)
 6	}
 7	defer statement.Close()
 8
 9	for i := 0; i < 10; i++ {
10		email := "Santekno " + strconv.Itoa(i) + "@gmail.com"
11		comment := "Komentar ke " + strconv.Itoa(i)
12		result, err := statement.ExecContext(ctx, email, comment)
13		if err != nil {
14			panic(err)
15		}
16		id, err := result.LastInsertId()
17		if err != nil {
18			panic(err)
19		}
20		fmt.Println("comment id ", id)
21	}
22}

Then, to try to execute it, we need to add the function that we have created to the main function as below.

1func main(){
2    ..
3    ..
4    PrepareStatement(ctx, db)
5}

This prepare statement is very useful when we have repeated queries into the database where the query used only changes parameters, so to reduce excessive connection creation, with a prepare statement the connection is created only once during the prepare statement so that when inserting data , the connection is used only once when preparing the statement.

Database Transactions

This feature of the database is very useful in carrying out data transactions into the database. By default, all SQL commands that we send using Golang will be automatically committed to the database automatically or auto commit. However, we can also use this transaction feature by manually committing it to the database. To start a transaction, we can use the Begin() function and it will generate a Tx struct that represents the transaction. We will use the Tx struct as a replacement for the DB to carry out transactions on each connection to the db, for example Exec, Query, and Prepare. After the transaction is complete, we will use the Commit() function to commit and Rollback() when we want to return the data to everyone for some reason, for example due to an error in one of the transactions.

Let’s just try to implement it below.

 1func TransactionDatabase(ctx context.Context, db *sql.DB) {
 2	tx, err := db.Begin()
 3	if err != nil {
 4		panic(err)
 5	}
 6
 7	query := "INSERT INTO comments(email,comment) VALUE(?, ?)"
 8
 9	// do transaction
10	for i := 0; i < 10; i++ {
11		email := "Santekno " + strconv.Itoa(i) + "@gmail.com"
12		comment := "Komentar ke " + strconv.Itoa(i)
13		result, err := tx.ExecContext(ctx, query, email, comment)
14		if err != nil {
15			panic(err)
16		}
17		id, err := result.LastInsertId()
18		if err != nil {
19			panic(err)
20		}
21		fmt.Println("comment id ", id)
22	}
23
24	err = tx.Commit()
25	if err != nil {
26		panic(err)
27	}
28}

In terms of this process, it is similar to when we use regular ExecContext but the difference is that we use manual transactions where we can control whatever the transaction is made into. For example, if there is an error in a transaction, it will not be stored in the database. If we want to try executing an insert but we can return it to the original, we can use tx.Rollback() so that the data that has been inserted will not be saved into the database.