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
servicefolder - Add a file with the file name
init.goand 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 calledrollback. - 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.
How to Overcome Handling Errors in Golang
Hot Articles
Understanding Kubernetes Services
03 Mar 2024Understanding Kubernetes Networking
03 Mar 2024Understanding Kubernetes Deployments
03 Mar 2024Understanding PODs Kubernetes
03 Mar 2024Introduction YAML Syntax
03 Mar 2024
Understanding Kubernetes Services
Understanding Kubernetes Networking
Understanding Kubernetes Deployments
Understanding PODs Kubernetes