v0.0.16 — Sumak grows up

This release marks sumak's transition from a "solid query builder" to "the SQL layer you can deploy to production and stop second-guessing." 47 PRs, ~50 new features, and a long stretch of focused work. But the PR count isn't the point. The point is this: nearly everything you used to drop down to unsafeRawExpr for now has a typed, dialect-aware, plan-cache-friendly first-class API.

What this release fixed

Up to v0.0.15, sumak had a solid core — SELECT / INSERT / UPDATE / DELETE / MERGE, CTEs, basic window functions, joins. But every real application eventually needed one of: a percentile calculation, a regex match, a Postgres EXCLUDE constraint, an RLS policy, a LISTEN/NOTIFY channel. And sumak forced you back to hand-written unsafeRawExpr("...") for each of them.

This release closes that gap. As of v0.0.16, every part of SQL:2003 / 2011 / 2016 / 2023 that modern Postgres / MySQL / SQLite / MSSQL actually ship has a typed builder behind it. Dialect mismatches surface at compile time as UnsupportedDialectFeatureError — you don't make it to the driver and get a parse error.

Analytics and statistics, natively

You used to abandon the builder and write raw SQL for any dashboard query. Now:

db.selectFrom("requests")
  .select("region")
  .select({
    p50: withinGroup(percentileCont(0.5), [{ expr: typedCol("latency_ms") }]),
    p99: withinGroup(percentileCont(0.99), [{ expr: typedCol("latency_ms") }]),
    jitter: stddev(typedCol("latency_ms")),
    spread: variance(typedCol("latency_ms")),
    cohort: anyValue(typedCol("user_cohort")),
  })
  .groupBy("region")

The full statistical aggregate family is typed. PERCENTILE_CONT / PERCENTILE_DISC ship with a generic withinGroup helper — not just for these two, but for any future ordered-set aggregate. The complete STDDEV / VARIANCE family, the linear-regression aggregates (CORR, COVAR, REGR_SLOPE, REGR_INTERCEPT, REGR_R2). BIT_AND / OR / XOR and BOOL_AND / OR for permission-flag folds. And the window-position helpers — FIRST_VALUE / LAST_VALUE / NTH_VALUE — with the classic frame-default footgun documented inline.

MSSQL uses different names for most of these (STDEV vs STDDEV, LOG vs LN). The MSSQL printer translates them transparently — you write the standard name and the right keyword goes over the wire.

Date / time and regex — the workhorses

EXTRACT(YEAR FROM ts), DATE_TRUNC('month', ts), AGE(end, start) — Postgres's everyday analytics primitives. Now typed:

.select({
  yr: extract("year", typedCol("created_at")),
  bucket: dateTrunc("month", typedCol("created_at")),
  tenure: age(typedCol("hired_at")),
})

dateAdd / dateSub unifies four different dialect grammars behind one API. PG emits expr + INTERVAL '7 days', MySQL emits DATE_ADD(expr, INTERVAL 7 DAY), MSSQL emits DATEADD(day, 7, expr), SQLite emits datetime(expr, '+7 days'). You write dateAdd(col, 7, "day") and the right grammar goes out.

On the regex side, regexpReplace / regexpLike / regexpMatches / regexpSubstr — each gated by a dialect feature flag, with clean refusals on dialects like MSSQL that don't have regex at all.

JSON — SQL:2016 standard functions

The standard JSON functions that Postgres 17 and MySQL 8 both implement — JSON_VALUE, JSON_QUERY, JSON_EXISTS — are now typed. They sit next to PG's operator-based ->, ->>, #>, #>>, but they ship with a RETURNING type cast clause for inline type coercion. IS [NOT] JSON [VALUE | SCALAR | ARRAY | OBJECT] predicate from SQL:2016 is also wired up, for asking "does this text column hold valid JSON?"

The full Postgres array function set ships under the arr.* namespace: append, prepend, cat, length, position(s), remove, replace, to_string, unnest. If you're building tag systems or multi-valued attributes, life gets quieter the moment you stop reaching for unsafeRawExpr.

MERGE statement reaches feature parity

MERGE was in sumak's core but half-finished. This release closes it out:

WHEN NOT MATCHED BY SOURCE (PG 17, MSSQL) — fires for target rows the source doesn't match. Critical for full-sync patterns:

db.mergeInto("target", { ... })
  .whenMatchedThenUpdate({ ... })
  .whenNotMatchedThenInsert({ ... })
  .whenNotMatchedBySourceThenDelete()  // "delete rows that no longer exist in source"

