How to Communication Golang with MySQL Database

by Ihsan
5 Feb 2023
9 minutes

Package atau Library

import  "github.com/go-sql-driver/mysql"

Project Initialization

Prepare a new folder with the name mysql-native, then initialize the Golang module to make it more modular. Here’s a quick command.

$ mkdir mysql-native
$ cd mysql-native
$ go mod init github.com/santekno/mysql-native

Add dependency

After the module is created, we also need to add the mysql dependency, which in this tutorial we will use the "github.com/go-sql-driver/mysql" dependency. Add this dependency to this module project using the command below.

➜  mysql-native git:(main) ✗ go get github.com/go-sql-driver/mysql 
go: downloading github.com/go-sql-driver/mysql v1.6.0
go get: added github.com/go-sql-driver/mysql v1.6.0

Look at the file in go.mod if there is a dependency like this, it means we have installed the dependency.

➜  mysql-native git:(main) ✗ cat go.mod   
module github.com/santekno/mysql-native

go 1.17

require github.com/go-sql-driver/mysql v1.6.0 // indirect

Next, do the command below to download the dependency in the vendor/ folder.

➜  mysql-native git:(main) ✗ go mod vendor

Program creation

At this time we will create a program in just one main file but not yet carry out several structural techniques that organize several file folders or what we often call Framework.

This program is simple so we only need 1 file main.go to operate everything we are going to do.

Next, in the main() function we will divide it into several parts, namely as follows.

Initialize Database Connection

At this stage we initialize some of the configurations needed to create a connection to the database. Some of them can be seen as follows.

cfg := mysql.Config{
    User:   os.Getenv("DBUSER"),
    Passwd: os.Getenv("DBPASS"),
    Net:    "tcp",
    Addr:   "127.0.0.1:3306",
    DBName: "mahasiswa",
}

In the mysql dependency there are several more complete configurations that we can see in the documentation for this dependency.

type Config struct {
	User             string            // Username
	Passwd           string            // Password (requires User)
	Net              string            // Network type
	Addr             string            // Network address (requires Net)
	DBName           string            // Database name
	Params           map[string]string // Connection parameters
	Collation        string            // Connection collation
	Loc              *time.Location    // Location for time.Time values
	MaxAllowedPacket int               // Max packet size allowed
	ServerPubKey     string            // Server public key name
	pubKey           *rsa.PublicKey    // Server public key
	TLSConfig        string            // TLS configuration name
	tls              *tls.Config       // TLS configuration
	Timeout          time.Duration     // Dial timeout
	ReadTimeout      time.Duration     // I/O read timeout
	WriteTimeout     time.Duration     // I/O write timeout

	AllowAllFiles           bool // Allow all files to be used with LOAD DATA LOCAL INFILE
	AllowCleartextPasswords bool // Allows the cleartext client side plugin
	AllowNativePasswords    bool // Allows the native password authentication method
	AllowOldPasswords       bool // Allows the old insecure password method
	CheckConnLiveness       bool // Check connections for liveness before using them
	ClientFoundRows         bool // Return number of matching rows instead of rows changed
	ColumnsWithAlias        bool // Prepend table alias to column names
	InterpolateParams       bool // Interpolate placeholders into query string
	MultiStatements         bool // Allow multiple statements in one query
	ParseTime               bool // Parse time values to time.Time
	RejectReadOnly          bool // Reject read-only connections
}

In this program we only carry out basic configurations including:

Configuration	Information
`User`	user connection required to access the mysql database
`Passwd`	password for the user required to connect to the mysql database
`Net`	protocol used for connection to the database
`Addr`	address indicating the server of the database
`DBName`	name of the target database

Create .env file

We see that this database connection requires user and password which will be taken from the environment. Judging from the function calls os.Getenv("DBUSER") and os.Getenv("DBPASS") which function to retrieve variables from the environment to get the user and password from the database to be connected.

At this time we are discussing, to get this environment it is usually used to separate several global variables which are needed so that they are more configurable if we are already running the program live.

How to? We need to create a new file .env then fill the file with code like this.

DBUSER=<username-database>
DBPASS=<password-database>

Connection and Ping Checking

We will continue to complete the program using the main() function. After we fill in the configuration required to connect to the mysql database. This is the time we need to call the connection and test whether the connection can work or not.

