A highly opinionated, strictly-typed, and functional SQL DSL generator for Go.

strsql is a highly opinionated, strictly-typed, and functional SQL DSL generator for Go.

It completely eliminates magic strings and runtime type errors by leveraging AST code generation and a Type-Safe Builder (Trait pattern), providing a Java-like dot-chaining autocompletion experience right inside your Go IDE.

Core Philosophy: Constructing correct and safe SQL. This library focuses only on safely generating SQL queries and arguments. It is entirely agnostic to your underlying database driver (whether you use database/sql, sqlx, or any other executor).

• Zero Magic Strings: Code generation extracts your struct db tags to create safe mapping Singletons (e.g., OrderSch.Id).
• Fail-Fast Type Checking: Pass an int to a string column? It panics before the SQL is even generated.
• Type-Safe Builder (Traits): Compile-time constraints ensure your SQL is syntactically valid (e.g., an UPDATE must have a SET clause).
• Pure Functional Logic: Say goodbye to ambiguous A.And(B).Or(C) chains. We use functional combinators like strsql.And(A, B) for crystal clear precedence.
• No Driver Dependencies: It outputs standard SQL strings and []any arguments, ready to be fed into any database executor.

# Install the core library
go get github.com/kcmvp/goat

# Install the code generator CLI globally
go install github.com/kcmvp/goat/cmd/strsql@latest

Your structs must implement the strsql.Entity interface (providing a TableName() string method). Use struct tags (default is db) to specify column names.

package models

import (
	"time"
        "github.com/kcmvp/goat"
)

type Order struct {
	Id      string    `db:"order_id"`
	OrdDate time.Time `db:"creation_date"`
}

// Implement the strsql.Entity interface
func (Order) TableName() string {
	return "orders"
}

// Ensure interface compliance
var _ strsql.Entity = Order{}

Use the built-in CLI tool to parse your structs and generate the type-safe mapping code.

# Generate schema for the ./models directory (using global installation)
strsql gen ./models

# Or using go run directly from the module
go run github.com/kcmvp/goat/cmd/strsql gen ./models

# You can also specify custom struct tags (e.g., gorm)
go run github.com/kcmvp/goat/cmd/strsql gen -t gorm ./models

This generates a schema_gen.go file containing Singletons like OrderSch, which expose your columns as closures.

Enjoy the flawless IDE autocompletion and compile-time/runtime safety.

import "github.com/kcmvp/goat"

// Default: SELECT *
sql, args := strsql.Select[Order]().
    Where(
        strsql.Eq(OrderSch.Id, "ORD-12345"),
        strsql.In(OrderSch.Id, "O1", "O2"), // Variadic arguments are automatically treated as AND
    ).
    OrderBy(OrderSch.OrdDate, strsql.Desc).
    Limit(10).
    Build()

// sql:  SELECT * FROM orders WHERE order_id = ? AND order_id IN (?, ?) ORDER BY creation_date DESC LIMIT 10
// args: [ORD-12345 O1 O2]

// Specific Columns & Aggregate Functions
sql, args = strsql.Select[Order](
    OrderSch.Id,
    strsql.Count(OrderSch.Id),
    strsql.Sum(OrderSch.Status),
).Where(strsql.Eq(OrderSch.IsPaid, true)).Build()

strsql supports both explicit and inferred GROUP BY.

• If a SELECT contains both aggregate expressions and non-aggregate columns, and you do not call GroupBy(...), the builder automatically groups by all selected non-aggregate columns.
• If you do call GroupBy(...), it must include all selected non-aggregate columns, otherwise the builder fails fast.
• HAVING compares expressions through ExprXxx(...) helpers, so aggregate expressions such as Count(...) and Sum(...) can be reused directly.

// Auto GROUP BY all selected non-aggregate columns.
sql, args := strsql.Select[Order](
    OrderSch.Status,
    strsql.Count(OrderSch.Id),
).Build()

// sql: SELECT status, COUNT(id) FROM orders GROUP BY status

// Explicit GROUP BY + HAVING
sql, args = strsql.Select[Order](
    OrderSch.Status,
    strsql.Count(OrderSch.Id),
).
    GroupBy(OrderSch.Status).
    Having(strsql.ExprGt(strsql.Count(OrderSch.Id), int64(1))).
    Build()

// sql:  SELECT status, COUNT(id) FROM orders GROUP BY status HAVING COUNT(id) > ?
// args: [1]

