sqletch

sqletch

Statically verified, dynamically composed SQL for Go. (pronounced like "sketch")

sqletch compiles plain SQL with a tiny conditional-template layer into fully typed Go code — like sqlc, but for the queries sqlc can't express: optional filters, optional joins, selectable sort orders. Every SQL fragment that can ever reach your database is verified at compile time; at runtime, generated code only selects and concatenates those pre-verified constants.

One sentence positioning: a query builder's everyday dynamism, authored the sqlc way.

Status: v0.0.1 — first published release. PostgreSQL, MySQL, and SQLite. The feature set is the v1 design freeze: the template language, the generated API, the runtime package, sqletch.yaml, the CLI, and the meanings of the SQLETCHnnn diagnostic codes are all implemented and intended to be final. The promise is not made yet — a v0.x version number carries no compatibility guarantee under semantic versioning, so pin an exact version and read the release notes when you bump. What v1.0.0 will freeze, and what may still change before it, is in compatibility and versioning.

The problem

Every sqlc user eventually reaches the same question: a search screen with optional filters. The usual answers all carry a real cost —

• WHERE (:status IS NULL OR u.status = :status) pessimizes the query plan for every caller, and
• runtime query builders (squirrel, goqu, …) trade away static verification.

sqletch is the third option: conditional SQL that stays statically verified.

What it looks like

-- name: SearchUsers :many
SELECT u.id, u.email, u.status, u.created_at
FROM users AS u

@if-present(organization_id)
JOIN organization_users AS ou
  ON ou.user_id = u.id
 AND ou.organization_id = :organization_id
@endif

WHERE TRUE

@if-present(status)
  AND u.status = :status
@endif

@if-present(email_prefix)
  AND u.email LIKE :email_prefix || '%'
@endif

@choose(sort)
@case(created_at_desc)
ORDER BY u.created_at DESC
@case(email_asc)
ORDER BY u.email ASC, u.id ASC
@default
ORDER BY u.id ASC
@end

LIMIT :limit;

sqletch generate turns that into one typed function:

rows, err := q.SearchUsers(ctx, gen.SearchUsersParams{
    Status: sqletch.Present("active"),       // the zero value omits the predicate
    Sort:   gen.SearchUsersSortCreatedAtDesc,
    Limit:  50,
})

Each combination of present parameters produces a plain static query as far as PostgreSQL is concerned — its own optimal plan, its own prepared statement. No IS NULL OR tricks, no string building.

Every construct, side by side with the Go it generates and the SQL it composes, is documented in the template language reference.

How it works

The template above reaches 2⁴ × 4 = 64 distinct query shapes, but sqletch never enumerates them. The key insight is that SQL's list-shaped clauses are compositional: if each AND conjunct verifies on its own, any subset of the conjunction verifies too. Structural rules confine the constructs to positions where that argument holds, so verifying

1. the maximal rendering (all fragments on), plus
2. each @choose case,

guarantees every reachable shape parses, resolves, and type-checks — at cost linear in template size, not shape count. Types come from PostgreSQL itself (PREPARE/Describe against a dev database), so there is no hand-written inference engine to disagree with your server.

At runtime, generated code concatenates pre-verified constant fragments selected by a shape key. User values travel exclusively through bind parameters — SQL injection is impossible by construction, and a conformance test pins that what was verified is byte-for-byte what gets composed.

The dev database is needed only on cache misses: oracle results and a catalog snapshot are committed to your repository, so CI and warm builds run fully offline.

Compared to the alternatives

Each of these tools is excellent at what it set out to do; sqletch aims at the one cell none of them targets, and holding it is what the design buys you:

• Verification scales with the template, not with the shape count. Adding an optional filter doubles the reachable shapes and adds one conjunct to check.
• Types come from the database itself. Parameter and result types are whatever PREPARE / Describe answered, cached in your repository so CI never opens a connection.
• What was verified is byte-for-byte what runs, pinned by a conformance test over every shape and every bind position — and values travel exclusively through bind parameters, so SQL injection is impossible by construction.
• One row type for every shape. Result columns, names, types, and nullability are identical across all reachable shapes, and nullability never narrows because a guarded join happens to be active — the analysis only claims non-null when it holds in all of them.
• Plans are checked, not just syntax. Each shape is a plain static query with its own optimal plan and its own prepared statement — no IS NULL OR idiom pessimizing every caller — and check --exhaustive prepares and EXPLAINs every one of them.
• Builder-grade composition stays typed. @filter-tree accepts filters composed across layer boundaries, and @order-by multi-key sorts, from a closed vocabulary fixed at compile time.
• The editor sees the same compiler. sqletch lsp reports the same SQLETCHnnn diagnostics as check while you type, without ever opening a database.

sqlc is a great tool, and sqletch deliberately does not replace it — sqletch owes it the whole authoring model and uses the same conventions (-- name: headers, DBTX/Queries/WithTx) so both generators coexist in one codebase and one transaction. Keep your static queries in sqlc; move the conditional ones to sqletch.

Quick start

Requirements: Go 1.27+ (go.mod requires it; GOTOOLCHAIN=auto fetches it). Cold generates need a disposable dev database — Docker, or a DSN you point at via database.dsn. On SQLite there is nothing to install: the oracle is the real engine, in-process.

sqletch resets the schema (drops it, then re-applies) before typing your queries, so the dev database must be disposable. A database sqletch starts itself (leave database.dsn empty for a throwaway container or temp file) resets automatically. If you point database.dsn at your own database, a cold run is refused (SQLETCH204) until you confirm it is disposable with --allow-destructive — the guard is there so cloning and running a project cannot silently wipe a database you care about.