The following is a program for connecting to a database and testing the connection.

var err error
db, err = sql.Open("mysql", cfg.FormatDSN())
if err != nil {
  log.Fatal(err)
}

pingErr := db.Ping()
if pingErr != nil {
  log.Fatal(pingErr)
}
fmt.Println("Connected!")

The function sql.Open("mysql",cfg.FormatDNS()) is used to connect to the database, if this connection cannot be made then this function also issues an err which we catch so that the program will error because it does not can connect to database.

Then the db.Ping() function is used to ensure that the connection can be used to retrieve, store, and even delete data into the database.

Initialize Service Package

At this stage we will create a service folder to separate all our query logic into one package.

Create a service folder
Add a file with the file name init.go and create the contents of the file as below.

package services

import "database/sql"

type Services struct {
	db *sql.DB
}

func InitServices(db *sql.DB) Services {
	return Services{
		db: db,
	}
}

The purpose of creating this function InitServices is so that we can use the connection that has been initialized in the main process into our services package. So later we just need to create methods and use this db in each method.

Don’t forget that when you have initialized the function, also call the function in the main.go file as below.

	service := services.InitServices(db)

Retrieve data for all students from the database

In the next stage we will create a function to retrieve data from the database, which at this meeting, the database that was available was all student data.

This function will retrieve data from the database and then store it in the form of a struct which we declared previously.

func (s *Services) GetAllMahasiswa() ([]Mahasiswa, error) {
	var mahasiswas []Mahasiswa

	rows, err := s.db.Query("SELECT id, nama, jenis_kelamin, tempat_lahir, tanggal_lahir, tahun_masuk FROM mahasiswa")
	if err != nil {
		return nil, fmt.Errorf("failed get all mahasiswa %v", err)
	}

	defer rows.Close()

	for rows.Next() {
		var mhs Mahasiswa
		if err := rows.Scan(&mhs.ID, &mhs.Nama, &mhs.JenisKelamin, &mhs.TempatLahir, &mhs.TanggalLahir, &mhs.TahunMasuk); err != nil {
			return nil, fmt.Errorf("failed get all mahasiswa %v", err)
		}
		mahasiswas = append(mahasiswas, mhs)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("failed rows: %v", err)
	}

	return mahasiswas, nil
}

The function is used to translate the query and retrieve data from the database according to the operation.

s.db.Query("SELECT id, name, gender, place of birth, date of birth, year of entry FROM students")

The rows.Next() function is used to fetch data from the rows variable and then translate it into the Student struct. We also need to do defer rows.Close() so that every connection declared at the end of execution must be closed to avoid max connection entering the database.

Next, don’t forget to check whether err and rows.Err() have errors so that we know whether the query has an error or not.

Retrieve student data by ID

The same as taking student data by ID above but the only difference is the return from this function. The following is a function to retrieve data.

func (s *Services) GetMahasiswaById(id int64) (Mahasiswa, error) {
	var mhs Mahasiswa

	row := s.db.QueryRow("SELECT id,nama,nim,jenis_kelamin,tempat_lahir,tanggal_lahir,tahun_masuk FROM mahasiswa WHERE id = ?", id)
	if err := row.Scan(&mhs.ID, &mhs.Nama, &mhs.NIM, &mhs.JenisKelamin, &mhs.TempatLahir, &mhs.TanggalLahir, &mhs.TahunMasuk); err != nil {
		if err == sql.ErrNoRows {
			return mhs, fmt.Errorf("failed get mahasiswa by id %d: no such mahasiswa", id)
		}

		return mhs, fmt.Errorf("failed get mahasiswa by id %d: %v", id, err)
	}

	return mhs, nil
}

The difference from get student is that student data is used here

s.db.QueryRow("SELECT id,nama,nim,jenis_kelamin,tempat_lahir,tanggal_lahir,tahun_masuk FROM mahasiswa WHERE id = ?", id)

where this function returns data for only one row.

In this function we also find sql.ErrNoRows used to check and ensure that the data retrieved is not empty.

Adding Students

Next we will add students to the database. The following is a function to save data into a database.

