Core Concepts

Soy has four primitives: the soy instance, query builders, conditions, and specs. Understanding these unlocks the full API.

Soy Instance

A Soy[T] instance is the entry point for all queries. Create one per table:

users, err := soy.New[User](db, "users")
orders, err := soy.New[Order](db, "orders")

The type parameter T flows through all operations, ensuring type safety:

user, err := users.Select().Exec(ctx, params)   // Returns *User
order, err := orders.Select().Exec(ctx, params) // Returns *Order

Create instances as package-level variables or singletons. Each instance caches schema metadata. Dynamic creation leads to redundant reflection.

// Good: fixed set of instances
var (
    Users  *soy.Soy[User]
    Orders *soy.Soy[Order]
)

func Init(db *sqlx.DB) error {
    var err error
    Users, err = soy.New[User](db, "users")
    if err != nil {
        return err
    }
    Orders, err = soy.New[Order](db, "orders")
    return err
}

// Bad: dynamic instance per request
func HandleRequest(userID string) {
    users, _ := soy.New[User](db, "users") // Redundant reflection
    // ...
}

Queries

Select vs Query

Soy provides two SELECT builders:

// Single user by ID
user, err := users.Select().
    Where("id", "=", "user_id").
    Exec(ctx, map[string]any{"user_id": 123})

// All active users
activeUsers, err := users.Query().
    Where("status", "=", "status_param").
    Exec(ctx, map[string]any{"status_param": "active"})

Field Selection

Select specific fields:

// Only id and email
users.Select().Fields("id", "email")

Invalid field names produce errors at build time.

Execution Methods

All builders provide:

// Inspect SQL before execution
result, err := users.Select().Where("id", "=", "id").Render()
fmt.Printf("SQL: %s\n", result.SQL)
// Output: SQL: SELECT id, email, name, age FROM users WHERE id = :id LIMIT 1

Conditions

Simple Conditions

WHERE clauses use three arguments: field, operator, parameter name.

users.Select().Where("age", ">=", "min_age")

The parameter name maps to values in the params map:

params := map[string]any{"min_age": 18}
user, err := users.Select().Where("age", ">=", "min_age").Exec(ctx, params)

Supported Operators

Condition Helpers

Use helpers for complex conditions:

// Simple condition
C("field", "operator", "param")

// NULL checks
Null("field")     // field IS NULL
NotNull("field")  // field IS NOT NULL

AND/OR Groups

Combine conditions:

// AND group
users.Query().WhereAnd(
    soy.C("age", ">=", "min_age"),
    soy.C("status", "=", "status"),
)
// WHERE (age >= :min_age AND status = :status)

// OR group
users.Query().WhereOr(
    soy.C("role", "=", "admin"),
    soy.C("role", "=", "moderator"),
)
// WHERE (role = :admin OR role = :moderator)

// Combined
users.Query().
    WhereAnd(
        soy.C("age", ">=", "min_age"),
        soy.C("status", "=", "status"),
    ).
    WhereOr(
        soy.C("role", "=", "admin"),
        soy.C("role", "=", "mod"),
    )
// WHERE (age >= :min_age AND status = :status) AND (role = :admin OR role = :mod)

NULL Conditions

// IS NULL
users.Query().WhereNull("deleted_at")

// IS NOT NULL
users.Query().WhereNotNull("email")

Ordering

Basic Ordering

users.Query().OrderBy("name", "asc")
users.Query().OrderBy("created_at", "desc")

NULL Handling

Control where NULLs appear:

// NULLs first
users.Query().OrderByNulls("score", "desc", "first")

// NULLs last
users.Query().OrderByNulls("score", "asc", "last")

Expression Ordering

For pgvector similarity or custom expressions:

// Order by vector distance
users.Query().OrderByExpr("embedding", "<->", "query_vec", "asc")

Pagination

users.Query().
    OrderBy("created_at", "desc").
    Limit(10).
    Offset(20)

Builders

Each operation has a dedicated builder:

Builder Pattern

Builders use method chaining with deferred execution:

query := users.Query().
    Where("status", "=", "status").
    OrderBy("name", "asc").
    Limit(10)

// SQL not generated yet

results, err := query.Exec(ctx, params)
// SQL generated and executed

Error Handling

Builders capture errors internally and report them at execution:

query := users.Query().
    Where("invalid_field", "=", "param")  // Error captured here

results, err := query.Exec(ctx, params)   // Error returned here
// err: field "invalid_field" not in schema

This allows fluent chaining without checking errors at each step.

Specs

Specs are JSON-serializable query definitions. Use them for:

• LLM-generated queries
• Configuration files
• API request bodies

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

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

See Specs Guide for complete spec documentation.

Transactions

Execute queries within transactions:

tx, err := db.BeginTxx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

user, err := users.Select().
    Where("id", "=", "user_id").
    ExecTx(ctx, tx, params)
if err != nil {
    return err
}

_, err = users.Modify().
    Set("login_count", "new_count").
    Where("id", "=", "user_id").
    ExecTx(ctx, tx, params)
if err != nil {
    return err
}

return tx.Commit()

Lifecycle Callbacks

Register callbacks on a Soy[T] instance to intercept records as they are read or written:

// Normalize email after every scan
users.OnScan(func(ctx context.Context, result *User) error {
    result.Email = strings.ToLower(result.Email)
    return nil
})

// Validate before every insert
users.OnRecord(func(ctx context.Context, record *User) error {
    if record.Email == "" {
        return errors.New("email is required")
    }
    return nil
})

• OnScan fires after each row is scanned into *T — in Select, Query, Update, Create, and Compound paths.
• OnRecord fires before each *T is written — in Create paths (single, batch, upsert).
• Callbacks are nil by default with zero overhead when unregistered.
• Returning an error aborts the operation.

See the Lifecycle Guide for details.

Row Locking

Lock rows for concurrent access:

// Exclusive lock
users.Select().Where("id", "=", "id").ForUpdate()

// Shared lock
users.Select().Where("id", "=", "id").ForShare()

// Less restrictive locks
users.Select().Where("id", "=", "id").ForNoKeyUpdate()
users.Select().Where("id", "=", "id").ForKeyShare()