Statements

This guide covers defining typed statements for your database operations.

Queries

Queries return multiple records. Use for lists, search results, and filtered collections.

Basic Query

var ActiveUsers = edamame.NewQueryStatement("active-users", "Find all active users", edamame.QuerySpec{
    Where: []edamame.ConditionSpec{
        {Field: "active", Operator: "=", Param: "active"},
    },
})

// Usage
users, err := exec.ExecQuery(ctx, ActiveUsers, map[string]any{
    "active": true,
})

With Ordering and Pagination

limit := 20
offset := 0

var RecentUsers = edamame.NewQueryStatement("recent-users", "Get users ordered by creation date", edamame.QuerySpec{
    OrderBy: []edamame.OrderBySpec{
        {Field: "created_at", Direction: "desc"},
    },
    Limit:  &limit,
    Offset: &offset,
})

With Field Selection

var UserNames = edamame.NewQueryStatement("user-names", "Get user names only", edamame.QuerySpec{
    Fields: []string{"id", "name"},  // Only select these columns
})

With Grouping

var UsersByRole = edamame.NewQueryStatement("users-by-role", "Group users by role", edamame.QuerySpec{
    Fields:  []string{"role", "COUNT(*) as count"},
    GroupBy: []string{"role"},
})

HAVING with Aggregates

Use HavingAgg for aggregate conditions in HAVING clauses:

var PopularRoles = edamame.NewQueryStatement("popular-roles", "Roles with minimum user count", edamame.QuerySpec{
    Fields:  []string{"role"},
    GroupBy: []string{"role"},
    HavingAgg: []edamame.HavingAggSpec{
        {Func: "count", Field: "*", Operator: ">=", Param: "min_count"},
    },
})

// Generates: SELECT role FROM users GROUP BY role HAVING COUNT(*) >= $1

Multiple aggregate conditions:

var HighValueRoles = edamame.NewQueryStatement("high-value-roles", "Roles with high balance and count", edamame.QuerySpec{
    Fields:  []string{"role"},
    GroupBy: []string{"role"},
    HavingAgg: []edamame.HavingAggSpec{
        {Func: "count", Field: "*", Operator: ">=", Param: "min_count"},
        {Func: "sum", Field: "balance", Operator: ">=", Param: "min_total"},
    },
})

DISTINCT ON (PostgreSQL)

Use DistinctOn for PostgreSQL's DISTINCT ON clause:

var LatestPerUser = edamame.NewQueryStatement("latest-per-user", "Latest record per user", edamame.QuerySpec{
    DistinctOn: []string{"user_id"},
    OrderBy: []edamame.OrderBySpec{
        {Field: "user_id", Direction: "asc"},
        {Field: "created_at", Direction: "desc"},
    },
})

// Generates: SELECT DISTINCT ON (user_id) * FROM ... ORDER BY user_id ASC, created_at DESC

Complex Conditions

var FilteredUsers = edamame.NewQueryStatement("filtered-users", "Filter users by age and role", edamame.QuerySpec{
    Where: []edamame.ConditionSpec{
        {Field: "age", Operator: ">=", Param: "min_age"},
        {Field: "age", Operator: "<=", Param: "max_age"},
        {
            Logic: "OR",
            Group: []edamame.ConditionSpec{
                {Field: "role", Operator: "=", Param: "role1"},
                {Field: "role", Operator: "=", Param: "role2"},
            },
        },
    },
})

// Generates: WHERE age >= $1 AND age <= $2 AND (role = $3 OR role = $4)

BETWEEN Conditions

var UsersInAgeRange = edamame.NewQueryStatement("users-in-age-range", "Users in age range", edamame.QuerySpec{
    Where: []edamame.ConditionSpec{
        {Field: "age", Between: true, LowParam: "min_age", HighParam: "max_age"},
    },
})

// Generates: WHERE age BETWEEN $1 AND $2

// NOT BETWEEN
var UsersOutsideRange = edamame.NewQueryStatement("users-outside-range", "Users outside age range", edamame.QuerySpec{
    Where: []edamame.ConditionSpec{
        {Field: "age", NotBetween: true, LowParam: "min_age", HighParam: "max_age"},
    },
})

// Generates: WHERE age NOT BETWEEN $1 AND $2

