v1.0.8
zoobzio December 14, 2025 Edit this page

Specs

Specs are JSON-serializable query definitions. They enable query construction from external sources: LLM-generated queries, configuration files, or API request bodies.

See API Reference for complete type definitions.

Use Cases

LLM Integration - Let language models generate query specifications that your application executes safely.

Configuration - Define queries in config files for report generation or scheduled jobs.

API Endpoints - Accept query parameters as JSON and convert to type-safe queries.

Dynamic Filters - Build complex filter UIs that serialise to specs.

Query Specs

QuerySpec

Multi-record SELECT queries:

spec := soy.QuerySpec{
    Fields:   []string{"id", "email", "name"},
    Where:    []soy.ConditionSpec{...},
    OrderBy:  []soy.OrderBySpec{...},
    Limit:    intPtr(10),
    Offset:   intPtr(0),
    Distinct: true,
}

query := users.QueryFromSpec(spec)
results, err := query.Exec(ctx, params)

SelectSpec

Single-record SELECT:

spec := soy.SelectSpec{
    Fields: []string{"id", "email"},
    Where:  []soy.ConditionSpec{
        {Field: "id", Operator: "=", Param: "user_id"},
    },
    ForUpdate: true,
}

sel := users.SelectFromSpec(spec)
user, err := sel.Exec(ctx, map[string]any{"user_id": 123})

Condition Specs

Simple Conditions

condition := soy.ConditionSpec{
    Field:    "age",
    Operator: ">=",
    Param:    "min_age",
}

NULL Conditions

// IS NULL
condition := soy.ConditionSpec{
    Field:    "deleted_at",
    Operator: "IS NULL",
    IsNull:   true,
}

// IS NOT NULL
condition := soy.ConditionSpec{
    Field:    "email",
    Operator: "IS NOT NULL",
    IsNull:   true,
}

Grouped Conditions

AND groups:

condition := soy.ConditionSpec{
    Logic: "AND",
    Group: []soy.ConditionSpec{
        {Field: "age", Operator: ">=", Param: "min"},
        {Field: "age", Operator: "<=", Param: "max"},
        {Field: "status", Operator: "=", Param: "status"},
    },
}

OR groups:

condition := soy.ConditionSpec{
    Logic: "OR",
    Group: []soy.ConditionSpec{
        {Field: "role", Operator: "=", Param: "admin"},
        {Field: "role", Operator: "=", Param: "mod"},
    },
}

Nested groups:

// (status = 'active' AND (role = 'admin' OR role = 'mod'))
condition := soy.ConditionSpec{
    Logic: "AND",
    Group: []soy.ConditionSpec{
        {Field: "status", Operator: "=", Param: "status"},
        {
            Logic: "OR",
            Group: []soy.ConditionSpec{
                {Field: "role", Operator: "=", Param: "admin"},
                {Field: "role", Operator: "=", Param: "mod"},
            },
        },
    },
}

OrderBy Specs

Basic Ordering

order := soy.OrderBySpec{
    Field:     "created_at",
    Direction: "desc",
}

NULL Handling

order := soy.OrderBySpec{
    Field:      "score",
    Direction:  "desc",
    NullsFirst: true,
}

order := soy.OrderBySpec{
    Field:     "score",
    Direction: "asc",
    NullsLast: true,
}

Expression Ordering

For pgvector or computed values:

order := soy.OrderBySpec{
    Expression: "embedding <-> :query_vec",
    Direction:  "asc",
}

Mutation Specs

CreateSpec

INSERT with conflict handling:

spec := soy.CreateSpec{
    OnConflict:     []string{"email"},
    ConflictAction: "nothing",
}

// Or with upsert
spec := soy.CreateSpec{
    OnConflict:     []string{"email"},
    ConflictAction: "update",
    ConflictSet:    map[string]string{"name": "name", "age": "age"},
}

create := users.InsertFromSpec(spec)
result, err := create.Exec(ctx, user)

UpdateSpec

spec := soy.UpdateSpec{
    Set: map[string]string{
        "name":   "new_name",
        "status": "new_status",
    },
    Where: []soy.ConditionSpec{
        {Field: "id", Operator: "=", Param: "user_id"},
    },
}

