Feature: Declarative table partitioning — .hasPartitioning()

What

The model builder can now declare a table as a PostgreSQL partitioned (parent)
table — CREATE TABLE ... PARTITION BY <strategy> (<key>) — for all three
strategies (range / list / hash), with a strongly-typed column selector or
a custom key expression.

class AppDb extends DbContext {
 get events(): DbEntityTable<Event> { return this.table(Event); }
 protected override setupModel(model: DbModelConfig): void {
 model.entity(Event, e => {
 e.property(x => x.id).hasType(integer('id').primaryKey());
 e.property(x => x.bucket).hasType(integer('bucket').primaryKey()); // part of PK (required)
 e.property(x => x.payload).hasType(varchar('payload', 200));
 // Strongly-typed, column-based (single or composite key):
 e.hasPartitioning({ strategy: 'range', columns: x => x.bucket });
 // e.hasPartitioning({ strategy: 'list', columns: x => [x.regionId] });
 // e.hasPartitioning({ strategy: 'hash', columns: x => [x.tenantId, x.id] });
 });
 }
}
// → CREATE TABLE "event" ( ... PRIMARY KEY ("id", "bucket") ) PARTITION BY RANGE ("bucket")

Two overloads — the second is the custom escape hatch for expression keys (or
anything the typed form can't express):

// 1. Strongly-typed column selector
hasPartitioning({ strategy: 'range', columns: e => e.createdAt });
// 2. Custom raw key expression
hasPartitioning({ strategy: 'range', expression: "date_trunc('month', created_at)" });
// → PARTITION BY RANGE (date_trunc('month', created_at))

The strategy is a strongly-typed union ('range' | 'list' | 'hash'), the
columns selector autocompletes the entity's properties (resolved to their
database column names), and a single column may be returned bare (e => e.col)
or in an array (e => [e.col1, e.col2]).

Scope

.hasPartitioning() declares the parent table's PARTITION BY clause. Child
partitions (CREATE TABLE ... PARTITION OF ... FOR VALUES ...) are intentionally
not part of the model — they are normally created/rotated at runtime (by
month, by hash modulus, etc.). Create them with db.query(...) /
db.getClient().query(...) as your retention policy dictates.

Applied at CREATE TABLE time (ensureCreated() and scaffolded migrations).
PostgreSQL cannot ALTER an existing plain table into a partitioned one, so
changing partitioning on a table that already exists is not auto-migrated.

Primary-key safety check

PostgreSQL requires every partition-key column to be part of the table's PRIMARY
KEY / UNIQUE constraints. When that is violated for a column-based key, the
schema manager throws a descriptive error before hitting the database:

Partitioned table "event" cannot be created: its PRIMARY KEY does not include
partition-key column(s) "bucket". PostgreSQL requires every partition-key column
to be part of the PRIMARY KEY / UNIQUE constraints — add "bucket" to the primary key.

(Expression keys can't carry a PK at all, so those tables simply declare no
primary key.)

How it threads through

The PARTITION BY clause is appended after the column-list's closing paren
(not inside it). The migration scaffold additionally emits a single full CREATE
for partitioned tables (all columns at once, instead of its usual PK-only +
ALTER ADD COLUMN split) because partition-key columns must exist at create time.

Files Changed

• src/schema/table-builder.ts — new PartitionStrategy + PartitioningConfig
  types; TableSchema.partitioning; TableBuilder.partitionBy() (cache-
  invalidating) and build() emits it.
• src/entity/entity-base.ts — EntityMetadata.partitioning.
• src/entity/entity-builder.ts — EntityConfigBuilder.hasPartitioning() (two
  overloads: typed columns vs. raw expression) + resolvePartitionColumns()
  (reuses the hasIndex proxy to map properties → column names).
• src/entity/model-config.ts — buildTables() threads metadata.partitioning
  onto the final TableBuilder (including the FK-rebuild fourth pass, which
  previously dropped it).
• src/migration/partition-sql.ts — new: buildPartitionByClause() and
  validatePartitioningPrimaryKey().
• src/migration/db-schema-manager.ts — createTable() appends PARTITION BY
  after the column list and runs the PK validation.
• src/migration/migration-scaffold.ts — buildCreateTableSql() emits a full
  CREATE + PARTITION BY for partitioned tables.
• src/index.ts — export PartitionStrategy, PartitioningConfig.
• tests/schema/partitioning.test.ts — new: 6 tests — buildPartitionByClause
  unit (columns/expression/strategy); and end-to-end against real PostgreSQL:
  RANGE column-based creates a relkind='p' table with PARTITION BY RANGE (bucket) and accepts rows through a child partition; LIST + HASH produce the
  right pg_get_partkeydef; the custom expression overload partitions by
  date_trunc(...); and a PK that omits a partition column is rejected with a
  descriptive error.

Linkgress-ORM v0.4.35

Query timeouts now work inside transactions

v0.4.34 added per-query timeouts (.withTimeout(ms)) and a connection-level
statement_timeout default, but inside db.transaction(...) the per-query
override was silently ignored — the transactional client dropped the execution
options, so only the connection-level default applied. This release closes that gap
and adds a transaction-level timeout.

.withTimeout(ms) is honored inside transactions

await db.transaction(async (trx) => {
 // Now actually cancelled after 5s (previously the override was a no-op here).
 await trx.reports.where(r => eq(r.heavy, true)).withTimeout(5000).toList();
});

Internally the driver issues a statement-scoped SET LOCAL statement_timeout for the
overridden statement and restores the previous value afterwards, so the override
applies to that one statement only. A cancellation still surfaces as QueryTimeoutError.

Transaction-level timeout: db.transaction(fn, options)

A new optional second argument applies a timeout to every statement in the
transaction — including bulk inserts/upserts that don't expose a per-query
.withTimeout():

await db.transaction(
 async (trx) => {
 await trx.things.insertBulk(rows); // covered
 await trx.other.upsert(values, cfg); // covered
 },
 { timeoutMs: 240000 }, // 4 min for this whole unit of work
);

