Go Database Integration

calendar_today August 12, 2025 schedule 21 min read

Learning Objectives: By the end of this lesson, you will be able to:

Connect to various databases using Go’s database/sql package

Implement CRUD operations with proper error handling

Manage database connections and connection pooling

Work with transactions and advanced SQL features

Build data access layers and repository patterns

Handle database migrations and schema management

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Every application needs to store data somewhere. Whether it’s user profiles, product catalogs, or transaction records, databases are the backbone of modern applications. Today we’ll learn how Go makes database integration both powerful and safe with its excellent standard library and ecosystem.

Lesson Overview

Duration: 3-4 days (8-10 hours)
Prerequisites: Basic Go syntax, structs, interfaces, error handling
Materials Needed: Go development environment, database server (PostgreSQL/MySQL)

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

Database/SQL Package - Go’s standard database interface
Connection Management - Pools, timeouts, and configuration
CRUD Operations - Create, Read, Update, Delete with best practices
Transactions - Managing data consistency and rollbacks
Advanced Queries - Joins, aggregations, and complex operations
Repository Pattern - Organizing database code
Migrations - Schema management and versioning

Success Criteria:

By the end of this session, you should be able to build a complete database-backed application with proper architecture and error handling.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

Go’s Database/SQL Package

Go’s database/sql package provides a generic interface around SQL databases. It’s designed to work with various database drivers while maintaining a consistent API.

Real-World Analogy: Think of database/sql as a universal translator that speaks “Go” on one side and can communicate with any SQL database on the other side through specific drivers.

Why This Matters: This abstraction allows you to write database code once and potentially switch between different database systems with minimal changes.

Key Components

DB: Represents a database connection pool
Rows: Result set from queries
Row: Single row result
Tx: Database transaction
Stmt: Prepared statement

Key Terminology

Driver: Database-specific implementation of the database/sql interface
Connection Pool: Managed set of database connections
Prepared Statement: Pre-compiled SQL statement for efficiency and security
Transaction: Group of operations that succeed or fail together
Repository: Design pattern for encapsulating data access logic

4. Detailed Explanation and Examples (25-30 minutes)

Understanding Database Connections

Database connections in Go are managed through connection pools automatically. The sql.DB type represents a pool of connections, not a single connection.

Basic Database Setup

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"
    
    _ "github.com/lib/pq" // PostgreSQL driver
)

type DatabaseConfig struct {
    Host     string
    Port     int
    Database string
    Username string
    Password string
    SSLMode  string
}

func (cfg DatabaseConfig) ConnectionString() string {
    return fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=%s",
        cfg.Host, cfg.Port, cfg.Username, cfg.Password, cfg.Database, cfg.SSLMode)
}

func InitializeDatabase(config DatabaseConfig) (*sql.DB, error) {
    db, err := sql.Open("postgres", config.ConnectionString())
    if err != nil {
        return nil, fmt.Errorf("failed to open database: %w", err)
    }
    
    // Configure connection pool
    db.SetMaxOpenConns(25)                 // Maximum open connections
    db.SetMaxIdleConns(5)                  // Maximum idle connections
    db.SetConnMaxLifetime(5 * time.Minute) // Connection lifetime
    db.SetConnMaxIdleTime(1 * time.Minute) // Idle connection timeout
    
    // Test the connection
    if err := db.Ping(); err != nil {
        return nil, fmt.Errorf("failed to ping database: %w", err)
    }
    
    return db, nil
}

func databaseSetupExample() {
    config := DatabaseConfig{
        Host:     "localhost",
        Port:     5432,
        Database: "testdb",
        Username: "user",
        Password: "password",
        SSLMode:  "disable",
    }
    
    db, err := InitializeDatabase(config)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    fmt.Println("Successfully connected to database!")
    
    // Check connection pool stats
    stats := db.Stats()
    fmt.Printf("Open connections: %d\n", stats.OpenConnections)
    fmt.Printf("In use: %d\n", stats.InUse)
    fmt.Printf("Idle: %d\n", stats.Idle)
}

CRUD Operations with Error Handling

package main

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

type User struct {
    ID        int       `json:"id"`
    Username  string    `json:"username"`
    Email     string    `json:"email"`
    FullName  string    `json:"full_name"`
    CreatedAt time.Time `json:"created_at"`
    UpdatedAt time.Time `json:"updated_at"`
}

