Skip to main content
ChameleonDB supports advanced query operations for sorting, pagination, field projection, and debugging.

​
Order By

Sort results by one or more fields: 
users, err := db.Users().
    OrderBy("created_at", "desc").
    Execute()

​
Multiple Order Clauses

Chain multiple OrderBy() calls for complex sorting: 
users, err := db.Users().
    OrderBy("name", "asc").
    OrderBy("created_at", "desc").
    Execute()
The order of OrderBy() calls matters. The first call is the primary sort, the second is the secondary sort, etc.

​
Limit and Offset

Paginate results using Limit() and Offset(): 
users, err := db.Users().
    OrderBy("created_at", "desc").
    Limit(10).
    Offset(20).
    Execute()
Best practice: Always use OrderBy() with Limit()/Offset() to ensure deterministic pagination.Without ordering, the database may return results in arbitrary order, causing inconsistent pagination.

​
Pagination Example

Implementing cursor-based pagination: 
func GetUsers(page int, pageSize int) ([]User, error) {
    offset := (page - 1) * pageSize
    
    users, err := db.Users().
        OrderBy("created_at", "desc").
        OrderBy("id", "asc").  // Secondary sort for consistency
        Limit(pageSize).
        Offset(offset).
        Execute()
    
    return users, err
}

// Usage:
page1, _ := GetUsers(1, 10)  // First 10 users
page2, _ := GetUsers(2, 10)  // Next 10 users
page3, _ := GetUsers(3, 10)  // Next 10 users

​
Select (Field Projection)

Select only the fields you need: 
users := db.Query("User").
    Select("id", "name", "email").
    Execute(ctx)
Performance: Field projection reduces memory usage and network transfer. Only select the fields you actually need.

​
Debug Mode

See generated SQL and query performance: 
users := db.Query("User").
    Select("id", "name").
    Filter("email", "like", "ana").
    Debug().
    Execute(ctx)

​
Debug Output Includes:

  • Generated SQL - The exact SQL sent to the database
  • Execution time - How long the query took
  • Row count - Number of rows returned
Use .Debug() during development to understand query performance and verify SQL generation.

​
Combining Everything

A realistic query combining multiple features: 
users, err := db.Users().
    Filter("age", "gte", 18).
    Filter("orders.total", "gt", 50).
    Include("orders").
    Include("orders.items").
    OrderBy("created_at", "desc").
    Limit(10).
    Execute()

​
What This Query Does:

  1. Finds users who are 18+ years old
  2. Who have at least one order with total > $50
  3. Loads all their orders (not just the ones > $50)
  4. Loads all items for those orders
  5. Sorts by most recent users first
  6. Returns only the first 10 results

​
Complex Filtering Example

// Find active users aged 25-40 with recent orders
users, err := db.Users().
    Filter("status", "eq", "active").
    Filter("age", "gte", 25).
    Filter("age", "lte", 40).
    Filter("orders.created_at", "gte", lastWeek).
    Include("orders").
    OrderBy("created_at", "desc").
    Limit(50).
    Execute()

​
Validation Rules

ChameleonDB validates queries before execution:
RuleErrorSolution
Pagination without order.Limit(10) without .OrderBy() warnsAdd .OrderBy()
Unknown field.Filter("invalid_field", ...)Use valid field name
Unknown entitydb.Products() (not in schema)Define entity in schema
Invalid relation.Include("invalid.path")Use valid relation path
Type mismatch.Filter("age", "eq", "string")Use correct type

​
Query Performance Tips

Only select the fields you need with .Select() to reduce memory and network transfer.
// ❌ Loads all fields
db.Users().Execute()

// ✅ Loads only needed fields
db.Users().Select("id", "name", "email").Execute()
Only include relations you actually need. Each Include() triggers an additional query.
// ❌ Loads unnecessary data
db.Users().
    Include("orders").
    Include("orders.items").
    Include("profile").
    Include("settings").Execute()

// ✅ Only load what you need
db.Users().Include("orders").Execute()
Without ordering, pagination results may be inconsistent.
// ❌ Unpredictable results
db.Users().Limit(10).Offset(20).Execute()

// ✅ Deterministic pagination
db.Users().
    OrderBy("created_at", "desc").
    OrderBy("id", "asc").
    Limit(10).
    Offset(20).
    Execute()
See the generated SQL to identify performance issues.
db.Users().
    Filter("orders.total", "gt", 100).
    Include("orders").
    Debug().  // ← Shows SQL and timing
    Execute()

​
Limitations (v0.1)

These features are not supported in the current version:
  • Aggregations (count, sum, avg)
  • Group by
  • Subqueries
  • Transactions
  • OR logic (all filters are AND)
These features are planned for future versions. Track progress on GitHub.

​
Complete Example

package main

import (
    "context"
    "fmt"
    "log"
    
    "github.com/chameleon-db/chameleondb/chameleon/pkg/engine"
)

func main() {
    ctx := context.Background()
    eng, err := engine.NewEngine()
    if err != nil {
        log.Fatal(err)
    }
    defer eng.Close()
    
    // Complex query with all features
    result, err := eng.Query("User").
        Select("id", "name", "email", "age").
        Filter("age", "gte", 18).
        Filter("status", "eq", "active").
        Filter("orders.total", "gt", 100).
        Include("orders").
        OrderBy("created_at", "desc").
        Limit(20).
        Debug().
        Execute(ctx)
    
    if err != nil {
        log.Fatal(err)
    }
    
    // Process results
    for _, user := range result.Rows {
        fmt.Printf("User: %s (%d)\n", user["name"], user["age"])
        
        if orders, ok := result.Relations["orders"]; ok {
            fmt.Printf("  Orders: %d\n", len(orders))
            for _, order := range orders {
                fmt.Printf("  - Total: $%.2f\n", order["total"])
            }
        }
    }
}

​
What’s Next?

Filtering

Review filter operators (eq, gt, like, in)

Relations

Master eager loading with Include()

Mutations

Learn about Insert, Update, and Delete operations