options.expectedExecutionMs (defaults to timeoutMs) sets the slow-query
"expected" threshold for the transaction, so a deliberately long unit of work does
not also trip the global onQueryTakingTooLong threshold.

export interface TransactionOptions {
 /** SET LOCAL statement_timeout (ms) for the whole transaction. 0 = disabled. */
 timeoutMs?: number;
 /** Slow-query expected time (ms) within the transaction. Defaults to timeoutMs. */
 expectedExecutionMs?: number;
}

The transaction timeout is issued once, up-front, as a SET LOCAL statement_timeout,
so it auto-resets at COMMIT/ROLLBACK and never leaks to other pooled connections.

Slow-query detection (onQueryTakingTooLong) already worked inside transactions in
v0.4.34 and is unchanged.

Compatibility

Fully backward compatible. The new transaction(fn, options?) argument is optional;
existing transaction(fn) calls are unaffected. The DatabaseClient.transaction
driver contract widened its internal queryFn to forward QueryExecutionOptions;
PgClient / BunClient continue to ignore per-query timeoutMs (only
PostgresClient honors it).

Feature: Query timeouts — connection-level default + per-query .withTimeout(ms)

What

Queries can now be bounded by a timeout, enforced natively by PostgreSQL via
statement_timeout — the server cancels the statement (error 57014) and frees
its resources, surfaced as a typed QueryTimeoutError.

The design keeps the common path untouched and only wraps the exceptions:

1. Default — set once at client creation (connection-level, zero wrapping).

const client = new PostgresClient({
 host, port, database, user, password,
 statement_timeout: 30000, // ms — applied to EVERY query on this client
});

This is sent as a native PostgreSQL connection parameter, so the server enforces
it on every query with no per-query overhead and no transaction wrapping.

2. Override — per query, for the specific cases that need it.

// Give this one report a longer budget; everything else keeps the default
await db.reports.where(r => eq(r.heavy, true)).withTimeout(120000).toList();
// Tighten just this query
await db.users.where(u => gt(u.age, 18)).withTimeout(5000).select(...).toList();
// Opt a single query out of the default entirely
await db.exports.withTimeout(0).toList();

Only the overridden query is wrapped — in a short transaction that issues
SET LOCAL statement_timeout first:

BEGIN;
SET LOCAL statement_timeout = 5000;
SELECT ... ;
COMMIT;

Because it's SET LOCAL, the override auto-resets at COMMIT and cannot leak
to other queries on the pooled connection. .withTimeout(0) disables the
timeout for that one query (overriding the connection-level default).

On timeout:

import { QueryTimeoutError } from 'linkgress-orm';
try {
 await db.users.withTimeout(5000).toList();
} catch (err) {
 if (err instanceof QueryTimeoutError) {
 // err.timeoutMs, err.sql, err.cause (the driver error, code 57014)
 }
}

.withTimeout(ms) is available on every execution endpoint: the entity API
(db.users), the LINQ builders (where/select/grouped/union/join), and write
paths — db.users.where(...).withTimeout(ms).update(data) / .delete() inherit
the timeout set on the builder before they run.

Why this shape

statement_timeout is native and server-enforced, but scoping it per query on
a pooled connection safely requires SET LOCAL inside a transaction. Rather than
wrap every query in BEGIN/SET LOCAL/COMMIT, the default is configured once
at the connection level (no wrapping at all) and only explicit .withTimeout()
overrides pay for the transaction. So the number of wrapped queries is as small
as possible, and an override can never affect an unrelated query.

Scope

Implemented for the postgres (porsager) driver (PostgresClient).
PgClient (node-postgres) already supports a connection-level statement_timeout
in its PoolConfig; the per-query .withTimeout() override and the
QueryTimeoutError mapping are currently honored by PostgresClient only —
PgClient/BunClient ignore the per-query override for now.

Files Changed

• src/database/database-client.interface.ts — QueryExecutionOptions.timeoutMs
  (per-query override); new exported QueryTimeoutError (carries timeoutMs,
  sql, cause).
• src/database/types.ts — PostgresOptions.statement_timeout (ms, the
  connection-level default).
• src/database/postgres-client.ts — normalizeConfig() moves a top-level
  statement_timeout onto porsager's connection params (native default);
  runStatement() runs the default path unwrapped and the override path inside
  sql.begin() + SET LOCAL statement_timeout, mapping 57014 →
  QueryTimeoutError.
• src/database/index.ts, src/index.ts — export QueryTimeoutError and the
  QueryExecutionOptions type.
• src/entity/db-context.ts — QueryExecutor carries a per-query override
  (withTimeout() clone) and threads timeoutMs to the driver; .withTimeout()
  on TableAccessor and DbEntityTable; withTimeout(ms) declared on the
  IEntityQueryable and EntitySelectQueryBuilder interfaces.
• src/query/query-builder.ts, grouped-query.ts, union-builder.ts,
  join-builder.ts — .withTimeout(ms) on QueryBuilder, SelectQueryBuilder,
  GroupedQueryBuilder, GroupedSelectQueryBuilder, GroupedJoinedQueryBuilder,
  UnionQueryBuilder, and JoinQueryBuilder (QueryExecutor switched from a
  type-only to a value import).
• tests/queries/query-timeout.test.ts — new: 9 integration tests against
  real PostgreSQL via PostgresClient — connection-level default cancels a slow
  query (no wrapping) / fast query completes / tighter .withTimeout overrides /
  .withTimeout(0) disables; per-query override wraps only the timed query and
  does not leak SET LOCAL to the next pooled query; and the same enforced
  end-to-end through the query builder.

Feature: Slow-query callback — onQueryTakingTooLong + .expectedExecutionTime(ms)

What

A diagnostic hook that fires when a query runs longer than expected — to log it,
trace it, or alert — without cancelling it (that's what .withTimeout() is
for). The callback receives the SQL, params, duration, threshold, and the user
call stack that started the query (ORM-internal frames removed).