type UserRepository struct {
    db *sql.DB
}

func NewUserRepository(db *sql.DB) *UserRepository {
    return &UserRepository{db: db}
}

// Create user
func (ur *UserRepository) Create(user *User) error {
    query := `
        INSERT INTO users (username, email, full_name, created_at, updated_at)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING id`
    
    err := ur.db.QueryRow(
        query,
        user.Username,
        user.Email,
        user.FullName,
        time.Now(),
        time.Now(),
    ).Scan(&user.ID)
    
    if err != nil {
        return fmt.Errorf("failed to create user: %w", err)
    }
    
    return nil
}

// Get user by ID
func (ur *UserRepository) GetByID(id int) (*User, error) {
    query := `
        SELECT id, username, email, full_name, created_at, updated_at
        FROM users
        WHERE id = $1`
    
    user := &User{}
    err := ur.db.QueryRow(query, id).Scan(
        &user.ID,
        &user.Username,
        &user.Email,
        &user.FullName,
        &user.CreatedAt,
        &user.UpdatedAt,
    )
    
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, fmt.Errorf("user with id %d not found", id)
        }
        return nil, fmt.Errorf("failed to get user: %w", err)
    }
    
    return user, nil
}

// Get user by username
func (ur *UserRepository) GetByUsername(username string) (*User, error) {
    query := `
        SELECT id, username, email, full_name, created_at, updated_at
        FROM users
        WHERE username = $1`
    
    user := &User{}
    err := ur.db.QueryRow(query, username).Scan(
        &user.ID,
        &user.Username,
        &user.Email,
        &user.FullName,
        &user.CreatedAt,
        &user.UpdatedAt,
    )
    
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, fmt.Errorf("user with username %s not found", username)
        }
        return nil, fmt.Errorf("failed to get user: %w", err)
    }
    
    return user, nil
}

// List all users with pagination
func (ur *UserRepository) List(limit, offset int) ([]*User, error) {
    query := `
        SELECT id, username, email, full_name, created_at, updated_at
        FROM users
        ORDER BY created_at DESC
        LIMIT $1 OFFSET $2`
    
    rows, err := ur.db.Query(query, limit, offset)
    if err != nil {
        return nil, fmt.Errorf("failed to query users: %w", err)
    }
    defer rows.Close()
    
    var users []*User
    
    for rows.Next() {
        user := &User{}
        err := rows.Scan(
            &user.ID,
            &user.Username,
            &user.Email,
            &user.FullName,
            &user.CreatedAt,
            &user.UpdatedAt,
        )
        if err != nil {
            return nil, fmt.Errorf("failed to scan user: %w", err)
        }
        users = append(users, user)
    }
    
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("error iterating rows: %w", err)
    }
    
    return users, nil
}

// Update user
func (ur *UserRepository) Update(user *User) error {
    query := `
        UPDATE users
        SET username = $1, email = $2, full_name = $3, updated_at = $4
        WHERE id = $5`
    
    result, err := ur.db.Exec(
        query,
        user.Username,
        user.Email,
        user.FullName,
        time.Now(),
        user.ID,
    )
    
    if err != nil {
        return fmt.Errorf("failed to update user: %w", err)
    }
    
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return fmt.Errorf("failed to get rows affected: %w", err)
    }
    
    if rowsAffected == 0 {
        return fmt.Errorf("user with id %d not found", user.ID)
    }
    
    return nil
}

// Delete user
func (ur *UserRepository) Delete(id int) error {
    query := `DELETE FROM users WHERE id = $1`
    
    result, err := ur.db.Exec(query, id)
    if err != nil {
        return fmt.Errorf("failed to delete user: %w", err)
    }
    
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return fmt.Errorf("failed to get rows affected: %w", err)
    }
    
    if rowsAffected == 0 {
        return fmt.Errorf("user with id %d not found", id)
    }
    
    return nil
}

// Count total users
func (ur *UserRepository) Count() (int, error) {
    query := `SELECT COUNT(*) FROM users`
    
    var count int
    err := ur.db.QueryRow(query).Scan(&count)
    if err != nil {
        return 0, fmt.Errorf("failed to count users: %w", err)
    }
    
    return count, nil
}

Transaction Management

package main

import (
    "database/sql"
    "fmt"
)

type Account struct {
    ID      int     `json:"id"`
    UserID  int     `json:"user_id"`
    Balance float64 `json:"balance"`
    Currency string `json:"currency"`
}

