GORM Media & Query Enhancement: Cover Upload, Pagination/Search & Comment Count

📚 Series Navigation

This series has seven parts:

1. GORM Crash Course: Building a Book Management API with Gin + GORM — single-table CRUD, soft delete, the zero-value trap, a complete runnable project
2. GORM Relations in Practice: Comment Model, CRUD & Preload — second table comments, comment CRUD, on-demand detail loading
3. GORM Media & Query Enhancement: Cover Upload, Pagination/Search & Comment Count — uploads & static serving, pagination/search/sort, comment-count aggregation
4. GORM Data Engineering: Batch Import, Request DTOs & Validation-Error Translation — batch import, request DTOs, validation-error translation
5. GORM Many-to-Many in Practice: Books & Tags — many2many join table, tag filtering, association add/remove
6. GORM Engineering in Practice (Part 1): Layering, Dependency Injection & Testability — Repository/Service layering, constructor injection, table-driven tests (P4, optional reading)
7. GORM Engineering in Practice (Part 2): Reliability & Production Readiness — unified errors, security hardening, object storage, connection pool (P4, optional reading)

---

Prerequisites: finish the crash course (GORM Crash Course: Building a Book Management API with Gin + GORM) and the relations part (GORM Relations in Practice: Comment Model, CRUD & Preload) — the two-table books + comments project. Code conventions are the same as in the crash course (WithContext on every DB call, errors.Is for 404, and the 400 · 404 · 201 semantics).

Two things real endpoints can’t dodge are filled in by this part: give books a cover image (Section 1), and upgrade the list query into pagination + search + sort + comment count (Section 2).

---

1. The Image Field: Cover Upload & Static Serving

Goal: give books a cover image: a working upload endpoint — the image lands on disk, the path lands in the database, and the static directory is reachable from outside.

1.1 Decide the Naming First: Why cover_path, Not cover_url

A field name should reflect what is stored:

What is stored	The better-fitting name
A complete URL (http://host/uploads/x.jpg)	cover_url
A relative path (/uploads/x.jpg) — this tutorial’s choice	cover_path
An object-storage key (S3, etc.)	cover_key

This tutorial writes images into the uploads/ directory on disk and stores relative paths in the database (switching domains, putting a CDN in front, or relocating never requires touching the data). Hence the name cover_path. Does your front end prefer coverUrl? Use a Go tag to decouple “internal semantics” from “external contract” — accurate internally, handy as JSON, both at no cost:

CoverPath string `json:"coverUrl" gorm:"column:cover_path"`

1.2 Adding the Field to the Model

models/book.go — append after Price (field-tag convention from 1.1; existing fields like Comments stay unchanged):

CoverPath string `json:"coverUrl" gorm:"column:cover_path"` // relative path, may be empty

AutoMigrate adds a nullable cover_path column to books; existing rows are unaffected (empty value).

1.3 The Upload Endpoint

handlers/cover.go:

package handlers

import (
    "errors"
    "fmt"
    "gin-demo/db"
    "gin-demo/models"
    "net/http"
    "path/filepath"
    "strings"
    "time"

    "github.com/gin-gonic/gin"
    "gorm.io/gorm"
)

// UploadCover uploads a book cover: type and size restricted, saved to uploads/,
// the path written back to cover_path
func UploadCover(c *gin.Context) {
    id := c.Param("id")

    // 1. The book must exist
    var book models.Book
    result := db.DB.WithContext(c.Request.Context()).First(&book, id)
    if errors.Is(result.Error, gorm.ErrRecordNotFound) {
        c.JSON(http.StatusNotFound, gin.H{"error": "book not found"})
        return
    }
    if result.Error != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "query failed"})
        return
    }

    // 2. Grab the file and validate it
    file, err := c.FormFile("cover")
    if err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": "missing file field cover"})
        return
    }
    ext := strings.ToLower(filepath.Ext(file.Filename))
    switch ext {
    case ".jpg", ".jpeg", ".png", ".webp":
    default:
        c.JSON(http.StatusBadRequest, gin.H{"error": "only jpg/png/webp allowed"})
        return
    }
    if file.Size > 2<<20 { // 2<<20 bytes = 2MB. Note this is a "post-hoc" check: FormFile has already read the whole file into memory; 2MB is not a hard request-level cap (fine for teaching — really stopping big requests needs a limit further out)
        c.JSON(http.StatusBadRequest, gin.H{"error": "file too large (max 2MB)"})
        return
    }

    // 3. Save to disk: a random filename — prevents overwrites and path injection
    filename := fmt.Sprintf("%d_%d%s", book.ID, time.Now().UnixNano(), ext)
    if err := c.SaveUploadedFile(file, filepath.Join("uploads", filename)); err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to save file"})
        return
    }

    // 4. Write the path back to the database (only this one field)
    // coverPath is a URL path, joined with forward slashes (why not filepath.Join — see the note below)
    coverPath := "/uploads/" + filename
    if err := db.DB.WithContext(c.Request.Context()).
        Model(&book).Update("cover_path", coverPath).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to update book"})
        return
    }

    c.JSON(http.StatusOK, gin.H{"coverUrl": coverPath})
}

