GORM Scopes for Reusable Query Logic Across a Codebase

A repeated Where is a bug waiting to happen. The condition that means "an active product" gets copied into twenty handlers, and the day the definition changes (active and not archived) nineteen of them are updated. GORM's answer is the scope: a function from *gorm.DB to *gorm.DB that you apply with Scopes(...). It is small enough to read in one glance and powerful enough to carry a codebase's filters, pagination and tenant rules. This article builds three scopes, prints the SQL each combination produces, and covers the trap in counting with a paginated scope.

It builds on the GORM tutorial. Scopes are the preferred home for shared query logic, and the hooks article covers why they are a better one than a hook. The pagination design that scopes usually serve is in GORM pagination.

Three scopes

A scope without arguments is a plain function:

func Active(db *gorm.DB) *gorm.DB { return db.Where("active = ?", true) }

A scope with arguments is a function that returns a scope. This is the form for Category and Paginate:

func Category(c string) func(*gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB { /* ... */ }
}

The lab defines all three over a Product table, and a helper that shows the SQL GORM would run. Session(&gorm.Session{DryRun: true}) builds the statement without executing it, and Statement.SQL and Statement.Vars expose the text and the bind values.

package main

import (
	"fmt"
	"log"
	"strings"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

type Product struct {
	ID       uint
	Name     string
	Category string
	Price    int
	Active   bool
}

func Active(db *gorm.DB) *gorm.DB { return db.Where("active = ?", true) }

func Category(c string) func(*gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if c == "" {
			return db
		}
		return db.Where("category = ?", c)
	}
}

func Paginate(page, size int) func(*gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if page < 1 {
			page = 1
		}
		if size < 1 || size > 100 {
			size = 20
		}
		return db.Offset((page - 1) * size).Limit(size)
	}
}

// sql shows the statement GORM would run, without running it.
func sql(db *gorm.DB) string {
	var ps []Product
	stmt := db.Session(&gorm.Session{DryRun: true}).Find(&ps).Statement
	return strings.TrimSpace(stmt.SQL.String()) + fmt.Sprintf("   vars=%v", stmt.Vars)
}

func main() {
	db := connect("l0712", nil)
	must(db.AutoMigrate(&Product{}))
	for i := 1; i <= 30; i++ {
		cat := []string{"books", "games", "tools"}[i%3]
		db.Create(&Product{Name: fmt.Sprintf("item %02d", i), Category: cat, Price: i * 100, Active: i%4 != 0})
	}

	fmt.Println(sql(db.Scopes(Active)))
	fmt.Println(sql(db.Scopes(Active, Category("games"), Paginate(2, 5))))
	fmt.Println(sql(db.Scopes(Category(""), Paginate(0, 500))))

	var ps []Product
	db.Scopes(Active, Category("games"), Paginate(2, 3)).Order("id").Find(&ps)
	for _, p := range ps {
		fmt.Println("row:", p.Name, p.Category)
	}

	// A scope is just a function, so it can be tested alone and used with Count.
	var n int64
	db.Model(&Product{}).Scopes(Active, Category("tools")).Count(&n)
	fmt.Println("active tools:", n)

	// Order matters when a scope sets Limit and a later one counts: count before paginating.
	var total int64
	q := db.Model(&Product{}).Scopes(Active, Category("books")).Session(&gorm.Session{})
	q.Count(&total)
	var page []Product
	q.Scopes(Paginate(1, 4)).Order("id").Find(&page)
	fmt.Println("books total:", total, "page size:", len(page))
}

SELECT * FROM "products" WHERE active = $1   vars=[true]
SELECT * FROM "products" WHERE active = $1 AND category = $2 LIMIT $3 OFFSET $4   vars=[true games 5 5]
SELECT * FROM "products" LIMIT $1   vars=[20]
row: item 13 games
row: item 19 games
row: item 22 games
active tools: 8
books total: 8 page size: 4

Reading the SQL

The first line is SELECT * FROM "products" WHERE active = $1 with vars=[true]. The scope added one condition, and the value is a bind parameter, not text spliced into the query.

The second combines three scopes: WHERE active = $1 AND category = $2 LIMIT $3 OFFSET $4 with vars=[true games 5 5]. Page 2 of size 5 is offset 5, limit 5. Scopes compose by each adding to the same statement, in the order given. Conditions combine with AND; the limit and offset replace any earlier ones.

