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]

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

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

and the result will be like bellow here

➜  integrasi-golang-database-mysql go get -u github.com/go-sql-driver/mysql        
go: downloading github.com/go-sql-driver/mysql v1.7.1
go: 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

import(
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

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.

    <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

If the sql.DB object is no longer used, our recommendation is to immediately close the connection using the Close() function.

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

package main

import (
	"database/sql"
)

func main() {
	db, err := sql.Open("mysql", "root:belajargolang@tcp(localhost:3306)/belajar-golang")
	if err != nil {
		panic(err)
	}

	defer db.Close()
}

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

func GetConnection() *sql.DB {
	db, err := sql.Open("mysql", "root:belajargolang@tcp(localhost:3306)/belajar-golang")
	if err != nil {
		panic(err)
	}

	db.SetMaxIdleConns(10)
	db.SetMaxOpenConns(100)
	db.SetConnMaxIdleTime(5 * time.Minute)
	db.SetConnMaxLifetime(60 * time.Minute)
	return db
}

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

CREATE TABLE customer
(
    id VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) 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

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

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

func main() {
    // initiate context
	ctx := context.Background()

    // initiate get connection to db
	db := GetConnection()
	defer db.Close()

    // get function insert into database
	InsertIntoDB(ctx, db)
}

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.

func GetDataCustomer(ctx context.Context, db *sql.DB) {
	rows, err := db.QueryContext(ctx, "SELECT id, name FROM customer")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id, name string
		err := rows.Scan(&id, &name)
		if err != nil {
			panic(err)
		}
		fmt.Println("Id : ", id)
		fmt.Println("Name: ", name)
	}
}

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.

func main() {
    // initiate context
	ctx := context.Background()

    // initiate get connection to db
	db := GetConnection()
	defer db.Close()

    // get function insert into database
	// InsertIntoDB(ctx, db)

    // get data customer
    GetDataCustomer(ctx, db)
}

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

✗ go run main.go
Id :  santekno
Name:  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.

DELETE FROM customer;
ALTER TABLE customer
ADD COLUMN email VARCHAR(100),
ADD COLUMN balance INTEGER DEFAULT 0,
ADD COLUMN rating DOUBLE DEFAULT 0.0,
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN birth_date DATE,
ADD COLUMN married BOOLEAN DEFAULT false;

Some of the data types that we will use are as follows.

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

INSERT INTO customer(id, name, email, balance, rating, birth_date, married)
VALUES('santekno','Santekno','santekno@gmail.com',100000,5.0,'2000-01-01', true);
INSERT INTO customer(id, name, email, balance, rating, birth_date, married)
VALUES('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.

func GetDataCustomer(ctx context.Context, db *sql.DB) {
	rows, err := db.QueryContext(ctx, "SELECT id, name, email, balance, rating, birth_date, married, created_at FROM customer")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id, name, email string
		var balance int32
		var rating float32
		var birthDate, createdAt time.Time
		var married bool
		err := rows.Scan(&id, &name, &email, &balance, &rating, &birthDate, &married, &createdAt)
		if err != nil {
			panic(err)
		}
		fmt.Println("Id : ", id)
		fmt.Println("Name: ", name)
		fmt.Println("Email: ", email)
		fmt.Println("Balance: ", balance)
		fmt.Println("Rating: ", rating)
		fmt.Println("Birth Date: ", birthDate)
		fmt.Println("Married: ", married)
		fmt.Println("Created At: ", createdAt)
	}
}

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

panic: sql: Scan error on column index 5, name "birth_date": unsupported Scan, storing driver.Value type []uint8 into type *time.Time

goroutine 1 [running]:
main.GetDataCustomer({0x1006302a8?, 0x14000086000?}, 0x60?)
        /Users/ihsanarifr/Documents/ihsan/learn-golang-database-mysql/main.go:59 +0x528
main.main()
        /Users/ihsanarifr/Documents/ihsan/learn-golang-database-mysql/main.go:20 +0x70
exit 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.

	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.

✗ go run main.go
Id :  ihsan
Name:  Ihsan Arif
Email:  ihsanarifr@gmail.com
Balance:  100000
Rating:  5
Birth Date:  2001-01-01 00:00:00 +0000 UTC
Married:  true
Created At:  2023-07-30 10:40:20 +0000 UTC
Id :  santekno
Name:  Santekno
Email:  santekno@gmail.com
Balance:  100000
Rating:  5
Birth Date:  2000-01-01 00:00:00 +0000 UTC
Married:  true
Created 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.