const db = new AppDatabase(client, {
 // Enable detection by providing the callback.
 onQueryTakingTooLong: ({ sql, durationMs, thresholdMs, stack }) => {
 logger.warn(`Slow query (${durationMs.toFixed(0)}ms > ${thresholdMs}ms)\n${sql}\n${stack}`);
 },
 // Optional: default threshold (ms). Default 10000 (10s).
 longRunningQueryThreshold: 5000,
});
// Per-query override (like .withTimeout) — flag a query you expect to be fast:
await db.users.where(u => gt(u.age, 18)).expectedExecutionTime(500).select(...).toList();

SlowQueryInfo:

interface SlowQueryInfo {
 sql: string;
 params?: any[];
 durationMs: number;
 thresholdMs: number;
 stack: string; // user frames only — top frame is the .toList()/.firstOrDefault()/... call site
}

• Default threshold is 10000ms, overridable on the context
  (longRunningQueryThreshold) or per query (.expectedExecutionTime(ms)).
• Providing onQueryTakingTooLong is what enables detection — when unset
  there is zero overhead (no timing, no stack capture). Callback errors are
  swallowed so a notice can never break the query.
• Available on every execution endpoint: the entity API (db.users), the LINQ
  builders (where/select/grouped/union/join), and the context option.

Stack trace points at your code, not ORM internals

The stack is captured synchronously at query start (inside your call chain, so
your frames are present) but formatted lazily (only paid for when a query is
actually slow). Internal frames are stripped by source-file identity
(basename) — db-context, query-builder, ... — rather than by matching an
install path, so it is robust wherever the package lives (node_modules, a
monorepo, ts-node) and also strips anonymous internal closures. The first frame
of stack is the line that called the terminal method:

at processOrders (/app/src/orders/service.ts:42:18) ← your code
at ...

Change: section-aware logger (breaking)

The logger callback's second argument is now a LogSection — what is
being logged — instead of a severity LogLevel. This lets a custom logger route
or filter by category (e.g. drop 'sql', keep 'error').

type LogSection = 'sql' | 'params' | 'timing' | 'slow' | 'info' | 'warn' | 'error';
new AppDatabase(client, {
 logQueries: true,
 logParameters: true,
 logExecutionTime: true,
 logger: (message, section) => {
 if (section === 'sql') sqlLog.write(message);
 else if (section === 'error') errorLog.write(message);
 // ignore the rest
 },
});

Breaking: the old 'debug' level no longer appears — query text is now
'sql', parameters 'params', execution time/traces 'timing'. LogLevel is
kept as a deprecated alias of LogSection; migration messages still use
'info'/'warn'/'error'. Query logging is disabled granularly as before
(logQueries / logParameters / logExecutionTime), and a consumer can
additionally filter by section. A defaultLogger (routes by section to
console.error/console.warn/console.log) is now exported.

Files Changed (slow-query callback + section-aware logger)

These two were shipped together; the changes share src/entity/db-context.ts.

• src/entity/db-context.ts —
  • LogSection type (+ deprecated LogLevel alias) and exported defaultLogger;
    QueryOptions.logger now (message, section?) => void; QueryExecutor and
    TimeTracer emit semantic sections ('sql'/'params'/'timing'/'error').
  • SlowQueryInfo; QueryOptions.onQueryTakingTooLong +
    longRunningQueryThreshold (default 10000); stack capture/filter helpers
    (captureStackHolder, extractUserStack, basename-based
    LINKGRESS_INTERNAL_FILES).
  • QueryExecutor gains a per-query expected-time override
    (withExpectedExecutionTime() clone) and beginTiming()/finishTiming()
    that log timing and fire the slow-query callback; executor is now also
    created when onQueryTakingTooLong is set.
  • .expectedExecutionTime(ms) on TableAccessor and DbEntityTable (the
    latter refactored to a shared _deriveWithExecutor helper); declared on the
    IEntityQueryable and EntitySelectQueryBuilder interfaces.
• src/query/query-builder.ts, grouped-query.ts, union-builder.ts,
  join-builder.ts — .expectedExecutionTime(ms) alongside .withTimeout(ms)
  on every builder.