type Transaction struct {
    ID          int     `json:"id"`
    FromAccount int     `json:"from_account"`
    ToAccount   int     `json:"to_account"`
    Amount      float64 `json:"amount"`
    Currency    string  `json:"currency"`
    Description string  `json:"description"`
}

type BankingService struct {
    db *sql.DB
}

func NewBankingService(db *sql.DB) *BankingService {
    return &BankingService{db: db}
}

// Transfer money between accounts (atomic operation)
func (bs *BankingService) Transfer(fromAccountID, toAccountID int, amount float64, description string) error {
    // Start transaction
    tx, err := bs.db.Begin()
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }
    
    // Defer rollback in case of panic or error
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p) // Re-throw panic after rollback
        } else if err != nil {
            tx.Rollback()
        }
    }()
    
    // Check source account balance
    var fromBalance float64
    var currency string
    err = tx.QueryRow(
        "SELECT balance, currency FROM accounts WHERE id = $1 FOR UPDATE",
        fromAccountID,
    ).Scan(&fromBalance, &currency)
    
    if err != nil {
        if err == sql.ErrNoRows {
            return fmt.Errorf("source account %d not found", fromAccountID)
        }
        return fmt.Errorf("failed to get source account: %w", err)
    }
    
    if fromBalance < amount {
        return fmt.Errorf("insufficient balance: have %.2f, need %.2f", fromBalance, amount)
    }
    
    // Check destination account exists and has same currency
    var toCurrency string
    err = tx.QueryRow(
        "SELECT currency FROM accounts WHERE id = $1 FOR UPDATE",
        toAccountID,
    ).Scan(&toCurrency)
    
    if err != nil {
        if err == sql.ErrNoRows {
            return fmt.Errorf("destination account %d not found", toAccountID)
        }
        return fmt.Errorf("failed to get destination account: %w", err)
    }
    
    if currency != toCurrency {
        return fmt.Errorf("currency mismatch: %s vs %s", currency, toCurrency)
    }
    
    // Debit source account
    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
        amount, fromAccountID,
    )
    if err != nil {
        return fmt.Errorf("failed to debit source account: %w", err)
    }
    
    // Credit destination account
    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
        amount, toAccountID,
    )
    if err != nil {
        return fmt.Errorf("failed to credit destination account: %w", err)
    }
    
    // Record transaction
    _, err = tx.Exec(
        `INSERT INTO transactions (from_account, to_account, amount, currency, description, created_at)
         VALUES ($1, $2, $3, $4, $5, NOW())`,
        fromAccountID, toAccountID, amount, currency, description,
    )
    if err != nil {
        return fmt.Errorf("failed to record transaction: %w", err)
    }
    
    // Commit transaction
    err = tx.Commit()
    if err != nil {
        return fmt.Errorf("failed to commit transaction: %w", err)
    }
    
    return nil
}

// Get account balance
func (bs *BankingService) GetBalance(accountID int) (*Account, error) {
    query := `
        SELECT id, user_id, balance, currency
        FROM accounts
        WHERE id = $1`
    
    account := &Account{}
    err := bs.db.QueryRow(query, accountID).Scan(
        &account.ID,
        &account.UserID,
        &account.Balance,
        &account.Currency,
    )
    
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, fmt.Errorf("account %d not found", accountID)
        }
        return nil, fmt.Errorf("failed to get account: %w", err)
    }
    
    return account, nil
}

// Get transaction history
func (bs *BankingService) GetTransactionHistory(accountID int, limit, offset int) ([]*Transaction, error) {
    query := `
        SELECT id, from_account, to_account, amount, currency, description
        FROM transactions
        WHERE from_account = $1 OR to_account = $1
        ORDER BY created_at DESC
        LIMIT $2 OFFSET $3`
    
    rows, err := bs.db.Query(query, accountID, limit, offset)
    if err != nil {
        return nil, fmt.Errorf("failed to query transactions: %w", err)
    }
    defer rows.Close()
    
    var transactions []*Transaction
    
    for rows.Next() {
        tx := &Transaction{}
        err := rows.Scan(
            &tx.ID,
            &tx.FromAccount,
            &tx.ToAccount,
            &tx.Amount,
            &tx.Currency,
            &tx.Description,
        )
        if err != nil {
            return nil, fmt.Errorf("failed to scan transaction: %w", err)
        }
        transactions = append(transactions, tx)
    }
    
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("error iterating rows: %w", err)
    }
    
    return transactions, nil
}

