Database Integration and Data Persistence with Go Gin

calendar_today August 6, 2025 schedule 21 min read

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

Connect Gin applications to relational databases using GORM and raw SQL

Design and implement database schemas with proper relationships

Perform CRUD operations through REST endpoints with proper error handling

Implement database migrations and seeding strategies

Handle database transactions and connection pooling

Integrate with NoSQL databases like MongoDB

Implement caching strategies with Redis

Test database operations with test containers and mocking

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever built a web application that needed to persist data beyond the server’s memory? Or perhaps you’ve wondered how to efficiently handle thousands of database connections in a production environment? Today, we’re diving into the world of database integration with Gin, exploring everything from SQL basics to advanced caching strategies that power real-world applications.

Lesson Overview

Duration: 150 minutes
Prerequisites: Lessons 1-5 completed, basic SQL knowledge, understanding of database concepts
Materials Needed: Go 1.25+ for Gin v1.12, Gin framework, Docker for databases, SQL client tool

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

Database Connections - Establishing and managing database connections
GORM Integration - Using Go’s most popular ORM for database operations
Raw SQL Operations - Direct database access with database/sql and sqlx
Schema Design - Creating maintainable database structures and migrations
Advanced Patterns - Transactions, connection pooling, and performance optimization
NoSQL Integration - Working with MongoDB and document databases
Caching Strategies - Implementing Redis for performance optimization

Success Criteria:

By the end of this session, you should be able to build production-ready applications with robust data persistence, efficient database operations, and proper error handling.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

Database Integration Patterns

Modern web applications typically use one of several patterns for database integration:

Active Record: Models contain both data and database logic (like Rails)
Data Mapper: Separate objects handle database operations (like GORM)
Repository Pattern: Abstract interface for data access operations
Query Builder: Programmatic SQL query construction

Real-World Analogy: Think of database patterns like different approaches to organizing a library. Active Record is like books that know their own location and can move themselves. Data Mapper is like having librarians who know where everything is. Repository Pattern is like having a standardized catalog system.

Why This Matters: The right database pattern affects maintainability, testability, and performance. Understanding these patterns helps you choose the right approach for your application’s needs.

GORM vs. Raw SQL

GORM Benefits: Type safety, migration support, relationship handling, query building
Raw SQL Benefits: Full control, complex queries, performance optimization, database-specific features
When to Use Each: GORM for rapid development and simple queries, raw SQL for complex operations and performance-critical code

Database Connection Management

Connection Pooling: Reusing database connections for efficiency
Connection Limits: Preventing database overload
Health Checking: Monitoring database connectivity
Graceful Shutdown: Properly closing connections during application shutdown

Key Terminology

ORM (Object-Relational Mapping): Framework that maps database tables to Go structs
Migration: Script that modifies database schema
Transaction: Group of database operations that succeed or fail together
Connection Pool: Cache of database connections for reuse
Query Builder: Tool for constructing SQL queries programmatically
Repository: Interface that abstracts data access operations

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

Understanding Database Architecture in Go

Go’s database ecosystem provides multiple layers of abstraction. At the lowest level, the database/sql package provides a common interface for SQL databases. GORM builds on top of this to provide ORM functionality, while packages like sqlx add convenience features to raw SQL operations.

Database Connection Lifecycle

Driver Registration: SQL drivers register themselves with database/sql
Connection Opening: Application establishes connection pool
Query Execution: Individual queries acquire connections from pool
Connection Return: Connections return to pool after query completion
Pool Management: Idle connections are managed and cleaned up

GORM Architecture

GORM provides several key components:

Models: Go structs that represent database tables
Associations: Relationships between models (has one, has many, belongs to)
Hooks: Functions that execute before/after database operations
Scopes: Reusable query modifications
Plugins: Extensions for additional functionality

Transaction Management

Transactions ensure data consistency by:

Atomicity: All operations succeed or none do
Consistency: Database constraints are maintained
Isolation: Concurrent transactions don’t interfere
Durability: Committed changes persist

Common Misconceptions

Misconception: “ORMs are always slower than raw SQL” Reality: Well-optimized ORM queries can perform similarly to raw SQL, and development speed often outweighs minor performance differences.
Misconception: “You should always use transactions for single operations” Reality: Transactions add overhead; use them when you need atomicity across multiple operations.
Misconception: “Connection pooling is automatic and doesn’t need configuration” Reality: Pool settings significantly impact performance and need proper tuning for production.

5. Live Coding Demonstration (20-25 minutes)

Example 1: GORM Setup and Basic CRUD Operations

// models/database.go
package models