RETURNING on MERGE (PG 17) — the mergeAction() helper tells you which branch fired per row ('INSERT' | 'UPDATE' | 'DELETE'). On MSSQL the same builder slot emits an OUTPUT clause instead, with automatic INSERTED. / DELETED. prefixing and a separate mergeActionMssql() helper for $action. Two different grammars, one builder.

Window functions — final gaps closed

Named WINDOW clause (SQL:2003) — stop repeating the same window spec across multiple OVER calls:

db.selectFrom("sales")
  .window("w", b => b.partitionBy("region").orderBy("date"))
  .select({
    rn: over(rowNumber(), "w"),
    total: over(sum(col.amount), "w"),
  })
// WINDOW "w" AS (PARTITION BY "region" ORDER BY "date")

Frame EXCLUDE (SQL:2011) — EXCLUDE CURRENT ROW | GROUP | TIES | NO OTHERS. Lets a running total skip the current row, or skip its peers, in one keyword instead of two CASE statements.

DDL surface — schema-as-code, fully

Sumak was good for migrations but you still had to drop to raw SQL for CREATE VIEW or CREATE SEQUENCE. This release brings sumak up to the level a real production codebase actually needs:

Views — CREATE VIEW, CREATE OR REPLACE VIEW (PG / MySQL / SQLite), CREATE OR ALTER VIEW (MSSQL), materialized views with REFRESH MATERIALIZED VIEW CONCURRENTLY.
Sequences — CREATE SEQUENCE with the full grammar (OWNED BY, CACHE, MINVALUE, CYCLE …), ALTER SEQUENCE, runtime nextval / currval / setval.
Custom types — CREATE TYPE … AS ENUM, CREATE DOMAIN with CHECK / DEFAULT / NOT NULL, ALTER TYPE … ADD VALUE, ALTER TYPE … RENAME [VALUE].
Row Level Security — CREATE POLICY, ALTER POLICY (both rename and modify forms), DROP POLICY, plus the four AlterTableBuilder toggles: ENABLE / DISABLE / FORCE / NO FORCE row-level security. The backbone of any multi-tenant SaaS on Postgres.
Constraints and indexes — UNIQUE NULLS NOT DISTINCT (PG 15+), partial indexes (CREATE INDEX … WHERE deleted_at IS NULL — the soft-delete classic), and PG EXCLUDE constraints for the booking-system overlap pattern that's nearly impossible to enforce correctly in application code:
```
EXCLUDE USING gist (room WITH =, during WITH &&)
```
Schema documentation — COMMENT ON TABLE, COMMENT ON COLUMN (standalone on PG, inline on MySQL).
Maintenance — VACUUM / ANALYZE / REINDEX (PG, with options), LOCK TABLE with shortcut methods for all eight PG lock modes, TRUNCATE TABLE with PG-flavored grammar, CREATE / DROP EXTENSION.
PG-specific — LISTEN / UNLISTEN / NOTIFY for pubsub, COPY FROM STDIN / COPY TO STDOUT for bulk transfer.

Plugin ecosystem grew

Sumak's hook layer already shipped audit-timestamp, soft-delete, multi-tenant, CASL, etc. This release adds four more:

normalizeStrings — auto-transform column values on INSERT / UPDATE / MERGE (lowercase emails, trim whitespace, empty-to-null, custom function). Lowercase-email consistency stops being a thing you remember to write everywhere.
defaults — fill missing INSERT columns from runtime context. Pass thunks like () => currentUserId() or () => currentTenantId() and every insert picks them up automatically.
validators — per-column predicate checks before the write, throwing ValidationError (with table + column + value attached) for clean error handling upstream.
debugLogger — observability for development: log every compiled and executed SQL with filter, slow-query threshold, and custom sink support.

By the numbers