5. Live Coding Demonstration (20-25 minutes)

Example 1: Blog Platform with Advanced Queries

package main

import (
    "database/sql"
    "fmt"
    "strings"
    "time"
)

type Post struct {
    ID          int       `json:"id"`
    Title       string    `json:"title"`
    Content     string    `json:"content"`
    AuthorID    int       `json:"author_id"`
    AuthorName  string    `json:"author_name,omitempty"`
    CategoryID  int       `json:"category_id"`
    Category    string    `json:"category,omitempty"`
    Status      string    `json:"status"`
    PublishedAt *time.Time `json:"published_at,omitempty"`
    CreatedAt   time.Time `json:"created_at"`
    UpdatedAt   time.Time `json:"updated_at"`
    ViewCount   int       `json:"view_count"`
    CommentCount int      `json:"comment_count"`
}

type Category struct {
    ID          int    `json:"id"`
    Name        string `json:"name"`
    Description string `json:"description"`
    PostCount   int    `json:"post_count"`
}

type Comment struct {
    ID        int       `json:"id"`
    PostID    int       `json:"post_id"`
    AuthorID  int       `json:"author_id"`
    Content   string    `json:"content"`
    CreatedAt time.Time `json:"created_at"`
}

type BlogRepository struct {
    db *sql.DB
}

func NewBlogRepository(db *sql.DB) *BlogRepository {
    return &BlogRepository{db: db}
}

// Get posts with author and category information (JOIN)
func (br *BlogRepository) GetPostsWithDetails(limit, offset int) ([]*Post, error) {
    query := `
        SELECT 
            p.id, p.title, p.content, p.author_id, u.username as author_name,
            p.category_id, c.name as category, p.status, p.published_at,
            p.created_at, p.updated_at, p.view_count,
            COALESCE(comment_counts.count, 0) as comment_count
        FROM posts p
        JOIN users u ON p.author_id = u.id
        JOIN categories c ON p.category_id = c.id
        LEFT JOIN (
            SELECT post_id, COUNT(*) as count
            FROM comments
            GROUP BY post_id
        ) comment_counts ON p.id = comment_counts.post_id
        WHERE p.status = 'published'
        ORDER BY p.published_at DESC
        LIMIT $1 OFFSET $2`
    
    rows, err := br.db.Query(query, limit, offset)
    if err != nil {
        return nil, fmt.Errorf("failed to query posts: %w", err)
    }
    defer rows.Close()
    
    var posts []*Post
    
    for rows.Next() {
        post := &Post{}
        err := rows.Scan(
            &post.ID,
            &post.Title,
            &post.Content,
            &post.AuthorID,
            &post.AuthorName,
            &post.CategoryID,
            &post.Category,
            &post.Status,
            &post.PublishedAt,
            &post.CreatedAt,
            &post.UpdatedAt,
            &post.ViewCount,
            &post.CommentCount,
        )
        if err != nil {
            return nil, fmt.Errorf("failed to scan post: %w", err)
        }
        posts = append(posts, post)
    }
    
    return posts, rows.Err()
}