import (
    "log"
    "net"
    "net/url"
    "os"
    "time"
    
    "gorm.io/driver/postgres"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

var DB *gorm.DB

// DatabaseConfig holds database configuration
type DatabaseConfig struct {
    Host     string
    Port     string
    User     string
    Password string
    DBName   string
    SSLMode  string
}

// InitDatabase initializes the database connection
func InitDatabase() *gorm.DB {
    var db *gorm.DB
    var err error
    
    // Get database configuration from environment
    config := DatabaseConfig{
        Host:     getEnv("DB_HOST", "localhost"),
        Port:     getEnv("DB_PORT", "5432"),
        User:     getEnv("DB_USER", "postgres"),
        Password: os.Getenv("DB_PASSWORD"),
        DBName:   getEnv("DB_NAME", "ginapp"),
        SSLMode:  getEnv("DB_SSLMODE", "require"), // set "disable" only for trusted local development
    }
    
    // Choose database driver based on environment
    if os.Getenv("DB_DRIVER") == "sqlite" {
        db, err = gorm.Open(sqlite.Open("app.db"), &gorm.Config{
            Logger: logger.Default.LogMode(logger.Info),
        })
    } else {
        if config.Password == "" { log.Fatal("DB_PASSWORD is required for PostgreSQL") }
        // url.UserPassword escapes special characters; string interpolation of
        // raw passwords into a key/value DSN can break its syntax.
        u := &url.URL{Scheme: "postgres", Host: net.JoinHostPort(config.Host, config.Port),
            User: url.UserPassword(config.User, config.Password), Path: "/" + config.DBName}
        q := url.Values{}
        q.Set("sslmode", config.SSLMode)
        u.RawQuery = q.Encode()
        
        db, err = gorm.Open(postgres.Open(u.String()), &gorm.Config{
            Logger: logger.Default.LogMode(logger.Info),
        })
    }
    
    if err != nil {
        log.Fatal("Failed to connect to database:", err)
    }
    
    // Configure connection pool
    sqlDB, err := db.DB()
    if err != nil {
        log.Fatal("Failed to get database instance:", err)
    }
    
    // Connection pool settings
    sqlDB.SetMaxOpenConns(25)                 // Maximum open connections
    sqlDB.SetMaxIdleConns(5)                  // Maximum idle connections
    sqlDB.SetConnMaxLifetime(5 * time.Minute) // Connection lifetime
    
    // Test connection
    if err := sqlDB.Ping(); err != nil {
        log.Fatal("Failed to ping database:", err)
    }
    
    log.Println("Database connection established successfully")
    
    DB = db
    return db
}

// getEnv gets environment variable with fallback
func getEnv(key, fallback string) string {
    if value := os.Getenv(key); value != "" {
        return value
    }
    return fallback
}

// models/user.go
package models

import (
    "time"
    "golang.org/x/crypto/bcrypt"
    "gorm.io/gorm"
)

// User represents a user in the system
type User struct {
    ID        uint           `json:"id" gorm:"primaryKey"`
    Username  string         `json:"username" gorm:"uniqueIndex;not null;size:50"`
    Email     string         `json:"email" gorm:"uniqueIndex;not null;size:100"`
    FullName  string         `json:"full_name" gorm:"size:100"`
    Password  string         `json:"-" gorm:"not null"` // Hidden from JSON
    Role      string         `json:"role" gorm:"default:user;size:20"`
    Active    bool           `json:"active" gorm:"default:true"`
    LastLogin *time.Time     `json:"last_login"`
    CreatedAt time.Time      `json:"created_at"`
    UpdatedAt time.Time      `json:"updated_at"`
    DeletedAt gorm.DeletedAt `json:"-" gorm:"index"` // Soft delete
    
    // Relationships
    Posts    []Post    `json:"posts,omitempty" gorm:"foreignKey:AuthorID"`
    Comments []Comment `json:"comments,omitempty" gorm:"foreignKey:AuthorID"`
}

// BeforeCreate GORM hook - runs before creating user
func (u *User) BeforeCreate(tx *gorm.DB) error {
    if u.Password != "" {
        hashedPassword, err := bcrypt.GenerateFromPassword([]byte(u.Password), bcrypt.DefaultCost)
        if err != nil {
            return err
        }
        u.Password = string(hashedPassword)
    }
    return nil
}

// CheckPassword validates user password
func (u *User) CheckPassword(password string) bool {
    err := bcrypt.CompareHashAndPassword([]byte(u.Password), []byte(password))
    return err == nil
}

// Post represents a blog post
type Post struct {
    ID          uint           `json:"id" gorm:"primaryKey"`
    Title       string         `json:"title" gorm:"not null;size:200"`
    Slug        string         `json:"slug" gorm:"uniqueIndex;not null;size:200"`
    Content     string         `json:"content" gorm:"type:text"`
    Excerpt     string         `json:"excerpt" gorm:"size:500"`
    AuthorID    uint           `json:"author_id" gorm:"not null"`
    Author      User           `json:"author" gorm:"foreignKey:AuthorID"`
    CategoryID  uint           `json:"category_id"`
    Category    Category       `json:"category" gorm:"foreignKey:CategoryID"`
    Tags        []Tag          `json:"tags" gorm:"many2many:post_tags;"`
    Published   bool           `json:"published" gorm:"default:false"`
    PublishedAt *time.Time     `json:"published_at"`
    ViewCount   int            `json:"view_count" gorm:"default:0"`
    CreatedAt   time.Time      `json:"created_at"`
    UpdatedAt   time.Time      `json:"updated_at"`
    DeletedAt   gorm.DeletedAt `json:"-" gorm:"index"`
    
    // Relationships
    Comments []Comment `json:"comments,omitempty" gorm:"foreignKey:PostID"`
}

// Category represents a post category
type Category struct {
    ID          uint           `json:"id" gorm:"primaryKey"`
    Name        string         `json:"name" gorm:"uniqueIndex;not null;size:50"`
    Slug        string         `json:"slug" gorm:"uniqueIndex;not null;size:50"`
    Description string         `json:"description" gorm:"size:200"`
    CreatedAt   time.Time      `json:"created_at"`
    UpdatedAt   time.Time      `json:"updated_at"`
    DeletedAt   gorm.DeletedAt `json:"-" gorm:"index"`
    
    // Relationships
    Posts []Post `json:"posts,omitempty" gorm:"foreignKey:CategoryID"`
}

// Tag represents a post tag
type Tag struct {
    ID        uint           `json:"id" gorm:"primaryKey"`
    Name      string         `json:"name" gorm:"uniqueIndex;not null;size:30"`
    CreatedAt time.Time      `json:"created_at"`
    UpdatedAt time.Time      `json:"updated_at"`
    DeletedAt gorm.DeletedAt `json:"-" gorm:"index"`
    
    // Relationships
    Posts []Post `json:"posts,omitempty" gorm:"many2many:post_tags;"`
}

// Comment represents a post comment
type Comment struct {
    ID        uint           `json:"id" gorm:"primaryKey"`
    PostID    uint           `json:"post_id" gorm:"not null"`
    Post      Post           `json:"post" gorm:"foreignKey:PostID"`
    AuthorID  uint           `json:"author_id" gorm:"not null"`
    Author    User           `json:"author" gorm:"foreignKey:AuthorID"`
    Content   string         `json:"content" gorm:"type:text;not null"`
    ParentID  *uint          `json:"parent_id"` // For nested comments
    Parent    *Comment       `json:"parent" gorm:"foreignKey:ParentID"`
    Replies   []Comment      `json:"replies,omitempty" gorm:"foreignKey:ParentID"`
    CreatedAt time.Time      `json:"created_at"`
    UpdatedAt time.Time      `json:"updated_at"`
    DeletedAt gorm.DeletedAt `json:"-" gorm:"index"`
}

// AutoMigrate runs database migrations
func AutoMigrate(db *gorm.DB) error {
    return db.AutoMigrate(
        &User{},
        &Category{},
        &Tag{},
        &Post{},
        &Comment{},
    )
}

Example 2: Repository Pattern with CRUD Operations

// repositories/user_repository.go
package repositories

import (
    "errors"
    "time"
    
    "gorm.io/gorm"
    "github.com/your-app/models"
)

// UserRepository interface defines user data operations
type UserRepository interface {
    Create(user *models.User) error
    GetByID(id uint) (*models.User, error)
    GetByUsername(username string) (*models.User, error)
    GetByEmail(email string) (*models.User, error)
    Update(user *models.User) error
    Delete(id uint) error
    List(offset, limit int) ([]models.User, int64, error)
    Search(query string, offset, limit int) ([]models.User, int64, error)
    UpdateLastLogin(id uint) error
}

// userRepository implements UserRepository interface
type userRepository struct {
    db *gorm.DB
}

// NewUserRepository creates a new user repository
func NewUserRepository(db *gorm.DB) UserRepository {
    return &userRepository{db: db}
}

// Create creates a new user
func (r *userRepository) Create(user *models.User) error {
    if err := r.db.Create(user).Error; err != nil {
        return err
    }
    return nil
}

// GetByID retrieves a user by ID
func (r *userRepository) GetByID(id uint) (*models.User, error) {
    var user models.User
    err := r.db.Preload("Posts").Preload("Comments").First(&user, id).Error
    if err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, errors.New("user not found")
        }
        return nil, err
    }
    return &user, nil
}