func (s *Services) AddMahasiswa(mhs Mahasiswa) (int64, int64, error) {
	result, err := s.db.Exec("INSERT INTO mahasiswa (nama,nim, jenis_kelamin, tempat_lahir, tanggal_lahir, tahun_masuk) VALUES (?, ?, ?, ?, ?, ?)", mhs.Nama, mhs.NIM, mhs.JenisKelamin, mhs.TempatLahir, mhs.TanggalLahir, mhs.TahunMasuk)
	if err != nil {
		return 0, 0, fmt.Errorf("failed add mahasiswa: %v", err)
	}
	id, err := result.LastInsertId()
	if err != nil {
		return 0, 0, fmt.Errorf("failed add mahasiswa: %v", err)
	}

	sum, err := result.RowsAffected()
	if err != nil {
		return 0, 0, fmt.Errorf("error when getting rows affected")
	}

	return id, sum, nil
}

In the function below

s.db.Exec("INSERT INTO mahasiswa (nama,nim, jenis_kelamin, tempat_lahir, tanggal_lahir, tahun_masuk) VALUES (?, ?, ?, ?, ?, ?)", mhs.Nama, mhs.NIM, mhs.JenisKelamin, mhs.TempatLahir, mhs.TanggalLahir, mhs.TahunMasuk)

functions to perform insert queries into the database so that the data sent can be stored in the database.

Deleting a Student

Next we will delete the student from the database. The following is a function to delete data into the database.

func (s *Services) DeleteMahasiswa(mhsId int64) error {
	if mhsId == 0 {
		return errors.New("mahasiswa ID was zero")
	}

	_, err := s.db.Exec("DELETE FROM mahasiswa WHERE id= ?", mhsId)
	if err != nil {
		log.Printf("error execution : %v", err)
		return err
	}

	return nil
}

The command to delete student data in the database is the same as adding, namely using the s.db.Exec function, the only difference is that the query used is DELETE FROM student WHERE id=?.

Add Students using Transaction Batching

Usually, sometimes we need operations to store student data in bulk (a lot at once) to save time when filling in data compared to filling in student data one by one. So we need to create a method that can support batching student data into a database. Here’s how we create a special method for batching.

func (s *Services) BulkInsertUsingTransaction(mahasiswas []Mahasiswa) ([]int64, error) {
	var insertID []int64

	if len(mahasiswas) == 0 {
		return insertID, errors.New("mahasiswa record was empty")
	}

	tx, err := s.db.Begin()
	if err != nil {
		return insertID, errors.New("begin mahasiswa transaction error")
	}

	defer tx.Rollback()

	for _, mhs := range mahasiswas {
		result, err := tx.Exec("INSERT INTO mahasiswa (nama, nim, jenis_kelamin, tempat_lahir, tanggal_lahir, tahun_masuk) VALUES (?, ?, ?, ?, ?, ?)", mhs.Nama, mhs.NIM, mhs.JenisKelamin, mhs.TempatLahir, mhs.TanggalLahir, mhs.TahunMasuk)
		if err != nil {
			log.Printf("error execution : %v", err)
			continue
		}

		lastInsertId, err := result.LastInsertId()
		if err != nil {
			log.Printf("error last insert : %v", err)
		}

		insertID = append(insertID, lastInsertId)
	}

	err = tx.Commit()
	if err != nil {
		log.Printf("error commit : %v", err)
		return insertID, err
	}

	return insertID, err
}

There are several notes when we use the transaction database, including:

At the beginning of the method using s.db.Begin() this is intended for us to initialize the transaction process into the database where at this time we allocate a special database connection for this transaction.
The use of defer tx.Rollback() is used so that when there is data in the middle or in certain parts of the data there is an error so that the data does not enter the database, then each transaction will be rolled back to the original or usually called rollback.
The use of tx.Commit() is used to end all transaction processes in the database so that all data will be immediately saved into the database.

Do we understand how to operate everything and communicate the data into the database? Hopefully friends can understand everything that has been explained in stages in this tutorial.

Usage Channel Select Range Timeout

How To Communication Golang with Postgres Database

comments powered by Disqus

Hot Topics

programming

pemrograman

tutorial

tips-dan-trik

cloud native

kubernetes

trick-and-tips

Hot Articles

Gambar How to Change Your Full Name and Username on a MacBook Pro: A Complete Guide