Skip to content

Query Builder

The Query Builder provides a fluent interface for building DynamoDB queries with automatic GSI selection.

Basic Queries

Where Conditions

// Equality
users, err := UserRepo.
    Where("Status", "=", "active").
    GetAll()

// Comparison operators
users, err := UserRepo.
    Where("Age", ">=", 18).
    GetAll()

// Multiple conditions (AND)
users, err := UserRepo.
    Where("Status", "=", "active").
    Where("Role", "=", "admin").
    GetAll()

Supported Operators

Operator Description Example
= Equal Where("Status", "=", "active")
!= Not equal Where("Status", "!=", "banned")
< Less than Where("Age", "<", 18)
<= Less than or equal Where("Age", "<=", 65)
> Greater than Where("Score", ">", 100)
>= Greater than or equal Where("Score", ">=", 50)
begins_with Prefix match Where("Name", "begins_with", "John")
contains Contains substring Where("Tags", "contains", "featured")
between Range WhereBetween("Age", 18, 65)
in In list WhereIn("Status", "active", "pending")

Convenience Methods

// begins_with shorthand
users, _ := UserRepo.
    WhereBeginsWith("Email", "john@").
    GetAll()

// between shorthand
users, _ := UserRepo.
    WhereBetween("Age", 18, 65).
    GetAll()

// in shorthand
users, _ := UserRepo.
    WhereIn("Status", "active", "pending", "verified").
    GetAll()

// contains shorthand
users, _ := UserRepo.
    WhereContains("Tags", "featured").
    GetAll()

Projection

Select specific fields to retrieve:

users, err := UserRepo.
    Select("ID", "Email", "FirstName").
    Where("Status", "=", "active").
    GetAll()

Performance

Use projection to reduce read capacity consumption when you only need specific fields.

Ordering

// Ascending (default)
users, _ := UserRepo.
    OrderBy("CreatedAt", "asc").
    GetAll()

// Descending
users, _ := UserRepo.
    OrderBy("CreatedAt", "desc").
    GetAll()

Ordering Limitations

DynamoDB only supports ordering by the sort key. For other orderings, use collection methods after fetching.

Limiting Results

// Get first 10 results
users, _ := UserRepo.
    Limit(10).
    GetAll()

// Combined with other conditions
topUsers, _ := UserRepo.
    Where("Status", "=", "active").
    OrderBy("Score", "desc").
    Limit(10).
    GetAll()

Getting Results

GetAll

Returns all matching entities as a Collection:

users, err := UserRepo.
    Where("Status", "=", "active").
    GetAll()

// Returns *Collection[User]
fmt.Printf("Found %d users\n", users.Count())

GetFirst

Returns the first matching entity:

user, err := UserRepo.
    Where("Email", "=", "john@example.com").
    GetFirst()

if user == nil {
    fmt.Println("User not found")
}

Count

Returns just the count:

count, err := UserRepo.
    Where("Status", "=", "active").
    Count()

fmt.Printf("Active users: %d\n", count)

DeleteAll

Deletes all matching entities:

err := UserRepo.
    Where("Status", "=", "banned").
    DeleteAll()

Index Selection

Dynorm automatically selects the best index for your query using a sophisticated scoring algorithm. This ensures optimal performance without manual index management.

How Auto-Selection Works

When you execute a query, Dynorm evaluates all available indexes (primary key and GSIs) and scores them based on your query conditions:

flowchart TD
    Query[Query Conditions] --> Score[Score Each Index]
    Score --> PK[Primary Key]
    Score --> GSI1[GSI 1]
    Score --> GSI2[GSI 2]
    Score --> GSI3[GSI N...]

    PK --> Compare{Compare Scores}
    GSI1 --> Compare
    GSI2 --> Compare
    GSI3 --> Compare

    Compare --> Best[Select Highest Score]
    Best --> Execute[Execute Query]

Scoring Algorithm

Each index is scored based on three factors:

Factor Score Requirement
Partition key equality +10 Query has = condition on partition key
Sort key condition +5 Query has any condition on sort key
Order field match +3 OrderBy field matches sort key

Example:

type Order struct {
    dynorm.Entity
    CustomerID string    `dynorm:"gsi:ByCustomer"`
    OrderDate  string    `dynorm:"gsi:ByCustomer:sk"`
    Status     string    `dynorm:"gsi:ByStatus"`
    Total      float64
}

For this query: 

orders, _ := OrderRepo.
    Where("CustomerID", "=", "cust123").
    Where("OrderDate", ">", "2024-01-01").
    OrderBy("OrderDate", "desc").
    GetAll()

Index PK Match (+10) SK Condition (+5) Order Match (+3) Total
Primary (id) No No No 0
ByCustomer Yes (+10) Yes (+5) Yes (+3) 18
ByStatus No No No 0

Result: ByCustomer GSI is selected (highest score of 18).

Automatic Selection Examples

// Uses ByEmail GSI (score: 10 for PK equality)
user, _ := UserRepo.
    Where("Email", "=", "john@example.com").
    GetFirst()

