- database_test.go, file contains to test connection to database
- database.go, file contains the connection to database, so make it reusable in every test or file
- sql_test.go, file contains test about Exec, Query, and Rows SQL
We are going to learn about :
- Introduction Golang Database
- Package Database
- How to Create Connection Database
- Execution SQL Command
- SQL Injection
- Prepare Statement
- Database Transaction
- Go-Lang has a package named database defaultly
- Package database is a package which contains a collection of standard interface which be the standard tool to communicate with database
- This makes our code program we created to access any type of database can use the code base
- The different is only in SQL Code which need to use same like the database we used
Aplikasi >>(call) Database Interface >>(call) Database Driver >>(call) DBMS
- In this subject will be focus on MySQL as DBMS
- Before we create code program which use database in Go-Lang, firstly, we need to add database driver
- Without database driver, database package in Go-Lang will not understand anything, because only contain contract interface
- You can visit for list of driver that you can use
- There is so much module driver that you can use, the best practice to choose which one is the most used one
go get -u github.com/go-sql-driver/mysql
- We can create our program, but firstly you have to import package MySQL
import (
"database/mysql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"testing"
)- Why we use _ because we only need to call init function, why init function? we only want to import package, without to use package manual, so automatically
- The first things that we have to do before we create our application is open connection to database
- To open connection to database in Golang, we can create object sql.DB using function sql.Open(driver, dataSourceName)
- To use MySQL database, we can use driver "mysql"
- For dataSourceName, every database has their own writing to connect to database.
- For MySQL, we can use dataSourceName like this
username:password@tcp(host:port)/database_name
- For MySQL, we can use dataSourceName like this
- If object sql.DB no more used, recommend to close the connection using Close() function to avoid connection league (condition where our application has been used but the connection is still open and running, which will make the database connection will always add that will make our database crashed because of there is so much connection used, because database has maximum connection)
db, err := sql.Open("mysql", "user:password@tcp(host:3306)/dbname")
if err != nil {
panic(err)
}
defer db.Close()- sql.DB in Golang actually is not a connection to database
- But it is a pool to database, or known as Database Pooling concept
- In sql.DB, Golang do management connection to database automatically. This make us not have to manage our connection to database manually
- With this database pooling ability, we can determine minimal and maximal connection created by Golang. So, not make flood to our connection to database, because usually there is maximum connection which handled by database we used
- Why we need to declare minimum connection, because to avoid when suddenly up traffic that we don't know, so will make the connection faster if we determine the minimum as much as we expected
- Why we need to declare maximum connection, because to border the connection when there is bomb traffic, so they have to queue to use the connection
- Method
- (DB) SetMaxIdleConns(number) >> setting how minimal connection created
- (DB) SetMaxOpenConns(number) >> setting how maximal connection created
- (DB) SetConnMaxIdleTime(duration) >> setting how long unused connection will be removed
- (DB) SetConnMaxLifetime(duration) >> setting how long connection might be used
func GetConnection() *sql.DB {
db, err := sql.Open("myql", "root:root@tcp(localhost:8889)/db_golang")
if err != nil {
panic(err)
}
db.SetMaxIdleConns(10)
db.SetMaxOpenConns(100)
db.SetConnMaxIdleTime(5 * time.Minute)
db.SetConnMaxLifetime(60 * time.Minute)
}- When we use database in our application, we must to communicate with database using SQL Command
- In Golang, there is functions that we can use to send SQL command to database using function
(DB) ExecContext(context, sql, params) - When sending SQL Command, we need to send context like we have learn before in course Golang Context, with context, we can send signal cancel if we cancel our SQL command sending
CREATE TABLE customer
(
id VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB;db := GetConnection()
defer db.Close()
ctx := context.Background()
_, err := db.ExecContext(ctx, "INSERT INTO customer(id, name) VALUES ('farhan', 'Farhan);")
if err != nil {
panic(err)
}
fmt.Println("Success Insert Data to Database")- For SQL operation which not need result, we can use Exec command, but if we need result, such as SELECT SQL, we need to use different Function
- Function for querying to database, we can use function
(DB) QueryContext(context, sql, params)
db := GetConnection()
defer db.Close()
ctx := context.Background()
rows, err := db.QueryContext(ctx, "SELECT id, name FROM customer")
if err != nil {
panic(err)
}
defer rows.Close()- Query Result is a data struct sql.Rows
- Rows is used to do iteration to query result
- We can use function
(Rows) Next() (boolean)to do iteration to data query result, if return data false, it means thers is no data again in result - To read every data, we can use
(Rows) Scan(columns...) - Do not forget, after Rows, we have to close the rows
(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)
}
defer rows.Close()