Architecture

Soy is a thin orchestration layer that combines three libraries to provide type-safe, schema-validated SQL queries.

Component Overview

┌─────────────────────────────────────────────────────────────────┐
│                          Soy[T]                                 │
│                                                                 │
│  ┌───────────────┐  ┌───────────────┐  ┌───────────────────┐   │
│  │   Sentinel    │  │    ASTQL      │  │      sqlx         │   │
│  │  (metadata)   │  │   (schema)    │  │   (execution)     │   │
│  └───────────────┘  └───────────────┘  └───────────────────┘   │
│         │                  │                    │               │
│         └──────────────────┴────────────────────┘               │
│                            │                                    │
│  ┌─────────────────────────┴─────────────────────────────────┐  │
│  │                    Query Builders                         │  │
│  │  Select │ Query │ Insert │ Modify │ Remove │ Aggregates  │  │
│  └───────────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────────┘

Dependencies

Sentinel

github.com/zoobz-io/sentinel

Sentinel extracts struct metadata from Go types. It reads db and type tags to build a schema representation.

type User struct {
    ID    int64  `db:"id" type:"bigserial primary key"`
    Email string `db:"email" type:"text unique not null"`
}

Sentinel inspects this once at soy.New[User]() and caches the field names, types, and constraints. This metadata is then passed to ASTQL for query validation.

ASTQL

github.com/zoobz-io/astql

ASTQL is an abstract SQL builder with schema validation. It:

1. Validates field names against the schema
2. Builds an AST representation of the query
3. Renders to dialect-specific SQL via providers

ASTQL providers handle database differences:

import "github.com/zoobz-io/astql/pkg/postgres"

// PostgreSQL: uses $1, $2 placeholders
users, _ := soy.New[User](db, "users", postgres.New())

// MariaDB: uses ? placeholders, different RETURNING syntax
users, _ := soy.New[User](db, "users", mariadb.New())

sqlx

github.com/jmoiron/sqlx

sqlx executes queries and scans results into structs. Soy uses:

• sqlx.ExtContext for query execution
• Named parameter binding
• Struct scanning for results

Data Flow

Initialisation (Cold Path)

soy.New[T](db, table, provider)
    │
    ├─→ Sentinel: Extract struct metadata
    │       └─→ Cache field names, types, constraints
    │
    ├─→ ASTQL: Create schema instance
    │       └─→ Register fields and parameters
    │
    └─→ Return Soy[T] with cached metadata

This happens once per table. All reflection occurs here.

Query Building (Hot Path)

users.Select().Where("email", "=", "email_param")
    │
    ├─→ Create new SelectBuilder (reuses cached metadata)
    │
    ├─→ Validate "email" against schema (map lookup)
    │
    ├─→ Validate "=" operator (map lookup)
    │
    ├─→ Register "email_param" as parameter
    │
    └─→ Return SelectBuilder (no SQL generated yet)

No reflection, no allocations beyond the builder struct.

Execution

builder.Exec(ctx, params)
    │
    ├─→ ASTQL: Render SQL string
    │       └─→ Provider transforms to dialect
    │
    ├─→ sqlx: Bind named parameters
    │
    ├─→ sqlx: Execute query
    │
    └─→ sqlx: Scan result into *T or []*T

Builder Pattern

Each operation returns a new builder that carries forward the state:

// Each method returns a new builder
b1 := users.Select()
b2 := b1.Where("email", "=", "email_param")
b3 := b2.OrderBy("created_at", "desc")

Builders are:

• Immutable — each method returns a new instance
• Chainable — methods can be called in sequence
• Lazy — SQL is only generated at Exec()

Error Handling

Errors can occur at two points:

1. Build time — invalid field names, operators, or parameters
2. Exec time — database errors

Build-time errors are accumulated in the builder:

builder := users.Select().
    Where("invalid_field", "=", "param")  // Error stored in builder

_, err := builder.Exec(ctx, params)       // Error returned here

This allows chaining to continue without interruption while ensuring errors are not silently ignored.

Event System

Soy integrates with capitan for structured events:

// Signals emitted during query execution
QueryStarted   // When a query begins
QueryCompleted // When a query succeeds
QueryFailed    // When a query fails

Events include contextual data:

• Table name
• Operation type (SELECT, INSERT, etc.)
• Duration in milliseconds
• Rows affected/returned
• Error message (on failure)

Type Safety

Go generics flow through the entire API:

// T is bound at creation
users, _ := soy.New[User](db, "users", provider)

// Select returns *User
user, err := users.Select().Exec(ctx, params)

// Query returns []*User
all, err := users.Query().Exec(ctx, params)

// Insert accepts *User, returns *User
created, err := users.Insert().Exec(ctx, &User{...})

The compiler ensures type consistency. You cannot accidentally mix models or get untyped results.

Performance Characteristics

Query building is designed to be allocation-free on the hot path. The cost of building a query should be negligible compared to network latency.