Key points:

• The filename never trusts user input: filepath.Ext takes only the extension; the base name is built from book.ID + timestamp — this blocks both overwrites and ../../-style path injection;
• Don’t mix the two kinds of paths: coverPath is a URL path, always forward slashes / (what is stored and handed to the front end) — use path.Join or plain concatenation; only saving to disk uses filepath.Join (OS separator). Windows backslashes are exactly the counterexample — joining a URL with filepath.Join would store \uploads\ on Windows, and the front end would never match it;
• Type validation is an extension whitelist (the simple teaching version; the strict approach sniffs file headers with http.DetectContentType — landed in Section 2 of GORM Engineering in Practice (Part 2): Reliability & Production Readiness);
• Model(&book).Update("cover_path", ...) updates a single field without touching any other — echoing the Updates(struct) semantics from the crash course.

1.4 Static Serving & Directory Setup

main.go, three small changes:

import (
    "os"
    "path/filepath"
    // ...everything else unchanged
)

func main() {
    db.InitDB()
    if err := db.DB.AutoMigrate(&models.Book{}, &models.Comment{}); err != nil {
        log.Fatal("migration failed:", err)
    }

    // Disk directory: joined with filepath.Join (OS separator); MkdirAll and Static share the same value
    uploadDir := filepath.Join(".", "uploads")
    _ = os.MkdirAll(uploadDir, 0o755) // make sure the directory exists

    r := gin.Default()
    r.Use(requestTimeout(5 * time.Second))
    r.Static("/uploads", uploadDir) // URL prefix hardcoded with forward slashes: /uploads; the directory follows uploadDir
    // /uploads/xxx.jpg → ./uploads/xxx.jpg

    // ...new in the routes block:
    r.POST("/books/:id/cover", handlers.UploadCover)
}

r.Static("/uploads", uploadDir) maps /uploads/<filename> straight onto the disk directory — the browser / front end only needs to concatenate coverUrl to display the image.

Testing:

curl -X POST http://localhost:8080/books/1/cover \
  -F "cover=@/path/to/cover.jpg"
# {"coverUrl":"/uploads/1_1734xxxx.jpg"}

curl -I http://localhost:8080/uploads/1_1734xxxx.jpg   # 200, Content-Type image/jpeg

# Counter-example: a non-image / over-2MB upload should return 400
curl -X POST http://localhost:8080/books/1/cover -F "cover=@/etc/hosts"
# {"error":"only jpg/png/webp allowed"}

---

2. Query Enhancement: Pagination, Search & Sort

Goal: upgrade GetBooks from “dump everything” into a general “pagination + search + sort” endpoint, and put a comment count on every book in the list (JOIN + GROUP BY). The pagination skeleton learned in the relations part (GORM Relations in Practice: Comment Model, CRUD & Preload) is reused here on a bigger table.

2.1 First, Consolidate the Helper

In the relations part, ListComments does its pagination parsing inline (seeing the full shape first is the right teaching order). Now fold it into handlers/pagination.go, shared by every paginated endpoint:

// handlers/pagination.go — pagination-param parsing + validation (defensive: bad values fall back to defaults)
package handlers

import (
    "strconv"

    "github.com/gin-gonic/gin"
)

