Database Integration and Dynamic Content
Learning Objectives: By the end of this lesson, you will be able to:
- Connect Gin applications to databases (SQL and NoSQL)
- Implement CRUD operations with template interfaces
- Handle database relationships in template rendering
- Implement pagination and search functionality
1. Welcome and Introduction (5 minutes)
Icebreaker Activity
Have you ever wondered how social media platforms like Facebook or Instagram can instantly display thousands of posts, comments, and user profiles? Behind every dynamic web application lies a sophisticated database system that stores, retrieves, and manages vast amounts of data seamlessly.
Today we’ll transform our static template applications into dynamic, data-driven systems that can handle real user data, complex relationships, and provide rich interactive experiences that scale to millions of users.
Lesson Overview
- Duration: 4-5 hours
- Prerequisites: Lessons 1-5 completed (all previous Gin and template concepts)
- Materials Needed: Go development environment, database server (PostgreSQL/MySQL), database management tools
2. Learning Objectives and Session Outline (5 minutes)
What We’ll Cover Today:
- Database Architecture - Connecting Go applications to relational and NoSQL databases
- ORM Integration - Using GORM for elegant database operations
- CRUD Operations - Complete Create, Read, Update, Delete functionality with templates
- Relationship Management - Handling complex data relationships in templates
- Advanced Features - Pagination, search, filtering, and performance optimization
Success Criteria:
By the end of this session, you should be able to build complete database-driven web applications with sophisticated data management, user interfaces for CRUD operations, and optimized performance for large datasets.
3. Foundation Knowledge (15-20 minutes)
Core Concepts
Database Integration Patterns
Modern web applications use databases to persist data between requests and provide dynamic content. The choice between SQL (relational) and NoSQL (document-based) databases depends on data structure, scalability requirements, and consistency needs.
Real-World Analogy: A database is like a massive, well-organized filing system in a large corporation. SQL databases are like traditional filing cabinets with strict folder structures and cross-references, while NoSQL databases are like flexible digital storage where documents can have varying formats but are easily searchable.
Why This Matters: Proper database integration enables applications to handle millions of users, maintain data consistency, provide fast search capabilities, and scale horizontally across multiple servers.
Object-Relational Mapping (ORM)
ORMs bridge the gap between object-oriented programming and relational databases by allowing developers to work with database records as Go structs, automatically handling SQL generation, migrations, and relationship management.
Real-World Analogy: An ORM is like a universal translator that allows you to speak in your native language (Go) while communicating with someone who only speaks another language (SQL), handling all the translation seamlessly.
Why This Matters: ORMs reduce development time, prevent SQL injection attacks, provide database abstraction, and make code more maintainable by keeping database logic organized and type-safe.
Template-Database Integration Patterns
Effectively displaying database content in templates requires understanding data loading strategies, relationship handling, pagination techniques, and performance optimization to ensure fast page loads even with large datasets.
Key Terminology
- ORM: Object-Relational Mapping - translates between database and Go structs
- Migration: Database schema changes managed programmatically
- Association: Relationships between database tables (one-to-one, one-to-many, many-to-many)
- Eager Loading: Loading related data in a single query to avoid N+1 problems
- Pagination: Dividing large datasets into manageable chunks
- Query Builder: Programmatic SQL query construction with type safety
- Transaction: Group of database operations that succeed or fail together
4. Detailed Explanation and Examples (25-30 minutes)
Database Architecture and Connection Management
Professional database integration involves several architectural considerations:
Connection Pooling
Database connections are expensive resources that must be managed efficiently. Connection pools maintain a set of reusable database connections, preventing the overhead of establishing new connections for each request.
Migration Management
Database schema changes must be versioned and applied consistently across development, staging, and production environments. Migration systems provide a systematic approach to evolving database structure.
Environment Configuration
Database credentials, connection strings, and configuration parameters should be environment-specific and securely managed through environment variables or configuration files.
GORM Integration Patterns
GORM provides sophisticated features for database operations:
Model Definition
GORM models use struct tags to define database schema, relationships, and constraints. Proper model design ensures data integrity and optimal query performance.
Relationship Handling
GORM supports all relationship types with sophisticated loading strategies:
- Belongs To: Child records referencing parent records
- Has One: Parent records with single child records
- Has Many: Parent records with multiple child records
- Many To Many: Complex relationships requiring junction tables
Query Optimization
Advanced query techniques include:
- Preloading: Loading relationships efficiently
- Scoping: Reusable query conditions
- Raw SQL: Direct SQL for complex operations
- Batch Operations: Efficient bulk data operations
Template Integration Strategies
Displaying database content effectively requires:
Data Transformation
Converting database models to template-friendly formats, handling null values, formatting dates and numbers, and preparing nested data structures.
Pagination Implementation
Dividing large datasets into pages with navigation controls, maintaining search parameters across pages, and providing user-friendly page size options.
Real-time Updates
Implementing live data updates without full page refreshes, handling concurrent user modifications, and maintaining data consistency.
5. Live Coding Demonstration (20-25 minutes)
Example 1: Complete Database Setup with GORM
package main
import (
"fmt"
"html/template"
"log"
"net/http"
"os"
"strconv"
"strings"
"time"
"github.com/gin-gonic/gin"
"gorm.io/driver/postgres"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
// Database models with comprehensive relationships
type User struct {
ID uint `gorm:"primaryKey" json:"id"`
Username string `gorm:"uniqueIndex;not null" json:"username"`
Email string `gorm:"uniqueIndex;not null" json:"email"`
FirstName string `json:"first_name"`
LastName string `json:"last_name"`
Avatar string `json:"avatar"`
Bio string `json:"bio"`
Role string `gorm:"default:user" json:"role"`
Active bool `gorm:"default:true" json:"active"`
CreatedAt time.Time `json:"created_at"`
UpdatedAt time.Time `json:"updated_at"`
// Relationships
Posts []Post `gorm:"foreignKey:AuthorID" json:"posts"`
Comments []Comment `gorm:"foreignKey:UserID" json:"comments"`
Profile Profile `gorm:"foreignKey:UserID" json:"profile"`
}
type Profile struct {
ID uint `gorm:"primaryKey" json:"id"`
UserID uint `gorm:"uniqueIndex" json:"user_id"`
PhoneNumber string `json:"phone_number"`
Address string `json:"address"`
Website string `json:"website"`
DateOfBirth *time.Time `json:"date_of_birth"`
// Belongs to relationship
User User `gorm:"foreignKey:UserID" json:"user"`
}
type Category struct {
ID uint `gorm:"primaryKey" json:"id"`
Name string `gorm:"uniqueIndex;not null" json:"name"`
Slug string `gorm:"uniqueIndex;not null" json:"slug"`
Description string `json:"description"`
Color string `json:"color"`
Icon string `json:"icon"`
// Relationships
Posts []Post `gorm:"foreignKey:CategoryID" json:"posts"`
}
type Post struct {
ID uint `gorm:"primaryKey" json:"id"`
Title string `gorm:"not null" json:"title"`
Slug string `gorm:"uniqueIndex;not null" json:"slug"`
Content string `gorm:"type:text" json:"content"`
Excerpt string `json:"excerpt"`
FeaturedImage string `json:"featured_image"`
Published bool `gorm:"default:false" json:"published"`
ViewCount int `gorm:"default:0" json:"view_count"`
AuthorID uint `gorm:"not null" json:"author_id"`
CategoryID uint `gorm:"not null" json:"category_id"`
CreatedAt time.Time `json:"created_at"`
UpdatedAt time.Time `json:"updated_at"`
PublishedAt *time.Time `json:"published_at"`
// Relationships
Author User `gorm:"foreignKey:AuthorID" json:"author"`
Category Category `gorm:"foreignKey:CategoryID" json:"category"`
Comments []Comment `gorm:"foreignKey:PostID" json:"comments"`
Tags []Tag `gorm:"many2many:post_tags;" json:"tags"`
}
type Comment struct {
ID uint `gorm:"primaryKey" json:"id"`
Content string `gorm:"type:text;not null" json:"content"`
UserID uint `gorm:"not null" json:"user_id"`
PostID uint `gorm:"not null" json:"post_id"`
ParentID *uint `json:"parent_id"` // For nested comments
Approved bool `gorm:"default:false" json:"approved"`
CreatedAt time.Time `json:"created_at"`
UpdatedAt time.Time `json:"updated_at"`
// Relationships
User User `gorm:"foreignKey:UserID" json:"user"`
Post Post `gorm:"foreignKey:PostID" json:"post"`
Parent *Comment `gorm:"foreignKey:ParentID" json:"parent"`
Replies []Comment `gorm:"foreignKey:ParentID" json:"replies"`
}
type Tag struct {
ID uint `gorm:"primaryKey" json:"id"`
Name string `gorm:"uniqueIndex;not null" json:"name"`
Slug string `gorm:"uniqueIndex;not null" json:"slug"`
Color string `json:"color"`
// Many-to-many relationship
Posts []Post `gorm:"many2many:post_tags;" json:"posts"`
}
// Database connection and configuration
type Database struct {
*gorm.DB
}
func NewDatabase() *Database {
// Database configuration
dsn := "host=localhost user=postgres password=password dbname=gin_blog port=5432 sslmode=disable"
db, err := gorm.Open(postgres.Open(dsn), &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)
}
sqlDB.SetMaxIdleConns(10)
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
return &Database{db}
}
// Auto migrate database schema
func (db *Database) Migrate() error {
return db.AutoMigrate(
&User{},
&Profile{},
&Category{},
&Post{},
&Comment{},
&Tag{},
)
}
// Seed database with sample data
func (db *Database) Seed() error {
// Create categories
categories := []Category{
{Name: "Technology", Slug: "technology", Description: "Tech news and tutorials", Color: "#3498db", Icon: "fas fa-laptop"},
{Name: "Programming", Slug: "programming", Description: "Programming guides and tips", Color: "#e74c3c", Icon: "fas fa-code"},
{Name: "Design", Slug: "design", Description: "Design inspiration and tutorials", Color: "#9b59b6", Icon: "fas fa-paint-brush"},
}
for _, category := range categories {
db.FirstOrCreate(&category, Category{Slug: category.Slug})
}
// Create users
users := []User{
{
Username: "john_doe", Email: "john@example.com", FirstName: "John", LastName: "Doe",
Avatar: "/static/images/avatars/john.jpg", Bio: "Full-stack developer with 5 years experience",
Role: "admin",
},
{
Username: "jane_smith", Email: "jane@example.com", FirstName: "Jane", LastName: "Smith",
Avatar: "/static/images/avatars/jane.jpg", Bio: "UI/UX designer passionate about user experience",
Role: "editor",
},
}
for _, user := range users {
db.FirstOrCreate(&user, User{Email: user.Email})
}
// Create tags
tags := []Tag{
{Name: "Go", Slug: "go", Color: "#00ADD8"},
{Name: "Web Development", Slug: "web-development", Color: "#61DAFB"},
{Name: "Tutorial", Slug: "tutorial", Color: "#FFA500"},
{Name: "Best Practices", Slug: "best-practices", Color: "#32CD32"},
}
for _, tag := range tags {
db.FirstOrCreate(&tag, Tag{Slug: tag.Slug})
}
return nil
}
// Repository pattern for database operations
type PostRepository struct {
db *Database
}
func NewPostRepository(db *Database) *PostRepository {
return &PostRepository{db: db}
}
// GetPosts with pagination and filtering
func (r *PostRepository) GetPosts(page, pageSize int, categoryID uint, search string) ([]Post, int64, error) {
var posts []Post
var total int64
query := r.db.Model(&Post{}).Preload("Author").Preload("Category").Preload("Tags")
// Apply filters
if categoryID > 0 {
query = query.Where("category_id = ?", categoryID)
}
if search != "" {
query = query.Where("title ILIKE ? OR content ILIKE ?", "%"+search+"%", "%"+search+"%")
}
// Only published posts
query = query.Where("published = ?", true)
// Get total count
if err := query.Count(&total).Error; err != nil {
return nil, 0, err
}
// Apply pagination
offset := (page - 1) * pageSize
if err := query.Offset(offset).Limit(pageSize).Order("created_at DESC").Find(&posts).Error; err != nil {
return nil, 0, err
}
return posts, total, nil
}
// GetPostBySlug with all relationships
func (r *PostRepository) GetPostBySlug(slug string) (*Post, error) {
var post Post
err := r.db.Preload("Author").Preload("Category").Preload("Tags").
Preload("Comments", "approved = ?", true).
Preload("Comments.User").
Where("slug = ? AND published = ?", slug, true).
First(&post).Error
if err != nil {
return nil, err
}
// Increment view count
r.db.Model(&post).UpdateColumn("view_count", gorm.Expr("view_count + ?", 1))
return &post, nil
}
// CreatePost with validation
func (r *PostRepository) CreatePost(post *Post) error {
// Generate slug from title
post.Slug = generateSlug(post.Title)
// Set published date if publishing
if post.Published && post.PublishedAt == nil {
now := time.Now()
post.PublishedAt = &now
}
return r.db.Create(post).Error
}
// UpdatePost with optimistic locking
func (r *PostRepository) UpdatePost(id uint, updates *Post) error {
var post Post
if err := r.db.First(&post, id).Error; err != nil {
return err
}
// Update published date if status changed
if updates.Published && !post.Published {
now := time.Now()
updates.PublishedAt = &now
}
return r.db.Model(&post).Updates(updates).Error
}
// DeletePost (soft delete)
func (r *PostRepository) DeletePost(id uint) error {
return r.db.Delete(&Post{}, id).Error
}
// Helper function to generate URL-friendly slug
func generateSlug(title string) string {
slug := strings.ToLower(title)
slug = strings.ReplaceAll(slug, " ", "-")
// Remove special characters (simplified)
return slug
}
// Pagination helper
type Pagination struct {
Page int `json:"page"`
PageSize int `json:"page_size"`
Total int64 `json:"total"`
TotalPages int `json:"total_pages"`
HasNext bool `json:"has_next"`
HasPrev bool `json:"has_prev"`
}
func NewPagination(page, pageSize int, total int64) *Pagination {
totalPages := int((total + int64(pageSize) - 1) / int64(pageSize))
return &Pagination{
Page: page,
PageSize: pageSize,
Total: total,
TotalPages: totalPages,
HasNext: page < totalPages,
HasPrev: page > 1,
}
}
func main() {
// Initialize database
db := NewDatabase()
if err := db.Migrate(); err != nil {
log.Fatal("Failed to migrate database:", err)
}
if err := db.Seed(); err != nil {
log.Fatal("Failed to seed database:", err)
}
// Initialize repositories
postRepo := NewPostRepository(db)
r := gin.Default()
// Template functions
r.SetFuncMap(template.FuncMap{
"formatDate": func(t time.Time) string {
return t.Format("January 2, 2006")
},
"timeAgo": func(t time.Time) string {
diff := time.Since(t)
if diff < time.Hour {
return fmt.Sprintf("%d minutes ago", int(diff.Minutes()))
} else if diff < 24*time.Hour {
return fmt.Sprintf("%d hours ago", int(diff.Hours()))
}
return fmt.Sprintf("%d days ago", int(diff.Hours()/24))
},
"truncate": func(s string, length int) string {
if len(s) <= length {
return s
}
return s[:length] + "..."
},
})
r.LoadHTMLGlob("templates/*")
r.Static("/static", "./static")
// Routes
r.GET("/", func(c *gin.Context) {
blogIndexHandler(c, postRepo)
})
r.GET("/post/:slug", func(c *gin.Context) {
postDetailHandler(c, postRepo)
})
r.GET("/category/:slug", func(c *gin.Context) {
categoryHandler(c, postRepo)
})
// Admin routes
adminPassword := os.Getenv("ADMIN_PASSWORD")
if len(adminPassword) < 16 { log.Fatal("set a strong ADMIN_PASSWORD before enabling admin routes") }
admin := r.Group("/admin")
admin.Use(gin.BasicAuth(gin.Accounts{"admin": adminPassword})) // demo guard, not a complete admin security design
{
admin.GET("/posts", func(c *gin.Context) {
adminPostsHandler(c, postRepo)
})
admin.GET("/posts/new", adminNewPostHandler)
admin.POST("/posts", func(c *gin.Context) {
adminCreatePostHandler(c, postRepo)
})
admin.GET("/posts/:id/edit", func(c *gin.Context) {
adminEditPostHandler(c, postRepo)
})
admin.PUT("/posts/:id", func(c *gin.Context) {
adminUpdatePostHandler(c, postRepo)
})
admin.DELETE("/posts/:id", func(c *gin.Context) {
adminDeletePostHandler(c, postRepo)
})
}
r.Run(":8080")
}
// Handlers
func blogIndexHandler(c *gin.Context, repo *PostRepository) {
page, _ := strconv.Atoi(c.DefaultQuery("page", "1"))
if page < 1 {
page = 1
}
pageSize := 10
categoryID, _ := strconv.Atoi(c.Query("category"))
search := c.Query("search")
posts, total, err := repo.GetPosts(page, pageSize, uint(categoryID), search)
if err != nil {
c.HTML(http.StatusInternalServerError, "error.html", gin.H{
"error": "Failed to load posts",
})
return
}
pagination := NewPagination(page, pageSize, total)
c.HTML(http.StatusOK, "blog_index.html", gin.H{
"title": "Blog",
"posts": posts,
"pagination": pagination,
"search": search,
"categoryID": categoryID,
})
}
func postDetailHandler(c *gin.Context, repo *PostRepository) {
slug := c.Param("slug")
post, err := repo.GetPostBySlug(slug)
if err != nil {
c.HTML(http.StatusNotFound, "404.html", gin.H{
"title": "Post Not Found",
})
return
}
c.HTML(http.StatusOK, "post_detail.html", gin.H{
"title": post.Title,
"post": post,
})
}
func adminPostsHandler(c *gin.Context, repo *PostRepository) {
page, _ := strconv.Atoi(c.DefaultQuery("page", "1"))
if page < 1 {
page = 1
}
posts, total, err := repo.GetPosts(page, 20, 0, "")
if err != nil {
c.HTML(http.StatusInternalServerError, "error.html", gin.H{
"error": "Failed to load posts",
})
return
}
pagination := NewPagination(page, 20, total)
c.HTML(http.StatusOK, "admin_posts.html", gin.H{
"title": "Manage Posts",
"posts": posts,
"pagination": pagination,
})
}
func adminCreatePostHandler(c *gin.Context, repo *PostRepository) {
var post Post
if err := c.ShouldBind(&post); err != nil {
c.HTML(http.StatusBadRequest, "admin_post_form.html", gin.H{
"title": "New Post",
"error": err.Error(),
"post": post,
})
return
}
// Set author (in real app, get from session)
post.AuthorID = 1
if err := repo.CreatePost(&post); err != nil {
c.HTML(http.StatusInternalServerError, "admin_post_form.html", gin.H{
"title": "New Post",
"error": "Failed to create post",
"post": post,
})
return
}
c.Redirect(http.StatusFound, "/admin/posts")
}
func adminNewPostHandler(c *gin.Context) {
c.HTML(http.StatusOK, "admin_post_form.html", gin.H{
"title": "New Post",
})
}
func adminEditPostHandler(c *gin.Context, repo *PostRepository) {
id, _ := strconv.Atoi(c.Param("id"))
var post Post
if err := repo.db.First(&post, id).Error; err != nil {
c.HTML(http.StatusNotFound, "404.html", gin.H{
"title": "Post Not Found",
})
return
}
c.HTML(http.StatusOK, "admin_post_form.html", gin.H{
"title": "Edit Post",
"post": post,
"edit": true,
})
}
func adminUpdatePostHandler(c *gin.Context, repo *PostRepository) {
id, _ := strconv.Atoi(c.Param("id"))
var updates Post
if err := c.ShouldBind(&updates); err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
if err := repo.UpdatePost(uint(id), &updates); err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to update post"})
return
}
c.Redirect(http.StatusFound, "/admin/posts")
}
func adminDeletePostHandler(c *gin.Context, repo *PostRepository) {
id, _ := strconv.Atoi(c.Param("id"))
if err := repo.DeletePost(uint(id)); err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to delete post"})
return
}
c.JSON(http.StatusOK, gin.H{"message": "Post deleted successfully"})
}
func categoryHandler(c *gin.Context, repo *PostRepository) {
// Implementation for category-specific posts
c.HTML(http.StatusOK, "category.html", gin.H{
"title": "Category Posts",
})
}
The admin BasicAuth guard is a demo only. Serve it over HTTPS, implement per-user authorization and CSRF protection for browser-initiated writes (Basic Auth credentials may be sent automatically), and replace demo credentials and AutoMigrate with appropriate production controls before exposing it publicly.
Example 2: Advanced Template with Database Integration
Create templates/blog_index.html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>{{.title}}</title>
<style>
body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; margin: 0; padding: 20px; background: #f8f9fa; }
.container { max-width: 1200px; margin: 0 auto; }
.header { background: white; padding: 30px; border-radius: 10px; margin-bottom: 30px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
.search-form { display: flex; gap: 15px; margin-bottom: 20px; }
.search-input { flex: 1; padding: 12px; border: 1px solid #ddd; border-radius: 5px; font-size: 16px; }
.search-button { padding: 12px 24px; background: #007bff; color: white; border: none; border-radius: 5px; cursor: pointer; }
.posts-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(400px, 1fr)); gap: 30px; margin-bottom: 40px; }
.post-card { background: white; border-radius: 10px; overflow: hidden; box-shadow: 0 4px 15px rgba(0,0,0,0.1); transition: transform 0.3s ease; }
.post-card:hover { transform: translateY(-5px); }
.post-image { width: 100%; height: 200px; background: #eee; display: flex; align-items: center; justify-content: center; color: #666; }
.post-content { padding: 25px; }
.post-title { font-size: 1.4rem; font-weight: bold; margin-bottom: 10px; color: #333; }
.post-title a { text-decoration: none; color: inherit; }
.post-title a:hover { color: #007bff; }
.post-meta { color: #666; font-size: 0.9rem; margin-bottom: 15px; }
.post-excerpt { color: #555; line-height: 1.6; margin-bottom: 20px; }
.post-tags { margin-bottom: 15px; }
.tag { background: #e9ecef; color: #495057; padding: 4px 8px; border-radius: 15px; font-size: 0.8rem; margin-right: 8px; text-decoration: none; }
.tag:hover { background: #007bff; color: white; }
.read-more { color: #007bff; text-decoration: none; font-weight: 500; }
.pagination { display: flex; justify-content: center; align-items: center; gap: 10px; margin: 40px 0; }
.page-link { padding: 10px 15px; background: white; border: 1px solid #ddd; text-decoration: none; color: #333; border-radius: 5px; }
.page-link:hover { background: #f8f9fa; }
.page-link.active { background: #007bff; color: white; border-color: #007bff; }
.page-link.disabled { color: #6c757d; pointer-events: none; }
.stats { display: flex; justify-content: space-between; align-items: center; margin-bottom: 20px; color: #666; }
</style>
</head>
<body>
<div class="container">
<div class="header">
<h1>{{.title}}</h1>
{{/* Search form */}}
<form method="GET" class="search-form">
<input type="text" name="search" placeholder="Search posts..."
value="{{.search}}" class="search-input">
<input type="hidden" name="category" value="{{.categoryID}}">
<button type="submit" class="search-button">Search</button>
</form>
{{/* Results stats */}}
<div class="stats">
<div>
Showing {{len .posts}} of {{.pagination.Total}} posts
{{if .search}}- searching for "{{.search}}"{{end}}
</div>
<div>
Page {{.pagination.Page}} of {{.pagination.TotalPages}}
</div>
</div>
</div>
{{if .posts}}
<div class="posts-grid">
{{range .posts}}
<article class="post-card">
{{if .FeaturedImage}}
<div class="post-image">
<img src="{{.FeaturedImage}}" alt="{{.Title}}" style="width: 100%; height: 100%; object-fit: cover;">
</div>
{{else}}
<div class="post-image">
<span>No Image</span>
</div>
{{end}}
<div class="post-content">
<h2 class="post-title">
<a href="/post/{{.Slug}}">{{.Title}}</a>
</h2>
<div class="post-meta">
By <strong>{{.Author.FirstName}} {{.Author.LastName}}</strong>
in <strong>{{.Category.Name}}</strong>
• {{formatDate .CreatedAt}}
• {{.ViewCount}} views
{{if .Comments}}
• {{len .Comments}} comments
{{end}}
</div>
{{if .Excerpt}}
<p class="post-excerpt">{{.Excerpt}}</p>
{{else}}
<p class="post-excerpt">{{truncate .Content 150}}</p>
{{end}}
{{if .Tags}}
<div class="post-tags">
{{range .Tags}}
<a href="/tag/{{.Slug}}" class="tag">{{.Name}}</a>
{{end}}
</div>
{{end}}
<a href="/post/{{.Slug}}" class="read-more">Read More →</a>
</div>
</article>
{{end}}
</div>
{{/* Pagination */}}
{{if gt .pagination.TotalPages 1}}
<div class="pagination">
{{if .pagination.HasPrev}}
<a href="?page={{sub .pagination.Page 1}}{{if .search}}&search={{.search}}{{end}}{{if .categoryID}}&category={{.categoryID}}{{end}}"
class="page-link">← Previous</a>
{{else}}
<span class="page-link disabled">← Previous</span>
{{end}}
{{/* Page numbers */}}
{{$currentPage := .pagination.Page}}
{{$totalPages := .pagination.TotalPages}}
{{range $i := sequence 1 $totalPages}}
{{if eq $i $currentPage}}
<span class="page-link active">{{$i}}</span>
{{else if or (le $i 3) (ge $i (sub $totalPages 2)) (and (ge $i (sub $currentPage 1)) (le $i (add $currentPage 1)))}}
<a href="?page={{$i}}{{if $.search}}&search={{$.search}}{{end}}{{if $.categoryID}}&category={{$.categoryID}}{{end}}"
class="page-link">{{$i}}</a>
{{else if or (eq $i 4) (eq $i (sub $totalPages 3))}}
<span class="page-link disabled">...</span>
{{end}}
{{end}}
{{if .pagination.HasNext}}
<a href="?page={{add .pagination.Page 1}}{{if .search}}&search={{.search}}{{end}}{{if .categoryID}}&category={{.categoryID}}{{end}}"
class="page-link">Next →</a>
{{else}}
<span class="page-link disabled">Next →</span>
{{end}}
</div>
{{end}}
{{else}}
<div style="text-align: center; padding: 60px; background: white; border-radius: 10px;">
<h2>No Posts Found</h2>
{{if .search}}
<p>No posts match your search for "{{.search}}".</p>
<a href="/" class="search-button" style="display: inline-block; margin-top: 20px;">View All Posts</a>
{{else}}
<p>No posts have been published yet.</p>
{{end}}
</div>
{{end}}
</div>
</body>
</html>
Example 3: Admin Interface for CRUD Operations
Create templates/admin_posts.html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>{{.title}} - Admin</title>
<style>
body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; margin: 0; padding: 0; background: #f8f9fa; }
.admin-header { background: #343a40; color: white; padding: 20px 0; }
.container { max-width: 1200px; margin: 0 auto; padding: 0 20px; }
.header-content { display: flex; justify-content: space-between; align-items: center; }
.admin-nav { display: flex; gap: 30px; }
.admin-nav a { color: white; text-decoration: none; padding: 10px 15px; border-radius: 5px; }
.admin-nav a:hover, .admin-nav a.active { background: rgba(255,255,255,0.1); }
.main-content { padding: 30px 0; }
.content-header { display: flex; justify-content: space-between; align-items: center; margin-bottom: 30px; }
.btn { padding: 12px 24px; border: none; border-radius: 5px; text-decoration: none; display: inline-block; cursor: pointer; font-weight: 500; }
.btn-primary { background: #007bff; color: white; }
.btn-success { background: #28a745; color: white; }
.btn-danger { background: #dc3545; color: white; }
.btn-warning { background: #ffc107; color: #212529; }
.btn:hover { opacity: 0.9; }
.posts-table { width: 100%; background: white; border-radius: 10px; overflow: hidden; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
.posts-table th, .posts-table td { padding: 15px; text-align: left; border-bottom: 1px solid #eee; }
.posts-table th { background: #f8f9fa; font-weight: 600; }
.posts-table tr:hover { background: #f8f9fa; }
.status-badge { padding: 4px 8px; border-radius: 12px; font-size: 0.8rem; font-weight: 500; }
.status-published { background: #d4edda; color: #155724; }
.status-draft { background: #f8d7da; color: #721c24; }
.actions { display: flex; gap: 8px; }
.actions .btn { padding: 6px 12px; font-size: 0.9rem; }
.pagination { display: flex; justify-content: center; margin: 30px 0; }
.stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 20px; margin-bottom: 30px; }
.stat-card { background: white; padding: 25px; border-radius: 10px; text-align: center; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
.stat-number { font-size: 2rem; font-weight: bold; color: #007bff; }
.stat-label { color: #666; margin-top: 5px; }
</style>
</head>
<body>
<header class="admin-header">
<div class="container">
<div class="header-content">
<h1>Admin Dashboard</h1>
<nav class="admin-nav">
<a href="/admin" class="active">Dashboard</a>
<a href="/admin/posts">Posts</a>
<a href="/admin/categories">Categories</a>
<a href="/admin/users">Users</a>
<a href="/admin/comments">Comments</a>
<a href="/" target="_blank">View Site</a>
</nav>
</div>
</div>
</header>
<main class="main-content">
<div class="container">
<div class="content-header">
<h2>{{.title}}</h2>
<a href="/admin/posts/new" class="btn btn-primary">New Post</a>
</div>
{{/* Statistics */}}
<div class="stats-grid">
<div class="stat-card">
<div class="stat-number">{{.pagination.Total}}</div>
<div class="stat-label">Total Posts</div>
</div>
<div class="stat-card">
<div class="stat-number">
{{$published := 0}}
{{range .posts}}
{{if .Published}}{{$published = add $published 1}}{{end}}
{{end}}
{{$published}}
</div>
<div class="stat-label">Published</div>
</div>
<div class="stat-card">
<div class="stat-number">
{{$drafts := 0}}
{{range .posts}}
{{if not .Published}}{{$drafts = add $drafts 1}}{{end}}
{{end}}
{{$drafts}}
</div>
<div class="stat-label">Drafts</div>
</div>
<div class="stat-card">
<div class="stat-number">
{{$totalViews := 0}}
{{range .posts}}
{{$totalViews = add $totalViews .ViewCount}}
{{end}}
{{$totalViews}}
</div>
<div class="stat-label">Total Views</div>
</div>
</div>
{{if .posts}}
<table class="posts-table">
<thead>
<tr>
<th>Title</th>
<th>Author</th>
<th>Category</th>
<th>Status</th>
<th>Views</th>
<th>Created</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
{{range .posts}}
<tr>
<td>
<strong>{{.Title}}</strong>
{{if .Excerpt}}
<div style="color: #666; font-size: 0.9rem; margin-top: 5px;">
{{truncate .Excerpt 60}}
</div>
{{end}}
</td>
<td>{{.Author.FirstName}} {{.Author.LastName}}</td>
<td>
<span style="background: {{.Category.Color}}; color: white; padding: 3px 8px; border-radius: 12px; font-size: 0.8rem;">
{{.Category.Name}}
</span>
</td>
<td>
{{if .Published}}
<span class="status-badge status-published">Published</span>
{{else}}
<span class="status-badge status-draft">Draft</span>
{{end}}
</td>
<td>{{.ViewCount}}</td>
<td>{{timeAgo .CreatedAt}}</td>
<td>
<div class="actions">
<a href="/post/{{.Slug}}" target="_blank" class="btn btn-success">View</a>
<a href="/admin/posts/{{.ID}}/edit" class="btn btn-warning">Edit</a>
<button onclick="deletePost({{.ID}})" class="btn btn-danger">Delete</button>
</div>
</td>
</tr>
{{end}}
</tbody>
</table>
{{/* Pagination */}}
{{if gt .pagination.TotalPages 1}}
<div class="pagination">
{{if .pagination.HasPrev}}
<a href="?page={{sub .pagination.Page 1}}" class="btn">← Previous</a>
{{end}}
<span style="margin: 0 20px; display: flex; align-items: center;">
Page {{.pagination.Page}} of {{.pagination.TotalPages}}
</span>
{{if .pagination.HasNext}}
<a href="?page={{add .pagination.Page 1}}" class="btn">Next →</a>
{{end}}
</div>
{{end}}
{{else}}
<div style="text-align: center; padding: 60px; background: white; border-radius: 10px;">
<h3>No Posts Yet</h3>
<p>Start by creating your first post.</p>
<a href="/admin/posts/new" class="btn btn-primary">Create Post</a>
</div>
{{end}}
</div>
</main>
<script>
function deletePost(postId) {
if (confirm('Are you sure you want to delete this post? This action cannot be undone.')) {
fetch(`/admin/posts/${postId}`, {
method: 'DELETE',
headers: {
'Content-Type': 'application/json',
},
})
.then(response => response.json())
.then(data => {
if (data.message) {
alert('Post deleted successfully');
location.reload();
} else {
alert('Error deleting post: ' + data.error);
}
})
.catch(error => {
alert('Error deleting post: ' + error);
});
}
}
// Auto-refresh every 30 seconds for real-time updates
setInterval(() => {
// Only refresh if user hasn't interacted recently
if (Date.now() - lastInteraction > 30000) {
location.reload();
}
}, 30000);
let lastInteraction = Date.now();
document.addEventListener('click', () => {
lastInteraction = Date.now();
});
</script>
</body>
</html>
6. Hands-On Practice (30-40 minutes)
Exercise 1: Database Model Design (10 minutes)
Objective: Design and implement a complete e-commerce product database
Instructions:
- Create models for Product, Category, Review, and Order
- Implement proper relationships and constraints
- Add validation tags and database indexes
- Create migration and seed functions
Starter Code:
type Product struct {
ID uint `gorm:"primaryKey"`
Name string `gorm:"not null"`
Price float64 `gorm:"not null"`
// TODO: Add more fields and relationships
}
type Category struct {
// TODO: Implement category model
}
type Review struct {
// TODO: Implement review model with user and product relationships
}
Solution:
type Product struct {
ID uint `gorm:"primaryKey" json:"id"`
Name string `gorm:"not null;index" json:"name"`
Slug string `gorm:"uniqueIndex;not null" json:"slug"`
Description string `gorm:"type:text" json:"description"`
Price float64 `gorm:"not null;check:price > 0" json:"price"`
SKU string `gorm:"uniqueIndex;not null" json:"sku"`
Stock int `gorm:"default:0" json:"stock"`
Active bool `gorm:"default:true" json:"active"`
CategoryID uint `gorm:"not null" json:"category_id"`
CreatedAt time.Time `json:"created_at"`
UpdatedAt time.Time `json:"updated_at"`
Category Category `gorm:"foreignKey:CategoryID" json:"category"`
Reviews []Review `gorm:"foreignKey:ProductID" json:"reviews"`
Orders []Order `gorm:"many2many:order_products;" json:"orders"`
}
type Category struct {
ID uint `gorm:"primaryKey" json:"id"`
Name string `gorm:"not null;uniqueIndex" json:"name"`
Slug string `gorm:"uniqueIndex;not null" json:"slug"`
Description string `json:"description"`
Image string `json:"image"`
ParentID *uint `json:"parent_id"`
CreatedAt time.Time `json:"created_at"`
Parent *Category `gorm:"foreignKey:ParentID" json:"parent"`
Children []Category `gorm:"foreignKey:ParentID" json:"children"`
Products []Product `gorm:"foreignKey:CategoryID" json:"products"`
}
type Review struct {
ID uint `gorm:"primaryKey" json:"id"`
ProductID uint `gorm:"not null" json:"product_id"`
UserID uint `gorm:"not null" json:"user_id"`
Rating int `gorm:"check:rating >= 1 AND rating <= 5" json:"rating"`
Title string `json:"title"`
Content string `gorm:"type:text" json:"content"`
Verified bool `gorm:"default:false" json:"verified"`
CreatedAt time.Time `json:"created_at"`
Product Product `gorm:"foreignKey:ProductID" json:"product"`
User User `gorm:"foreignKey:UserID" json:"user"`
}
Exercise 2: Advanced Search and Filtering (15 minutes)
Objective: Implement comprehensive search functionality with filters
Requirements:
- Full-text search across multiple fields
- Price range filtering
- Category and tag filtering
- Sorting options (price, date, popularity)
- Results highlighting and faceted search
Hints:
- Use GORM’s query builder for complex conditions
- Implement proper indexing for search performance
- Handle edge cases like empty results and invalid parameters
Exercise 3: Real-time Dashboard with Live Updates (15 minutes)
Objective: Create a live dashboard with real-time data updates
Challenge: Build a dashboard that displays:
- Live visitor count and page views
- Recent user activities and interactions
- Real-time sales and order statistics
- System health metrics and alerts
Extension Ideas:
- Add WebSocket integration for real-time updates
- Implement data export functionality
- Create customizable dashboard widgets
- Add notification system for important events
7. Common Pitfalls and Troubleshooting (10 minutes)
Frequent Mistakes
-
Problem: N+1 query problems causing slow page loads Cause: Not using proper preloading for related data Solution: Use
Preload()for associations and analyze query patterns -
Problem: Database connection timeouts under load Cause: Insufficient connection pool configuration Solution: Properly configure connection pool size and timeouts
-
Problem: Data consistency issues with concurrent updates Cause: Missing transaction boundaries or optimistic locking Solution: Use database transactions and implement proper concurrency control
Debugging Tips
- Use GORM’s debug mode to see generated SQL queries
- Monitor database performance with query analysis tools
- Implement proper logging for database operations
- Use database migrations for schema consistency
8. Best Practices and Professional Tips (5 minutes)
Industry Standards
- Data Modeling: Design normalized database schemas with proper relationships
- Query Optimization: Use indexes, limit result sets, and optimize expensive queries
- Security: Implement proper input validation and SQL injection prevention
- Scalability: Design for horizontal scaling and implement proper caching
Next Steps in Your Learning Journey
- Learn advanced database optimization techniques
- Explore database replication and sharding strategies
- Study caching layers and performance monitoring
- Practice building high-availability database systems
9. Summary and Review (5 minutes)
Key Takeaways
- Database Integration: GORM provides powerful ORM capabilities for complex data operations
- Template Integration: Proper data loading and relationship handling creates rich user interfaces
- Performance Optimization: Pagination, preloading, and caching are essential for scalable applications
Quick Review Questions
- How do you prevent N+1 query problems when loading related data?
- What’s the difference between eager loading and lazy loading?
- How do you implement secure pagination for large datasets?
For Instructors
Teaching Tips
- Emphasize database design principles before implementation
- Show real-world examples of query optimization and performance issues
- Demonstrate proper error handling for database operations
- Practice debugging database queries and relationship issues
Assessment Ideas
- Database design project with complex relationships
- Performance optimization challenge with query analysis
- CRUD application development with proper validation
- Security audit focusing on SQL injection prevention
Adaptation Notes
- For beginners: Focus on basic CRUD operations before advanced relationships
- For advanced students: Discuss database scaling and distributed systems
- Online delivery: Provide database setup scripts and sample data for consistent environments