// Search posts with full-text search
func (br *BlogRepository) SearchPosts(searchTerm string, categoryID *int, limit, offset int) ([]*Post, error) {
    var queryParts []string
    var args []interface{}
    argCount := 0
    
    baseQuery := `
        SELECT 
            p.id, p.title, p.content, p.author_id, u.username as author_name,
            p.category_id, c.name as category, p.status, p.published_at,
            p.created_at, p.updated_at, p.view_count,
            COALESCE(comment_counts.count, 0) as comment_count
        FROM posts p
        JOIN users u ON p.author_id = u.id
        JOIN categories c ON p.category_id = c.id
        LEFT JOIN (
            SELECT post_id, COUNT(*) as count
            FROM comments
            GROUP BY post_id
        ) comment_counts ON p.id = comment_counts.post_id
        WHERE p.status = 'published'`
    
    queryParts = append(queryParts, baseQuery)
    
    if searchTerm != "" {
        argCount++
        queryParts = append(queryParts, fmt.Sprintf(
            "AND (p.title ILIKE $%d OR p.content ILIKE $%d)",
            argCount, argCount))
        args = append(args, "%"+searchTerm+"%")
    }
    
    if categoryID != nil {
        argCount++
        queryParts = append(queryParts, fmt.Sprintf("AND p.category_id = $%d", argCount))
        args = append(args, *categoryID)
    }
    
    queryParts = append(queryParts, "ORDER BY p.published_at DESC")
    
    argCount++
    queryParts = append(queryParts, fmt.Sprintf("LIMIT $%d", argCount))
    args = append(args, limit)
    
    argCount++
    queryParts = append(queryParts, fmt.Sprintf("OFFSET $%d", argCount))
    args = append(args, offset)
    
    query := strings.Join(queryParts, " ")
    
    rows, err := br.db.Query(query, args...)
    if err != nil {
        return nil, fmt.Errorf("failed to search posts: %w", err)
    }
    defer rows.Close()
    
    var posts []*Post
    
    for rows.Next() {
        post := &Post{}
        err := rows.Scan(
            &post.ID,
            &post.Title,
            &post.Content,
            &post.AuthorID,
            &post.AuthorName,
            &post.CategoryID,
            &post.Category,
            &post.Status,
            &post.PublishedAt,
            &post.CreatedAt,
            &post.UpdatedAt,
            &post.ViewCount,
            &post.CommentCount,
        )
        if err != nil {
            return nil, fmt.Errorf("failed to scan post: %w", err)
        }
        posts = append(posts, post)
    }
    
    return posts, rows.Err()
}

// Get popular posts by view count
func (br *BlogRepository) GetPopularPosts(limit int, since time.Time) ([]*Post, error) {
    query := `
        SELECT 
            p.id, p.title, p.content, p.author_id, u.username as author_name,
            p.category_id, c.name as category, p.status, p.published_at,
            p.created_at, p.updated_at, p.view_count,
            COALESCE(comment_counts.count, 0) as comment_count
        FROM posts p
        JOIN users u ON p.author_id = u.id
        JOIN categories c ON p.category_id = c.id
        LEFT JOIN (
            SELECT post_id, COUNT(*) as count
            FROM comments
            GROUP BY post_id
        ) comment_counts ON p.id = comment_counts.post_id
        WHERE p.status = 'published' AND p.published_at >= $1
        ORDER BY p.view_count DESC, p.published_at DESC
        LIMIT $2`
    
    rows, err := br.db.Query(query, since, limit)
    if err != nil {
        return nil, fmt.Errorf("failed to query popular posts: %w", err)
    }
    defer rows.Close()
    
    var posts []*Post
    
    for rows.Next() {
        post := &Post{}
        err := rows.Scan(
            &post.ID,
            &post.Title,
            &post.Content,
            &post.AuthorID,
            &post.AuthorName,
            &post.CategoryID,
            &post.Category,
            &post.Status,
            &post.PublishedAt,
            &post.CreatedAt,
            &post.UpdatedAt,
            &post.ViewCount,
            &post.CommentCount,
        )
        if err != nil {
            return nil, fmt.Errorf("failed to scan post: %w", err)
        }
        posts = append(posts, post)
    }
    
    return posts, rows.Err()
}

// Get categories with post counts
func (br *BlogRepository) GetCategoriesWithCounts() ([]*Category, error) {
    query := `
        SELECT 
            c.id, c.name, c.description,
            COUNT(p.id) as post_count
        FROM categories c
        LEFT JOIN posts p ON c.id = p.category_id AND p.status = 'published'
        GROUP BY c.id, c.name, c.description
        ORDER BY c.name`
    
    rows, err := br.db.Query(query)
    if err != nil {
        return nil, fmt.Errorf("failed to query categories: %w", err)
    }
    defer rows.Close()
    
    var categories []*Category
    
    for rows.Next() {
        category := &Category{}
        err := rows.Scan(
            &category.ID,
            &category.Name,
            &category.Description,
            &category.PostCount,
        )
        if err != nil {
            return nil, fmt.Errorf("failed to scan category: %w", err)
        }
        categories = append(categories, category)
    }
    
    return categories, rows.Err()
}

// Increment view count (optimistic)
func (br *BlogRepository) IncrementViewCount(postID int) error {
    query := `UPDATE posts SET view_count = view_count + 1 WHERE id = $1`
    
    _, err := br.db.Exec(query, postID)
    if err != nil {
        return fmt.Errorf("failed to increment view count: %w", err)
    }
    
    return nil
}

Example 2: Database Migration System