The third passes Category("") and Paginate(0, 500). The output is SELECT * FROM "products" LIMIT $1 with vars=[20]. Two decisions are encoded in the scopes, and neither is visible at the call site:

• An empty category adds no filter. Category returns db unchanged for an empty string, so a handler can pass a query string value straight through without an if.
• Paginate clamps its input. A page below 1 becomes 1, and a size outside 1 to 100 becomes 20. A caller cannot ask for 500 rows, which is the right place to put that rule, because every list endpoint inherits it.

Executing the combined scope against the 30 seeded products returned the three games after the first three active ones: items 13, 19 and 22 on page 2 of size 3. That is the expected result and also a reminder that Offset needs an Order, as the lab adds. Without an order, a page is a sample of rows in whatever order PostgreSQL returns them, and two requests can disagree.

Testing a scope without a database

The DryRun helper is also a testing tool. A scope is a function over *gorm.DB, so a unit test can apply it to a session with DryRun on, and assert on Statement.SQL.String() and Statement.Vars. That tests the shape of the query at near zero cost, and it needs only an opened handle, as in the lab. A second test against a real database then confirms behaviour. For pure functions like these, the dry run covers most of what can go wrong, such as a wrong operator or a missing bind value.

A scope that adds an OR is the one to be careful with. db.Where("a = ?", 1).Or("b = ?", 2) combined with another scope's Where can emit a = 1 OR b = 2 AND c = 3 when it is not grouped, and SQL's precedence then gives an unexpected result. Group the alternatives with a nested db.Where(db.Where("a = ?", 1).Or("b = ?", 2)) so that the group is parenthesised, and test the emitted SQL.

Scopes with Count

The last block of the lab shows a trap in the most common use. A list endpoint needs the total number of matches and one page of them. The natural code builds the filtered query once and uses it twice: Count, then Find with Paginate. The lab did it, and the output is books total: 8 page size: 4.

It worked because the base was made reusable: .Session(&gorm.Session{}) at the end of the chain turns the statement into a clean base that later calls clone, instead of mutating. Without that call, the chain is a one use value. Count then adds a SELECT count(*) to the shared statement, and the Find after it can carry state from the first call. That bug is real enough that GORM's documentation warns about reuse, and the symptom is a page query that returns a single row of counts or a wrong LIMIT.

The rules for scopes and reuse:

1. Build the filter part as a base statement and call Session(&gorm.Session{}) on it.
2. Run Count on the base.
3. Apply Paginate and Order and run Find on the base.

Never put Paginate in the base. A count with a LIMIT and OFFSET is wrong in a way that looks right on page one: it counts the rows in the page, so the total changes as you page.

Where scopes belong

Scopes are plain functions, so place them where the knowledge lives. A ProductActive scope belongs in the products package next to the model. Cross cutting scopes (Paginate, Tenant(id)) belong in a small shared package. Name them as adjectives or conditions (Active, ByCategory, CreatedAfter), and keep them to one idea each. A scope that does three things is hard to combine and impossible to name.

Three more uses are worth knowing:

• A tenant scope. func Tenant(id int64) func(*gorm.DB) *gorm.DB appended to every query keeps data separate. It is only as safe as the discipline to apply it, which is why the multi tenant article looks at making it automatic and what that costs.
• A soft delete scope. GORM's own DeletedAt support is built as a scope, which is why Unscoped() exists.
• A scope that joins. A scope may add a Joins and a condition together, such as WithAuthor. If two scopes join the same table, you get a duplicate alias error, so give joins to one scope or alias them.

Avoid

• Scopes that hide queries. A scope should add conditions, never run a query of its own.
• Scopes with side effects on shared state, like the package level page variable captured in a closure. The lab's Paginate assigns to its own parameters, which is safe because each call has its own.
• Scopes that depend on call order. If Paginate must come last, a reader has to know it. Put the ordering rule in a comment or build a function that applies them in the right order.

Checklist

• Write a scope for any condition used in more than two places.
• Return the input db unchanged for an absent optional filter.
• Clamp page and size inside Paginate.
• Use Session(&gorm.Session{}) before reusing a chain, and count before paginating.
• Test scopes with a dry run, and check the emitted SQL for OR precedence.

The API is covered in the GORM scopes guide, and the dry run and session options in the GORM session documentation.