func parsePagination(c *gin.Context) (page, pageSize int) {
    page, err := strconv.Atoi(c.DefaultQuery("page", "1"))
    if err != nil || page < 1 {
        page = 1
    }
    pageSize, _ = strconv.Atoi(c.DefaultQuery("pageSize", "10"))
    if pageSize < 1 {
        pageSize = 10
    }
    if pageSize > 100 {
        pageSize = 100 // cap at 100: stop a client pulling too much data in one go
    }
    return page, pageSize
}

2.2 Reworking GetBooks

⚠️ Breaking contract change: GET /books’s response changes from the crash course’s bare array into a {items,total,page,pageSize} object — existing front ends must adapt in step (the series’ route inventory logs this entry as a “1 → 3 rework”).

handlers/book.go:

// GetBooks lists books: q (fuzzy search over title/author), page/pageSize pagination;
// sort is fixed to newest-created-first (external sort fields need whitelisting first, see §2.1 of
// [GORM Engineering in Practice (Part 2)](./gorm-gin-engineering-reliability)).
// Returns {items, total, page, pageSize}.
func GetBooks(c *gin.Context) {
    // 1. Parse the query params
    // 1.1 q: fuzzy-search keyword over title/author
    q := c.Query("q")
    // 1.2 pagination parsing + validation (parsePagination in handlers/pagination.go)
    page, pageSize := parsePagination(c)

    // 2. Assemble the query: optional fuzzy search over title/author
    query := db.DB.WithContext(c.Request.Context()).Model(&models.Book{})
    if q != "" {
        like := "%" + q + "%"
        query = query.Where("title ILIKE ? OR author ILIKE ?", like, like)
    }

    // 3. Count the filtered total first
    var total int64
    if err := query.Count(&total).Error; err != nil {
        _ = c.Error(err) // specific error goes to Gin's log; the client message stays uniform
        c.JSON(http.StatusInternalServerError, gin.H{"error": "query failed"})
        return
    }

    // 4. Then fetch the current page (newest first)
    var books []models.Book
    if err := query.
        Order("created_at DESC").
        Offset((page - 1) * pageSize).
        Limit(pageSize).
        Find(&books).Error; err != nil {
        _ = c.Error(err) // specific error goes to Gin's log; the client message stays uniform
        c.JSON(http.StatusInternalServerError, gin.H{"error": "query failed"})
        return
    }

    // 5. Return the page result
    c.JSON(http.StatusOK, gin.H{
        "items":    books,
        "total":    total,
        "page":     page,
        "pageSize": pageSize,
    })
}

Testing:

curl "http://localhost:8080/books?q=Go&page=1&pageSize=10"
# {"items":[...],"total":1,"page":1,"pageSize":10}

curl "http://localhost:8080/books?page=2&pageSize=5"
# paging: total stays the same, items are the second page

Key points (all skeleton-level GORM query knowledge):

• ILIKE is PG-specific: LIKE is case-sensitive, ILIKE is not. The tutorial runs PostgreSQL, so it writes ILIKE; on MySQL use LIKE, on SQLite use LIKE (SQLite’s LIKE is case-insensitive for ASCII) — one concrete instance of driver differences. Also note a q containing %/_ is treated as wildcards: parameterization only stops SQL injection, not wildcard semantics — a literal search needs escaping or an ESCAPE clause first, which the tutorial doesn’t expand on;
• query is a reusable chain: the same query variable runs Count first, then takes Order/Offset/Limit for Find — no paging conditions are attached before Count, so what you get is the filtered total, the canonical shape of a paginated endpoint;
• ORDER injection: if a sort parameter ever comes from users, never concatenate it into Order() ("/books?sort=created_at;DROP...") — the tutorial simply fixes the sort; accepting an external sort field requires whitelisting it first (landed in §2.1 of GORM Engineering in Practice (Part 2): Reliability & Production Readiness).

2.3 Adding a Comment Count to the List: JOIN + GROUP BY

Preload can only fetch “an array of comment objects”, never “a comment count”. To get “a comment count per book” you aggregate — which needs a struct to carry the result:

// List-response struct: the Book itself + the comment count (not a DB table — a query carrier only)
type BookListItem struct {
    models.Book
    CommentCount int64 `json:"commentCount"`
}

Then, on top of GetBooks, change only step 4 (steps 1/2/3/5 stay exactly as they are: pagination still runs through parsePagination, and the total is still a plain Count on the books table) — swap step 4’s var books []models.Book + Find for this var items []BookListItem + Scan (BookListItem is declared above):