47 PRs merged (#142 → #189)
Tests: 1689 → 3138 (+1449 new tests, all green in parallel)
Bench scenarios: 7 → 41
src/ LOC added: ~12,000
All four dialect printers (pg / mysql / sqlite / mssql) updated; refusals route through the central feature-flag matrix

Migration

If you're coming from kysely or drizzle, docs/migration-from-kysely-and-drizzle.md ships in this release — side-by-side pattern table, critical differences, and an incremental-port strategy.

Credits

This release was produced over a multi-agent Claude Code session. Every PR carries a Co-Authored-By: Claude Opus 4.7 (1M context) trailer alongside @productdevbook.

Bug fixes and performance

normalize: identity-preserving recurse + restored fixpoint loop — #104
plugin-manager: cache hasTransformNode at construction — #125

All PRs in this release

SQL functions — aggregates

#142 — ANY_VALUE aggregate
#146 — PERCENTILE_CONT / PERCENTILE_DISC + withinGroup
#156 — STDDEV / VARIANCE / CORR / COVAR / REGR_* statistical aggregates
#167 — BIT/BOOL aggregates + FIRST_VALUE / LAST_VALUE / NTH_VALUE window helpers

SQL functions — date / time

#155 — EXTRACT, DATE_TRUNC, AGE typed builders
#159 — dateAdd / dateSub with dialect-aware emission

SQL functions — strings, regex, math, JSON, arrays, sequences

#149 — JSON_VALUE
#150 — JSON_QUERY / JSON_EXISTS
#162 — REGEXP_REPLACE / LIKE / MATCHES / SUBSTR
#164 — REPLACE / POSITION / OVERLAY / LTRIM / RTRIM / REVERSE
#165 — POWER / SQRT / LN / LOG / EXP / SIGN / PI / DEGREES / RADIANS / SIN / COS / TAN
#166 — PG array helpers (array_append / cat / length / etc.)
#163 — CREATE SEQUENCE + nextval / currval / setval

SQL clauses & predicates

#144 — IS JSON predicate
#145 — Named WINDOW clause (SQL:2003)
#147 — WHEN NOT MATCHED BY SOURCE for MERGE
#148 — RETURNING on MERGE + mergeAction()
#151 — Frame EXCLUDE clause (SQL:2011)
#152 — OVERRIDING SYSTEM / USER VALUE
#157 — MSSQL OUTPUT clause for MERGE / INSERT / UPDATE / DELETE

DDL — views, sequences, types, RLS, constraints

#143 — UNIQUE NULLS NOT DISTINCT (PG 15+)
#154 — Partial indexes (CREATE INDEX … WHERE)
#158 — COMMENT ON TABLE / COLUMN
#160 — PG EXCLUDE constraints
#161 — CREATE VIEW / MATERIALIZED VIEW
#168 — TRUNCATE TABLE
#170 — ALTER SEQUENCE
#171 — VACUUM / ANALYZE / REINDEX
#172 — CREATE POLICY / DROP POLICY + RLS toggles
#173 — CREATE / DROP EXTENSION
#175 — LOCK TABLE
#176 — CREATE TYPE AS ENUM / CREATE DOMAIN
#178 — ALTER TYPE ADD VALUE
#179 — ALTER POLICY (rename + modify)
#180 — COPY FROM STDIN / COPY TO STDOUT
#181 — LISTEN / UNLISTEN / NOTIFY
#185 — ALTER TYPE RENAME / RENAME VALUE

Plugins

#169 — normalizeStrings
#177 — defaults
#182 — validators
#183 — debugLogger

Schema typing

#107 — InferSelectModel / InferInsertModel / InferUpdateModel

Bench, tests, and refactor

#153 — Bench scenarios for the new SQL features
#184 — More bench scenarios (regex / extract / date_trunc / stddev / position / array_length / power)
#138 — Split aggregate.ts out of eb.ts
#140 — Extract function tables out of printer/base.ts
#141 — ADR 004 documenting the deferred flat-logical_op AST design
#137 — SQL:2023 coverage notes
#139 — Env-gated MySQL integration suite

Bug fixes & performance

#104 — fix(normalize): identity-preserving recurse + restored fixpoint loop
#125 — perf(plugin-manager): cache hasTransformNode

Docs

#186 — CHANGELOG.md (removed in #189)
#187 — Migration guide from kysely / drizzle
#188 — README "What's New" section (removed in #189)
#189 — Cleanup: remove CHANGELOG.md and README "What's New"

Full diff: v0.0.15 → v0.0.16

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

v0.0.16

Choose a tag to compare

Sorry, something went wrong.

Sorry, something went wrong.

Uh oh!

No results found

v0.0.16 — Sumak grows up

What this release fixed

Analytics and statistics, natively

Date / time and regex — the workhorses

JSON — SQL:2016 standard functions

MERGE statement reaches feature parity

Window functions — final gaps closed

DDL surface — schema-as-code, fully

Plugin ecosystem grew

By the numbers

Migration

Credits

Bug fixes and performance

All PRs in this release

SQL functions — aggregates

SQL functions — date / time

SQL functions — strings, regex, math, JSON, arrays, sequences

SQL clauses & predicates

DDL — views, sequences, types, RLS, constraints

Plugins

Schema typing

Bench, tests, and refactor

Bug fixes & performance

Docs

Contributors

Uh oh!