v1.0.8
zoobzio December 18, 2025 Edit this page

Overview

Query builders in Go often mean string concatenation or runtime reflection on every query.

Soy offers a different approach: type-safe query building with reflection performed once at initialization, not on hot paths.

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

// Define your model
type User struct {
    ID    int    `db:"id" type:"serial" constraints:"primary key"`
    Email string `db:"email" type:"text" constraints:"not null unique"`
    Name  string `db:"name" type:"text"`
    Age   int    `db:"age" type:"int"`
}

// Create a soy instance with a database provider
users, err := soy.New[User](db, "users", postgres.New())
if err != nil {
    log.Fatal(err)
}

// Query with type safety
user, err := users.Select().
    Where("email", "=", "user_email").
    Exec(ctx, map[string]any{"user_email": "alice@example.com"})

Named parameters, compile-time type guarantees, zero reflection on query execution.

Architecture

┌─────────────────────────────────────────────────────────────┐
│                        Soy[T]                            │
│                                                             │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │  Sentinel   │  │   ASTQL     │  │     sqlx.DB         │  │
│  │  (types)    │  │  (schema)   │  │   (execution)       │  │
│  └─────────────┘  └─────────────┘  └─────────────────────┘  │
│         │               │                    │              │
│         └───────────────┴────────────────────┘              │
│                         │                                   │
│  ┌──────────────────────┴───────────────────────────────┐   │
│  │              Query Builders                          │   │
│  │  Select │ Query │ Insert │ Update │ Delete │ Agg    │   │
│  └──────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────┘

Soy wraps three components:

  • Sentinel - Type inspection library, caches struct metadata
  • ASTQL - Abstract SQL with schema validation
  • sqlx - Database execution layer

Reflection occurs once during New[T](). All subsequent queries use cached metadata.

Philosophy

Soy prioritises safety without sacrificing ergonomics:

// Type-safe field references
users.Select().Where("email", "=", "email_param")  // Field "email" validated against schema

// Named parameters prevent SQL injection
users.Query().Where("age", ">=", "min_age").Exec(ctx, map[string]any{
    "min_age": 18,
})

// Required WHERE for destructive operations
users.Modify().Set("name", "new_name").Exec(ctx, params)  // Error: WHERE required

The schema is derived from struct tags at initialisation. Invalid field names or parameters fail fast.

Capabilities

A unified query builder opens possibilities:

Basic Queries - SELECT single records or multiple with pagination, ordering, and complex WHERE conditions.

Mutations - INSERT with ON CONFLICT handling, UPDATE with required WHERE, DELETE with batch support.

Aggregates - COUNT, SUM, AVG, MIN, MAX with GROUP BY and HAVING clauses.

Compound Queries - UNION, INTERSECT, EXCEPT for combining result sets.

Spec System - JSON-serializable query specifications for external construction (LLM-generated queries, config files).

Soy provides the query layer. How you use it is up to you.

Priorities

Type Safety

Fields are validated at compile time via generics. The struct type T flows through the entire chain:

users, _ := soy.New[User](db, "users")

// Returns *User
user, err := users.Select().Where("id", "=", "user_id").Exec(ctx, params)

// Returns []*User
results, err := users.Query().OrderBy("name", "asc").Exec(ctx, params)

Schema validation catches invalid field names before execution:

// Error: field "invalid_field" not in schema
users.Select().Where("invalid_field", "=", "param")

Safety

Destructive operations require explicit WHERE clauses:

// Error: UPDATE requires WHERE clause
users.Modify().Set("status", "inactive").Exec(ctx, params)

// Correct
users.Modify().Set("status", "inactive").Where("id", "=", "user_id").Exec(ctx, params)

Named parameters prevent SQL injection. Raw SQL is never constructed from user input.

Performance

  • Lazy reflection - Struct metadata cached on first use
  • Named parameters - PostgreSQL prepared statement reuse
  • Minimal allocations - Builder pattern reuses internal state
  • Per-query isolation - Each builder chain is independent

Multi-Database Support

Soy supports multiple databases through ASTQL providers:

import (
    "github.com/zoobz-io/astql/pkg/postgres"
    "github.com/zoobz-io/astql/pkg/mariadb"
    "github.com/zoobz-io/astql/pkg/sqlite"
    "github.com/zoobz-io/astql/pkg/mssql"
)

// Use the appropriate provider for your database
users, err := soy.New[User](db, "users", postgres.New())
users, err := soy.New[User](db, "users", mariadb.New())
users, err := soy.New[User](db, "users", sqlite.New())
users, err := soy.New[User](db, "users", mssql.New())

Database-Specific Features

Some features are PostgreSQL-specific:

  • DISTINCT ON - Get first row per distinct column combination
  • Row locking - FOR UPDATE, FOR SHARE, FOR NO KEY UPDATE, FOR KEY SHARE
  • Array operators - @>, <@, && for containment and overlap
  • pgvector - <->, <#>, <=>, <+> for similarity search
  • NULLS FIRST/LAST - Control NULL ordering in ORDER BY
  • FILTER aggregates - Conditional aggregation with FILTER clause
© 2026 Alexander Thorwaldson