package main

import (
    "database/sql"
    "fmt"
    "io/ioutil"
    "path/filepath"
    "sort"
    "strconv"
    "strings"
    "time"
)

type Migration struct {
    Version   int
    Name      string
    UpSQL     string
    DownSQL   string
    AppliedAt *time.Time
}

type MigrationManager struct {
    db            *sql.DB
    migrationsDir string
}

func NewMigrationManager(db *sql.DB, migrationsDir string) *MigrationManager {
    return &MigrationManager{
        db:            db,
        migrationsDir: migrationsDir,
    }
}

func (mm *MigrationManager) Initialize() error {
    // Create migrations table if it doesn't exist
    query := `
        CREATE TABLE IF NOT EXISTS schema_migrations (
            version INTEGER PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        )`
    
    _, err := mm.db.Exec(query)
    if err != nil {
        return fmt.Errorf("failed to create migrations table: %w", err)
    }
    
    return nil
}

func (mm *MigrationManager) LoadMigrations() ([]*Migration, error) {
    files, err := ioutil.ReadDir(mm.migrationsDir)
    if err != nil {
        return nil, fmt.Errorf("failed to read migrations directory: %w", err)
    }
    
    var migrations []*Migration
    
    for _, file := range files {
        if !strings.HasSuffix(file.Name(), ".sql") {
            continue
        }
        
        // Parse filename: 001_create_users_table.sql
        parts := strings.SplitN(file.Name(), "_", 2)
        if len(parts) != 2 {
            continue
        }
        
        version, err := strconv.Atoi(parts[0])
        if err != nil {
            continue
        }
        
        name := strings.TrimSuffix(parts[1], ".sql")
        
        // Read migration file
        content, err := ioutil.ReadFile(filepath.Join(mm.migrationsDir, file.Name()))
        if err != nil {
            return nil, fmt.Errorf("failed to read migration file %s: %w", file.Name(), err)
        }
        
        // Split UP and DOWN sections
        sections := strings.Split(string(content), "-- +migrate Down")
        if len(sections) != 2 {
            return nil, fmt.Errorf("invalid migration format in %s", file.Name())
        }
        
        upSQL := strings.TrimSpace(strings.TrimPrefix(sections[0], "-- +migrate Up"))
        downSQL := strings.TrimSpace(sections[1])
        
        migration := &Migration{
            Version: version,
            Name:    name,
            UpSQL:   upSQL,
            DownSQL: downSQL,
        }
        
        migrations = append(migrations, migration)
    }
    
    // Sort by version
    sort.Slice(migrations, func(i, j int) bool {
        return migrations[i].Version < migrations[j].Version
    })
    
    return migrations, nil
}

func (mm *MigrationManager) GetAppliedMigrations() (map[int]*time.Time, error) {
    query := `SELECT version, applied_at FROM schema_migrations ORDER BY version`
    
    rows, err := mm.db.Query(query)
    if err != nil {
        return nil, fmt.Errorf("failed to query applied migrations: %w", err)
    }
    defer rows.Close()
    
    applied := make(map[int]*time.Time)
    
    for rows.Next() {
        var version int
        var appliedAt time.Time
        
        err := rows.Scan(&version, &appliedAt)
        if err != nil {
            return nil, fmt.Errorf("failed to scan migration: %w", err)
        }
        
        applied[version] = &appliedAt
    }
    
    return applied, rows.Err()
}

func (mm *MigrationManager) Migrate() error {
    migrations, err := mm.LoadMigrations()
    if err != nil {
        return err
    }
    
    applied, err := mm.GetAppliedMigrations()
    if err != nil {
        return err
    }
    
    for _, migration := range migrations {
        if _, exists := applied[migration.Version]; exists {
            fmt.Printf("Migration %03d_%s already applied\n", migration.Version, migration.Name)
            continue
        }
        
        fmt.Printf("Applying migration %03d_%s...\n", migration.Version, migration.Name)
        
        // Start transaction
        tx, err := mm.db.Begin()
        if err != nil {
            return fmt.Errorf("failed to begin transaction: %w", err)
        }
        
        // Execute migration
        _, err = tx.Exec(migration.UpSQL)
        if err != nil {
            tx.Rollback()
            return fmt.Errorf("failed to execute migration %d: %w", migration.Version, err)
        }
        
        // Record migration
        _, err = tx.Exec(
            "INSERT INTO schema_migrations (version, name) VALUES ($1, $2)",
            migration.Version, migration.Name,
        )
        if err != nil {
            tx.Rollback()
            return fmt.Errorf("failed to record migration %d: %w", migration.Version, err)
        }
        
        // Commit transaction
        err = tx.Commit()
        if err != nil {
            return fmt.Errorf("failed to commit migration %d: %w", migration.Version, err)
        }
        
        fmt.Printf("Successfully applied migration %03d_%s\n", migration.Version, migration.Name)
    }
    
    return nil
}