// GetByUsername retrieves a user by username
func (r *userRepository) GetByUsername(username string) (*models.User, error) {
    var user models.User
    err := r.db.Where("username = ?", username).First(&user).Error
    if err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, errors.New("user not found")
        }
        return nil, err
    }
    return &user, nil
}

// GetByEmail retrieves a user by email
func (r *userRepository) GetByEmail(email string) (*models.User, error) {
    var user models.User
    err := r.db.Where("email = ?", email).First(&user).Error
    if err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, errors.New("user not found")
        }
        return nil, err
    }
    return &user, nil
}

// Update updates a user
func (r *userRepository) Update(user *models.User) error {
    return r.db.Save(user).Error
}

// Delete soft deletes a user
func (r *userRepository) Delete(id uint) error {
    return r.db.Delete(&models.User{}, id).Error
}

// List retrieves paginated users
func (r *userRepository) List(offset, limit int) ([]models.User, int64, error) {
    var users []models.User
    var total int64
    
    // Get total count
    if err := r.db.Model(&models.User{}).Count(&total).Error; err != nil {
        return nil, 0, err
    }
    
    // Get paginated results
    err := r.db.Offset(offset).Limit(limit).Find(&users).Error
    return users, total, err
}

// Search searches users by username or full name
func (r *userRepository) Search(query string, offset, limit int) ([]models.User, int64, error) {
    var users []models.User
    var total int64
    
    searchQuery := "%" + query + "%"
    baseQuery := r.db.Where("username ILIKE ? OR full_name ILIKE ?", searchQuery, searchQuery)
    
    // Get total count
    if err := baseQuery.Model(&models.User{}).Count(&total).Error; err != nil {
        return nil, 0, err
    }
    
    // Get paginated results
    err := baseQuery.Offset(offset).Limit(limit).Find(&users).Error
    return users, total, err
}

