sqlx

README

sqlx — Consolidated Design Notes

This document consolidates the content of the following files in sqlx/:

• join.md
• pure.md
• sqlx.md
• special_query.md

It gathers the current design, goals, invariants, and prioritized TODOs in one place and flags items that may be outdated or need review. Use this file as the single reference for the package-level design; the original files remain in the repository for historical context.

---

Table of Contents

• Summary
• Core goals and constraints (from pure.md)
• Single-table CRUD design (detailed)
• Where DSL and helpers
• Join design (2-table only) — consolidated
• Missing features / known gaps (from sqlx.md)
• Special queries and priority work (from special_query.md)
• Outdated / review-notes (items to re-check)
• Next steps (actionable checklist)

---

Summary

sqlx aims to provide a small, generator-friendly SQL DSL and execution layer focused on deterministic SQL generation for simple CRUD and two-table joins. The package relies on generator-produced metadata (meta.Schema, meta.Field, meta.ValueObject) and emphasizes:

• Pure function-style SQL generation
• Small and predictable public API
• Deterministic column/alias naming (stable mapping to value objects)
• Safety rules for Update/Delete (require WHERE)

This consolidation gathers existing docs and priorities in one place and flags areas where designs have drifted or need confirmation.

---

Core goals and constraints (extracted from pure.md)

• SQL generation is pure and deterministic; sql() returns SQL and is side-effect free.
• Public API surface is intentionally small: Query, Delete, Update, and join variants.
• The package uses meta.Schema ([]meta.Field) as the canonical projection description.
• Where is the only way to express predicates; combinators (And, Or) manage parentheses and precedence.
• Use ? placeholders by default. Postgres-style $1, $2 is a later enhancement.
• For safety, Update / Delete must be called with non-empty Where.

---

Single-table CRUD design (detailed)

API shape (current/target):

• Query

  • Query[T](schema meta.Schema) func(where Where) Executor
  • Execution: Executor.Execute(ctx, *sql.DB) -> mo.Either[[]meta.ValueObject, sql.Result]
  • selectSQL generates SELECT <cols> FROM <table> [WHERE ...] using schema order and deterministic table__column aliases for mapping.

• Update

  • Update[T](values meta.ValueObject) func(where Where) Executor
  • Implementation reads schema from meta.SchemaOf[T]() (registered schema) at runtime.
  • updateSQL builds UPDATE <table> SET col = ? ... WHERE <clause> and uses the provided meta.ValueObject (or all placeholders when nil).
  • Safety: where required and must produce a non-empty clause.

• Delete

  • Delete[T](where Where) Executor
  • deleteSQL enforces non-empty where to prevent accidental full-table deletes.

• Count, Exists, CountDistinct (special-query helpers) — planned priorities in special_query.md.

Execution contract:

• Final executors accept (context.Context, *sql.DB); results are either []meta.ValueObject (select) or sql.Result (non-query).

Mapping rules:

• Projection columns are produced from meta.Field.QualifiedName() and aliased as table__column so rowsToValueObjects can reliably map results back to field names.
• Private fields (unexported struct fields) are not included in generation.

---

Where DSL

• Where is an interface: Build() (string, []any).
• Primitive predicates: Eq/Ne/Gt/Gte/Lt/Lte/Like/In take a meta.Field and value(s) and return a Where.
• Combinators: And, Or accept multiple Where and produce parenthesized expressions.
• In with empty values yields a safe 1=0 clause.

Implementation detail:

• whereFunc (function type) is used to adapt closures into Where values by providing a Build method.

---

Join design (two-table only) — consolidated from join.md

Principles:

• sqlx intentionally supports only 2-table joins: E1 (driven/base) and E2 (joined).
• This keeps the API small and avoids complex join graphs and aliasing issues.
• For 3+ table joins or complex reporting queries, recommend using raw SQL.

Key points:

• E1 is the driven/base entity (the table being primarily operated on). Joins can be expressed as FROM E1 JOIN E2 ... or as WHERE EXISTS (SELECT 1 FROM E2 WHERE ...) when the intention is filtering E1.
• Join predicates can be composite (multiple equality predicates combined with AND).
• Join predicates should reuse the Where DSL to get correct boolean composition and parentheses handling.

Public API (planned):

• QueryJoin(schema meta.Schema) func(joinstmt string, where Where) Executor — implemented in the codebase as a pragmatic approach where joinstmt is injected into the FROM clause; parameters must be provided via Where.
• DeleteJoin and UpdateJoin are implemented via EXISTS-style semantics: the join becomes an inner query used for filtering.

Constraints/requirements:

• joinstmt must not contain ? placeholders; all parameters must be supplied through the Where value.
• The code currently expects a single JOIN ... ON ... pattern when translating joinstmt to EXISTS(...) for delete/update.

---

Missing features / Known gaps (from sqlx.md)

These items were documented as missing in the original sqlx.md and should be prioritized or reviewed:

1. Core implementation completeness — some higher-level helpers were pending; now implemented for the main use-cases.
2. Transaction management — no explicit Tx API currently; design decisions required on how to expose Tx support in the fluent API.
3. Advanced query features (aggregation, GROUP BY, HAVING) — deferred.
4. Dialect support — currently ? placeholders; Postgres placeholder style is a planned enhancement.
5. Connection management — db.go provides data source registry; sqlx executors currently accept *sql.DB.

---

Special queries and priority work (from special_query.md)

Priority 1 (implement now):

• Count[T](ctx, schema *Schema, where Where) (int64, error) — SELECT COUNT(1) FROM table [WHERE ...].
• Exists[T](ctx, schema *Schema, where Where) (bool, error) — SELECT 1 FROM table WHERE ... LIMIT 1.
• CountDistinct[T](ctx, schema *Schema, field FieldProvider, where Where) (int64, error) — SELECT COUNT(DISTINCT <col>) FROM table [WHERE ...].

Priority 2 (soon):

• First[T](...) — single-row fetch (LIMIT 1) returning a meta.ValueObject.

Notes on implementation:

• Use deterministic field->column mapping: db_column = lo.SnakeCase(providerName) (or rely on meta.Field.QualifiedName() where available).
• Require fully-qualified provider tokens in Where to avoid ambiguous replacements; unqualified support can be added carefully later.

Tests to add:

• Table-driven tests for Count/Exists/CountDistinct using testdata/sqlite_data.json.

---

Outdated / review notes (things to re-check)

While consolidating, I identified areas that likely need review or may be outdated relative to the current code and tests:

• Placeholder strategy: many docs assume ?. If we decide to fully support Postgres, we must add a placeholder adapter.
• pure.md contains a few typed-generic Where[T] signatures; the implemented code uses Where without generics in places — verify the intended generic usage.
• The sqlx.md file described many unimplemented functions; most core builders are now implemented in builder_helpers.go — mark sqlx.md items as reviewed or removed.
• join.md suggests a QueryJoint[E1,E2] API; current QueryJoin(schema) uses a string joinstmt. Consider whether to change to a typed API in the future.
• special_query.md suggested CountDistinct and Count API signatures that include passing schema *Schema — current code often retrieves schema from meta.SchemaOf[T](); pick one consistent approach and update docs.

Action: review the items above and decide which documentation lines should be amended or removed.

---

Next steps (actionable checklist)

• Review and accept this consolidated document; remove or archive the older MD files if you want a single source-of-truth.
• Decide on schema lookup strategy: pass schema into public APIs or use meta.SchemaOf[T]() runtime registry — update docs and code for consistency.
• Implement/verify Priority 1 special queries (Count, Exists, CountDistinct) and add tests using testdata/sqlite_data.json.
• Decide whether to keep QueryJoin as string-based joinstmt or replace with a typed QueryJoint[E1,E2] generic API; update join.md accordingly.
• Add a short section on dialect strategy (placeholders) — finalize whether sqlx will adopt an adapter for $1 vs ?.
• Remove or mark out-of-date the original *.md files (optional) once you accept this consolidation.

