How to Implement Pagination with Cache/Page Utilities in Gorig

Gorig provides a generic Pager[T] interface that enables offset-based pagination with SQLite backing, allowing you to cache structs, query with dynamic conditions, and retrieve paginated results using the Find() method with automatic LIMIT/OFFSET handling.

The jom-io/gorig repository offers a robust pagination layer built on top of its cache system. Located in the cache/page.go and cache/page.sqlite.go files, these utilities allow developers to store arbitrary Go structs in SQLite, define indexes via struct tags, and execute paginated queries without writing raw SQL.

Understanding the Gorig Pagination Architecture

The Pager[T] Interface

At the heart of Gorig’s pagination system is the Pager[T] interface defined in cache/page.go. This generic interface provides CRUD-style methods including Put(), Get(), and Delete(), alongside powerful query helpers such as Find(), GroupByTime(), and GroupByFields(). The type parameter T represents the struct type you wish to cache and paginate.

SQLite Implementation

The default concrete implementation is SQLiteCachePage[T] found in cache/page.sqlite.go. This implementation automatically creates the necessary database table, generates indexes based on struct field tags, and constructs safe SQL queries from Go maps. When you call NewPager[T]() with the cache.Sqlite type, you receive a Pager[T] backed by this SQLite implementation.

Setting Up Pagination with Cache/Page Utilities

To implement pagination, you must first define a concrete type with index tags, then instantiate a pager and populate it with data.

Step 1: Define Your Model with Index Tags

Create a struct representing the data you want to cache. Use the idx struct tag to mark fields that should be indexed for querying:

type User struct {
    ID   int    `json:"id" idx:"id"`
    Name string `json:"name" idx:"name"`
    Age  int    `json:"age" idx:"age"`
}

Step 2: Instantiate the Pager

Use cache.NewPager[T]() to create a pager instance. Pass the context, cache type (cache.Sqlite), and an optional table name (defaults to the lowercase type name):

func userPager(ctx context.Context) cache.Pager[User] {
    return cache.NewPager[User](ctx, cache.Sqlite, "user")
}

Step 3: Populate the Cache

Insert items using the Put() method. This is typically done during application initialization or when syncing from an external data source:

func seedUsers(ctx context.Context) error {
    p := userPager(ctx)
    users := []User{
        {ID: 1, Name: "Alice", Age: 30},
        {ID: 2, Name: "Bob", Age: 25},
        {ID: 3, Name: "Charlie", Age: 35},
    }
    for _, u := range users {
        if err := p.Put(u); err != nil {
            return err
        }
    }
    return nil
}

Querying Paginated Data with Find()

The Find() method is the primary interface for retrieving paginated results. It automatically handles SQL generation, LIMIT/OFFSET calculation, and total count queries.

Method Signature and Parameters

func (p *SQLiteCachePage[T]) Find(page, size int, conditions map[string]any, sort ...PageSorter) (*PageCache[T], error)

• page: The page number (1-indexed).
• size: The number of items per page.
• conditions: A map defining WHERE clauses (supports operators like $gt, $lt, $eq).
• sort: Variadic PageSorter arguments controlling ORDER BY.

Return Structure

The method returns a PageCache[T] struct containing:

type PageCache[T any] struct {
    Total int   // Total number of matching records
    Page  int   // Current page number
    Size  int   // Items per page
    Items []T   // Slice of results for this page
}

Practical Query Example

func listUsers(ctx context.Context) (*cache.PageCache[User], error) {
    p := userPager(ctx)

    // Filter: users older than 20
    conditions := map[string]any{
        "age": map[string]any{
            "$gt": 20,
        },
    }

    // Sort by age descending
    sort := []cache.PageSorter{cache.PageSorterDesc("age")}

    // Get page 2, 10 items per page
    return p.Find(2, 10, conditions, sort...)
}

Advanced Pagination Patterns

Multi-Field Sorting

You can pass multiple PageSorter values to sort by secondary fields when primary values collide:

sorts := []cache.PageSorter{
    cache.PageSorterAsc("name"),
    cache.PageSorterDesc("age"),
}
p.Find(1, 20, nil, sorts...)

Grouped Pagination with GroupByFields

For aggregation queries, use GroupByFields() to paginate grouped results. This method accepts aggregation definitions via AggField structs:

agg := []cache.AggField{
    {Field: "age", Agg: cache.AggAvg, Alias: "avg_age"},
}
groups, err := p.GroupByFields(
    nil, 
    []string{"name"}, 
    agg, 
    1, 5, 
    cache.PageSorterDesc("avg_age"),
)

Time-Based Aggregation with GroupByTime

The GroupByTime() method buckets records by time intervals (minute, hour, day) and returns paginated aggregation results, useful for time-series data analysis.

Summary

• Gorig’s cache/page utilities provide a generic Pager[T] interface for type-safe pagination backed by SQLite.
• Key files: cache/page.go defines the interface and helpers; cache/page.sqlite.go implements SQL generation and query execution.
• Core workflow: Define indexed structs, instantiate with NewPager(), populate via Put(), and query using Find() with automatic LIMIT/OFFSET handling.
• Advanced features: Multi-field sorting with PageSorter, grouped aggregation via GroupByFields(), and time-based bucketing with GroupByTime().

Frequently Asked Questions

How does Gorig handle SQL injection protection in the pagination queries?

The SQLiteCachePage[T] implementation in cache/page.sqlite.go constructs SQL clauses using parameterized queries and safe map-to-SQL conversion. When you pass conditions as map[string]any with operators like $gt or $eq, the implementation sanitizes these inputs and uses SQLite parameters rather than string concatenation, preventing SQL injection attacks.

Can I use a different storage backend instead of SQLite for pagination?

While the default implementation uses SQLite via SQLiteCachePage[T], the Pager[T] interface in cache/page.go is storage-agnostic. You can implement the interface for other backends such as Redis, PostgreSQL, or in-memory stores by providing implementations of methods like Put(), Get(), Find(), and Delete(). The NewPager() function currently selects SQLite when passed cache.Sqlite, but you can extend this pattern for additional cache types.

What is the performance impact of the automatic total count query in Find()?

The Find() method executes two queries: one to retrieve the paginated slice with LIMIT/OFFSET, and one to count total matching records for the Total field in PageCache[T]. For large datasets, the count query can become expensive because it requires scanning all matching rows. If you need to optimize for high-traffic scenarios, consider implementing cursor-based pagination or caching the total count separately, though this would require extending beyond the current Pager[T] interface implementation.

How do I define composite indexes for complex query patterns?

The current implementation in cache/page.sqlite.go automatically creates indexes based on the idx struct tag on individual fields. For composite indexes spanning multiple columns, you would need to modify the table creation logic in the SQLiteCachePage initialization or execute custom SQL after table creation. The struct tag system currently supports single-field indexes, so composite indexing requires extending the NewPager initialization sequence to include additional CREATE INDEX statements for your specific query patterns.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}