// UpdateLastLogin updates user's last login time
func (r *userRepository) UpdateLastLogin(id uint) error {
    now := time.Now()
    return r.db.Model(&models.User{}).Where("id = ?", id).Update("last_login", &now).Error
}

// handlers/user_handler.go
package handlers

import (
    "net/http"
    "strconv"
    
    "github.com/gin-gonic/gin"
    "github.com/your-app/models"
    "github.com/your-app/repositories"
    "github.com/your-app/services"
)

// UserHandler handles user-related HTTP requests
type UserHandler struct {
    userRepo    repositories.UserRepository
    userService services.UserService
}

// NewUserHandler creates a new user handler
func NewUserHandler(userRepo repositories.UserRepository, userService services.UserService) *UserHandler {
    return &UserHandler{
        userRepo:    userRepo,
        userService: userService,
    }
}

// CreateUser creates a new user
func (h *UserHandler) CreateUser(c *gin.Context) {
    var req struct {
        Username string `json:"username" binding:"required,min=3,max=50,alphanum"`
        Email    string `json:"email" binding:"required,email"`
        FullName string `json:"full_name" binding:"required,min=2,max=100"`
        Password string `json:"password" binding:"required,min=8"`
        Role     string `json:"role" binding:"omitempty,oneof=user admin"`
    }
    
    if err := c.ShouldBindJSON(&req); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{
            "error":   "Invalid input",
            "details": err.Error(),
        })
        return
    }
    
    // Check if user already exists
    if existingUser, _ := h.userRepo.GetByUsername(req.Username); existingUser != nil {
        c.JSON(http.StatusConflict, gin.H{
            "error": "Username already exists",
        })
        return
    }
    
    if existingUser, _ := h.userRepo.GetByEmail(req.Email); existingUser != nil {
        c.JSON(http.StatusConflict, gin.H{
            "error": "Email already exists",
        })
        return
    }
    
    // Create user
    user := &models.User{
        Username: req.Username,
        Email:    req.Email,
        FullName: req.FullName,
        Password: req.Password,
        Role:     req.Role,
    }
    
    if user.Role == "" {
        user.Role = "user"
    }
    
    if err := h.userRepo.Create(user); err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{
            "error": "Failed to create user",
        })
        return
    }
    
    c.JSON(http.StatusCreated, gin.H{
        "message": "User created successfully",
        "user":    user,
    })
}

// GetUser retrieves a user by ID
func (h *UserHandler) GetUser(c *gin.Context) {
    idStr := c.Param("id")
    id, err := strconv.ParseUint(idStr, 10, 32)
    if err != nil {
        c.JSON(http.StatusBadRequest, gin.H{
            "error": "Invalid user ID",
        })
        return
    }
    
    user, err := h.userRepo.GetByID(uint(id))
    if err != nil {
        c.JSON(http.StatusNotFound, gin.H{
            "error": err.Error(),
        })
        return
    }
    
    c.JSON(http.StatusOK, gin.H{
        "user": user,
    })
}