// Uses ByCustomer GSI (score: 15 for PK + SK conditions)
orders, _ := OrderRepo.
    Where("CustomerID", "=", "cust123").
    Where("OrderDate", ">=", "2024-01-01").
    GetAll()

// Uses ByCustomer GSI (score: 18 for PK + SK + ordering)
orders, _ := OrderRepo.
    Where("CustomerID", "=", "cust123").
    OrderBy("OrderDate", "desc").
    GetAll()

// Uses primary key (no GSI matches - falls back to scan)
orders, _ := OrderRepo.
    Where("Total", ">", 100).
    GetAll()

Debug Index Selection

Use SelectedIndex() to see which index was chosen:

query := OrderRepo.
    Where("CustomerID", "=", "cust123").
    Where("OrderDate", ">", "2024-01-01")

// Check which index will be used
fmt.Printf("Using index: %s\n", query.SelectedIndex())
// Output: "Using index: ByCustomer"

orders, _ := query.GetAll()

Debugging Tip

If a query is slower than expected, use SelectedIndex() to verify the correct index is being used. If not, ensure your entity has the appropriate GSI tags.

Explicit Index Override

When you need to force a specific index:

// Force use of a specific GSI
users, _ := UserRepo.
    UseIndex("ByStatus").
    Where("Status", "=", "active").
    GetAll()

// Force table scan (use primary key, no GSI)
users, _ := UserRepo.
    UseIndex("").
    Where("Status", "=", "active").
    GetAll()

Manual Override

Only override automatic selection when you have a specific reason. The algorithm chooses the optimal index in most cases.

When No Index Matches

If no index can efficiently serve the query, Dynorm falls back to a table scan:

// No GSI on "LastName" - performs table scan
users, _ := UserRepo.
    Where("LastName", "=", "Smith").
    GetAll()

To optimize this:

  1. Add a GSI for frequently queried fields
  2. Or accept the scan cost for rare queries
type User struct {
    dynorm.Entity
    Email    string `dynorm:"gsi:ByEmail"`
    LastName string `dynorm:"gsi:ByLastName"`  // Add GSI
}

Composite Index Selection

For GSIs with both partition and sort keys, the algorithm considers both:

type Event struct {
    dynorm.Entity
    UserID    string `dynorm:"gsi:ByUserDate"`
    EventDate string `dynorm:"gsi:ByUserDate:sk"`
    Type      string
}

// Score 15: PK (10) + SK condition (5)
events, _ := EventRepo.
    Where("UserID", "=", "user123").
    Where("EventDate", "begins_with", "2024-03").
    GetAll()

// Score 18: PK (10) + SK condition (5) + order match (3)
events, _ := EventRepo.
    Where("UserID", "=", "user123").
    OrderBy("EventDate", "desc").
    Limit(10).
    GetAll()

Query vs Scan

Dynorm automatically chooses between Query and Scan:

  • Query: When condition on partition key (faster, cheaper)
  • Scan: When no partition key condition (slower, more expensive)
flowchart TD
    Start[Query Builder] --> Check{Has PK/GSI-PK<br>condition?}
    Check -->|Yes| Query[DynamoDB Query]
    Check -->|No| Scan[DynamoDB Scan]
    Query --> Filter{Has filter<br>conditions?}
    Scan --> Filter
    Filter -->|Yes| Apply[Apply Filter Expression]
    Filter -->|No| Return[Return Results]
    Apply --> Return

Eager Loading Relationships

Prevent N+1 queries by eager loading:

// Load users with their posts
users, err := UserRepo.
    With("Posts").
    GetAll()

// Load multiple relationships
users, err := UserRepo.
    With("Posts", "Profile", "Roles").
    GetAll()

Complex Query Examples

Find Active Admins

admins, err := UserRepo.
    Where("Status", "=", "active").
    Where("Role", "=", "admin").
    OrderBy("CreatedAt", "desc").
    GetAll()

Search by Email Prefix

users, err := UserRepo.
    WhereBeginsWith("Email", "john").
    Limit(10).
    GetAll()

Users in Age Range

users, err := UserRepo.
    WhereBetween("Age", 25, 35).
    Where("Status", "=", "active").
    GetAll()

Users with Specific Statuses

users, err := UserRepo.
    WhereIn("Status", "active", "pending", "verified").
    OrderBy("CreatedAt", "desc").
    Limit(50).
    GetAll()

Select Only Needed Fields

users, err := UserRepo.
    Select("ID", "Email", "FirstName", "LastName").
    Where("Status", "=", "active").
    Limit(100).
    GetAll()

Chaining Pattern

All query methods return the query builder, enabling fluent chaining:

query := UserRepo.
    Select("ID", "Email", "Status").
    Where("Status", "=", "active")

if onlyAdmins {
    query = query.Where("Role", "=", "admin")
}

if limit > 0 {
    query = query.Limit(limit)
}

users, err := query.GetAll()

Best Practices

Do

  • Use GSIs for frequently queried fields
  • Use projection to reduce data transfer
  • Let Dynorm auto-select indexes
  • Use GetFirst() when expecting single result

Don't

  • Perform table scans on large tables
  • Ignore query costs in high-traffic scenarios
  • Over-fetch data when you need specific fields

Next Steps