JOIN queries use generated Refs() / As(alias) values so every selected column is source-bound.

oi := OrderItemSch.As("oi")
p := ProductSch.As("p")

sql, args := strsql.WithJoins(
    strsql.InnerJoin(p.Source(), strsql.OnEq(oi.ProductID, p.ID)),
).
    Select(oi.OrderID, oi.Quantity).
    Select(p.Name).
    Select(strsql.Sum(oi.Quantity)).
    Having(strsql.ExprGt(strsql.Sum(oi.Quantity), 10)).
    Build()

// sql:  SELECT oi.order_id, oi.quantity, p.name, SUM(oi.quantity)
//       FROM order_items AS oi
//       INNER JOIN products AS p ON oi.product_id = p.id
//       GROUP BY oi.order_id, oi.quantity, p.name
//       HAVING SUM(oi.quantity) > ?
// args: [10]

The Limit method accepts an optional second argument for the OFFSET.

sql, args := strsql.Select[Order]().
    OrderBy(OrderSch.OrdDate, strsql.Desc).
    Limit(10, 20). // LIMIT 10 OFFSET 20
    Build()

Functional combinators eliminate precedence ambiguity.

sql, args := strsql.Select[Order]().
    Where(
        strsql.Or(
            strsql.Eq(OrderSch.Id, "O-A"),
            strsql.And(
                strsql.NotEq(OrderSch.Id, "O-B"),
                strsql.Like(OrderSch.Id, "%O-C%"),
                strsql.Between(OrderSch.Status, 1, 5),
                strsql.NotIn(OrderSch.Id, "O-X", "O-Y"),
            ),
        ),
    ).
    Limit(1).
    Build()

The compiler forces you to call Set before Build.

sql, args := strsql.Update[Order]().
    Set(
        strsql.Set(OrderSch.OrdDate, time.Now()),
    ).
    Where(strsql.Eq(OrderSch.Id, "ORD-12345")).
    Build()

Safely increment/decrement numeric columns. The library will fail-fast if you try this on a string column!

sql, args := strsql.Update[OrderItem]().
    Set(
        strsql.IncrNum(OrderItemSch.Qty, 5),
        strsql.DecrNum(OrderItemSch.Qty, 2),
    ).
    Where(strsql.Eq(OrderItemSch.PrdId, "P-100")).
    Build()

sql, args := strsql.Delete[Order]().
    Where(strsql.Eq(OrderSch.Id, "ORD-12345")).
    Build()

Predicates (WHERE clauses):

• Eq (=)
• NotEq (<>)
• Gt (>)
• Gte (>=)
• Lt (<)
• Lte (<=)
• Like (LIKE)
• NotLike (NOT LIKE)
• In (IN)
• NotIn (NOT IN)
• Between (BETWEEN ? AND ?)
• IsNull (IS NULL)
• IsNotNull (IS NOT NULL)

Expression Predicates (HAVING / expression comparisons):

• ExprEq
• ExprNotEq
• ExprGt
• ExprGte
• ExprLt
• ExprLte
• ExprLike
• ExprNotLike
• ExprIn
• ExprNotIn
• ExprBetween
• ExprIsNull
• ExprIsNotNull

Aggregate Functions:

• Count (COUNT(col) or COUNT(*))
• Sum (SUM(col))
• Max (MAX(col))
• Min (MIN(col))
• Avg (AVG(col))

Assignments (SET clauses):

• Set (column = ?)
• IncrNum (column = column + ?)
• DecrNum (column = column - ?)

Combinators:

• And
• Or

• GROUP BY / HAVING requires explicit selected columns; it is not supported together with SELECT *.
• Without an explicit GroupBy(...), aggregate queries automatically group by all selected non-aggregate columns.
• With an explicit GroupBy(...), every selected non-aggregate column must be included.
• HAVING requires either aggregate selections or an effective GROUP BY.

• JOIN mode requires source-bound columns (Ref[T]), not bare Attribute[T].
• A single .Select(...) call in JOIN mode may contain:
  • columns from one source
  • or aggregate expressions
• A single .Select(...) call in JOIN mode may not mix:
  • multiple sources
  • aggregate and non-aggregate expressions
• JOIN GroupBy(...) requires source-bound columns.
• JOIN Having(...) requires source-aware expressions; COUNT(*) cannot be used in JOIN HAVING because it has no source metadata.