func (mm *MigrationManager) Rollback(targetVersion int) error {
    applied, err := mm.GetAppliedMigrations()
    if err != nil {
        return err
    }
    
    migrations, err := mm.LoadMigrations()
    if err != nil {
        return err
    }
    
    // Create map for quick lookup
    migrationMap := make(map[int]*Migration)
    for _, m := range migrations {
        migrationMap[m.Version] = m
    }
    
    // Get versions to rollback (in reverse order)
    var toRollback []int
    for version := range applied {
        if version > targetVersion {
            toRollback = append(toRollback, version)
        }
    }
    
    // Sort in descending order
    sort.Sort(sort.Reverse(sort.IntSlice(toRollback)))
    
    for _, version := range toRollback {
        migration, exists := migrationMap[version]
        if !exists {
            return fmt.Errorf("migration %d not found", version)
        }
        
        fmt.Printf("Rolling back migration %03d_%s...\n", migration.Version, migration.Name)
        
        // Start transaction
        tx, err := mm.db.Begin()
        if err != nil {
            return fmt.Errorf("failed to begin transaction: %w", err)
        }
        
        // Execute rollback
        _, err = tx.Exec(migration.DownSQL)
        if err != nil {
            tx.Rollback()
            return fmt.Errorf("failed to rollback migration %d: %w", migration.Version, err)
        }
        
        // Remove migration record
        _, err = tx.Exec(
            "DELETE FROM schema_migrations WHERE version = $1",
            migration.Version,
        )
        if err != nil {
            tx.Rollback()
            return fmt.Errorf("failed to remove migration record %d: %w", migration.Version, err)
        }
        
        // Commit transaction
        err = tx.Commit()
        if err != nil {
            return fmt.Errorf("failed to commit rollback %d: %w", migration.Version, err)
        }
        
        fmt.Printf("Successfully rolled back migration %03d_%s\n", migration.Version, migration.Name)
    }
    
    return nil
}

func createSampleMigrations() error {
    err := os.MkdirAll("migrations", 0755)
    if err != nil {
        return err
    }
    
    // Migration 001: Create users table
    migration001 := `-- +migrate Up
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);

-- +migrate Down
DROP TABLE users;`
    
    // Migration 002: Create categories table
    migration002 := `-- +migrate Up
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

INSERT INTO categories (name, description) VALUES
('Technology', 'Posts about technology and programming'),
('Lifestyle', 'Posts about lifestyle and personal development'),
('Business', 'Posts about business and entrepreneurship');

-- +migrate Down
DROP TABLE categories;`
    
    // Migration 003: Create posts table
    migration003 := `-- +migrate Up
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    author_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    category_id INTEGER REFERENCES categories(id) ON DELETE RESTRICT,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    published_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    view_count INTEGER DEFAULT 0
);

CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_category ON posts(category_id);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_published ON posts(published_at);

-- +migrate Down
DROP TABLE posts;`
    
    files := map[string]string{
        "001_create_users_table.sql":      migration001,
        "002_create_categories_table.sql": migration002,
        "003_create_posts_table.sql":      migration003,
    }
    
    for filename, content := range files {
        err := ioutil.WriteFile(filepath.Join("migrations", filename), []byte(content), 0644)
        if err != nil {
            return err
        }
    }
    
    return nil
}

func migrationExample() {
    // Create sample migration files
    fmt.Println("Creating sample migration files...")
    err := createSampleMigrations()
    if err != nil {
        log.Fatal(err)
    }
    
    // Initialize database connection (use your own config)
    config := DatabaseConfig{
        Host:     "localhost",
        Port:     5432,
        Database: "testdb",
        Username: "user",
        Password: "password",
        SSLMode:  "disable",
    }
    
    db, err := InitializeDatabase(config)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // Initialize migration manager
    mm := NewMigrationManager(db, "migrations")
    
    err = mm.Initialize()
    if err != nil {
        log.Fatal(err)
    }
    
    // Run migrations
    fmt.Println("Running migrations...")
    err = mm.Migrate()
    if err != nil {
        log.Fatal(err)
    }
    
    fmt.Println("All migrations completed successfully!")
}

