soy

Type-safe SQL query builder for Go with schema validation and multi-database support.

Extract schema from struct tags, validate queries at initialization, execute with zero reflection.

Schema Once, Query Forever

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

// Schema extracted and validated here — once
users, _ := soy.New[User](db, "users", postgres.New())

// Every query after: type-safe, zero reflection, validated fields
user, _ := users.Select().
    Where("email", "=", "email_param").
    Exec(ctx, map[string]any{"email_param": "alice@example.com"})
// Returns *User, not interface{}

Field names validated against struct tags. Type-safe results. No reflection on the hot path.

Install

go get github.com/zoobz-io/soy

Requires Go 1.24+.

Quick Start

package main

import (
    "context"
    "fmt"
    "log"

    "github.com/jmoiron/sqlx"
    _ "github.com/lib/pq"
    "github.com/zoobz-io/astql/pkg/postgres"
    "github.com/zoobz-io/soy"
)

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

func main() {
    db, _ := sqlx.Connect("postgres", "postgres://localhost/mydb?sslmode=disable")
    defer db.Close()

    // Create instance — schema validated here
    users, _ := soy.New[User](db, "users", postgres.New())
    ctx := context.Background()

    // Insert
    created, _ := users.Insert().Exec(ctx, &User{
        Email: "alice@example.com",
        Name:  "Alice",
        Age:   30,
    })
    fmt.Printf("Created: %d\n", created.ID)

    // Select one
    user, _ := users.Select().
        Where("email", "=", "email_param").
        Exec(ctx, map[string]any{"email_param": "alice@example.com"})
    fmt.Printf("Found: %s\n", user.Name)

    // Query many
    all, _ := users.Query().
        Where("age", ">=", "min_age").
        OrderBy("name", "asc").
        Limit(10).
        Exec(ctx, map[string]any{"min_age": 18})
    fmt.Printf("Users: %d\n", len(all))

    // Update
    updated, _ := users.Modify().
        Set("age", "new_age").
        Where("id", "=", "user_id").
        Exec(ctx, map[string]any{"new_age": 31, "user_id": created.ID})
    fmt.Printf("Updated age: %d\n", updated.Age)

    // Aggregate
    count, _ := users.Count().
        Where("age", ">=", "min_age").
        Exec(ctx, map[string]any{"min_age": 18})
    fmt.Printf("Count: %.0f\n", count)
}

Capabilities

Feature	Description	Docs
Type-Safe Queries	Generics return *T or []*T, not interface{}	Queries
Schema Validation	Field names checked against struct tags at init	Concepts
Multi-Database	PostgreSQL, MariaDB, SQLite, SQL Server via ASTQL	Quickstart
Fluent Builders	Chainable API for SELECT, INSERT, UPDATE, DELETE	Mutations
Aggregates	COUNT, SUM, AVG, MIN, MAX with FILTER clauses	Aggregates
Window Functions	ROW_NUMBER, RANK, LAG, LEAD, and more	API
Compound Queries	UNION, INTERSECT, EXCEPT	Compound
Safety Guards	DELETE/UPDATE require WHERE; prevents accidents	Concepts

Why soy?

• Zero reflection on hot path — all introspection happens once at New()
• Type-safe results — queries return *T or []*T, never interface{}
• Schema validation at init — field name typos caught immediately, not at runtime
• Multi-database parity — same API across PostgreSQL, MariaDB, SQLite, SQL Server
• Safety by default — DELETE/UPDATE require WHERE; prevents accidental full-table operations
• Minimal dependencies — sqlx plus purpose-built libraries (astql, sentinel, atom)

Type-Safe Database Layer

Soy enables a pattern: define types once, query safely everywhere.

Your struct definitions become the contract. Sentinel extracts metadata from struct tags. ASTQL validates queries against that schema. Soy wraps it all in a fluent API.

// Your domain type — the single source of truth
type Order struct {
    ID        int64     `db:"id" type:"bigserial primary key"`
    UserID    int64     `db:"user_id" type:"bigint not null" references:"users(id)"`
    Total     float64   `db:"total" type:"numeric(10,2) not null"`
    Status    string    `db:"status" type:"text" check:"status IN ('pending','paid','shipped')"`
    CreatedAt time.Time `db:"created_at" type:"timestamptz default now()"`
}

// Soy validates against the schema
orders, _ := soy.New[Order](db, "orders", postgres.New())

// Invalid field? Caught at init, not runtime
orders.Select().Where("totla", "=", "x")  // Error: field "totla" not found

Three packages, one type definition, complete safety from struct tags to SQL execution.

Documentation

• Overview — what soy does and why
• Learn
  • Quickstart — get started in minutes
  • Concepts — queries, conditions, builders
• Guides
  • Queries — SELECT with filtering and ordering
  • Mutations — INSERT, UPDATE, DELETE
  • Aggregates — COUNT, SUM, AVG, MIN, MAX
  • Specs — JSON-serializable query definitions
  • Compound Queries — UNION, INTERSECT, EXCEPT
• Cookbook
  • Pagination — LIMIT/OFFSET patterns
  • Vector Search — pgvector similarity queries
• Reference
  • API — complete function documentation

Contributing

See CONTRIBUTING.md for guidelines.

License

MIT License — see LICENSE for details.