var items []BookListItem
if err := query.
    Select("books.*, COUNT(comments.id) AS comment_count").
    Joins("LEFT JOIN comments ON comments.book_id = books.id AND comments.deleted_at IS NULL").
    Group("books.id").
    Order("books.created_at DESC").
    Offset((page - 1) * pageSize).
    Limit(pageSize).
    Scan(&items).Error; err != nil {
    _ = c.Error(err)
    c.JSON(http.StatusInternalServerError, gin.H{"error": "query failed"})
    return
}

Three aggregate-query traps (all textbook-grade):

• LEFT JOIN + Group("books.id"): books without comments must still show up (comment_count = 0) — hence LEFT, not INNER;
• The comments.deleted_at IS NULL condition belongs in the JOIN, not the WHERE: put in the WHERE, it drops the whole rows of “books without comments”; put in the JOIN, it keeps the left table and counts only undeleted comments — the most common trap in aggregate queries;
• Scan into a custom struct: BookListItem embeds models.Book and adds one count field — hand-written aggregate SQL lands in a “query-only carrier”, which is exactly how GORM wires “raw SQL” back into the type world (the carrier never becomes a DB table; it exists only to fetch);
• Ambiguous columns: after the JOIN, created_at and id exist in both tables — write books.created_at and books.id in the SQL (Group("books.id") the same way); without the table prefixes you get “ambiguous column”.

Driver-difference note: Select("books.*") + Group("books.id") holds together thanks to PostgreSQL’s “functional dependency” feature — grouping by the primary key lets you select the other columns directly. MySQL with only_full_group_by on (the default) errors out and needs books.* expanded into the full column list, all of it grouped; that is the typical cross-database difference in “hand-written aggregate SQL”, and this PG-based tutorial adds no compatibility handling.

Testing:

curl "http://localhost:8080/books?q=Go&pageSize=10"
# items[0].commentCount should be that book's count of undeleted comments (0 for books without comments)

Contract layering between the list and the detail endpoints: GET /books returns {items,total,page,pageSize}; GET /books/:id returns one book + its comments. Lists stay light, details run heavy — this is where every earlier “load on demand” decision lands.

---

Wrap-Up

• New/changed routes in this part:

Method	Path	Handler / role
POST	/books/:id/cover	UploadCover
GET	/uploads/*	r.Static static serving
GET	/books (reworked)	GetBooks: pagination + search + comment count

• Your project now: the books + comments two tables, cover upload with static serving, a paginated & searchable list, and a comment count on every book;
• Next part, GORM Data Engineering: Batch Import, Request DTOs & Validation-Error Translation: batch-import real data, request DTOs with parameterized validation, friendly translation of validation errors — and it closes with a series overview plus a list of the engineering items (the centralized reconciliation promised by the Parts ⁶⁄₇ previews).

---

Appendix: PostgreSQL Features Used in This Series (MySQL / SQLite Reference)

The only places in the whole series that truly depend on PostgreSQL are the few below — everything else is standard SQL, common to all three databases:

PG feature	Where it appears	This series’ syntax	Moving to MySQL	Moving to SQLite
Case-insensitive fuzzy search	this part §2.2	ILIKE ?	LIKE ? (case is decided by the collation; use LOWER(col) LIKE LOWER(?) when needed)	LIKE ? (case-insensitive within ASCII; non-ASCII needs COLLATE NOCASE)
Selecting other columns directly after grouping by the primary key (functional dependency)	this part §2.3	SELECT books.*, COUNT(comments.id) ... GROUP BY books.id	errors under the default only_full_group_by: expand books.* into the full column list and GROUP BY all of it	same as MySQL (no functional dependency; must group every column)
Partial unique index on a soft-delete + unique field	crash course, Part 8	CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL	no direct equivalent: use a generated column (a boolean column of (deleted_at IS NULL)) to do the partial-unique, or guarantee it at the application layer	same as MySQL

Also % / _ are wildcards in both ILIKE and LIKE (parameterization only stops SQL injection, not wildcard semantics) — identical in all three databases. The tutorial’s mainline runs through PostgreSQL; to switch to MySQL / SQLite, replace the few rows above and nothing else in the code needs to change.