====== Go Stdlib Wiki: package database/sql ======

===== 1. Overview =====
database/sql provides a generic SQL database interface in Go. It does NOT talk to a database by itself. Instead, it works together with a database-specific driver (e.g., lib/pq for PostgreSQL, go-sql-driver/mysql for MySQL) that implements the low-level protocol.

generic /dʒəˈnerɪk/ = chung, tổng quát

driver /ˈdraɪvər/ = trình điều khiển (thư viện kết nối DB)

protocol /ˈproʊtəˌkɔːl/ = giao thức

<code go> import "database/sql" </code>

===== 2. What it is for =====
Use database/sql to:

Open/manage database connections via a driver

Run SQL queries (Query, QueryRow) and commands (Exec)

Use prepared statements (Prepare)

Control transactions (Begin, Commit, Rollback)

Configure connection pooling (max open/idle, lifetime)

transaction /trænˈzækʃən/ = giao dịch

prepared statement /prɪˈperd ˈsteɪtmənt/ = câu lệnh chuẩn bị sẵn

connection pool /kəˈnekʃən puːl/ = bể kết nối (tái sử dụng kết nối)

===== 3. What it is NOT for =====

It does NOT provide an ORM (like GORM).

It does NOT include DB drivers.

It does NOT auto-migrate schemas.

If you need higher-level features, you use libraries on top of it.

ORM /ˌɔːrˈɛm/ = Object-Relational Mapping (ánh xạ object–bảng)

===== 4. Core types (what is what) =====

==== 4.1 *sql.DB ====
What it is: a handle representing a pool of connections, safe for concurrent use.
Important: sql.Open does not necessarily connect immediately; use Ping() to verify.

concurrent /kənˈkɝːənt/ = đồng thời

handle /ˈhændl/ = “tay cầm”/đối tượng đại diện

==== 4.2 *sql.Rows / *sql.Row ====

Rows for multiple-row results (Query)

Row for single-row results (QueryRow)

==== 4.3 sql.Result ====
Returned from Exec, used to get:

LastInsertId() (if supported by driver)

RowsAffected()

==== 4.4 *sql.Tx ====
Transaction handle created by Begin/BeginTx.

==== 4.5 *sql.Stmt ====
Prepared statement created by Prepare (or db.PrepareContext).

===== 5. Driver relationship (why you need pq etc.) =====
database/sql defines the common API. Drivers register themselves (via database/sql/driver) so database/sql can call them.

Typical usage pattern:

Import driver for its side effects (registration)

Use sql.Open(driverName, dsn)

Example (PostgreSQL driver github.com/lib/pq):
<code go>
import (
"database/sql"
_ "github.com/lib/pq"
)

db, err := sql.Open("postgres", "postgres://user:pass@localhost:5432/dbname?sslmode=disable")
</code>

side effect /saɪd ɪˈfekt/ = tác dụng phụ (ở đây: tự đăng ký driver khi import)

DSN /ˌdiː es ˈɛn/ = Data Source Name (chuỗi cấu hình kết nối)

===== 6. Common recipes =====

==== 6.1 Open and verify connection ====
<code go>
db, err := sql.Open("postgres", dsn)
if err != nil {
return err
}
defer db.Close()

if err := db.Ping(); err != nil {
return err
}
</code>

==== 6.2 Query multiple rows ====
<code go>
rows, err := db.Query("SELECT id, name FROM users WHERE active = $1", true)
if err != nil {
return err
}
defer rows.Close()

for rows.Next() {
var id int64
var name string
if err := rows.Scan(&id, &name); err != nil {
return err
}
}
if err := rows.Err(); err != nil {
return err
}
</code>

scan /skæn/ = quét/đổ dữ liệu từ row vào biến

==== 6.3 Query a single row ====
<code go>
var count int
err := db.QueryRow("SELECT COUNT(*) FROM users").Scan(&count)
</code>

==== 6.4 Exec (INSERT/UPDATE/DELETE) ====
<code go>
res, err := db.Exec("UPDATE users SET active = $1 WHERE id = $2", true, 10)
if err != nil {
return err
}
n, _ := res.RowsAffected()
_ = n
</code>

==== 6.5 Transaction (Tx) ====
<code go>
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback() // safe: no-op if committed

if _, err := tx.Exec("UPDATE accounts SET balance = balance - $1 WHERE id = $2", 100, 1); err != nil {
return err
}
if _, err := tx.Exec("UPDATE accounts SET balance = balance + $1 WHERE id = $2", 100, 2); err != nil {
return err
}

return tx.Commit()
</code>

===== 7. Connection pool settings =====
Tuning (depends on workload/DB limits):

db.SetMaxOpenConns(n)

db.SetMaxIdleConns(n)

db.SetConnMaxLifetime(d)

db.SetConnMaxIdleTime(d)

tuning /ˈtuːnɪŋ/ = tinh chỉnh

workload /ˈwɝːkˌloʊd/ = tải công việc

===== 8. Quick “what is what for” map =====

database/sql — standard, generic SQL API

DB driver (e.g., lib/pq) — actual DB protocol implementation

sql.Open — create *sql.DB (pool handle), not necessarily connect yet

db.Ping — verify connectivity

db.Query / db.QueryRow — SELECT

db.Exec — INSERT/UPDATE/DELETE

db.Begin / tx.Commit / tx.Rollback — transactions

db.SetMaxOpenConns etc. — pool configuration