Chapter 6: Database

1 database/sql Interface

Go's database/sql package provides a generic interface for SQL databases. You need a separate driver for each database (MySQL, PostgreSQL, SQLite, etc.), but the API remains the same across all drivers.

Connecting to MySQL

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql" // blank import registers the driver
)

func main() {
    // DSN format: user:password@tcp(host:port)/dbname?params
    dsn := "root:password@tcp(127.0.0.1:3306)/mydb?charset=utf8mb4&parseTime=true"

    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal("Failed to open database:", err)
    }
    defer db.Close()

    // sql.Open doesn't actually connect — Ping verifies connectivity
    if err := db.Ping(); err != nil {
        log.Fatal("Failed to connect:", err)
    }
    fmt.Println("Connected to MySQL!")
}

2 CRUD Operations

The three main query methods: db.Query for multiple rows, db.QueryRow for a single row, and db.Exec for inserts, updates, and deletes.

Define the Model

type User struct {
    ID        int
    Name      string
    Email     string
    CreatedAt time.Time
}

INSERT — db.Exec

func createUser(db *sql.DB, name, email string) (int64, error) {
    result, err := db.Exec(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        name, email,
    )
    if err != nil {
        return 0, err
    }
    return result.LastInsertId()
}

// Usage
id, err := createUser(db, "Alice", "alice@example.com")
if err != nil {
    log.Fatal(err)
}
fmt.Printf("Inserted user with ID: %d\n", id)

SELECT Single Row — db.QueryRow

func getUserByID(db *sql.DB, id int) (User, error) {
    var user User
    err := db.QueryRow(
        "SELECT id, name, email, created_at FROM users WHERE id = ?", id,
    ).Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt)

    if err == sql.ErrNoRows {
        return user, fmt.Errorf("user %d not found", id)
    }
    return user, err
}

SELECT Multiple Rows — db.Query

func listUsers(db *sql.DB) ([]User, error) {
    rows, err := db.Query("SELECT id, name, email, created_at FROM users")
    if err != nil {
        return nil, err
    }
    defer rows.Close() // always close rows!

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt); err != nil {
            return nil, err
        }
        users = append(users, u)
    }

    // Check for errors from iterating over rows
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return users, nil
}

UPDATE & DELETE — db.Exec

func updateUser(db *sql.DB, id int, name string) error {
    result, err := db.Exec("UPDATE users SET name = ? WHERE id = ?", name, id)
    if err != nil {
        return err
    }
    rows, _ := result.RowsAffected()
    if rows == 0 {
        return fmt.Errorf("user %d not found", id)
    }
    return nil
}

func deleteUser(db *sql.DB, id int) error {
    result, err := db.Exec("DELETE FROM users WHERE id = ?", id)
    if err != nil {
        return err
    }
    rows, _ := result.RowsAffected()
    if rows == 0 {
        return fmt.Errorf("user %d not found", id)
    }
    return nil
}

3 Prepared Statements

Prepared statements pre-compile SQL on the server side. They improve performance when executing the same query many times and prevent SQL injection by separating SQL logic from data.

func batchInsertUsers(db *sql.DB, users []User) error {
    stmt, err := db.Prepare("INSERT INTO users (name, email) VALUES (?, ?)")
    if err != nil {
        return err
    }
    defer stmt.Close()

    for _, u := range users {
        _, err := stmt.Exec(u.Name, u.Email)
        if err != nil {
            return fmt.Errorf("failed to insert user %s: %w", u.Name, err)
        }
    }
    return nil
}

func findUsersByAge(db *sql.DB, minAge, maxAge int) ([]User, error) {
    stmt, err := db.Prepare(
        "SELECT id, name, email, created_at FROM users WHERE age BETWEEN ? AND ?",
    )
    if err != nil {
        return nil, err
    }
    defer stmt.Close()

    rows, err := stmt.Query(minAge, maxAge)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt); err != nil {
            return nil, err
        }
        users = append(users, u)
    }
    return users, rows.Err()
}

query := "SELECT * FROM users WHERE name = '" + name + "'"
db.Query(query)

db.Query("SELECT * FROM users WHERE name = ?", name)

4 Transactions

Transactions group multiple operations into an atomic unit — either all succeed or all are rolled back. Go uses db.Begin() to start a transaction, then tx.Commit() or tx.Rollback() to finish it.

func transferFunds(db *sql.DB, fromID, toID int, amount float64) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    // defer Rollback is safe — it's a no-op after Commit
    defer tx.Rollback()

    // Check sender's balance
    var balance float64
    err = tx.QueryRow("SELECT balance FROM accounts WHERE id = ?", fromID).Scan(&balance)
    if err != nil {
        return fmt.Errorf("query sender: %w", err)
    }
    if balance < amount {
        return fmt.Errorf("insufficient funds: have %.2f, need %.2f", balance, amount)
    }

    // Deduct from sender
    _, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromID)
    if err != nil {
        return fmt.Errorf("deduct from sender: %w", err)
    }

    // Add to receiver
    _, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toID)
    if err != nil {
        return fmt.Errorf("add to receiver: %w", err)
    }

    // Log the transfer
    _, err = tx.Exec(
        "INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)",
        fromID, toID, amount,
    )
    if err != nil {
        return fmt.Errorf("log transfer: %w", err)
    }

    return tx.Commit()
}

Generic Transaction Helper

func withTransaction(db *sql.DB, fn func(tx *sql.Tx) error) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    if err := fn(tx); err != nil {
        return err
    }
    return tx.Commit()
}

// Usage
err := withTransaction(db, func(tx *sql.Tx) error {
    _, err := tx.Exec("INSERT INTO orders (user_id, total) VALUES (?, ?)", userID, total)
    if err != nil {
        return err
    }
    _, err = tx.Exec("UPDATE inventory SET stock = stock - ? WHERE product_id = ?", qty, productID)
    return err
})

5 Connection Pool & Best Practices

sql.DB manages a pool of connections automatically. Proper configuration prevents resource exhaustion and stale connections.

func setupDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        return nil, err
    }

    // Maximum number of open connections (default: unlimited)
    db.SetMaxOpenConns(25)

    // Maximum number of idle connections in the pool
    db.SetMaxIdleConns(10)

    // Maximum lifetime of a connection (recycle stale connections)
    db.SetConnMaxLifetime(5 * time.Minute)

    // Maximum time a connection can be idle before being closed
    db.SetConnMaxIdleTime(3 * time.Minute)

    if err := db.Ping(); err != nil {
        return nil, err
    }
    return db, nil
}

6 Chapter Summary