---

Document provenance

This file was generated by consolidating the sqlx package markdown files. It intentionally preserves the original design text where useful and adds flags for review. If you want, I can also:

• Produce a cleaned, canonical sqlx/README.md based on this file.
• Create a PR that renames old docs to .md.bak after you accept the consolidation.

Tell me which of the next steps you'd like me to execute, and I will proceed (implement tests, add Count/Exists/CountDistinct, or create README and archive old docs).

Documentation

Index

• Constants
• func CloseAllDataSources() error
• func CloseDataSource(name string) error
• func Query[T entity.Entity](schema Schema) func(where Where) Executor
• func QueryJoin(schema Schema) func(joinstmt string, where Where) Executor
• func SetSQLLogger(l *log.Logger)
• func Update[T entity.Entity](values ValueObject) func(where Where) Executor
• func UpdateJoin[T entity.Entity](values ValueObject) func(joinstmt string, where Where) Executor
• type DB
  • func DefaultDS() (DB, bool)
  • func GetDS(name string) (DB, bool)
  • func WithSQLLogger(db DB, logger *log.Logger) DB
• type Executor
  • func Delete[T entity.Entity](where Where) Executor
  • func DeleteJoin[T entity.Entity](joinstmt string, where Where) Executor
• type Schema
• type ValueObject
  • func NewValueObject(m map[string]any) ValueObject
• type Where
  • func And(wheres ...Where) Where
  • func Eq(field dvo.Field, value any) Where
  • func Gt(field dvo.Field, value any) Where
  • func Gte(field dvo.Field, value any) Where
  • func In(field dvo.Field, values ...any) Where
  • func Like(field dvo.Field, value string) Where
  • func Lt(field dvo.Field, value any) Where
  • func Lte(field dvo.Field, value any) Where
  • func Ne(field dvo.Field, value any) Where
  • func Or(wheres ...Where) Where

Constants

const (
	UserKey     = "${user}"
	PasswordKey = "${password}"
	HostKey     = "${host}"
)

Functions

func CloseAllDataSources

func CloseAllDataSources() error

CloseAllDataSources closes and removes all registered datasources from the registry. It returns the first error encountered while closing any datasource, or nil on success.

func CloseDataSource

func CloseDataSource(name string) error

CloseDataSource closes and removes the named datasource from the registry.

func Query

func Query[T entity.Entity](schema Schema) func(where Where) Executor

Query builds a single-table SELECT query.

Usage example:

// build executor
exec := Query[Account](schema)(Eq(field, value))
// run
resEither, err := exec.Execute(ctx, db)
// check left/right and handle accordingly

func QueryJoin

func QueryJoin(schema Schema) func(joinstmt string, where Where) Executor

QueryJoin builds a select executor that injects `joinstmt` into the FROM clause. The returned Executor follows the existing `Executor` contract.

func SetSQLLogger

func SetSQLLogger(l *log.Logger)

SetSQLLogger enables SQL logging for all datasources registered after this call. Call this early (e.g., in main) before any DefaultDS/GetDS calls.

func Update

func Update[T entity.Entity](values ValueObject) func(where Where) Executor

Update builds a single-table UPDATE query.

New design: public Update accepts the update payload as a meta.ValueObject; the ValueObject may include a special "__schema" entry or provide its own Fields() listing. This avoids a global runtime schema registry.

func UpdateJoin

func UpdateJoin[T entity.Entity](values ValueObject) func(joinstmt string, where Where) Executor

UpdateJoin builds an update executor that applies an EXISTS-correlated join filter. The update payload values are supplied as a meta.ValueObject when creating the executor via UpdateJoin[T](values)(joinstmt, where).

Types

type DB