// ListUsers retrieves paginated users
func (h *UserHandler) ListUsers(c *gin.Context) {
    page, _ := strconv.Atoi(c.DefaultQuery("page", "1"))
    limit, _ := strconv.Atoi(c.DefaultQuery("limit", "20"))
    search := c.Query("search")
    
    if page < 1 {
        page = 1
    }
    if limit < 1 || limit > 100 {
        limit = 20
    }
    
    offset := (page - 1) * limit
    
    var users []models.User
    var total int64
    var err error
    
    if search != "" {
        users, total, err = h.userRepo.Search(search, offset, limit)
    } else {
        users, total, err = h.userRepo.List(offset, limit)
    }
    
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{
            "error": "Failed to retrieve users",
        })
        return
    }
    
    totalPages := (total + int64(limit) - 1) / int64(limit)
    
    c.JSON(http.StatusOK, gin.H{
        "users": users,
        "pagination": gin.H{
            "page":        page,
            "limit":       limit,
            "total":       total,
            "total_pages": totalPages,
            "has_next":    page < int(totalPages),
            "has_prev":    page > 1,
        },
    })
}

// UpdateUser updates an existing user
func (h *UserHandler) UpdateUser(c *gin.Context) {
    idStr := c.Param("id")
    id, err := strconv.ParseUint(idStr, 10, 32)
    if err != nil {
        c.JSON(http.StatusBadRequest, gin.H{
            "error": "Invalid user ID",
        })
        return
    }
    
    user, err := h.userRepo.GetByID(uint(id))
    if err != nil {
        c.JSON(http.StatusNotFound, gin.H{
            "error": err.Error(),
        })
        return
    }
    
    var req struct {
        FullName string `json:"full_name" binding:"omitempty,min=2,max=100"`
        Email    string `json:"email" binding:"omitempty,email"`
        Role     string `json:"role" binding:"omitempty,oneof=user admin"`
        Active   *bool  `json:"active"`
    }
    
    if err := c.ShouldBindJSON(&req); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{
            "error":   "Invalid input",
            "details": err.Error(),
        })
        return
    }
    
    // Update fields if provided
    if req.FullName != "" {
        user.FullName = req.FullName
    }
    if req.Email != "" {
        user.Email = req.Email
    }
    if req.Role != "" {
        user.Role = req.Role
    }
    if req.Active != nil {
        user.Active = *req.Active
    }
    
    if err := h.userRepo.Update(user); err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{
            "error": "Failed to update user",
        })
        return
    }
    
    c.JSON(http.StatusOK, gin.H{
        "message": "User updated successfully",
        "user":    user,
    })
}

// DeleteUser soft deletes a user
func (h *UserHandler) DeleteUser(c *gin.Context) {
    idStr := c.Param("id")
    id, err := strconv.ParseUint(idStr, 10, 32)
    if err != nil {
        c.JSON(http.StatusBadRequest, gin.H{
            "error": "Invalid user ID",
        })
        return
    }
    
    if err := h.userRepo.Delete(uint(id)); err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{
            "error": "Failed to delete user",
        })
        return
    }
    
    c.JSON(http.StatusOK, gin.H{
        "message": "User deleted successfully",
    })
}

Example 3: Raw SQL Operations and Transactions

// repositories/post_repository_sql.go
package repositories

import (
    "database/sql"
    "fmt"
    "strings"
    "time"
    
    "github.com/jmoiron/sqlx"
    "github.com/your-app/models"
)

// PostRepositorySQL implements post repository using raw SQL
type PostRepositorySQL struct {
    db *sqlx.DB
}

// NewPostRepositorySQL creates a new SQL-based post repository
func NewPostRepositorySQL(db *sqlx.DB) *PostRepositorySQL {
    return &PostRepositorySQL{db: db}
}

// CreateWithTransaction creates a post with tags in a transaction
func (r *PostRepositorySQL) CreateWithTransaction(post *models.Post, tagNames []string) error {
    // Start transaction
    tx, err := r.db.Beginx()
    if err != nil {
        return fmt.Errorf("failed to start transaction: %w", err)
    }
    
    // Ensure transaction is rolled back if something goes wrong
    defer func() {
        if err != nil {
            tx.Rollback()
        }
    }()
    
    // Insert post
    query := `
        INSERT INTO posts (title, slug, content, excerpt, author_id, category_id, published, created_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
        RETURNING id`
    
    err = tx.QueryRow(query,
        post.Title, post.Slug, post.Content, post.Excerpt,
        post.AuthorID, post.CategoryID, post.Published,
        time.Now(), time.Now(),
    ).Scan(&post.ID)
    
    if err != nil {
        return fmt.Errorf("failed to insert post: %w", err)
    }
    
    // Insert or get tags
    for _, tagName := range tagNames {
        var tagID uint
        
        // Try to get existing tag
        tagQuery := "SELECT id FROM tags WHERE name = $1"
        err := tx.QueryRow(tagQuery, tagName).Scan(&tagID)
        
        if err == sql.ErrNoRows {
            // Tag doesn't exist, create it
            insertTagQuery := `
                INSERT INTO tags (name, created_at, updated_at)
                VALUES ($1, $2, $3)
                RETURNING id`
            
            err = tx.QueryRow(insertTagQuery, tagName, time.Now(), time.Now()).Scan(&tagID)
            if err != nil {
                return fmt.Errorf("failed to create tag %s: %w", tagName, err)
            }
        } else if err != nil {
            return fmt.Errorf("failed to query tag %s: %w", tagName, err)
        }
        
        // Link post to tag
        linkQuery := `
            INSERT INTO post_tags (post_id, tag_id)
            VALUES ($1, $2)
            ON CONFLICT (post_id, tag_id) DO NOTHING`
        
        _, err = tx.Exec(linkQuery, post.ID, tagID)
        if err != nil {
            return fmt.Errorf("failed to link post to tag: %w", err)
        }
    }
    
    // Commit transaction
    if err = tx.Commit(); err != nil {
        return fmt.Errorf("failed to commit transaction: %w", err)
    }
    
    return nil
}