Field-to-Field Comparisons

Compare two columns directly without parameters:

var ModifiedAfterCreated = edamame.NewQueryStatement("modified-after-created", "Records updated after creation", edamame.QuerySpec{
    Where: []edamame.ConditionSpec{
        {Field: "updated_at", Operator: ">", RightField: "created_at"},
    },
})

// Generates: WHERE updated_at > created_at

Parameterized Pagination

Use parameter-driven limits and offsets for flexible pagination:

var PaginatedUsers = edamame.NewQueryStatement("paginated-users", "Paginated user list", edamame.QuerySpec{
    LimitParam:  "page_size",
    OffsetParam: "offset",
    OrderBy: []edamame.OrderBySpec{
        {Field: "created_at", Direction: "desc"},
    },
})

// Usage
users, err := exec.ExecQuery(ctx, PaginatedUsers, map[string]any{
    "page_size": 20,
    "offset":    40,  // Page 3
})

Select Expressions

Add computed columns using SQL functions:

var UsersWithComputed = edamame.NewQueryStatement("users-with-computed", "Users with computed columns", edamame.QuerySpec{
    Fields: []string{"id", "name"},
    SelectExprs: []edamame.SelectExprSpec{
        {Func: "upper", Field: "name", Alias: "upper_name"},
        {Func: "length", Field: "email", Alias: "email_length"},
        {Func: "count_star", Alias: "total"},
        {Func: "now", Alias: "query_time"},
    },
    GroupBy: []string{"id", "name"},
})

Available functions include: upper, lower, length, trim, concat, abs, ceil, floor, round, now, current_date, cast, count, sum, avg, min, max, coalesce, nullif.

With Row Locking

var ForUpdate = edamame.NewQueryStatement("for-update", "Query with row lock", edamame.QuerySpec{
    Where:      []edamame.ConditionSpec{{Field: "status", Operator: "=", Param: "status"}},
    ForLocking: "update",  // FOR UPDATE
})

Locking options: "update", "no_key_update", "share", "key_share"

Selects

Selects return a single record. Use for lookups by unique identifier.

By Unique Field

var ByEmail = edamame.NewSelectStatement("by-email", "Find user by email address", edamame.SelectSpec{
    Where: []edamame.ConditionSpec{
        {Field: "email", Operator: "=", Param: "email"},
    },
})

// Usage - returns error if not found
user, err := exec.ExecSelect(ctx, ByEmail, map[string]any{
    "email": "alice@example.com",
})

With Locking

var ForShare = edamame.NewSelectStatement("for-share", "Select with shared lock", edamame.SelectSpec{
    Where:      []edamame.ConditionSpec{{Field: "id", Operator: "=", Param: "id"}},
    ForLocking: "share",  // FOR SHARE
})

Updates

Updates modify records and return the updated row.

Single Field Update

var Activate = edamame.NewUpdateStatement("activate", "Activate a user by ID", edamame.UpdateSpec{
    Set: map[string]string{
        "active": "active",  // field -> param
    },
    Where: []edamame.ConditionSpec{
        {Field: "id", Operator: "=", Param: "id"},
    },
})

// Usage
updated, err := exec.ExecUpdate(ctx, Activate, map[string]any{
    "id":     123,
    "active": true,
})

Multi-Field Update

var UpdateProfile = edamame.NewUpdateStatement("update-profile", "Update user profile", edamame.UpdateSpec{
    Set: map[string]string{
        "name":  "new_name",
        "email": "new_email",
        "bio":   "new_bio",
    },
    Where: []edamame.ConditionSpec{
        {Field: "id", Operator: "=", Param: "id"},
    },
})

Batch Updates

// Execute same update with different params
count, err := exec.ExecUpdateBatch(ctx, Activate, []map[string]any{
    {"id": 1, "active": true},
    {"id": 2, "active": true},
    {"id": 3, "active": false},
})

Deletes

Deletes remove records and return the count of deleted rows.

By Single Field

var ByStatus = edamame.NewDeleteStatement("by-status", "Delete all users with given status", edamame.DeleteSpec{
    Where: []edamame.ConditionSpec{
        {Field: "status", Operator: "=", Param: "status"},
    },
})

// Usage
count, err := exec.ExecDelete(ctx, ByStatus, map[string]any{
    "status": "inactive",
})

