Pagination
Recipe: Implement efficient pagination with ASTQL.
Pagination is essential for handling large result sets. ASTQL supports both offset-based and cursor-based pagination.
Offset Pagination
The simplest approach using LIMIT and OFFSET:
func GetUsersPage(instance *astql.ASTQL, page, pageSize int) (*astql.QueryResult, error) {
offset := (page - 1) * pageSize
return astql.Select(instance.T("users")).
Fields(instance.F("id"), instance.F("username"), instance.F("email")).
OrderBy(instance.F("id"), astql.ASC).
Limit(pageSize).
Offset(offset).
Render(postgres.New())
}
Usage
// Page 1: LIMIT 20 OFFSET 0
result, _ := GetUsersPage(instance, 1, 20)
// Page 2: LIMIT 20 OFFSET 20
result, _ := GetUsersPage(instance, 2, 20)
// Page 3: LIMIT 20 OFFSET 40
result, _ := GetUsersPage(instance, 3, 20)
With Total Count
Get total count alongside paginated results:
type PagedResult struct {
DataSQL string
CountSQL string
Params []string
}
func GetPagedUsers(instance *astql.ASTQL, page, pageSize int) (*PagedResult, error) {
offset := (page - 1) * pageSize
// Data query
dataResult, err := astql.Select(instance.T("users")).
Fields(instance.F("id"), instance.F("username")).
Where(instance.C(instance.F("active"), astql.EQ, instance.P("is_active"))).
OrderBy(instance.F("id"), astql.ASC).
Limit(pageSize).
Offset(offset).
Render(postgres.New())
if err != nil {
return nil, err
}
// Count query (same WHERE, no LIMIT/OFFSET)
countResult, err := astql.Count(instance.T("users")).
Where(instance.C(instance.F("active"), astql.EQ, instance.P("is_active"))).
Render(postgres.New())
if err != nil {
return nil, err
}
return &PagedResult{
DataSQL: dataResult.SQL,
CountSQL: countResult.SQL,
Params: dataResult.RequiredParams,
}, nil
}
Limitations of Offset Pagination
- Performance degrades with large offsets (database must skip rows)
- Inconsistent results if data changes between pages
- Not suitable for infinite scroll or real-time feeds
Cursor Pagination
For large datasets, use cursor-based pagination (keyset pagination):
func GetUsersAfter(instance *astql.ASTQL, cursor int64, limit int) (*astql.QueryResult, error) {
query := astql.Select(instance.T("users")).
Fields(instance.F("id"), instance.F("username"), instance.F("email")).
OrderBy(instance.F("id"), astql.ASC).
Limit(limit)
if cursor > 0 {
query = query.Where(instance.C(instance.F("id"), astql.GT, instance.P("cursor")))
}
return query.Render(postgres.New())
}
Usage
// First page: no cursor
result, _ := GetUsersAfter(instance, 0, 20)
// SELECT "id", "username", "email" FROM "users" ORDER BY "id" ASC LIMIT 20
// Next page: use last ID as cursor
result, _ := GetUsersAfter(instance, lastID, 20)
// SELECT ... WHERE "id" > :cursor ORDER BY "id" ASC LIMIT 20
Bidirectional Cursor
Support both forward and backward navigation:
type Direction string
const (
Forward Direction = "forward"
Backward Direction = "backward"
)
func GetUsersWithCursor(
instance *astql.ASTQL,
cursor int64,
direction Direction,
limit int,
) (*astql.QueryResult, error) {
query := astql.Select(instance.T("users")).
Fields(instance.F("id"), instance.F("username"))
if cursor > 0 {
if direction == Forward {
query = query.
Where(instance.C(instance.F("id"), astql.GT, instance.P("cursor"))).
OrderBy(instance.F("id"), astql.ASC)
} else {
query = query.
Where(instance.C(instance.F("id"), astql.LT, instance.P("cursor"))).
OrderBy(instance.F("id"), astql.DESC)
}
} else {
query = query.OrderBy(instance.F("id"), astql.ASC)
}
return query.Limit(limit).Render(postgres.New())
}
Multi-Column Cursor
For sorting by non-unique columns, use composite cursors:
func GetPostsByDate(
instance *astql.ASTQL,
cursorDate string,
cursorID int64,
limit int,
) (*astql.QueryResult, error) {
query := astql.Select(instance.T("posts")).
Fields(instance.F("id"), instance.F("title"), instance.F("created_at")).
Limit(limit)
if cursorDate != "" {
// Composite cursor: (created_at, id)
query = query.Where(instance.Or(
// Same date, higher ID
instance.And(
instance.C(instance.F("created_at"), astql.EQ, instance.P("cursor_date")),
instance.C(instance.F("id"), astql.GT, instance.P("cursor_id")),
),
// Later date
instance.C(instance.F("created_at"), astql.GT, instance.P("cursor_date")),
))
}
return query.
OrderBy(instance.F("created_at"), astql.ASC).
OrderBy(instance.F("id"), astql.ASC).
Render(postgres.New())
}
Filtering with Pagination
Combine filters with pagination:
func SearchUsers(
instance *astql.ASTQL,
filters UserFilters,
cursor int64,
limit int,
) (*astql.QueryResult, error) {
query := astql.Select(instance.T("users")).
Fields(instance.F("id"), instance.F("username"), instance.F("email"))
// Build filter conditions using instance.ConditionItems()
conditions := instance.ConditionItems()
if filters.Active != nil {
conditions = append(conditions,
instance.C(instance.F("active"), astql.EQ, instance.P("is_active")))
}
if filters.Role != "" {
conditions = append(conditions,
instance.C(instance.F("role"), astql.EQ, instance.P("role")))
}
// Add cursor condition
if cursor > 0 {
conditions = append(conditions,
instance.C(instance.F("id"), astql.GT, instance.P("cursor")))
}
// Apply conditions
if len(conditions) > 0 {
query = query.Where(instance.And(conditions...))
}
return query.
OrderBy(instance.F("id"), astql.ASC).
Limit(limit).
Render(postgres.New())
}
Pagination with JOINs
Paginate joined results:
func GetUserPosts(
instance *astql.ASTQL,
userID int64,
cursor int64,
limit int,
) (*astql.QueryResult, error) {
query := astql.Select(instance.T("posts", "p")).
Fields(
instance.WithTable(instance.F("id"), "p"),
instance.WithTable(instance.F("title"), "p"),
instance.WithTable(instance.F("username"), "u"),
).
Join(instance.T("users", "u"), astql.CF(
instance.WithTable(instance.F("user_id"), "p"),
astql.EQ,
instance.WithTable(instance.F("id"), "u"),
)).
Where(instance.C(
instance.WithTable(instance.F("user_id"), "p"),
astql.EQ,
instance.P("user_id"),
))
if cursor > 0 {
query = query.Where(instance.C(
instance.WithTable(instance.F("id"), "p"),
astql.GT,
instance.P("cursor"),
))
}
return query.
OrderBy(instance.WithTable(instance.F("id"), "p"), astql.ASC).
Limit(limit).
Render(postgres.New())
}
Best Practices
1. Always Include ORDER BY
Pagination without ORDER BY produces unpredictable results:
// Bad: no ordering
astql.Select(table).Limit(10).Render(postgres.New())
// Good: explicit ordering
astql.Select(table).OrderBy(field, astql.ASC).Limit(10).Render(postgres.New())
2. Use Indexed Columns for Cursors
Ensure cursor columns are indexed for performance:
CREATE INDEX idx_users_id ON users(id);
CREATE INDEX idx_posts_created_at_id ON posts(created_at, id);
3. Fetch N+1 for "Has More"
Fetch one extra row to determine if more pages exist:
func GetUsersWithHasMore(instance *astql.ASTQL, cursor int64, limit int) (sql string, hasMore bool) {
// Fetch limit + 1
result, _ := GetUsersAfter(instance, cursor, limit+1)
// If we got limit+1 rows, there are more
// (Actual row count determined at execution time)
return result.SQL, true // hasMore determined after execution
}
4. Consider DISTINCT ON for Deduplication
When joining might produce duplicates:
astql.Select(instance.T("users")).
DistinctOn(instance.F("id")).
OrderBy(instance.F("id"), astql.ASC).
Limit(20)