// GetWithDetails retrieves a post with all related data
func (r *PostRepositorySQL) GetWithDetails(id uint) (*models.Post, error) {
    query := `
        SELECT 
            p.id, p.title, p.slug, p.content, p.excerpt, p.published, 
            p.published_at, p.view_count, p.created_at, p.updated_at,
            u.id as author_id, u.username, u.full_name,
            c.id as category_id, c.name as category_name, c.slug as category_slug
        FROM posts p
        JOIN users u ON p.author_id = u.id
        LEFT JOIN categories c ON p.category_id = c.id
        WHERE p.id = $1 AND p.deleted_at IS NULL`
    
    var post models.Post
    var publishedAt sql.NullTime
    var categoryID sql.NullInt64
    var categoryName, categorySlug sql.NullString
    
    err := r.db.QueryRow(query, id).Scan(
        &post.ID, &post.Title, &post.Slug, &post.Content, &post.Excerpt,
        &post.Published, &publishedAt, &post.ViewCount, &post.CreatedAt, &post.UpdatedAt,
        &post.Author.ID, &post.Author.Username, &post.Author.FullName,
        &categoryID, &categoryName, &categorySlug,
    )
    
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, fmt.Errorf("post not found")
        }
        return nil, fmt.Errorf("failed to query post: %w", err)
    }
    
    // Handle nullable fields
    if publishedAt.Valid {
        post.PublishedAt = &publishedAt.Time
    }
    
    if categoryID.Valid {
        post.Category = models.Category{
            ID:   uint(categoryID.Int64),
            Name: categoryName.String,
            Slug: categorySlug.String,
        }
    }
    
    // Get tags
    tagQuery := `
        SELECT t.id, t.name
        FROM tags t
        JOIN post_tags pt ON t.id = pt.tag_id
        WHERE pt.post_id = $1`
    
    rows, err := r.db.Query(tagQuery, id)
    if err != nil {
        return nil, fmt.Errorf("failed to query tags: %w", err)
    }
    defer rows.Close()
    
    var tags []models.Tag
    for rows.Next() {
        var tag models.Tag
        if err := rows.Scan(&tag.ID, &tag.Name); err != nil {
            return nil, fmt.Errorf("failed to scan tag: %w", err)
        }
        tags = append(tags, tag)
    }
    
    post.Tags = tags
    
    return &post, nil
}