With Multiple Conditions

var ExpiredSessions = edamame.NewDeleteStatement("expired-sessions", "Delete expired sessions", edamame.DeleteSpec{
    Where: []edamame.ConditionSpec{
        {Field: "expires_at", Operator: "<", Param: "now"},
        {Field: "active", Operator: "=", Param: "active"},
    },
})

Aggregates

Aggregates compute values across records.

Count

var CountActive = edamame.NewAggregateStatement("count-active", "Count active users", edamame.AggCount, edamame.AggregateSpec{
    Where: []edamame.ConditionSpec{
        {Field: "active", Operator: "=", Param: "active"},
    },
})

count, err := exec.ExecAggregate(ctx, CountActive, map[string]any{
    "active": true,
})

Sum, Avg, Min, Max

// Sum
var TotalBalance = edamame.NewAggregateStatement("total-balance", "Total balance", edamame.AggSum, edamame.AggregateSpec{
    Field: "balance",
})

// Average
var AvgAge = edamame.NewAggregateStatement("avg-age", "Average age", edamame.AggAvg, edamame.AggregateSpec{
    Field: "age",
})

// Min/Max
var Youngest = edamame.NewAggregateStatement("youngest", "Youngest user", edamame.AggMin, edamame.AggregateSpec{
    Field: "age",
})

Inserts

Inserts don't use statements - they're driven by struct fields:

// Single insert
inserted, err := exec.ExecInsert(ctx, &user)

// Batch insert
count, err := exec.ExecInsertBatch(ctx, users)

With Conflict Handling

For upsert patterns, use the underlying soy API:

s := exec.Soy()

// ON CONFLICT DO NOTHING
result, err := s.Insert().
    OnConflictDoNothing("email").
    Exec(ctx, &user)

// ON CONFLICT DO UPDATE
result, err := s.Insert().
    OnConflict("email").
    DoUpdate(map[string]string{"name": "name"}).
    Exec(ctx, &user)

Compound Queries

Compound queries combine multiple SELECT statements using set operations.

UNION

spec := edamame.CompoundQuerySpec{
    Base: edamame.QuerySpec{
        Where: []edamame.ConditionSpec{
            {Field: "role", Operator: "=", Param: "role1"},
        },
    },
    Operands: []edamame.CompoundOperand{
        {
            Operation: "union",
            Query: edamame.QuerySpec{
                Where: []edamame.ConditionSpec{
                    {Field: "role", Operator: "=", Param: "role2"},
                },
            },
        },
    },
    OrderBy: []edamame.OrderBySpec{
        {Field: "name", Direction: "asc"},
    },
}

users, err := exec.ExecCompound(ctx, spec, map[string]any{
    "role1": "admin",
    "role2": "moderator",
})

Available Operations

Rendering for Inspection

sql, err := exec.RenderCompound(spec)
// SELECT ... WHERE role = $1 UNION SELECT ... WHERE role = $2 ORDER BY name ASC

Statement Metadata

Inspect statement properties:

fmt.Println(ByStatus.Name())        // "by-status"
fmt.Println(ByStatus.Description()) // "Find users by status"
fmt.Println(ByStatus.ID())          // UUID
fmt.Println(ByStatus.Tags())        // ["user", "filter"]

for _, p := range ByStatus.Params() {
    fmt.Printf("Param: %s (type: %s, required: %v)\n", p.Name, p.Type, p.Required)
}

Tags

Statements support optional tags for categorization:

var ByRole = edamame.NewQueryStatement("by-role", "Find users by role", edamame.QuerySpec{
    Where: []edamame.ConditionSpec{
        {Field: "role", Operator: "=", Param: "role"},
    },
}, "user", "filter", "security")  // Tags as variadic args

Parameter Derivation

Params are automatically derived from specs:

var Example = edamame.NewQueryStatement("example", "Example query", edamame.QuerySpec{
    Where: []edamame.ConditionSpec{
        {Field: "age", Operator: ">=", Param: "min_age"},
        {Field: "status", Operator: "=", Param: "status"},
    },
})

// Params auto-derived:
// - min_age (required)
// - status (required)

for _, p := range Example.Params() {
    fmt.Printf("Param: %s (required: %v)\n", p.Name, p.Required)
}