Database Operations

Lesson 27: Database Operations

Real-World Analogy

Imagine you run a library —

The database is the library's warehouse, where all books (data) are neatly stored on shelves.
The database/sql interface is like the librarian's manual, specifying standard procedures for "how to shelve books, how to find books, how to lend books" — whether the warehouse uses wooden shelves (SQLite) or steel shelves (MySQL), the manual works universally.
Drivers are specific keys for different warehouses — the SQLite key opens a local small warehouse, the MySQL key opens a remote large warehouse.
Prepared statements are like pre-printed borrowing forms — you only need to fill in the name and book title each time, making it both fast and secure.
Transactions are like atomic "borrow then return" operations — either all complete or all roll back, avoiding the embarrassment of "borrowed but not recorded."

After this lesson, you'll be able to work with databases confidently using Go.

Core Concepts

Concept	Description
`database/sql`	Go standard library, defines a unified database operation interface
Driver	A third-party package implementing the `database/sql/driver` interface
`sql.Open()`	Creates a database connection pool (doesn't immediately establish connections)
`sql.DB`	Database connection pool object, thread-safe
`Query` / `QueryRow`	Executes query statements, returns result sets
`Exec`	Executes non-query statements (INSERT/UPDATE/DELETE)
`Prepare`	Prepared statements, improves repeated execution performance
`Tx`	Transaction object, supports Commit and Rollback
Connection pool	Reuses connections, avoiding the overhead of frequently opening/closing connections

Basic Syntax and Usage

1. Importing Drivers

package main

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3" // SQLite driver (anonymous import, only registers the driver)
)

💡 Tip: Drivers must be anonymously imported with _, because they only need to execute the init() function to register themselves with database/sql — we don't directly call driver functions.

2. Opening a Database

db, err := sql.Open("sqlite3", "./mydb.db")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

// Verify the connection is available
if err = db.Ping(); err != nil {
    log.Fatal("Cannot connect to database:", err)
}

💡 Tip: sql.Open() doesn't immediately establish connections — it only initializes the connection pool configuration. Use Ping() to actually attempt a connection.

3. Creating Tables

createTableSQL := `
CREATE TABLE IF NOT EXISTS users (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    name  TEXT    NOT NULL,
    email TEXT    NOT NULL UNIQUE,
    age   INTEGER DEFAULT 0
);`
_, err = db.Exec(createTableSQL)
if err != nil {
    log.Fatal("Failed to create table:", err)
}

4. CRUD Operations

// ---- INSERT ----
result, err := db.Exec("INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
    "Alice", "alice@example.com", 25)
if err != nil {
    log.Fatal(err)
}
id, _ := result.LastInsertId() // Get auto-increment ID
fmt.Println("Inserted successfully, ID:", id)

// ---- SELECT ----
var name, email string
var age int
err = db.QueryRow("SELECT name, email, age FROM users WHERE id = ?", id).
    Scan(&name, &email, &age)
if err != nil {
    log.Fatal(err)
}
fmt.Printf("Query result: %s, %s, %d years old\n", name, email, age)

// ---- UPDATE ----
_, err = db.Exec("UPDATE users SET age = ? WHERE id = ?", 26, id)
if err != nil {
    log.Fatal(err)
}

// ---- DELETE ----
_, err = db.Exec("DELETE FROM users WHERE id = ?", id)
if err != nil {
    log.Fatal(err)
}

5. Prepared Statements

stmt, err := db.Prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()

// The same stmt can be reused multiple times for better performance
stmt.Exec("Bob", "bob@example.com", 30)
stmt.Exec("Charlie", "charlie@example.com", 28)

💡 Tip: Prepared statements both improve performance (the database only parses the SQL once) and prevent SQL injection.

6. Transaction Handling

tx, err := db.Begin()
if err != nil {
    log.Fatal(err)
}

// Execute multiple statements within a transaction
_, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", 100, 1)
if err != nil {
    tx.Rollback() // Rollback on error
    log.Fatal(err)
}

_, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", 100, 2)
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}

if err = tx.Commit(); err != nil { // Commit on success
    log.Fatal(err)
}
fmt.Println("Transaction committed successfully")

7. Connection Pool Configuration

db.SetMaxOpenConns(25)                 // Maximum open connections
db.SetMaxIdleConns(10)                 // Maximum idle connections
db.SetConnMaxLifetime(5 * time.Minute) // Maximum connection lifetime
db.SetConnMaxIdleTime(3 * time.Minute) // Maximum idle connection lifetime

💡 Tip: Proper connection pool configuration can significantly improve performance in high-concurrency scenarios. Setting MaxOpenConns too low causes request queuing; setting it too high exhausts database resources.

Hands-On Practice

Example: SQLite User Management (Difficulty ⭐)

A complete user management example demonstrating basic CRUD operations.

package main

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

    _ "github.com/mattn/go-sqlite3" // SQLite driver
)

// User structure
type User struct {
    ID    int
    Name  string
    Email string
    Age   int
}

func main() {
    // Open (or create) SQLite database
    db, err := sql.Open("sqlite3", "./users.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create table
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS users (
            id    INTEGER PRIMARY KEY AUTOINCREMENT,
            name  TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            age   INTEGER DEFAULT 0
        )
    `)
    if err != nil {
        log.Fatal("Failed to create table:", err)
    }

    // Insert user
    result, err := db.Exec(
        "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
        "Alice", "alice@example.com", 25,
    )
    if err != nil {
        log.Fatal("Insert failed:", err)
    }
    id, _ := result.LastInsertId()
    fmt.Printf("User inserted successfully, ID: %d\n", id)

    // Query single user
    var u User
    err = db.QueryRow("SELECT id, name, email, age FROM users WHERE id = ?", id).
        Scan(&u.ID, &u.Name, &u.Email, &u.Age)
    if err != nil {
        log.Fatal("Query failed:", err)
    }
    fmt.Printf("Query result: %+v\n", u)

    // Update user age
    _, err = db.Exec("UPDATE users SET age = ? WHERE id = ?", 26, id)
    if err != nil {
        log.Fatal("Update failed:", err)
    }
    fmt.Println("Updated successfully")

    // Query all users
    rows, err := db.Query("SELECT id, name, email, age FROM users")
    if err != nil {
        log.Fatal("Query all failed:", err)
    }
    defer rows.Close()

    fmt.Println("\nAll users:")
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.Age); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("  ID:%d  Name:%s  Email:%s  Age:%d\n",
            user.ID, user.Name, user.Email, user.Age)
    }

    // Delete user
    _, err = db.Exec("DELETE FROM users WHERE id = ?", id)
    if err != nil {
        log.Fatal("Delete failed:", err)
    }
    fmt.Println("User deleted successfully")
}

▶ Try it Yourself

TEXT

User inserted successfully, ID: 1
Query result: {ID:1 Name:Alice Email:alice@example.com Age:25}
Updated successfully

All users:
  ID:1  Name:Alice  Email:alice@example.com  Age:26
User deleted successfully

Example: CRUD Encapsulation + Prepared Statements (Difficulty ⭐⭐)

Encapsulates database operations as struct methods, using prepared statements to improve performance.

package main

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

    _ "github.com/mattn/go-sqlite3"
)

// User model
type User struct {
    ID    int
    Name  string
    Email string
    Age   int
}

// UserDAO user data access object
type UserDAO struct {
    db         *sql.DB
    insertStmt *sql.Stmt
    getByID    *sql.Stmt
    updateStmt *sql.Stmt
    deleteStmt *sql.Stmt
}

// NewUserDAO creates a DAO and initializes prepared statements
func NewUserDAO(db *sql.DB) (*UserDAO, error) {
    dao := &UserDAO{db: db}

    // Initialize table
    _, err := db.Exec(`
        CREATE TABLE IF NOT EXISTS users (
            id    INTEGER PRIMARY KEY AUTOINCREMENT,
            name  TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            age   INTEGER DEFAULT 0
        )
    `)
    if err != nil {
        return nil, err
    }

    // Prepare all commonly used statements
    dao.insertStmt, err = db.Prepare(
        "INSERT INTO users (name, email, age) VALUES (?, ?, ?)")
    if err != nil {
        return nil, err
    }

    dao.getByID, err = db.Prepare(
        "SELECT id, name, email, age FROM users WHERE id = ?")
    if err != nil {
        return nil, err
    }

    dao.updateStmt, err = db.Prepare(
        "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?")
    if err != nil {
        return nil, err
    }

    dao.deleteStmt, err = db.Prepare(
        "DELETE FROM users WHERE id = ?")
    if err != nil {
        return nil, err
    }

    return dao, nil
}

// Close closes all prepared statements
func (d *UserDAO) Close() {
    d.insertStmt.Close()
    d.getByID.Close()
    d.updateStmt.Close()
    d.deleteStmt.Close()
}

// Create inserts a new user
func (d *UserDAO) Create(u *User) error {
    result, err := d.insertStmt.Exec(u.Name, u.Email, u.Age)
    if err != nil {
        return err
    }
    id, err := result.LastInsertId()
    if err != nil {
        return err
    }
    u.ID = int(id)
    return nil
}

// GetByID queries a user by ID
func (d *UserDAO) GetByID(id int) (*User, error) {
    u := &User{}
    err := d.getByID.QueryRow(id).Scan(&u.ID, &u.Name, &u.Email, &u.Age)
    if err != nil {
        return nil, err
    }
    return u, nil
}

// Update updates user information
func (d *UserDAO) Update(u *User) error {
    _, err := d.updateStmt.Exec(u.Name, u.Email, u.Age, u.ID)
    return err
}

// Delete deletes a user
func (d *UserDAO) Delete(id int) error {
    _, err := d.deleteStmt.Exec(id)
    return err
}

// ListAll queries all users
func (d *UserDAO) ListAll() ([]User, error) {
    rows, err := d.db.Query("SELECT id, name, email, age FROM users")
    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.Age); err != nil {
            return nil, err
        }
        users = append(users, u)
    }
    return users, rows.Err()
}

func main() {
    db, err := sql.Open("sqlite3", ":memory:") // In-memory database for demonstration
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    dao, err := NewUserDAO(db)
    if err != nil {
        log.Fatal(err)
    }
    defer dao.Close()

    // Batch insert
    users := []User{
        {Name: "Alice", Email: "alice@example.com", Age: 25},
        {Name: "Bob", Email: "bob@example.com", Age: 30},
        {Name: "Charlie", Email: "charlie@example.com", Age: 28},
    }

    for i := range users {
        if err := dao.Create(&users[i]); err != nil {
            log.Printf("Insert failed: %v", err)
            continue
        }
        fmt.Printf("Inserted: %s (ID=%d)\n", users[i].Name, users[i].ID)
    }

    // Query and modify
    u, err := dao.GetByID(1)
    if err != nil {
        log.Fatal("Query failed:", err)
    }
    fmt.Printf("\nQuery ID=1: %+v\n", u)

    u.Age = 26
    if err := dao.Update(u); err != nil {
        log.Fatal("Update failed:", err)
    }
    fmt.Printf("After update: %+v\n", u)

    // List all
    all, _ := dao.ListAll()
    fmt.Println("\nAll users:")
    for _, user := range all {
        fmt.Printf("  [%d] %s | %s | %d years old\n",
            user.ID, user.Name, user.Email, user.Age)
    }

    // Delete
    dao.Delete(2)
    fmt.Println("\nAfter deleting ID=2:")
    all, _ = dao.ListAll()
    for _, user := range all {
        fmt.Printf("  [%d] %s | %s | %d years old\n",
            user.ID, user.Name, user.Email, user.Age)
    }
}

▶ Try it Yourself

TEXT

Inserted: Alice (ID=1)
Inserted: Bob (ID=2)
Inserted: Charlie (ID=3)

Query ID=1: {ID:1 Name:Alice Email:alice@example.com Age:25}
After update: {ID:1 Name:Alice Email:alice@example.com Age:26}

All users:
  [1] Alice | alice@example.com | 26 years old
  [2] Bob | bob@example.com | 30 years old
  [3] Charlie | charlie@example.com | 28 years old

After deleting ID=2:
  [1] Alice | alice@example.com | 26 years old
  [3] Charlie | charlie@example.com | 28 years old

Example: Transaction Transfer + Connection Pool Configuration (Difficulty ⭐⭐⭐)

Simulates a bank transfer scenario, demonstrating transaction atomicity guarantees and connection pool configuration.

package main

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

    _ "github.com/mattn/go-sqlite3"
)

// Account bank account
type Account struct {
    ID      int
    Name    string
    Balance float64
}

func initDB(db *sql.DB) error {
    // Create table
    _, err := db.Exec(`
        CREATE TABLE IF NOT EXISTS accounts (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            name    TEXT    NOT NULL,
            balance REAL    NOT NULL DEFAULT 0
        )
    `)
    return err
}

// Transfer performs a transfer (transaction operation)
// Transfers amount from fromID to toID
func Transfer(db *sql.DB, fromID, toID int, amount float64) error {
    // Begin transaction
    tx, err := db.Begin()
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }

    // Ensure transaction either commits or rolls back
    defer func() {
        if err != nil {
            tx.Rollback()
        }
    }()

    // Check source account balance
    var fromBalance float64
    err = tx.QueryRow("SELECT balance FROM accounts WHERE id = ?", fromID).Scan(&fromBalance)
    if err != nil {
        return fmt.Errorf("failed to query source account: %w", err)
    }

    if fromBalance < amount {
        return fmt.Errorf("insufficient balance: current %.2f, required %.2f", fromBalance, amount)
    }

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

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

    // Simulate business logic: record operation log (in the same transaction)
    _, err = tx.Exec(
        "INSERT INTO transfer_log (from_id, to_id, amount, created_at) VALUES (?, ?, ?, ?)",
        fromID, toID, amount, time.Now().Format(time.RFC3339),
    )
    if err != nil {
        // If log table doesn't exist, ignore error (for demonstration only)
        fmt.Printf("  [Warning] Transfer log recording failed: %v\n", err)
    }

    // Commit transaction
    if err = tx.Commit(); err != nil {
        return fmt.Errorf("failed to commit transaction: %w", err)
    }

    return nil
}

// PrintBalances prints all account balances
func PrintBalances(db *sql.DB) {
    rows, err := db.Query("SELECT id, name, balance FROM accounts ORDER BY id")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    fmt.Println("  Account balances:")
    for rows.Next() {
        var a Account
        if err := rows.Scan(&a.ID, &a.Name, &a.Balance); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("    [%d] %-6s Balance: %.2f\n", a.ID, a.Name, a.Balance)
    }
}

func main() {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Configure connection pool
    db.SetMaxOpenConns(10)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(5 * time.Minute)
    db.SetConnMaxIdleTime(3 * time.Minute)

    fmt.Println("✓ Connection pool configured:")
    fmt.Println("  Max connections: 10")
    fmt.Println("  Max idle: 5")
    fmt.Println("  Connection lifetime: 5 minutes")
    fmt.Println()

    // Initialize tables
    if err := initDB(db); err != nil {
        log.Fatal(err)
    }

    // Create transfer log table (optional)
    db.Exec(`CREATE TABLE IF NOT EXISTS transfer_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        from_id INTEGER, to_id INTEGER,
        amount REAL, created_at TEXT
    )`)

    // Insert initial accounts
    _, err = db.Exec("INSERT INTO accounts (name, balance) VALUES (?, ?), (?, ?)",
        "Alice", 1000.00, "Bob", 500.00)
    if err != nil {
        log.Fatal("Failed to initialize accounts:", err)
    }

    fmt.Println("=== Initial State ===")
    PrintBalances(db)
    fmt.Println()

    // Normal transfer: Alice -> Bob 300
    fmt.Println("=== Alice -> Bob Transfer 300 ===")
    if err := Transfer(db, 1, 2, 300); err != nil {
        fmt.Printf("Transfer failed: %v\n", err)
    } else {
        fmt.Println("Transfer successful!")
    }
    PrintBalances(db)
    fmt.Println()

    // Insufficient balance transfer: Bob -> Alice 900
    fmt.Println("=== Bob -> Alice Transfer 900 ===")
    if err := Transfer(db, 2, 1, 900); err != nil {
        fmt.Printf("Transfer failed: %v\n", err)
    } else {
        fmt.Println("Transfer successful!")
    }
    PrintBalances(db)
    fmt.Println()

    // Another normal transfer
    fmt.Println("=== Bob -> Alice Transfer 100 ===")
    if err := Transfer(db, 2, 1, 100); err != nil {
        fmt.Printf("Transfer failed: %v\n", err)
    } else {
        fmt.Println("Transfer successful!")
    }
    PrintBalances(db)
}

▶ Try it Yourself

TEXT

✓ Connection pool configured:
  Max connections: 10
  Max idle: 5
  Connection lifetime: 5 minutes

=== Initial State ===
  Account balances:
    [1] Alice  Balance: 1000.00
    [2] Bob    Balance: 500.00

=== Alice -> Bob Transfer 300 ===
Transfer successful!
  Account balances:
    [1] Alice  Balance: 700.00
    [2] Bob    Balance: 800.00

=== Bob -> Alice Transfer 900 ===
Transfer failed: insufficient balance: current 800.00, required 900.00
  Account balances:
    [1] Alice  Balance: 700.00
    [2] Bob    Balance: 800.00

=== Bob -> Alice Transfer 100 ===
Transfer successful!
  Account balances:
    [1] Alice  Balance: 800.00
    [2] Bob    Balance: 700.00

Real-World Scenarios

Uses MySQL to store user information, implementing registration and password verification.

package main

import (
    "crypto/sha256"
    "database/sql"
    "encoding/hex"
    "fmt"
    "log"
    "time"

    _ "github.com/go-sql-driver/mysql" // MySQL driver
)

// hashPassword simple password hashing (production should use bcrypt)
func hashPassword(password string) string {
    h := sha256.Sum256([]byte(password))
    return hex.EncodeToString(h[:])
}

// UserRepository user repository
type UserRepository struct {
    db *sql.DB
}

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

    // Configure connection pool
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(10 * time.Minute)

    if err := db.Ping(); err != nil {
        return nil, fmt.Errorf("database connection failed: %w", err)
    }

    return &UserRepository{db: db}, nil
}

func (r *UserRepository) Close() error {
    return r.db.Close()
}

// Register registers a new user
func (r *UserRepository) Register(username, password, email string) (int64, error) {
    hashedPwd := hashPassword(password)

    result, err := r.db.Exec(
        "INSERT INTO users (username, password_hash, email, created_at) VALUES (?, ?, ?, ?)",
        username, hashedPwd, email, time.Now(),
    )
    if err != nil {
        return 0, fmt.Errorf("registration failed: %w", err)
    }

    return result.LastInsertId()
}

// Login verifies user login
func (r *UserRepository) Login(username, password string) (int, error) {
    hashedPwd := hashPassword(password)

    var id int
    var storedHash string

    err := r.db.QueryRow(
        "SELECT id, password_hash FROM users WHERE username = ?",
        username,
    ).Scan(&id, &storedHash)

    if err == sql.ErrNoRows {
        return 0, fmt.Errorf("user does not exist")
    }
    if err != nil {
        return 0, fmt.Errorf("query failed: %w", err)
    }

    if storedHash != hashedPwd {
        return 0, fmt.Errorf("incorrect password")
    }

    // Update last login time
    _, _ = r.db.Exec(
        "UPDATE users SET last_login = ? WHERE id = ?",
        time.Now(), id,
    )

    return id, nil
}

func main() {
    // DSN format: username:password@tcp(host:port)/dbname?parseTime=true
    dsn := "root:password@tcp(127.0.0.1:3306)/myapp?parseTime=true"

    repo, err := NewUserRepository(dsn)
    if err != nil {
        log.Fatal("Initialization failed:", err)
    }
    defer repo.Close()

    // Register
    id, err := repo.Register("alice", "mypassword123", "alice@example.com")
    if err != nil {
        log.Fatal("Registration failed:", err)
    }
    fmt.Printf("Registration successful, user ID: %d\n", id)

    // Login
    userID, err := repo.Login("alice", "mypassword123")
    if err != nil {
        fmt.Printf("Login failed: %v\n", err)
    } else {
        fmt.Printf("Login successful, user ID: %d\n", userID)
    }

    // Wrong password
    _, err = repo.Login("alice", "wrongpassword")
    if err != nil {
        fmt.Printf("Login failed: %v\n", err)
    }
}

Scenario 2: Paginated Queries and Batch Insert

Efficiently handles paginated queries and batch inserts for large amounts of data.

package main

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

    _ "github.com/mattn/go-sqlite3"
)

// Product product
type Product struct {
    ID    int
    Name  string
    Price float64
    Stock int
}

// PageResult pagination result
type PageResult struct {
    Items      []Product
    Total      int
    Page       int
    PageSize   int
    TotalPages int
}

// ProductStore product storage
type ProductStore struct {
    db *sql.DB
}

func NewProductStore(db *sql.DB) (*ProductStore, error) {
    _, err := db.Exec(`
        CREATE TABLE IF NOT EXISTS products (
            id    INTEGER PRIMARY KEY AUTOINCREMENT,
            name  TEXT NOT NULL,
            price REAL NOT NULL,
            stock INTEGER DEFAULT 0
        )
    `)
    if err != nil {
        return nil, err
    }
    return &ProductStore{db: db}, nil
}

// BatchInsert batch insert (uses transaction for better performance)
func (s *ProductStore) BatchInsert(products []Product) error {
    tx, err := s.db.Begin()
    if err != nil {
        return err
    }

    stmt, err := tx.Prepare("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)")
    if err != nil {
        tx.Rollback()
        return err
    }
    defer stmt.Close()

    for _, p := range products {
        _, err := stmt.Exec(p.Name, p.Price, p.Stock)
        if err != nil {
            tx.Rollback()
            return fmt.Errorf("failed to insert %s: %w", p.Name, err)
        }
    }

    return tx.Commit()
}

// Paginate paginated query
func (s *ProductStore) Paginate(page, pageSize int) (*PageResult, error) {
    if page < 1 {
        page = 1
    }
    if pageSize < 1 {
        pageSize = 10
    }

    // Query total count
    var total int
    err := s.db.QueryRow("SELECT COUNT(*) FROM products").Scan(&total)
    if err != nil {
        return nil, err
    }

    // Calculate total pages
    totalPages := total / pageSize
    if total%pageSize > 0 {
        totalPages++
    }

    // Query current page data
    offset := (page - 1) * pageSize
    rows, err := s.db.Query(
        "SELECT id, name, price, stock FROM products ORDER BY id LIMIT ? OFFSET ?",
        pageSize, offset,
    )
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var items []Product
    for rows.Next() {
        var p Product
        if err := rows.Scan(&p.ID, &p.Name, &p.Price, &p.Stock); err != nil {
            return nil, err
        }
        items = append(items, p)
    }

    return &PageResult{
        Items:      items,
        Total:      total,
        Page:       page,
        PageSize:   pageSize,
        TotalPages: totalPages,
    }, nil
}

// Search fuzzy search by name
func (s *ProductStore) Search(keyword string, page, pageSize int) (*PageResult, error) {
    likePattern := "%" + keyword + "%"

    var total int
    err := s.db.QueryRow(
        "SELECT COUNT(*) FROM products WHERE name LIKE ?", likePattern,
    ).Scan(&total)
    if err != nil {
        return nil, err
    }

    totalPages := total / pageSize
    if total%pageSize > 0 {
        totalPages++
    }

    offset := (page - 1) * pageSize
    rows, err := s.db.Query(
        "SELECT id, name, price, stock FROM products WHERE name LIKE ? ORDER BY id LIMIT ? OFFSET ?",
        likePattern, pageSize, offset,
    )
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var items []Product
    for rows.Next() {
        var p Product
        if err := rows.Scan(&p.ID, &p.Name, &p.Price, &p.Stock); err != nil {
            return nil, err
        }
        items = append(items, p)
    }

    return &PageResult{
        Items:      items,
        Total:      total,
        Page:       page,
        PageSize:   pageSize,
        TotalPages: totalPages,
    }, nil
}

func printPage(result *PageResult) {
    fmt.Printf("  Page %d/%d (total %d items)\n",
        result.Page, result.TotalPages, result.Total)
    for _, p := range result.Items {
        fmt.Printf("    [%d] %-10s Price: %6.2f  Stock: %d\n",
            p.ID, p.Name, p.Price, p.Stock)
    }
}

func main() {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    store, err := NewProductStore(db)
    if err != nil {
        log.Fatal(err)
    }

    // Batch insert 50 products
    var products []Product
    names := []string{"Keyboard", "Mouse", "Monitor", "Headset", "Speaker",
        "Webcam", "USB Drive", "Hard Drive", "RAM", "Motherboard"}
    for i := 0; i < 50; i++ {
        products = append(products, Product{
            Name:  fmt.Sprintf("%s-%02d", names[i%len(names)], i+1),
            Price: float64(50 + i*10),
            Stock: 100 - i,
        })
    }

    if err := store.BatchInsert(products); err != nil {
        log.Fatal("Batch insert failed:", err)
    }
    fmt.Printf("✓ Batch inserted %d products successfully\n\n", len(products))

    // Paginated query
    fmt.Println("=== Paginated Query (10 per page) ===")
    for page := 1; page <= 3; page++ {
        result, err := store.Paginate(page, 10)
        if err != nil {
            log.Fatal(err)
        }
        printPage(result)
        fmt.Println()
    }

    // Search
    fmt.Println("=== Search 'Keyboard' ===")
    result, err := store.Search("Keyboard", 1, 10)
    if err != nil {
        log.Fatal(err)
    }
    printPage(result)
}

TEXT

✓ Batch inserted 50 products successfully

=== Paginated Query (10 per page) ===
  Page 1/5 (total 50 items)
    [1] Keyboard-01  Price:  50.00  Stock: 100
    [2] Mouse-02     Price:  60.00  Stock: 99
    ...

=== Search 'Keyboard' ===
  Page 1/1 (total 5 items)
    [1] Keyboard-01  Price:  50.00  Stock: 100
    [11] Keyboard-11 Price: 150.00  Stock: 90
    [21] Keyboard-21 Price: 250.00  Stock: 80
    [31] Keyboard-31 Price: 350.00  Stock: 70
    [41] Keyboard-41 Price: 450.00  Stock: 60

❓ FAQ

Q1: Why does `sql.Open()` report `unknown driver`?

The driver wasn't imported correctly. Make sure to use anonymous import:

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3" // This line is required
)

_ means only execute the package's init() function (registering the driver), without directly referencing the package name. Forgetting to import the driver is the most common beginner mistake.

Q2: Why does `QueryRow().Scan()` report an error when there's no data?

When a query has no matching rows, Scan() returns sql.ErrNoRows. It needs to be handled separately:

var name string
err := db.QueryRow("SELECT name FROM users WHERE id = ?", 999).Scan(&name)
if err == sql.ErrNoRows {
    fmt.Println("User does not exist")
} else if err != nil {
    log.Fatal("Query error:", err)
}

Q3: When should `db.Close()` be called?

sql.DB is a connection pool and should remain open for the entire program lifecycle, not closed after each operation. Typically use defer db.Close() at the beginning of the main() function:

func main() {
    db, err := sql.Open("sqlite3", "./mydb.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close() // Only closed when program exits

    // ... use db for various operations
}

Q4: How to prevent SQL injection?

Never use string concatenation to build SQL statements. Always use parameterized queries:

// ❌ Dangerous! SQL injection risk
query := fmt.Sprintf("SELECT * FROM users WHERE name = '%s'", userInput)

// ✓ Safe: use parameter placeholders
db.Query("SELECT * FROM users WHERE name = ?", userInput)

The ? placeholder is automatically escaped by the driver, and the database treats it as pure data rather than SQL code.

📖 Summary

In this lesson we learned:

Topic	Key Points
`database/sql`	Go standard library unified interface, supports any database driver
Driver import	Must use `_` anonymous import, only registers the driver
`sql.Open`	Creates connection pool, doesn't connect immediately; use `Ping()` to verify
CRUD	`Exec` for write operations, `Query`/`QueryRow` for reading data
Prepared statements	`Prepare` improves performance, prevents SQL injection
Transactions	`Begin`→`Exec`→`Commit`/`Rollback`, ensures atomicity
Connection pool	`SetMaxOpenConns`/`SetMaxIdleConns` and other tuning methods
`Scan`	Maps database columns to Go variables
`sql.ErrNoRows`	Special error when query returns no results, needs separate handling

With these skills, you have the complete ability to operate relational databases in Go projects.

📝 Exercises

Exercise 1: Basic CRUD (Warm-up)

Create a SQLite database, create a books table (fields: id, title, author, price, published_year), and complete the following operations:

Insert at least 5 books
Query books with price greater than 50
Update a book's price
Delete books published before 2000

Exercise 2: Transactions and Error Handling

Based on Exercise 1, implement a TransferBook function: transfer all books from one author to another (use transactions to ensure atomicity). Requirements:

If the original author doesn't exist, rollback and return an error
After successful update, print the number of affected rows
Use defer + recover to prevent panics from causing transaction hangs

Exercise 3: Comprehensive Challenge — Mini Book Management System

Build a command-line book management system with the following requirements:

Support CRUD operations and paginated list
Use DAO pattern to encapsulate database operations
Support fuzzy search by title and filtering by price range
Use prepared statements to optimize repeated queries
Configure reasonable connection pool parameters
Use structured error handling instead of log.Fatal

Next Lesson: Lesson 28: Project Deployment — Learn how to compile, package, and deploy Go projects to servers.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Database Operations

Lesson 27: Database Operations

Real-World Analogy

Core Concepts

Basic Syntax and Usage

1. Importing Drivers

2. Opening a Database

3. Creating Tables

4. CRUD Operations

5. Prepared Statements

6. Transaction Handling

7. Connection Pool Configuration

Hands-On Practice

Example: SQLite User Management (Difficulty ⭐)

Example: CRUD Encapsulation + Prepared Statements (Difficulty ⭐⭐)

Example: Transaction Transfer + Connection Pool Configuration (Difficulty ⭐⭐⭐)

Real-World Scenarios

Scenario 1: User Registration and Login (MySQL)

Scenario 2: Paginated Queries and Batch Insert

❓ FAQ

Q1: Why does sql.Open() report unknown driver?

Q2: Why does QueryRow().Scan() report an error when there's no data?

Q3: When should db.Close() be called?

Q4: How to prevent SQL injection?

📖 Summary

📝 Exercises

Exercise 1: Basic CRUD (Warm-up)

Exercise 2: Transactions and Error Handling

Exercise 3: Comprehensive Challenge — Mini Book Management System

🙏 帮我们做得更好

Q1: Why does `sql.Open()` report `unknown driver`?

Q2: Why does `QueryRow().Scan()` report an error when there's no data?

Q3: When should `db.Close()` be called?