// SearchPosts performs full-text search with filters
func (r *PostRepositorySQL) SearchPosts(params SearchParams) ([]models.Post, int, error) {
    var conditions []string
    var args []interface{}
    argIndex := 1
    
    // Base WHERE conditions
    conditions = append(conditions, "p.deleted_at IS NULL")
    
    if params.Published != nil {
        conditions = append(conditions, fmt.Sprintf("p.published = $%d", argIndex))
        args = append(args, *params.Published)
        argIndex++
    }
    
    if params.CategoryID > 0 {
        conditions = append(conditions, fmt.Sprintf("p.category_id = $%d", argIndex))
        args = append(args, params.CategoryID)
        argIndex++
    }
    
    if params.AuthorID > 0 {
        conditions = append(conditions, fmt.Sprintf("p.author_id = $%d", argIndex))
        args = append(args, params.AuthorID)
        argIndex++
    }
    
    if params.Search != "" {
        searchCondition := fmt.Sprintf(`
            (p.title ILIKE $%d OR p.content ILIKE $%d OR p.excerpt ILIKE $%d)`,
            argIndex, argIndex, argIndex)
        conditions = append(conditions, searchCondition)
        args = append(args, "%"+params.Search+"%")
        argIndex++
    }
    
    if len(params.Tags) > 0 {
        placeholders := make([]string, len(params.Tags))
        for i, tag := range params.Tags {
            placeholders[i] = fmt.Sprintf("$%d", argIndex)
            args = append(args, tag)
            argIndex++
        }
        
        tagCondition := fmt.Sprintf(`
            p.id IN (
                SELECT pt.post_id 
                FROM post_tags pt 
                JOIN tags t ON pt.tag_id = t.id 
                WHERE t.name IN (%s)
                GROUP BY pt.post_id 
                HAVING COUNT(DISTINCT t.name) = %d
            )`, strings.Join(placeholders, ","), len(params.Tags))
        
        conditions = append(conditions, tagCondition)
    }
    
    whereClause := strings.Join(conditions, " AND ")
    
    // Count query
    countQuery := fmt.Sprintf(`
        SELECT COUNT(DISTINCT p.id)
        FROM posts p
        JOIN users u ON p.author_id = u.id
        LEFT JOIN categories c ON p.category_id = c.id
        WHERE %s`, whereClause)
    
    var total int
    err := r.db.QueryRow(countQuery, args...).Scan(&total)
    if err != nil {
        return nil, 0, fmt.Errorf("failed to count posts: %w", err)
    }
    
    // Main query
    orderBy := "p.created_at DESC"
    if params.SortBy != "" {
        switch params.SortBy {
        case "title":
            orderBy = "p.title " + params.SortDir
        case "created_at":
            orderBy = "p.created_at " + params.SortDir
        case "view_count":
            orderBy = "p.view_count " + params.SortDir
        }
    }
    
    query := fmt.Sprintf(`
        SELECT DISTINCT
            p.id, p.title, p.slug, p.excerpt, p.published, 
            p.published_at, p.view_count, p.created_at, p.updated_at,
            u.id as author_id, u.username, u.full_name,
            c.id as category_id, c.name as category_name, c.slug as category_slug
        FROM posts p
        JOIN users u ON p.author_id = u.id
        LEFT JOIN categories c ON p.category_id = c.id
        WHERE %s
        ORDER BY %s
        LIMIT $%d OFFSET $%d`,
        whereClause, orderBy, argIndex, argIndex+1)
    
    args = append(args, params.Limit, params.Offset)
    
    rows, err := r.db.Query(query, args...)
    if err != nil {
        return nil, 0, fmt.Errorf("failed to query posts: %w", err)
    }
    defer rows.Close()
    
    var posts []models.Post
    for rows.Next() {
        var post models.Post
        var publishedAt sql.NullTime
        var categoryID sql.NullInt64
        var categoryName, categorySlug sql.NullString
        
        err := rows.Scan(
            &post.ID, &post.Title, &post.Slug, &post.Excerpt, &post.Published,
            &publishedAt, &post.ViewCount, &post.CreatedAt, &post.UpdatedAt,
            &post.Author.ID, &post.Author.Username, &post.Author.FullName,
            &categoryID, &categoryName, &categorySlug,
        )
        
        if err != nil {
            return nil, 0, fmt.Errorf("failed to scan post: %w", err)
        }
        
        if publishedAt.Valid {
            post.PublishedAt = &publishedAt.Time
        }
        
        if categoryID.Valid {
            post.Category = models.Category{
                ID:   uint(categoryID.Int64),
                Name: categoryName.String,
                Slug: categorySlug.String,
            }
        }
        
        posts = append(posts, post)
    }
    
    return posts, total, nil
}

// SearchParams represents search parameters
type SearchParams struct {
    Search     string
    CategoryID uint
    AuthorID   uint
    Tags       []string
    Published  *bool
    SortBy     string
    SortDir    string
    Limit      int
    Offset     int
}

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

Exercise 1: E-commerce Database Design (15 minutes)

Objective: Design and implement a complete e-commerce database schema

Instructions:

Create models for Products, Categories, Orders, OrderItems, and Customers
Implement proper relationships and constraints
Add GORM hooks for automatic price calculations
Create repository interfaces and implementations
Build REST endpoints for all entities

Starter Code:

// Define your models with proper relationships
type Product struct {
    // TODO: Add fields for product management
    // Include: ID, Name, Description, Price, SKU, Stock, CategoryID
}

type Order struct {
    // TODO: Add fields for order management
    // Include: ID, CustomerID, Status, Total, Items, CreatedAt
}

// TODO: Implement repositories and handlers

Solution:

// models/ecommerce.go
type Product struct {
    ID          uint            `json:"id" gorm:"primaryKey"`
    Name        string          `json:"name" gorm:"not null;size:200"`
    Description string          `json:"description" gorm:"type:text"`
    Price       decimal.Decimal `json:"price" gorm:"type:decimal(10,2);not null"`
    SKU         string          `json:"sku" gorm:"uniqueIndex;not null;size:50"`
    Stock       int             `json:"stock" gorm:"default:0"`
    CategoryID  uint            `json:"category_id"`
    Category    Category        `json:"category" gorm:"foreignKey:CategoryID"`
    Images      []ProductImage  `json:"images" gorm:"foreignKey:ProductID"`
    CreatedAt   time.Time       `json:"created_at"`
    UpdatedAt   time.Time       `json:"updated_at"`
    DeletedAt   gorm.DeletedAt  `json:"-" gorm:"index"`
}

