How to Communication Golang with MySQL Database
Package atau Library
1import "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.
1$ mkdir mysql-native
2$ cd mysql-native
3$ go mod init github.com/santekno/mysql-nativeAdd 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.
1➜ mysql-native git:(main) ✗ go get github.com/go-sql-driver/mysql
2go: downloading github.com/go-sql-driver/mysql v1.6.0
3go get: added github.com/go-sql-driver/mysql v1.6.0go.mod if there is a dependency like this, it means we have installed the dependency.1➜ mysql-native git:(main) ✗ cat go.mod
2module github.com/santekno/mysql-native
3
4go 1.17
5
6require github.com/go-sql-driver/mysql v1.6.0 // indirectvendor/ folder.1➜ mysql-native git:(main) ✗ go mod vendorProgram 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.
1cfg := mysql.Config{
2 User: os.Getenv("DBUSER"),
3 Passwd: os.Getenv("DBPASS"),
4 Net: "tcp",
5 Addr: "127.0.0.1:3306",
6 DBName: "mahasiswa",
7}In the mysql dependency there are several more complete configurations that we can see in the documentation for this dependency.
1type Config struct {
2 User string // Username
3 Passwd string // Password (requires User)
4 Net string // Network type
5 Addr string // Network address (requires Net)
6 DBName string // Database name
7 Params map[string]string // Connection parameters
8 Collation string // Connection collation
9 Loc *time.Location // Location for time.Time values
10 MaxAllowedPacket int // Max packet size allowed
11 ServerPubKey string // Server public key name
12 pubKey *rsa.PublicKey // Server public key
13 TLSConfig string // TLS configuration name
14 tls *tls.Config // TLS configuration
15 Timeout time.Duration // Dial timeout
16 ReadTimeout time.Duration // I/O read timeout
17 WriteTimeout time.Duration // I/O write timeout
18
19 AllowAllFiles bool // Allow all files to be used with LOAD DATA LOCAL INFILE
20 AllowCleartextPasswords bool // Allows the cleartext client side plugin
21 AllowNativePasswords bool // Allows the native password authentication method
22 AllowOldPasswords bool // Allows the old insecure password method
23 CheckConnLiveness bool // Check connections for liveness before using them
24 ClientFoundRows bool // Return number of matching rows instead of rows changed
25 ColumnsWithAlias bool // Prepend table alias to column names
26 InterpolateParams bool // Interpolate placeholders into query string
27 MultiStatements bool // Allow multiple statements in one query
28 ParseTime bool // Parse time values to time.Time
29 RejectReadOnly bool // Reject read-only connections
30}| 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.
1DBUSER=<username-database>
2DBPASS=<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.
1var err error
2db, err = sql.Open("mysql", cfg.FormatDSN())
3if err != nil {
4 log.Fatal(err)
5}
6
7pingErr := db.Ping()
8if pingErr != nil {
9 log.Fatal(pingErr)
10}
11fmt.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.1package services 2 3import "database/sql" 4 5type Services struct { 6 db *sql.DB 7} 8 9func InitServices(db *sql.DB) Services { 10 return Services{ 11 db: db, 12 } 13}
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.
1 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.
1func (s *Services) GetAllMahasiswa() ([]Mahasiswa, error) {
2 var mahasiswas []Mahasiswa
3
4 rows, err := s.db.Query("SELECT id, nama, jenis_kelamin, tempat_lahir, tanggal_lahir, tahun_masuk FROM mahasiswa")
5 if err != nil {
6 return nil, fmt.Errorf("failed get all mahasiswa %v", err)
7 }
8
9 defer rows.Close()
10
11 for rows.Next() {
12 var mhs Mahasiswa
13 if err := rows.Scan(&mhs.ID, &mhs.Nama, &mhs.JenisKelamin, &mhs.TempatLahir, &mhs.TanggalLahir, &mhs.TahunMasuk); err != nil {
14 return nil, fmt.Errorf("failed get all mahasiswa %v", err)
15 }
16 mahasiswas = append(mahasiswas, mhs)
17 }
18
19 if err := rows.Err(); err != nil {
20 return nil, fmt.Errorf("failed rows: %v", err)
21 }
22
23 return mahasiswas, nil
24}1s.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.
1func (s *Services) GetMahasiswaById(id int64) (Mahasiswa, error) {
2 var mhs Mahasiswa
3
4 row := s.db.QueryRow("SELECT id,nama,nim,jenis_kelamin,tempat_lahir,tanggal_lahir,tahun_masuk FROM mahasiswa WHERE id = ?", id)
5 if err := row.Scan(&mhs.ID, &mhs.Nama, &mhs.NIM, &mhs.JenisKelamin, &mhs.TempatLahir, &mhs.TanggalLahir, &mhs.TahunMasuk); err != nil {
6 if err == sql.ErrNoRows {
7 return mhs, fmt.Errorf("failed get mahasiswa by id %d: no such mahasiswa", id)
8 }
9
10 return mhs, fmt.Errorf("failed get mahasiswa by id %d: %v", id, err)
11 }
12
13 return mhs, nil
14}The difference from get student is that student data is used here
1s.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.
1func (s *Services) AddMahasiswa(mhs Mahasiswa) (int64, int64, error) {
2 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)
3 if err != nil {
4 return 0, 0, fmt.Errorf("failed add mahasiswa: %v", err)
5 }
6 id, err := result.LastInsertId()
7 if err != nil {
8 return 0, 0, fmt.Errorf("failed add mahasiswa: %v", err)
9 }
10
11 sum, err := result.RowsAffected()
12 if err != nil {
13 return 0, 0, fmt.Errorf("error when getting rows affected")
14 }
15
16 return id, sum, nil
17}In the function below
1s.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)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.
1func (s *Services) DeleteMahasiswa(mhsId int64) error {
2 if mhsId == 0 {
3 return errors.New("mahasiswa ID was zero")
4 }
5
6 _, err := s.db.Exec("DELETE FROM mahasiswa WHERE id= ?", mhsId)
7 if err != nil {
8 log.Printf("error execution : %v", err)
9 return err
10 }
11
12 return nil
13}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.
1func (s *Services) BulkInsertUsingTransaction(mahasiswas []Mahasiswa) ([]int64, error) {
2 var insertID []int64
3
4 if len(mahasiswas) == 0 {
5 return insertID, errors.New("mahasiswa record was empty")
6 }
7
8 tx, err := s.db.Begin()
9 if err != nil {
10 return insertID, errors.New("begin mahasiswa transaction error")
11 }
12
13 defer tx.Rollback()
14
15 for _, mhs := range mahasiswas {
16 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)
17 if err != nil {
18 log.Printf("error execution : %v", err)
19 continue
20 }
21
22 lastInsertId, err := result.LastInsertId()
23 if err != nil {
24 log.Printf("error last insert : %v", err)
25 }
26
27 insertID = append(insertID, lastInsertId)
28 }
29
30 err = tx.Commit()
31 if err != nil {
32 log.Printf("error commit : %v", err)
33 return insertID, err
34 }
35
36 return insertID, err
37}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.