>_
GolangStepByStep
Software Engineer

Data Access

SQL patterns, transactions, connection pools, migrations

# Accessing the Data Vault

Imagine your database is an incredibly secure, massive Vault at a bank.

Your software developers (Goroutines) are not allowed to walk directly inside the vault. They must approach a Teller (a Connection), hand the Teller an envelope with specific instructions (a Query), and wait for the Teller to bring their data back to them.

Go provides a built-in standard library to manage all these Tellers natively: database/sql.

# Level 1: Connecting and Querying (Beginner)

Because Go compiles to a static binary, the database/sql package is just an empty interface. To use it, you must "import" a driver for your specific database (like Postgres or MySQL). You typically use an underscore _ to anonymously import it so the package runs its initialization hooks without your code directly calling its functions.

import (
    "database/sql"
    _ "github.com/lib/pq" // The Postgres driver
    "log"
)

func main() {
    // 1. "Open" creates the pool, but doesn't actually test the connection yet!
    db, err := sql.Open("postgres", "postgres://user:pass@localhost/mydb?sslmode=disable")
    if err != nil { log.Fatal(err) }
    
    // 2. "Ping" forces a connection to test if the database is actually online
    if err := db.Ping(); err != nil { log.Fatal("Cannot reach DB!") }
    
    // 3. Executing a basic insert. Exec() is used when you DO NOT expect rows returned.
    _, err = db.Exec("INSERT INTO users (name) VALUES ($1)", "Alice")
}

$1 Parameterized Security: Notice we didn't use string replacement like `INSERT ... VALUES (" + name + ")"`. String replacement allows users to run SQL Injections. Passing the variable via `$1` securely isolates the data from the SQL command.

# Level 2: Fetching Data & Reading Rows (Intermediate)

If db.Exec() is for Writes, how do we Read? Go splits this into two functions: QueryRow (expecting 1 result) and Query (expecting many results).

// A. Fetching a Single User
var age int
err := db.QueryRow("SELECT age FROM users WHERE name = $1", "Alice").Scan(&age)

if err == sql.ErrNoRows {
    fmt.Println("Alice does not exist.")
}

// B. Fetching Multiple Users
rows, err := db.Query("SELECT name, age FROM users WHERE age > $1", 20)
if err != nil { log.Fatal(err) }
defer rows.Close() // CRITICAL: You MUST close rows, or you leak connections!

for rows.Next() {
    var n string
    var a int
    // Scan copies the current row's columns into our variables
    if err := rows.Scan(&n, &a); err != nil { log.Fatal(err) }
    fmt.Printf("%s is %d years old\n", n, a)
}

When rows.Close() runs, the database connection is cleaned up and handed back to the Connection Pool. If you forget to close it, that "Teller" stands there idle forever, waiting for you to finish reading!

# Level 3: Database Transactions (Advanced)

What off we want to transfer $100 from Alice to Bob?

If we deduct $100 from Alice, but then our server crashes *before* giving the $100 to Bob, the money vanishes into thin air. We need Transactions. A transaction groups multiple operations together. It explicitly states: "Either EVERY query completely succeeds, or ALL of them completely fail."

// db.Begin() opens a dedicated, locked transaction line
tx, err := db.Begin()
if err != nil { return err }

// Safety Net: If anything below panics, the defer guarantees 
// the transaction is abandoned (Rolled Back).
defer tx.Rollback()

// NOTE: We run Exec on 'tx', NOT on 'db'!
_, err = tx.Exec("UPDATE balance SET amount = amount - 100 WHERE user = $1", "Alice")
if err != nil { return err } // The defer catches the rollback!

_, err = tx.Exec("UPDATE balance SET amount = amount + 100 WHERE user = $1", "Bob")
if err != nil { return err } 

// If we reach the end without errors, Commit makes it permanent!
err = tx.Commit()
return err

# Level 4: Connection Pools and Tuning (Expert)

The biggest mistake developers make in Go is treating *sql.DB like it is a single TCP socket connection. It is not. It is a highly concurrency-optimized Connection Pool Supervisor.

If you have 10,000 Goroutines trying to query the database simultaneously, the connection pool prevents your database from melting down by enforcing firm physical limits.

db, _ := sql.Open("postgres", dbURL)

// 1. Don't let more than 50 connections open at once. 
// If routine 51 shows up, they gracefully wait in line.
db.SetMaxOpenConns(50)

// 2. After a traffic spike drops, keep 10 connections alive 
// and warm to quickly handle the next wave.
db.SetMaxIdleConns(10)

// 3. Kill and recreate connections after 5 minutes to avoid
// frustrating stale firewall/haproxy packet timeouts.
db.SetConnMaxLifetime(5 * time.Minute)

By dialing these three settings in, your Go server can sit comfortably in front of extreme load, maintaining perfectly stable database CPU and Ram utilization. No ORM magic required!

practice & review