INSERT INTO customer(id, name, email, balance, rating, birth_date, married)
VALUES('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.

✗ go run main.go
panic: sql: Scan error on column index 2, name "email": converting NULL to string is unsupported

goroutine 1 [running]:
main.GetDataCustomer({0x104d182a8?, 0x14000014090?}, 0x60?)
        /Users/ihsanarifr/Documents/ihsan/learn-golang-database-mysql/main.go:59 +0x528
main.main()
        /Users/ihsanarifr/Documents/ihsan/learn-golang-database-mysql/main.go:20 +0x70
exit 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.

func GetDataCustomer(ctx context.Context, db *sql.DB) {
	rows, err := db.QueryContext(ctx, "SELECT id, name, email, balance, rating, birth_date, married, created_at FROM customer")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id, name string
		var email sql.NullString
		var balance int32
		var rating float32
		var birthDate sql.NullTime
		var createdAt time.Time
		var married bool
		err := rows.Scan(&id, &name, &email, &balance, &rating, &birthDate, &married, &createdAt)
		if err != nil {
			panic(err)
		}
		fmt.Println("Id : ", id)
		fmt.Println("Name: ", name)
		if email.Valid {
			fmt.Println("Email: ", email)
		}
		fmt.Println("Balance: ", balance)
		fmt.Println("Rating: ", rating)
		if birthDate.Valid {
			fmt.Println("Birth Date: ", birthDate)
		}
		fmt.Println("Married: ", married)
		fmt.Println("Created At: ", createdAt)
	}
}

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.

CREATE TABLE user(
    username VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL,
    PRIMARY KEY (username)
)ENGINE = InnoDB;

# insert data admin that sample data
INSERT 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.

func Login(ctx context.Context, db *sql.DB, username, password string) bool {
	sqlQuery := "SELECT username FROM user WHERE username='" + username + "' AND password='" + password + "' LIMIT 1"
	rows, err := db.QueryContext(ctx, sqlQuery)
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	if rows.Next() {
		var username string
		rows.Scan(&username)
		fmt.Println("Success Login ", username)
		return true
	} else {
		fmt.Println("Failed Login")
	}
	return false
}

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

func main() {
	// initiate context
	ctx := context.Background()

	// initiate get connection to db
	db := GetConnection()
	defer db.Close()

	// get function insert into database
	// InsertIntoDB(ctx, db)

	// get data customer
	// GetDataCustomer(ctx, db)

	username := "admin"
	password := "admin"
	Login(ctx, db, username, password)
}

Then the results will come out like this.

✗ go run main.go 
Success Login  admin

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

✗ go run main.go
Failed Login

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

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

So, what will happen?

go run main.go
Success 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.

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

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

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

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

✗ go run main.go
Failed 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.

func Register(ctx context.Context, db *sql.DB, username, password string) bool {
	_, err := db.ExecContext(ctx, "INSERT INTO user(username, password) VALUE(?,?)", username, password)
	if err != nil {
		panic(err)
	}

	fmt.Println("success insert new user")
	return true
}

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

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

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

✗ go run main.go
success 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.

CREATE TABLE comments
(
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    comment TEXT,
    PRIMARY KEY (id)
) 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.

func InsertComment(ctx context.Context, db *sql.DB, email, comment string) int64 {
	sqlQuery := "INSERT INTO comments(email, comment) VALUES(?, ?)"
	result, err := db.ExecContext(ctx, sqlQuery, email, comment)
	if err != nil {
		panic(err)
	}

	insertID, err := result.LastInsertId()
	if err != nil {
		panic(err)
	}

	return insertID
}

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

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

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

✗ go run main.go
Last 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.

func PrepareStatement(ctx context.Context, db *sql.DB) {
	query := "INSERT INTO comments(email,comment) VALUE(?, ?)"
	statement, err := db.PrepareContext(ctx, query)
	if err != nil {
		panic(err)
	}
	defer statement.Close()

	for i := 0; i < 10; i++ {
		email := "Santekno " + strconv.Itoa(i) + "@gmail.com"
		comment := "Komentar ke " + strconv.Itoa(i)
		result, err := statement.ExecContext(ctx, email, comment)
		if err != nil {
			panic(err)
		}
		id, err := result.LastInsertId()
		if err != nil {
			panic(err)
		}
		fmt.Println("comment id ", id)
	}
}

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

func main(){
    ..
    ..
    PrepareStatement(ctx, db)
}

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.

func TransactionDatabase(ctx context.Context, db *sql.DB) {
	tx, err := db.Begin()
	if err != nil {
		panic(err)
	}

	query := "INSERT INTO comments(email,comment) VALUE(?, ?)"

	// do transaction
	for i := 0; i < 10; i++ {
		email := "Santekno " + strconv.Itoa(i) + "@gmail.com"
		comment := "Komentar ke " + strconv.Itoa(i)
		result, err := tx.ExecContext(ctx, query, email, comment)
		if err != nil {
			panic(err)
		}
		id, err := result.LastInsertId()
		if err != nil {
			panic(err)
		}
		fmt.Println("comment id ", id)
	}

	err = tx.Commit()
	if err != nil {
		panic(err)
	}
}

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.