type Order struct {
    ID         uint              `json:"id" gorm:"primaryKey"`
    CustomerID uint              `json:"customer_id" gorm:"not null"`
    Customer   Customer          `json:"customer" gorm:"foreignKey:CustomerID"`
    Status     string            `json:"status" gorm:"default:pending;size:20"`
    Total      decimal.Decimal   `json:"total" gorm:"type:decimal(10,2)"`
    Items      []OrderItem       `json:"items" gorm:"foreignKey:OrderID"`
    CreatedAt  time.Time         `json:"created_at"`
    UpdatedAt  time.Time         `json:"updated_at"`
}

type OrderItem struct {
    ID        uint            `json:"id" gorm:"primaryKey"`
    OrderID   uint            `json:"order_id" gorm:"not null"`
    ProductID uint            `json:"product_id" gorm:"not null"`
    Product   Product         `json:"product" gorm:"foreignKey:ProductID"`
    Quantity  int             `json:"quantity" gorm:"not null"`
    Price     decimal.Decimal `json:"price" gorm:"type:decimal(10,2);not null"`
    Total     decimal.Decimal `json:"total" gorm:"type:decimal(10,2)"`
}

// GORM hooks for automatic calculations
func (oi *OrderItem) BeforeCreate(tx *gorm.DB) error {
    oi.Total = oi.Price.Mul(decimal.NewFromInt(int64(oi.Quantity)))
    return nil
}

func (o *Order) AfterFind(tx *gorm.DB) error {
    var total decimal.Decimal
    for _, item := range o.Items {
        total = total.Add(item.Total)
    }
    o.Total = total
    return nil
}

Exercise 2: Blog with Full-Text Search (15 minutes)

Objective: Implement advanced search functionality for a blog platform

Requirements:

Full-text search across title and content
Tag-based filtering
Category filtering
Date range filtering
Sorting options (relevance, date, popularity)
Pagination with proper metadata

Hints:

Use PostgreSQL’s full-text search capabilities
Implement proper indexing for performance
Consider search result ranking

Exercise 3: NoSQL Integration with MongoDB (10 minutes)

Objective: Add MongoDB support for flexible data storage

Challenge: Implement a logging system using MongoDB to store:

API request logs
User activity logs
Error logs
Performance metrics

Extension Ideas:

Add log aggregation and analytics
Implement log retention policies
Create dashboard for log visualization

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

Problem: N+1 query problems with GORM Cause: Not using proper preloading for related data Solution: Use Preload() or Joins() to eagerly load related data
Problem: Database connection pool exhaustion Cause: Not configuring connection pool limits properly Solution: Set appropriate MaxOpenConns, MaxIdleConns, and ConnMaxLifetime
Problem: Memory leaks with large result sets Cause: Loading too much data into memory at once Solution: Use pagination, streaming, or chunked processing for large datasets

Debugging Tips

Enable GORM’s logger to see generated SQL queries
Use database query analyzers to identify slow queries
Monitor connection pool metrics in production
Use database profiling tools to identify bottlenecks

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

Use database migrations for schema changes
Implement proper error handling and transaction rollback
Use connection pooling and prepared statements
Index frequently queried columns
Implement soft deletes for data integrity

Next Steps in Your Learning Journey

Learn about database performance optimization and indexing strategies
Explore advanced GORM features like custom data types and hooks
Study database sharding and replication patterns
Investigate event sourcing and CQRS patterns
Practice with different database technologies (PostgreSQL, MongoDB, Redis)

9. Summary and Review (5 minutes)

Key Takeaways

GORM provides powerful ORM capabilities while raw SQL offers maximum control
Repository pattern creates clean separation between data access and business logic
Connection pooling and transactions are essential for production applications
Proper error handling prevents data corruption and improves user experience

Quick Review Questions

When should you use GORM vs. raw SQL?
How do you implement database transactions in Go?
What is the repository pattern and why is it useful?
How do you configure database connection pooling?

For Instructors

Teaching Tips

Use database visualization tools to show relationships and schema design
Demonstrate both successful operations and error scenarios
Show performance differences between different query approaches
Encourage students to use database profiling tools
Emphasize the importance of data consistency and integrity

Assessment Ideas

Design a complete database schema for a real-world application
Implement a repository pattern with full CRUD operations
Create a performance comparison between GORM and raw SQL
Build a data migration system with rollback capabilities

Adaptation Notes

For beginners: Focus on GORM basics before introducing raw SQL
For advanced students: Introduce advanced database patterns and performance optimization
Online delivery: Provide Docker containers for consistent database environments
Time management: The raw SQL section can be extended for more complex query examples