type DB interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	PingContext(ctx context.Context) error
	Close() error
}

DB is the minimal database contract used by this package. It mirrors the methods we use from *sql.DB and can be backed by *sql.DB or a thin wrapper.

This indirection lets us add cross-cutting features (SQL logging, tracing, metrics) without changing the higher-level query builder APIs.

func DefaultDS

func DefaultDS() (DB, bool)

DefaultDS returns the default datasource if registered.

func GetDS

func GetDS(name string) (DB, bool)

GetDS returns a registered datasource by name.

func WithSQLLogger

func WithSQLLogger(db DB, logger *log.Logger) DB

WithSQLLogger wraps db with a SQL logger if logger is not nil.

type Executor

type Executor interface {
	Execute(ctx context.Context, ds *sql.DB) (mo.Either[[]ValueObject, sql.Result], error)
	// contains filtered or unexported methods
}

Executor represents the delayed execution step constructed by the top-level factory helpers (`Query`, `Delete`, `Update`).

Execution contract:

• Callers obtain an Executor via one of the factory functions and then call `Execute(ctx, db)` to run the statement. Parameter values for the prepared statement are set during `Where.Build()` (the Where returns the SQL fragment and its argument list). For INSERT/UPDATE builders which consume a `meta.ValueObject` for column values, those helpers read the ValueObject inside their builder functions.
• `Execute` will call the appropriate lower-level builder helper to obtain both the SQL string and the argument list (e.g. `selectSQL`).
• For SELECT statements the left side of `mo.Either` holds the `[]meta.ValueObject` results; for non-SELECT statements the right side holds the `sql.Result`.

Note: `sql()` is kept pure and only returns the generated SQL string and an error. It is primarily useful for testing and inspection.

func Delete

func Delete[T entity.Entity](where Where) Executor

Delete builds a single-table DELETE query.

Note: per design, callers should provide a non-empty where clause; the implementation enforces this at builder time (deleteSQL returns error if where is empty).

func DeleteJoin

func DeleteJoin[T entity.Entity](joinstmt string, where Where) Executor

DeleteJoin builds a delete executor that uses an EXISTS-correlated subquery to apply the join-based filter. It derives base table from generic type T.

type Schema

type Schema []dvo.Field

type ValueObject

type ValueObject interface {
	internal.ValueObject
	// contains filtered or unexported methods
}

ValueObject is a thin alias over internal.ValueObject to expose it

func NewValueObject

func NewValueObject(m map[string]any) ValueObject

type Where

type Where interface {
	Build() (string, []any)
}

Where is the only contract used to express predicates for CRUD.

The DSL helpers in this package (And/Or/Eq/In/Like/...) produce values that implement `Where`. `Where.Build()` returns a SQL fragment and the parameter list suitable for use in a prepared statement.

func And

func And(wheres ...Where) Where

And combines multiple Where conditions with the AND operator. Nil or empty clauses are ignored.

func Eq

func Eq(field dvo.Field, value any) Where

Eq builds a "field = ?" predicate.

func Gt

func Gt(field dvo.Field, value any) Where

Gt builds a "field > ?" predicate.

func Gte

func Gte(field dvo.Field, value any) Where

Gte builds a "field >= ?" predicate.

func In

func In(field dvo.Field, values ...any) Where

In builds a "field IN (?, ?, ...)" predicate. Empty values produce an always-false clause (1=0).

func Like

func Like(field dvo.Field, value string) Where

Like builds a "field LIKE ?" predicate.

func Lt

func Lt(field dvo.Field, value any) Where

Lt builds a "field < ?" predicate.

func Lte

func Lte(field dvo.Field, value any) Where

Lte builds a "field <= ?" predicate.

func Ne

func Ne(field dvo.Field, value any) Where

Ne builds a "field != ?" predicate.

func Or

func Or(wheres ...Where) Where

Or combines multiple Where conditions with the OR operator. Nil or empty clauses are ignored.