update := users.ModifyFromSpec(spec)
result, err := update.Exec(ctx, params)

DeleteSpec

spec := soy.DeleteSpec{
    Where: []soy.ConditionSpec{
        {Field: "status", Operator: "=", Param: "status"},
        {Field: "created_at", Operator: "<", Param: "cutoff"},
    },
}

del := users.RemoveFromSpec(spec)
count, err := del.Exec(ctx, params)

Aggregate Specs

The aggregate function is determined by which method you call:

// COUNT (Field is ignored)
countSpec := soy.AggregateSpec{
    Where: []soy.ConditionSpec{
        {Field: "status", Operator: "=", Param: "status"},
    },
}
count, err := users.CountFromSpec(countSpec).Exec(ctx, params)

// SUM/AVG/MIN/MAX (Field is required)
sumSpec := soy.AggregateSpec{
    Field: "amount",
    Where: []soy.ConditionSpec{
        {Field: "status", Operator: "=", Param: "paid"},
    },
}
total, err := users.SumFromSpec(sumSpec).Exec(ctx, params)
avg, err := users.AvgFromSpec(sumSpec).Exec(ctx, params)
min, err := users.MinFromSpec(sumSpec).Exec(ctx, params)
max, err := users.MaxFromSpec(sumSpec).Exec(ctx, params)

Compound Query Specs

Combine queries with set operations:

spec := soy.CompoundQuerySpec{
    Base: soy.QuerySpec{
        Where: []soy.ConditionSpec{
            {Field: "status", Operator: "=", Param: "active"},
        },
    },
    Operands: []soy.SetOperandSpec{
        {
            Operation: "union",
            Query: soy.QuerySpec{
                Where: []soy.ConditionSpec{
                    {Field: "status", Operator: "=", Param: "pending"},
                },
            },
        },
    },
    OrderBy: []soy.OrderBySpec{
        {Field: "name", Direction: "asc"},
    },
    Limit: intPtr(100),
}

compound := users.CompoundFromSpec(spec)
results, err := compound.Exec(ctx, params)

JSON Serialisation

Specs marshal to JSON for storage or transmission:

spec := soy.QuerySpec{
    Fields: []string{"id", "email"},
    Where: []soy.ConditionSpec{
        {Field: "status", Operator: "=", Param: "status"},
    },
    OrderBy: []soy.OrderBySpec{
        {Field: "name", Direction: "asc"},
    },
    Limit: intPtr(10),
}

data, _ := json.Marshal(spec)

{
  "fields": ["id", "email"],
  "where": [
    {"field": "status", "operator": "=", "param": "status"}
  ],
  "order_by": [
    {"field": "name", "direction": "asc"}
  ],
  "limit": 10
}

API Example

Accept query specs from HTTP requests:

func HandleSearch(w http.ResponseWriter, r *http.Request) {
    var spec soy.QuerySpec
    if err := json.NewDecoder(r.Body).Decode(&spec); err != nil {
        http.Error(w, "invalid spec", http.StatusBadRequest)
        return
    }

    // Validate and sanitise spec
    if spec.Limit == nil || *spec.Limit > 100 {
        limit := 100
        spec.Limit = &limit
    }

    // Extract params from request (e.g., query string)
    params := extractParams(r)

    query := users.QueryFromSpec(spec)
    results, err := query.Exec(r.Context(), params)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    json.NewEncoder(w).Encode(results)
}

LLM Integration

Generate specs from natural language:

// Example: User asks "Find active users over 30, sorted by name"
// LLM generates:
specJSON := `{
  "where": [
    {"field": "status", "operator": "=", "param": "status"},
    {"field": "age", "operator": ">", "param": "min_age"}
  ],
  "orderBy": [{"field": "name", "direction": "asc"}]
}`

var spec soy.QuerySpec
json.Unmarshal([]byte(specJSON), &spec)

// Safe execution with validated params
params := map[string]any{
    "status":  "active",
    "min_age": 30,
}

results, err := users.QueryFromSpec(spec).Exec(ctx, params)

The spec system ensures that:

  1. Only valid field names are used (schema validation)
  2. SQL injection is impossible (named parameters)
  3. Query structure is predictable (type-safe builders)
© 2026 Alexander Thorwaldson