• src/index.ts — export LogSection, SlowQueryInfo, defaultLogger.
• tests/queries/slow-query-logging.test.ts — new: 6 tests — callback fires
  past the threshold with a user-facing stack (asserts the top frame is the
  test's .toList() call site ...

Feature: NULLS NOT DISTINCT unique indexes (PostgreSQL 15+)

What

A new opt-in IndexBuilder.nullsNotDistinct() makes a unique index treat NULLs
as equal — so at most one row may hold NULL in the indexed column(s), instead of
PostgreSQL's default where every NULL is distinct and a unique index allows
unlimited NULL rows.

entity.hasIndex('uq_user_external_ref', e => [e.externalRef])
 .isUnique()
 .nullsNotDistinct();
// → CREATE UNIQUE INDEX "uq_user_external_ref" ON ... ("external_ref") NULLS NOT DISTINCT

It composes with the other hasIndex options (partial WHERE, operator classes,
concurrency); the clause is emitted in PostgreSQL's grammar slot — after the
column list and before any WHERE:

CREATE UNIQUE INDEX "uq" ON "t" ("a", "b") NULLS NOT DISTINCT WHERE deleted_at IS NULL

Unique-only — enforced by guard, not by error

NULLS NOT DISTINCT is only valid on a unique index; PostgreSQL rejects it
otherwise. Rather than throw, the SQL builder emits the clause only when the
index is also unique, so a model that sets the flag without .isUnique()
produces byte-identical SQL to the same index without it (a guarded no-op). The
signature normalizer mirrors that guard (nullsNotDistinct normalizes to false
on a non-unique spec), so such a misconfigured index never falsely churns under
auto-migration.

Auto-migration detects DISTINCT ↔ NULLS NOT DISTINCT

Building on the v0.4.31 same-name index-recreate feature, the index signature now
carries nullsNotDistinct and compareIndexDefinition() reports it:

1. Recreate index "uq_user_external_ref" on "users" (changed: nulls not distinct false -> true)

Toggling .nullsNotDistinct() on or off on an existing unique index is detected
and the index is dropped + recreated; an unchanged declaration does not churn.

Fixes a latent parser blind spot

parseDbIndexSignature() parses PostgreSQL's deparsed pg_get_indexdef output. It
previously had no case for the NULLS NOT DISTINCT token PG15+ emits between the
column list and WHERE, so a live NND index failed to parse and collapsed to
null — which the reconciler treats as "unparseable → leave unchanged". The
practical consequence: a NULLS NOT DISTINCT → DISTINCT change was invisible
(no recreate planned). The parser now strips a leading NULLS NOT DISTINCT before
the WHERE check, so live NND indexes parse correctly and the difference is
detected in both directions.

Files Changed

• src/entity/entity-builder.ts — new chainable IndexBuilder.nullsNotDistinct().
• src/entity/entity-base.ts — nullsNotDistinct?: boolean on IndexMetadata.
• src/schema/table-builder.ts — nullsNotDistinct?: boolean on IndexDefinition.
• src/migration/index-sql.ts — IndexSqlSpec.nullsNotDistinct; buildCreateIndexStatement()
  emits NULLS NOT DISTINCT (isUnique-guarded, before WHERE); IndexSignature.nullsNotDistinct
  • modelIndexSignature() mirroring the guard; parseDbIndexSignature() strips a leading
    NULLS NOT DISTINCT (the parse fix); compareIndexDefinition() reports the diff.
• src/migration/db-schema-manager.ts — nullsNotDistinct threaded through the
  create_index/recreate_index migration operations, analyze(), executeCreateIndex(),
  and the migrate/execute paths.
• src/migration/migration-scaffold.ts — renders nullsNotDistinct into scaffolded
  create_index operations.
• tests/migration/index-sql.test.ts — 10 unit tests (SQL emission + ordering before WHERE,
  guarded no-op on a non-unique index, flag-unset UNIQUE unchanged, parse of NND ± WHERE,
  reconcile NND==NND / NND↔DISTINCT in both directions, non-unique spec normalizes to false).
• tests/schema/nulls-not-distinct-index.test.ts — new: 5 end-to-end tests against real
  PostgreSQL (creates a genuine NND unique index; PostgreSQL enforces NULLs-as-equal — a second
  NULL row is rejected; no-churn on an unchanged NND index; DISTINCT → NND detected, recreated,
  and converges; NND → DISTINCT detected via the parse fix).

Fix: exists / notExists over a hasMany collection inside a chained .where()

What

Reaching a hasMany collection from a second, chained .where() — through exists(), notExists(), the .exists() method, or a nested collection chain — threw at query-build time:

TypeError: p.postComments.where is not a function

// Build a base query, then refine it later — a common conditional-query pattern
let q = db.posts.where(p => eq(p.userId, 1));
q = q.where(p => exists(p.postComments!.where(pc => eq(pc.comment, 'Great post!'))));
// ^^^^^^^^^^^^^^^^^^^^ threw: .where is not a function

A single .where() (db.posts.where(p => exists(p.postComments!.where(...)))) was never affected — which is why the existing exists() coverage didn't catch it.

Why it failed before

DbEntityTable.where() appends an implicit .select(allColumnsSelector), so the first .where() returns a SelectQueryBuilder. A chained second .where() therefore runs its selector through createFieldRefProxy, whose recursive-wrap branch wrapped every object value in a get-trap proxy — including the CollectionQueryBuilder that the hasMany getter returns. The wrapping proxy intercepted .where / .exists / .select as if they were column accessors and returned a { __fieldName: 'where', __dbColumnName: 'where' } FieldRef; calling that as a function threw.

(hasOne / reference column refs such as p.user!.username were unaffected: that getter surfaces a plain mock-row, not a builder, so it correctly fell through to recursive wrapping.)

The fix

A pass-through guard in createFieldRefProxy, placed before the recursive nested-object wrap — query-builder instances are returned untouched so their methods stay callable:

if (
 value instanceof CollectionQueryBuilder
 || value instanceof ReferenceQueryBuilder
 || value instanceof QueryBuilder
 || value instanceof SelectQueryBuilder
) {
 return value;
}

This path only ever produced broken FieldRefs for these instances, so nothing that previously worked changes.

Affected shapes (all fixed)

All four went from throwing to executing with correct results. CollectionQueryBuilder was strictly the culprit; the other guard branches are defensive:

• exists(p.coll.where(...)) — function form
• notExists(p.coll.where(...))
• p.coll.where(...).exists() — method form
• nested exists(p.coll.where(c => c.ref.coll.where(...).exists()))

Files Changed

• src/query/query-builder.ts — pass-through guard for builder instances in createFieldRefProxy(), before the recursive nested-object wrap.
• tests/queries/chained-where-exists-collection.test.ts — new: 7 integration tests over the chained-.where() proxy path, each asserting the actual rows returned (correlated exists → matching row only; per-row correlation + first predicate preserved → empty result; chained path ≡ single-.where() path; notExists; .exists() method form; nested collection → reference → collection; and a hasOne/reference column ref guarding the adjacent recursive-wrap path). Verified fail-before / pass-after.

Feature: auto-migration recreates indexes whose definition changed

What

Until now, automatic migration compared indexes by name only: if an index
with the model's name already existed, it was left untouched — so changing how
a column is indexed (operator class, expression, method, uniqueness, columns, or
partial predicate) while keeping the same name was silently ignored.

migrate() now compares the full definition of each same-named index
against what PostgreSQL actually stores and, when they differ, drops and
recreates it. This is on by default and can be turned off:

// default — recreate same-named indexes when their definition changed
await db.getSchemaManager().migrate();
// legacy name-only behavior
await db.getSchemaManager({ recreateChangedIndexes: false }).migrate();

Example — add text_pattern_ops to an existing index, or flip a btree index to
a trigram GIN, under the same name, and the change is now applied:

1. Recreate index "ix_users_email" on "users" (changed: method btree -> gin; columns (...) -> (...))

Non-blocking by default-eligible

The recreate uses DROP INDEX CONCURRENTLY + CREATE INDEX CONCURRENTLY (no
long write lock) when the index opts into concurrency — either per-index via
.concurrent() or globally via getSchemaManager({ concurrentIndexes: true }).
Without that opt-in it is a plain (briefly locking) drop + recreate, and a log
line points this out. CONCURRENTLY must run outside a transaction, which the
auto-migrate path already does.

Why a needless rebuild is impossible (two-tier comparison)

The headline guarantee: an index is recreated only when PostgreSQL itself
confirms the model's definition differs from the stored one. The comparison
runs in two tiers:

Tier 1 — fast string compare. Parses PostgreSQL's canonical
pg_get_indexdef(oid, 0, true) and normalizes it toward the SQL the model would
generate. Designed against real output, it folds the cosmetic differences —
USING btree the model omits, dropped identifier quotes, the public. prefix on
search_normalize, and the ::text cast PostgreSQL injects for ixNormalized
indexes on varchar columns (search_normalize(email::text) vs
search_normalize("email")) — plus the predicate rewrites PostgreSQL applies at
parse time: !=→<>, LIKE/ILIKE→~~/~~*, IN (...)↔= ANY (ARRAY[...]),
BETWEEN→>=...AND...<=, and per-element/array casts. If Tier 1 sees no difference
(the overwhelmingly common case), nothing more happens — read-only and fast.

Tier 2 — authoritative confirmation. If Tier 1 thinks an index changed, it
does not recreate yet. The model's index is rebuilt on a throwaway empty
mirror table (CREATE TEMP TABLE ... (LIKE realTable) — instant regardless of
table size), and PostgreSQL's pg_get_indexdef of that rebuild is compared to the
stored index's. Both strings now come from PostgreSQL's own deparser, so they are
byte-identical for equivalent definitions — no normalization guesswork. Only when
they genuinely differ is a recreate_index emitted.

This dissolves every case text normalization can't reach — bare date/time literals
PostgreSQL expands to a timestamptz with the server offset, sub-expressions it
re-parenthesizes by precedence, an explicitly-specified default operator class
it hides — because the model's rebuild is canonicalized the same way. There
are end-to-end tests proving a timestamp-literal and a re-parenthesized partial
index do not churn.

If Tier 2 can't run — a read-only session, missing search_normalize support
outside migrate(), or no TEMP privilege — it fails closed: the index is left
untouched. The worst case is a missed change, never a churning rebuild.

This whole approach was chosen over storing a definition hash precisely because a
hash can't distinguish "the model changed the index" from "this index wasn't
created by the ORM"; comparing against the live definition treats hand-made and
ORM-made indexes uniformly.

Remaining conservative cases (no churn, possible missed change)

INCLUDE/COLLATE and storage-parameter (WITH (...)) clauses the model can't
express are skipped at Tier 1 (treated as unchanged), so such indexes are never
rebuilt — the trade-off being that a change to one of those isn't auto-detected.

Scaffold

MigrationScaffold now renders recreate_index operations into generated
migration files (UP = drop + recreate, DOWN = restore the previous definition
captured from the database). While fixing this, the scaffold's CREATE INDEX
generation was corrected to include USING <method> and per-column operator
classes, which it previously dropped.

Files Changed

• src/migration/index-sql.ts — new: shared CREATE/DROP INDEX builders
  (used by both the live path and the scaffold so generated SQL is identical),
  the index-definition normalizer, the partial-predicate normalizer
  (normalizeIndexPredicate), the Tier-1 compareIndexDefinition(), and the
  Tier-2 canonical comparison (indexCanonicalSignature, canonicalDefsEquivalent)
• src/migration/db-schema-manager.ts — recreateChangedIndexes option
  (default true); new recreate_index migration operation; getExistingIndexes()
  now returns the canonical def; analyze() collects Tier-1 candidates and only
  emits a recreate after Tier-2 confirmIndexChanges() (empty-mirror-table rebuild
  • canonical compare, fail-closed) confirms the difference;
    executeRecreateIndex() (concurrent-aware drop + recreate);
    executeCreateIndex() refactored onto the shared builder; describeOperation()
• src/migration/migration-scaffold.ts — renders recreate_index up/down; index
  SQL now goes through the shared builder (fixes missing USING/operator class)
• src/entity/db-context.ts — recreateChangedIndexes threaded through both
  getSchemaManager() overloads
• tests/migration/index-sql.test.ts — new: 33 unit tests — pins the
  normalizer to real pg_get_indexdef output (every ORM index shape compares
  equal, real changes detected, unsupported defs conservative) and covers the
  Tier-2 canonicalDefsEquivalent comparison
• tests/migration/index-where.test.ts — new: 44 unit tests — a 29-shape
  partial-predicate matrix that must not churn, 8 genuine predicate changes that
  must be detected, the rewrite rules, and the Tier-1 cases Tier-2 resolves
• tests/schema/recreate-changed-index.test.ts — new: 10 integration tests
  (no-churn on an ixNormalized varchar index; method/operator-class change
  applied + converges; uniqueness change detected; switch disables it; partial
  WHERE add / change / remove + no-churn round-trip; and no-churn on a
  timestamp-literal and a re-parenthesized partial index, proving Tier-2)

Feature: normalized (accent/case-insensitive) text search

What

Built-in accent- and case-insensitive text search, backed by a single indexable PostgreSQL function public.search_normalize(text):

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE OR REPLACE FUNCTION public.search_normalize(value text)
RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE RETURNS NULL ON NULL INPUT
AS $$ SELECT lower(public.unaccent('public.unaccent', value)) $$;

The ORM creates this function (and the unaccent extension, plus pg_trgm when a GIN index needs it) automatically during migration — you don't write the SQL.

Index helper: ixNormalized

Wrap a column in search_normalize() inside a hasIndex selector, composable with the other ix* helpers:

// unique normalized lookup (btree expression index)
entity.hasIndex('user_admin_query', e => [ixNormalized(e.email), e.hash]).isUnique();
// → CREATE UNIQUE INDEX ... (public.search_normalize(email), hash)
// fuzzy substring search (trigram GIN); pg_trgm + gin_trgm_ops added automatically
entity.hasIndex('user_name_search', e => [ixNormalized(e.username, { gin: true })]);
// → CREATE INDEX ... USING gin (public.search_normalize(username) gin_trgm_ops)

• Declaring any ixNormalized index auto-creates the unaccent extension + search_normalize function before the index is built.
• The btree form uses the text_pattern_ops operator class, so it serves both normalizedEq (=) and normalizedStartsWith (LIKE 'prefix%') as index scans — even on non-C-locale databases, where the default opclass would seq-scan prefix queries. Unique semantics are unchanged.
• { gin: true } opts into a trigram GIN index (defaults gin_trgm_ops, installs pg_trgm) for substring search (normalizedLike('%x%')). A UNIQUE GIN index is rejected with a clear error (GIN can't be unique).
• The indexed column is tagged with a transferable hasNormalizedIndex flag (on both the entity PropertyMetadata and the built ColumnConfig) so tooling/queries know a normalized index is available.

Index usage at a glance

Query helpers

All wrap their operands in search_normalize(...) so the database can use the matching index:

import { normalizedEq, normalizedLike, normalizedStartsWith, searchNormalize, containsSearch } from 'linkgress-orm';
db.users.where(u => normalizedEq(u.email, 'José')) // = ignoring accents/case
db.users.where(u => normalizedStartsWith(u.name, 'jo')) // prefix (wildcard appended after normalization)
db.users.where(u => normalizedLike(u.name, containsSearch(q)))// LIKE '%q%' normalized
// low-level building blocks for sql`` templates
db.users.where(u => sql<boolean>`
 ${searchNormalize(u.username)} LIKE ${searchNormalize(containsSearch(query))}
`)

• searchNormalize(fieldOrValue) → public.search_normalize(...) as a SqlFragment (FieldRefs render as columns, literals/placeholders are parameterized).
• containsSearch / startsWithSearch / endsWithSearch build %x% / x% / %x patterns.

Query-only opt-in

When you use the query helpers without a dedicated ixNormalized index, register the support objects so the function exists:

protected setupModel(model: DbModelConfig): void {
 model.useSearchNormalize();
 // ...
}

Note

The unaccent dictionary inside the function is schema-qualified ('public.unaccent') on purpose. PostgreSQL evaluates functional-index expressions with a restricted search_path, so an unqualified 'unaccent' would fail with "text search dictionary unaccent does not exist" when the index is built.

Files Changed

• src/entity/entity-base.ts — ixNormalized() helper; __requiresSearchNormalize/__gin on IndexColumnRef; requiresSearchNormalize on IndexMetadata; hasNormalizedIndex on PropertyMetadata
• src/entity/entity-builder.ts — hasIndex() picks up the normalized/gin flags, sets using='gin'/operatorClass='gin_trgm_ops', and marks the column (new markColumnNormalized helper)
• src/schema/table-builder.ts — requiresSearchNormalize on IndexDefinition
• src/schema/column-builder.ts — hasNormalizedIndex on ColumnConfig + internal markNormalizedIndex()
• src/entity/model-config.ts — useSearchNormalize() opt-in + isSearchNormalizeRequired()
• src/entity/db-context.ts — threads searchNormalizeRequired into the getSchemaManager() override
• src/migration/db-schema-manager.ts — searchNormalizeRequired option; ensureSearchNormalizeSupport() (creates unaccent + search_normalize, plus pg_trgm for GIN) before tables/indexes in ensureCreated() and migrate(); UNIQUE-GIN guard in executeCreateIndex()
• src/query/conditions.ts — searchNormalize, containsSearch, startsWithSearch, endsWithSearch, normalizedEq, normalizedLike, normalizedStartsWith
• src/index.ts — exports ixNormalized and the new query helpers
• tests/schema/normalized-index.test.ts — 5 tests (btree auto-create, GIN + pg_trgm, UNIQUE-GIN rejection, column flag, query-only opt-in)
• tests/queries/normalized-search.test.ts — 5 tests (eq/startsWith/like across accents and case, low-level `sql`` form)

Feature: normalized (accent/case-insensitive) text search

What

Built-in accent- and case-insensitive text search, backed by a single indexable PostgreSQL function public.search_normalize(text):

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE OR REPLACE FUNCTION public.search_normalize(value text)
RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE RETURNS NULL ON NULL INPUT
AS $$ SELECT lower(public.unaccent('public.unaccent', value)) $$;

The ORM creates this function (and the unaccent extension, plus pg_trgm when a GIN index needs it) automatically during migration — you don't write the SQL.

Index helper: ixNormalized

Wrap a column in search_normalize() inside a hasIndex selector, composable with the other ix* helpers:

// unique normalized lookup (btree expression index)
entity.hasIndex('user_admin_query', e => [ixNormalized(e.email), e.hash]).isUnique();
// → CREATE UNIQUE INDEX ... (public.search_normalize(email), hash)
// fuzzy substring search (trigram GIN); pg_trgm + gin_trgm_ops added automatically
entity.hasIndex('user_name_search', e => [ixNormalized(e.username, { gin: true })]);
// → CREATE INDEX ... USING gin (public.search_normalize(username) gin_trgm_ops)

• Declaring any ixNormalized index auto-creates the unaccent extension + search_normalize function before the index is built.
• { gin: true } opts into a trigram GIN index (defaults gin_trgm_ops, installs pg_trgm). A UNIQUE GIN index is rejected with a clear error (GIN can't be unique).
• The indexed column is tagged with a transferable hasNormalizedIndex flag (on both the entity PropertyMetadata and the built ColumnConfig) so tooling/queries know a normalized index is available.

Query helpers

All wrap their operands in search_normalize(...) so the database can use the matching index:

import { normalizedEq, normalizedLike, normalizedStartsWith, searchNormalize, containsSearch } from 'linkgress-orm';
db.users.where(u => normalizedEq(u.email, 'José')) // = ignoring accents/case
db.users.where(u => normalizedStartsWith(u.name, 'jo')) // prefix (wildcard appended after normalization)
db.users.where(u => normalizedLike(u.name, containsSearch(q)))// LIKE '%q%' normalized
// low-level building blocks for sql`` templates
db.users.where(u => sql<boolean>`
 ${searchNormalize(u.username)} LIKE ${searchNormalize(containsSearch(query))}
`)

• searchNormalize(fieldOrValue) → public.search_normalize(...) as a SqlFragment (FieldRefs render as columns, literals/placeholders are parameterized).
• containsSearch / startsWithSearch / endsWithSearch build %x% / x% / %x patterns.

Query-only opt-in

When you use the query helpers without a dedicated ixNormalized index, register the support objects so the function exists:

protected setupModel(model: DbModelConfig): void {
 model.useSearchNormalize();
 // ...
}

Note

The unaccent dictionary inside the function is schema-qualified ('public.unaccent') on purpose. PostgreSQL evaluates functional-index expressions with a restricted search_path, so an unqualified 'unaccent' would fail with "text search dictionary unaccent does not exist" when the index is built.

Files Changed

• src/entity/entity-base.ts — ixNormalized() helper; __requiresSearchNormalize/__gin on IndexColumnRef; requiresSearchNormalize on IndexMetadata; hasNormalizedIndex on PropertyMetadata
• src/entity/entity-builder.ts — hasIndex() picks up the normalized/gin flags, sets using='gin'/operatorClass='gin_trgm_ops', and marks the column (new markColumnNormalized helper)
• src/schema/table-builder.ts — requiresSearchNormalize on IndexDefinition
• src/schema/column-builder.ts — hasNormalizedIndex on ColumnConfig + internal markNormalizedIndex()
• src/entity/model-config.ts — useSearchNormalize() opt-in + isSearchNormalizeRequired()
• src/entity/db-context.ts — threads searchNormalizeRequired into the getSchemaManager() override
• src/migration/db-schema-manager.ts — searchNormalizeRequired option; ensureSearchNormalizeSupport() (creates unaccent + search_normalize, plus pg_trgm for GIN) before tables/indexes in ensureCreated() and migrate(); UNIQUE-GIN guard in executeCreateIndex()
• src/query/conditions.ts — searchNormalize, containsSearch, startsWithSearch, endsWithSearch, normalizedEq, normalizedLike, normalizedStartsWith
• src/index.ts — exports ixNormalized and the new query helpers
• tests/schema/normalized-index.test.ts — 5 tests (btree auto-create, GIN + pg_trgm, UNIQUE-GIN rejection, column flag, query-only opt-in)
• tests/queries/normalized-search.test.ts — 5 tests (eq/startsWith/like across accents and case, low-level `sql`` form)

Feature: onMigrationStart pre-migration hook

What

Adds a pre-migration hook — the symmetric counterpart to the existing onMigrationComplete post-migration hook. Override onMigrationStart(client) on your DbContext to run custom SQL before the ORM analyzes or changes the schema, and before any file-based migration is applied.

This solves "run this script as the very first thing, before any other migration applies" — which previously had no clean answer. Naming a migration file to sort first does not work on a fresh database, because the runner builds the schema via auto-migration and records all migration files as applied without executing them.

Usage

export class AppDatabase extends DbContext {
 protected override setupModel(model: DbModelConfig): void {
 // ... entity configuration
 }
 // Runs first — before tables are created/altered, before file migrations
 protected override async onMigrationStart(client: DatabaseClient): Promise<void> {
 await client.query(`CREATE EXTENSION IF NOT EXISTS "pg_trgm"`);
 }
}

When it fires

• At the start of getSchemaManager().migrate() (auto-migration) — even when the schema is already in sync (unlike onMigrationComplete, which is skipped when there are no operations).
• At the start of getSchemaManager().ensureCreated().
• Via MigrationRunner.up():
  • Fresh database — the runner triggers auto-migration, whose migrate() fires the hook first.
  • Existing database — the runner fires the hook explicitly before applying any pending migration files.

Notes

• Make the script idempotent (IF NOT EXISTS, CREATE OR REPLACE) — it may run on every migration.
• Runs outside the per-file migration transaction, so non-transactional statements (e.g. CREATE INDEX CONCURRENTLY) are allowed.
• Errors thrown abort the migration before any schema change is made.
• The default implementation is a no-op; existing contexts are unaffected.

Files Changed

• src/migration/db-schema-manager.ts — new preMigrationHook constructor option and field; new public runPreMigrationHook() method; invoked at the start of migrate() (before analyze()) and ensureCreated()
• src/migration/migration-runner.ts — up() fires the pre-migration hook on the existing-database path (the fresh-database path already runs it via migrate())
• src/entity/db-context.ts — new onMigrationStart(client) protected hook on DatabaseContext (default no-op); wired as preMigrationHook in the getSchemaManager() override
• docs/guides/migrations.md — new "Pre-Migration Hooks" section
• tests/migration/migration-hooks.test.ts — 2 new tests: ordering (start before schema creation and before complete) and firing on migrate() when the schema is already in sync

Feat: nested-object and collection projections inside UNION legs

What

A UNION / UNION ALL leg can now project the same rich shapes a stand-alone query can — nested objects and .toList() / .sum() / .count() etc. collection projections — and the result rows are reconstructed correctly post-fetch.

// Nested object inside each leg
const active = db.users
 .where(u => eq(u.isActive, true))
 .select(u => ({ id: u.id, info: { city: u.email, tag: sql`'active'` } }));
const older = db.users
 .where(u => gt(u.age, 30))
 .select(u => ({ id: u.id, info: { city: u.email, tag: sql`'older'` } }));
const rows = await active.unionAll(older).toList();
// rows[i].info.city / rows[i].info.tag now populated as a real object.

// Collection projection inside each leg
const left = db.users.where(u => eq(u.isActive, true))
 .select(u => ({ id: u.id, posts: u.posts!.select(p => ({ title: p.title })).toList() }));
const right = db.users.where(u => gt(u.age, 30))
 .select(u => ({ id: u.id, posts: u.posts!.select(p => ({ title: p.title })).toList() }));
const rows = await left.unionAll(right).toList();
// Each row has `posts` as a populated array (LATERAL correlated subquery per leg).

Why it failed before

SelectQueryBuilder.buildUnionSql delegated to buildQueryCore, which only handled the simple flat-FieldRef and SqlFragment cases. Two important shapes were silently broken:

• Nested objects ({ address: { street, city } }) fell through to the else { selectParts.push($N as "${key}") } branch and were bound as a single parameter. The driver JSON-stringified the JS object, which arrived as an opaque string in the result column (or sometimes threw Buffer.byteLength).
• Collection projections ({ posts: u.posts.toList() }) hit an explicit continue with a comment "not supported in UNION queries for simplicity" — the column was silently dropped. If only one leg had the collection, the leg-column-count mismatch surfaced as a Postgres error; if every leg dropped the same column, the rows came back without the field.

Even if those columns had been emitted, UnionQueryBuilder.toList() had no post-fetch step to drive reconstructNestedObjects (for the __nested__<path> flat columns the regular query path produces) or transformResults (for collection mappers and FieldRef mappers). The reconstruction simply did not run for UNION results.

How the fix routes it

Two parity branches added to buildQueryCore so a UNION leg builds the same way a stand-alone query does:

• Collection field detected → route through buildCTE(), which produces a selectExpression (e.g. COALESCE("lateral_0".data, '[]'::json) as "posts") and a joinClause (LATERAL subquery or CTE). The expression is pushed to SELECT; the join is appended to the leg's FROM clause. Same flow as buildQuery line ~4266.
• Nested object detected → call existing tryBuildFlatNestedSelect to emit __nested__<path>__<leaf> flat columns (deterministic ordering so all legs project the same column list), or tryBuildFlatNestedSelectExcludingCollections when the nested object itself contains a collection.

The fallback collection-aggregation slot-emission block (COALESCE("cte".data, ...) for COUNT / EXISTS / array-agg / json-agg variants) was copied from buildQuery so the LATERAL-vs-CTE strategy choice and the scalar-vs-array distinction both work identically inside UNION legs.

Post-fetch reconstruction is plumbed through a new builder contract. Three new internal pieces on SelectQueryBuilder:

• _lastUnionMetadata — the leg stashes { nestedPaths, selectionResult } here at the end of each buildUnionSql call.
• _consumeUnionMetadata() — UnionQueryBuilder reads (and clears) it.
• _applyUnionPostProcessing(rows, meta) — applies reconstructNestedObjects (for the flat __nested__<path> columns) followed by transformResults (for collection mappers, FieldRef mappers, scalar mappers).

The new exported UnionLegBuilder interface formalizes the contract: buildUnionSql is required; _consumeUnionMetadata / _applyUnionPostProcessing are optional so non-SelectQueryBuilder legs (rare, e.g. test fixtures) still work without them. UnionComponent carries an ownerBuilder reference so toList can call back to the leg that produced the SQL.

Only the first leg's metadata is used for post-processing. This is correct because Postgres's UNION enforces column-shape equality across legs — otherwise the SQL itself errors before reaching the mapper. The result mapper only cares about column names, types, and __nested__<path> encoding, all of which are identical across legs by definition. Non-first legs still have their metadata drained to avoid stale state on the builder.

Files Changed

• src/query/query-builder.ts
  • buildUnionSql captures nestedPaths from buildQueryCore and stashes { nestedPaths, selectionResult } on _lastUnionMetadata.
  • New _consumeUnionMetadata() and _applyUnionPostProcessing() on SelectQueryBuilder formalize the contract with UnionQueryBuilder.toList().
  • buildQueryCore collection branch: route through buildCTE() (replacing the previous continue), emit the selectExpression to SELECT, queue the joinClause.
  • buildQueryCore nested-object branch: call tryBuildFlatNestedSelect and tryBuildFlatNestedSelectExcludingCollections so nested projections flatten the same way as buildQuery.
  • buildQueryCore post-loop: emit the collection-slot expressions (COUNT / EXISTS / array-agg / json-agg variants), append the LATERAL / CTE join clauses to FROM. Mirrors buildQuery line ~4498 and ~4678.
• src/query/union-builder.ts
  • New exported UnionLegBuilder interface (the contract a union leg must satisfy).
  • UnionComponent gains an ownerBuilder reference.
  • constructor, union(), unionAll() updated to accept UnionLegBuilder and stash the owner reference.
  • toList() drains metadata from every leg (first-leg's is canonical), then runs _applyUnionPostProcessing on the result rows when present.
• tests/queries/union-nested-select.test.ts — new: 3 tests for nested-object projections in UNION legs.
• tests/queries/union-collection-nav.test.ts — new: 2 tests for .toList() collection projections in UNION legs.
• tests/queries/union-nav-thru-relation.test.ts — new: 2 tests for navigation through an intermediary table followed by a target-side collection projection inside each leg.
• tests/queries/union-comprehensive.test.ts — new: 27 broad-coverage tests (single/multi-leg UNION and UNION ALL combined with WHERE / ORDER BY / LIMIT / nested objects / collections / SqlFragments / FieldRef mappers).