$ cat sqletch.yaml
version: 1
dialect: postgres
server_version: "16"
schema:
  files: [db/schema.sql]
targets:
  - queries: [queries/*.sql]
    output: {package: gen, path: gen}

$ go run github.com/moznion/go-sqletch/cmd/sqletch generate
sqletch: 3 queries ok (oracle cache: 0 hits, 6 misses; offline: no)

$ go run github.com/moznion/go-sqletch/cmd/sqletch check   # warm: no DB needed
sqletch: 3 queries ok (oracle cache: 6 hits, 0 misses; offline: yes)

Commit .sqletch/cache/ — that's what keeps CI offline. The rest of .sqletch/ (explain/, expanded/) is derived output that an offline generate rewrites, so .gitignore it. See examples/ for a complete working project (its generated code and cache are committed; it builds with no database at all).

Other commands:

$ sqletch check --exhaustive   # prepare + EXPLAIN every reachable shape
$ sqletch explain SearchUsers  # guards, cases, types, shape counts
$ sqletch lsp                  # language server over stdio (offline)

The language server reports the same SQLETCHnnn diagnostics as check while you type — scanner and structural rules always, the oracle-backed checks whenever the committed cache covers the query — and provides go-to-definition between :param occurrences and their -- @param annotations. It never touches a database. Point any LSP client at sqletch lsp for .sql template files.

Templates do not have to live in .sql files. A //sqletch:query const inside a Go file compiles identically — same generated code, same cache entries — so a query can sit next to the repository code that uses it:

//sqletch:query
const searchUsersSQL = `
-- name: SearchUsers :many
SELECT u.id, u.email FROM users AS u
WHERE TRUE
@if-present(status)
  AND u.status = :status
@endif
;
`

List the file in a target's queries: and generate as usual. Conditionality still lives in the constructs — the const requirement is what keeps Go control flow out of SQL construction. See the template language.

Guarantees and limits

Verified at compile time, for every reachable shape: syntax, identifier resolution, parameter and result types, constant result shape, and (conservatively) nullability — nullable columns become go-optional Option[T] fields, and the analysis never claims non-null unless it holds in all shapes. Parameters written into nullable columns are Option[T] too (None writes NULL), distinct from the sqletch.Omittable[T] of optional fragments. Planner-only failures (e.g. FOR UPDATE with an optional LEFT JOIN) are rejected statically where known and covered by check --exhaustive otherwise.

Deliberately out of scope: dynamic table/column names, shape-changing projections, and caller-supplied SQL strings. The full boundary — and the reasoning behind every rule — lives in docs/spec.md; the implementation design is under docs/design/. User documentation lives in the manual — getting started, the template language reference, per-dialect guides, and the sqlc migration guide.

What's in the box

• Constructs: @if-present (optional WHERE/HAVING conjuncts, filter-only INNER/LEFT joins, UPDATE SET items for PATCH semantics, paired INSERT column/value items), @when value guards, @choose closed choices (ORDER BY, projections, GROUP BY), @order-by multi-key sorting, @filter-tree typed filters composable across layer boundaries (with a required mode for multi-tenant safety), and @in variable-arity membership.
• Dialects: PostgreSQL (types inferred by the server), MySQL and SQLite (types from -- @param / -- @column annotations). SQLite needs no Docker and no server at all — its oracle is the real engine, in-process. MySQL can run the same way with database.oracle: native: inference over a catalog built from your DDL, proven byte-identical to a real server over a committed corpus, refusing anything it cannot prove (dialects).
• Cross-query policies: a predicate declared once in sqletch.yaml (tenant scoping, say) is woven at compile time into every query touching the designated tables, and enforcement proves no reachable shape touches them unscoped; opting out is an explicit -- @policy-optout annotation. See the policies chapter.
• Authoring: .sql files or //sqletch:query consts in Go files; sqletch fmt for canonical layout; strict static expansion when every SQL text must exist on disk for audit.
• Performance lints (opt-in, lint: true or --lint): warnings for index-defeating predicates, leading-wildcard LIKE, :many without LIMIT, OFFSET paging, and parameter types that convert the column — checked across every reachable shape, suppressible per query with -- @nolint. See the lint chapter.
• Runtime: per-query client-side deadlines (-- @timeout, or a config-wide default), an Explain<Query> sibling for every method that EXPLAINs (and on PostgreSQL/MySQL ANALYZEs, inside a rolled-back transaction) exactly the SQL the method would send, and metrics via runtime.Observer with an OpenTelemetry adapter. See generated code and runtime metrics.
• Tooling: generate, check [--exhaustive], explain [--enumerate|--analyze], fmt, and lsp — plus editor grammars under editors/ (a VS Code extension with a TextMate injection grammar and an LSP client, and a tree-sitter grammar).

Beyond v1.0

Recorded, unscheduled, and none of it changes the verification model:

• Embedded PostgreSQL oracle — cold generate/check with no external database, the way SQLite already works. The spike is done and feasible; shipping waits on upstream libpglite (docs/design/09-embedded-oracle.md).

Development

$ go test ./...                          # unit suites
$ go test -tags devdb ./internal/e2e/    # real-database E2E (Docker)
$ golangci-lint run --build-tags devdb ./...

Every change follows the testing policy: test-first, rejections asserted to their diagnostic code, must-stay tests for every soundness grant, mutation-checked soundness tests, and fuzz targets with independent oracles.

License

Apache-2.0. Attribution notices are in NOTICE.