6. Hands-On Practice (30-40 minutes)

Exercise 1: E-commerce Product Catalog (10 minutes)

Objective: Build a product management system with categories and inventory

Instructions:

Create tables for products, categories, and inventory
Implement CRUD operations for products
Add search functionality with filters
Track inventory changes with audit trail

Starter Code:

type Product struct {
    ID          int     `json:"id"`
    Name        string  `json:"name"`
    Description string  `json:"description"`
    Price       float64 `json:"price"`
    CategoryID  int     `json:"category_id"`
    SKU         string  `json:"sku"`
    InStock     int     `json:"in_stock"`
}

func main() {
    // TODO: Implement product catalog system
}

Exercise 2: Order Management System (15 minutes)

Objective: Create a complete order processing system with transactions

Requirements:

Handle order creation with multiple items
Update inventory levels atomically
Calculate totals and apply discounts
Track order status changes
Generate order reports

Hints:

Use transactions for order processing
Implement proper foreign key relationships
Consider using prepared statements for performance

Exercise 3: Analytics Dashboard (15 minutes)

Objective: Build reporting queries for business analytics

Challenge: Create queries that provide:

Sales reports by time period
Top-selling products and categories
Customer behavior analysis
Inventory turnover rates

Extension Ideas:

Add caching for expensive queries
Implement data aggregation tables
Create a simple web dashboard

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

Problem: SQL injection vulnerabilities Cause: String concatenation instead of prepared statements Solution: Always use parameterized queries

// Wrong - vulnerable to SQL injection
query := fmt.Sprintf("SELECT * FROM users WHERE username = '%s'", username)

// Right - safe parameterized query
query := "SELECT * FROM users WHERE username = $1"
rows, err := db.Query(query, username)

Problem: Not handling sql.ErrNoRows Cause: Treating “no rows” as a general error Solution: Check specifically for sql.ErrNoRows

err := db.QueryRow("SELECT * FROM users WHERE id = $1", id).Scan(&user)
if err != nil {
    if err == sql.ErrNoRows {
        return nil, fmt.Errorf("user not found")
    }
    return nil, fmt.Errorf("database error: %w", err)
}

Debugging Tips

Use database logging to see actual SQL queries
Monitor connection pool statistics
Use database profiling tools to identify slow queries
Test with realistic data volumes

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

Connection Pooling: Configure appropriate pool sizes
Query Optimization: Use indexes and analyze query plans
Error Handling: Provide meaningful error messages
Security: Validate inputs and use prepared statements

Performance Considerations

// Use prepared statements for repeated queries
func (r *Repository) PrepareStatements() error {
    var err error
    r.getUserStmt, err = r.db.Prepare("SELECT id, name, email FROM users WHERE id = $1")
    if err != nil {
        return err
    }
    
    r.createUserStmt, err = r.db.Prepare("INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id")
    return err
}

func (r *Repository) GetUser(id int) (*User, error) {
    user := &User{}
    err := r.getUserStmt.QueryRow(id).Scan(&user.ID, &user.Name, &user.Email)
    return user, err
}

Next Steps in Your Learning Journey

Learn about ORM libraries like GORM
Study database-specific optimizations
Explore NoSQL databases with Go
Practice with database scaling patterns

9. Summary and Review (5 minutes)

Key Takeaways

Go’s database/sql package provides a robust foundation for database operations
Connection pooling is handled automatically but should be configured properly
Transactions are essential for maintaining data consistency
Prepared statements improve both performance and security

Quick Review Questions

When should you use transactions in your database operations?
What’s the difference between QueryRow() and Query()?
How do you handle database connection pooling in Go?

For Instructors

Teaching Tips

Use real database examples with actual tables
Demonstrate both simple and complex queries
Show the importance of proper error handling
Emphasize security best practices throughout

Assessment Ideas

Build a complete CRUD application with a web interface
Implement a database migration system
Create a reporting system with complex queries

Adaptation Notes

For beginners: Focus on basic CRUD operations and error handling
For advanced students: Include performance optimization and advanced patterns
Online delivery: Provide Docker containers with pre-configured databases