Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 39 ยท Databases and GORM

Queries, Filtering and Pagination with GORM

Build list endpoints in Gin with GORM: Where conditions, safe sorting, offset and keyset pagination, reusable scopes, counting and the SQL injection traps to avoid.

A list endpoint that returns the whole table works for ten rows and falls over at ten million. Real APIs let clients filter (?category=books), sort (?sort=-price) and page (?page=2&per_page=20). This lesson shows how to turn query-string parameters into GORM queries safely, and compares the two main pagination strategies.

GORM and Gin are shown as read-only code. The runnable parts re-create the logic over in-memory data, and a small SQL builder that prints the statement with its placeholders, so you can see what a safe query looks like. If you want the full theory of SQL behind it, the SQL tutorial covers WHERE, ORDER BY and LIMIT.

Reading query parameters

Gin can bind the query string into a struct with ShouldBindQuery and the form tag:

Go
type ListParams struct {
	Q        string `form:"q"`
	Category string `form:"category"`
	MinPrice int    `form:"min_price" binding:"gte=0"`
	Sort     string `form:"sort"`
	Page     int    `form:"page,default=1" binding:"gte=1"`
	PerPage  int    `form:"per_page,default=20" binding:"gte=1,lte=100"`
}

var p ListParams
if err := c.ShouldBindQuery(&p); err != nil {
	c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
	return
}

The upper bound on PerPage matters: without it a client can ask for a million rows in one response.

Conditions with Where

Go
q := s.db.Model(&Product{})

if p.Category != "" {
	q = q.Where("category = ?", p.Category)
}
if p.MinPrice > 0 {
	q = q.Where("price_cents >= ?", p.MinPrice)
}
if p.Q != "" {
	q = q.Where("name ILIKE ?", "%"+p.Q+"%") // ILIKE is PostgreSQL; use LIKE elsewhere
}

Each Where adds an AND. The ? placeholders are the crucial part: GORM sends the values separately from the SQL text (parameterized queries), so a visitor sending category=x' OR '1'='1 cannot change the statement. Compare the two ways of writing the same condition:

Go
q.Where("category = ?", p.Category)             // safe: value is a parameter
q.Where("category = '" + p.Category + "'")      // SQL INJECTION: never do this

Struct and map conditions (Where(&Product{Category: "books"}), Where(map[string]any{"category": "books"})) are safe too, but a struct condition ignores zero values, so it cannot express "price equals 0".

Sorting is different: whitelist it

Placeholders work for values, not for column names. Order("price " + userInput) would be injectable, and you cannot parameterize identifiers. The safe pattern is a whitelist that maps allowed API names to real columns:

Go
var sortable = map[string]string{
	"name":  "name",
	"price": "price_cents",
	"newest": "created_at",
}

func applySort(q *gorm.DB, raw string) *gorm.DB {
	dir := "ASC"
	if strings.HasPrefix(raw, "-") {
		dir, raw = "DESC", raw[1:]
	}
	if col, ok := sortable[raw]; ok {
		return q.Order(col + " " + dir)
	}
	return q.Order("id ASC") // default, never user text
}

Offset pagination

The classic approach is LIMIT per_page OFFSET (page-1)*per_page, plus a COUNT so the client knows how many pages exist:

Go
var total int64
q.Count(&total)

var products []Product
err := applySort(q, p.Sort).
	Limit(p.PerPage).
	Offset((p.Page - 1) * p.PerPage).
	Find(&products).Error

c.JSON(http.StatusOK, gin.H{
	"data": products,
	"meta": gin.H{"page": p.Page, "per_page": p.PerPage, "total": total,
		"pages": (total + int64(p.PerPage) - 1) / int64(p.PerPage)},
})

Run Count before Limit, on the filtered query, otherwise you count only one page. Offset pagination is simple and lets users jump to page 37, but the database still has to read and skip all earlier rows, so deep pages get slow, and rows inserted between two requests shift the pages (an item may appear twice or be skipped).

Keyset (cursor) pagination

Instead of "skip N rows", say "give me the rows after the last one I saw". With an index on the sort column this is fast at any depth and stable under inserts:

Go
// GET /products?after=42&limit=20
q := s.db.Order("id ASC").Limit(limit + 1) // fetch one extra to know if there is more
if after > 0 {
	q = q.Where("id > ?", after)
}

For a non-unique sort column such as price, the cursor needs a tie-breaker: WHERE (price_cents, id) > (?, ?).

The same logic, runnable

This stand-in applies filtering, a whitelisted sort and both pagination styles to a slice, and prints the SQL that GORM would build as a parameterized string:

Go Filter, sort and paginate with a whitelist

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

In the last statement the malicious category is only an argument, and the malicious sort name was not in the whitelist, so the harmless default applied.

Keyset pagination in code

Go Cursor pagination returns stable pages

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

Reusable scopes

GORM scopes are functions func(*gorm.DB) *gorm.DB that you attach with db.Scopes(...). They keep handlers short and let list and count share the same filters:

Go
func Paginate(page, perPage int) func(*gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		return db.Limit(perPage).Offset((page - 1) * perPage)
	}
}
func InCategory(cat string) func(*gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if cat == "" {
			return db
		}
		return db.Where("category = ?", cat)
	}
}

s.db.Scopes(InCategory(p.Category), Paginate(p.Page, p.PerPage)).Find(&products)

Other useful methods

Select("id", "name") loads only some columns, Pluck("name", &names) reads one column into a slice, Distinct, Group and Having build aggregates, Joins writes explicit joins and Raw/Exec run hand-written SQL (still with placeholders). Check what GORM generates by adding .Debug() to a query, which logs the SQL.

Common mistakes

  • Concatenating user text into Where or Order.
  • No upper limit on per_page.
  • Counting after Limit (you count one page).
  • Offset pagination on huge, changing tables.
  • Sorting by a column with duplicate values and no tie-breaker, so pages overlap.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which line is safe against SQL injection?

    A placeholder sends the value separately from the SQL text, so it cannot change the statement.
  2. 2. Why can't you use a ? placeholder for a column name in ORDER BY?

    Identifiers cannot be parameterized. Map allowed API names to known column names.
  3. 3. What is the main drawback of large OFFSET values?

    OFFSET N does the work of reading N rows. Keyset pagination avoids it with an indexed WHERE.
  4. 4. Where must Count be called relative to Limit and Offset?

    Count the filtered query without Limit, otherwise the total only reflects the current page.

Summary

  • Bind query parameters into a struct with ShouldBindQuery, and cap per_page.
  • Use ? placeholders for values; whitelist column names for sorting.
  • Offset pagination is simple but slows down deep; keyset pagination scales and stays stable.
  • Scopes make filters reusable; .Debug() shows the generated SQL.

Next: Relationships and Preloading