MOONGATE
// Created At 2026-09-03
// Go · PostgreSQL · ORM

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

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 the directory means not a single row changes — only the base you join with when reading does.

Hence the name cover_path (it stores a path). Externally, though, the front end wants “something it can drop straight into <img src>”, so the JSON key is coverUrl: same-origin today it is /uploads/x.jpg, and once a CDN or object storage is in front it becomes a full https://... — the key describes the external contract (a displayable address), not the literal value in the column right now.

In Go that is a single line: the field CoverPath, plus json:"coverUrl" to separate the two names (no column tag needed — the default naming strategy turns CoverPath into cover_path, as Part 3 of the crash course explains). The exact place to add it is 1.2.

Two things to note:

  • json:"coverUrl" is the one necessary tag: without it the JSON carries the Go field name CoverPath. And remember that a JSON key is an external contract — renaming it after release costs far more than renaming a column (front ends, caches, docs, already-shipped clients all have to follow), so decide deliberately;
  • The cost of a relative path: the front end does not get an absolute address. A same-origin page can use <img src="/uploads/x.jpg"> directly; a cross-origin deployment (say the Nuxt site on its own domain) must join it with the API base: apiBase + coverUrl. If you want “what you get is ready to use”, join it into an absolute address in the response (the configured public base + path) — which is also the legitimate reason to keep the key named coverUrl.

1.2 Adding the Field to the Model

models/book.go — append one line after Price (this is 1.1’s naming decision landing in code; existing fields like Comments stay unchanged):

CoverPath string `json:"coverUrl"` // relative path, may be empty; the cover_path column comes from the default naming strategy

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 (
    "fmt"
    "gin-demo/db"
    "gin-demo/models"
    "net/http"
    "path/filepath"
    "strings"
    "time"

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

// UploadCover uploads a book cover: type and size restricted, saved to uploads/,
// the path written back to cover_path
func UploadCover(c *gin.Context) {
    // 1. The parent check already happened in 2.4's requireBook middleware: a missing book
    //    never reaches this handler. Take the entity the middleware stashed in the context,
    //    which also saves a second query for the book data
    book := c.MustGet("book").(*models.Book)

    // 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 route must hang off the /books/:id group (2.4’s bookGroup): c.MustGet("book") relies on requireBook having called c.Set, and a missing middleware panics outright. If you skipped 2.4, put the First(&book, id) existence check back as step 1;
  • 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"
    // ...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: locally this defaults to uploads/ relative to the working directory;
    // in production point UPLOAD_DIR at an absolute path
    uploadDir := "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: the cover route joins 2.4's bookGroup (its path is already /books/:id)
    bookGroup.POST("/cover", handlers.UploadCover)
}

uploads is a relative path, resolved against the process’s working directory — running go run from the project root is fine locally, but under systemd / Docker / an IDE that starts the service elsewhere, images get written to (and read from) a different directory. Point an UPLOAD_DIR environment variable at an absolute path in production.

r.Static("/uploads", uploadDir) maps /uploads/<filename> straight onto the disk directory: a same-origin page can use <img src="/uploads/x.jpg"> right away; a cross-origin front end joins it with the API base (see the two notes in 1.1).

Testing:

curl -X POST http://localhost:8080/books/1/cover \
  -F "cover=@/path/to/cover.jpg"
# {"coverUrl":"/uploads/1_1734xxxx.jpg"}   <- a relative path; cross-origin front ends need apiBase + this value

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 move those lines out verbatim into handlers/pagination.go — same behavior, just one reusable home instead of an inline copy:

// 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, _ = strconv.Atoi(c.DefaultQuery("page", "1")) // Atoi returns 0 on error; the < 1 check below catches it
    if 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 (Part 4’s 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)
        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;
  • Search on top of Part 2’s skeleton: the Count → Order/Offset/Limit order is unchanged; fetching just has one extra Where in front of it. Because Count runs before any paging condition, it naturally yields the filtered total — the canonical shape of a search-plus-pagination 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: GET /books returns {items,total,page,pageSize}; GET /books/:id returns one book + its comments — lists stay light, details run heavy.

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 route quick reference plus a list of the engineering items (the centralized reconciliation promised by the Parts 6⁄7 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)
Leading-wildcard search (ILIKE '%q%') hitting an index this part §2.2 full table scan by default; in production add pg_trgm + CREATE INDEX ... USING gin (title gin_trgm_ops) LIKE '%q%' scans too — use a full-text index or an external engine no trigram; a prefix LIKE 'q%' can use an index
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 / tags part §1.1 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.

If this article helped you, you can buy me a coffee ☕️
Ali PayWechat Pay