@ultimat3/db - npm Package Compare versions

Comparing version 12.0.0 to 13.0.0

src/generated-column.ts

+// Single responsibility: what a column the DATABASE computes contributes to DDL, and what changes
+// to one a migration may emit. Split from `generate.ts` because Postgres treats a generated column
+// as a different thing at every step — its clause, its retype, its NOT NULL add and the way its
+// expression moves are each a rule of their own, and none of them is the ordinary column's.
+
+import { assert } from '@ultimat3/core';
+import type { ColumnDescriptionLike } from './entity-shape';
+import type { Plan } from './foreign-key-plan';
+import type { ColumnDescription } from './introspect';
+
+/** How a column that moved was brought back into line — what the caller has to do next, if anything. */
+export type Regeneration = 'unchanged' | 'altered' | 'rebuilt';
+
+const alterColumn = (table: string, column: string): string =>
+  `alter table "${table}" alter column "${column}"`;
+
+/**
+ * The `generated always as (…) stored` clause, or `''` for every ordinary column — so a description
+ * written before this field existed emits the statement it always emitted, byte for byte.
+ *
+ * The two rules Postgres has about the pair are refused HERE, where the entity is still named, for
+ * the reason `createIndex` refuses a unique GIN in the same file: an unguarded generator writes DDL
+ * whose first reader is `ROLE=migrate`, and the server's message carries none of the declaration's
+ * words. A column may not be both DEFAULTED and GENERATED (`42601` — a generated column's value IS
+ * its expression), and an empty expression is not one.
+ */
+export function generatedClause(column: ColumnDescriptionLike): string {
+  const expression = column.generated;
+  if (expression === undefined || expression === null) return '';
+  assert(
+    !column.hasDefault,
+    `column "${column.column}" is declared both generated and defaulted, and Postgres has neither`,
+    `drop the default from "${column.property}" — a generated column's value is its expression, computed on every write`,
+  );
+  assert(
+    expression.trim().length > 0,
+    `column "${column.column}" is generated by an empty expression`,
+    `give "${column.property}" an expression, or drop the generated declaration`,
+  );
+  return ` generated always as (${expression}) stored`;
+}
+
+export const isGenerated = (column: ColumnDescriptionLike): boolean =>
+  typeof column.generated === 'string';
+
+/**
+ * A generated column whose TYPE or whose EXPRESSION moved, brought into line without rebuilding it.
+ *
+ * `set expression as (…)` (Postgres 17) rewrites the table and recomputes every row, and the
+ * column's indexes survive — measured. Drop-and-recreate was the alternative and is worse in two
+ * ways that matter: dropping the column takes its indexes with it and nothing in this diff puts
+ * them back, and `alter table … drop column` is what `destructive.ts` reads as a data loss, so
+ * every expression change would have carried `-- destructive: true` on a migration that loses
+ * nothing. A marker on a migration that destroys nothing is a marker reviewers learn to ignore.
+ *
+ * The retype carries no `using`: Postgres refuses one on a generated column outright ("column … is
+ * a generated column"), which is exactly the statement `retypeColumn` emits for every other column
+ * — and there is nothing to convert, because the expression produces the new type itself.
+ *
+ * Two transitions this cannot express, and both are refused rather than half-emitted:
+ * plain → generated (there is no `set expression` for a column that has none) and a column whose
+ * recorded expression is unknown. `drop expression` is the one that HAS a statement, and it is the
+ * generated → plain direction, which keeps the values it computed.
+ */
+export function regenerate(
+  table: string,
+  column: ColumnDescriptionLike,
+  wantedType: string,
+  recorded: ColumnDescription,
+  plan: Plan,
+): Regeneration {
+  const wanted = column.generated ?? null;
+  const held = recorded.generated ?? null;
+  if (wanted === null && held === null) return 'unchanged';
+  // Generated -> plain: the column keeps every value it computed and simply stops being derived.
+  if (wanted === null) {
+    plan.up.push(`${alterColumn(table, column.column)} drop expression;`);
+    plan.down.push(`${alterColumn(table, column.column)} set expression as (${held ?? ''});`);
+    return 'altered';
+  }
+  // Plain -> generated: `set expression` needs a column that already has one, so this is the whole
+  // column again. Reported as `rebuilt` so the caller can put the indexes back — an `add column`
+  // implies none of them.
+  if (held === null) {
+    plan.up.push(
+      `alter table "${table}" drop column "${column.column}";`,
+      `alter table "${table}" add column "${column.column}" ${wantedType}` +
+        `${generatedClause(column)}${column.notNull ? ' not null' : ''};`,
+    );
+    // Pushed forwards and read backwards — `down` is reversed at assembly.
+    plan.down.push(
+      `alter table "${table}" add column "${column.column}" ${recorded.dataType};` +
+        ' -- was not a generated column',
+      `alter table "${table}" drop column "${column.column}";`,
+    );
+    return 'rebuilt';
+  }
+  let moved = false;
+  if (recorded.dataType !== wantedType) {
+    plan.up.push(`${alterColumn(table, column.column)} type ${wantedType};`);
+    plan.down.push(`${alterColumn(table, column.column)} type ${recorded.dataType};`);
+    moved = true;
+  }
+  if (held !== wanted) {
+    plan.up.push(`${alterColumn(table, column.column)} set expression as (${wanted});`);
+    plan.down.push(`${alterColumn(table, column.column)} set expression as (${held});`);
+    moved = true;
+  }
+  return moved ? 'altered' : 'unchanged';
+}

package.json

 {
   "name": "@ultimat3/db",
-  "version": "12.0.0",
+  "version": "13.0.0",
   "description": "Postgres access, transactions, migrations and drift detection",
@@ -34,3 +34,3 @@ "license": "MIT",
   "dependencies": {
-    "@ultimat3/core": "12.0.0"
+    "@ultimat3/core": "13.0.0"
   },
@@ -37,0 +37,0 @@ "peerDependencies": {

src/entity-shape.ts

@@ -24,2 +24,13 @@ // Single responsibility: the structural mirror of `@ultimat3/entity`'s entity description. `db` is
   readonly onDelete?: string | null | undefined;
+  /**
+   * The `generated always as (<expr>) stored` body, when the DATABASE computes this column rather
+   * than a writer. Absent on every ordinary column, exactly like `IndexDescriptionLike.using`: a
+   * description written before this existed emits the statement it always emitted.
+   *
+   * `@ultimat3/entity` (tier 2) is the declarer and this package cannot import it, so the
+   * expression crosses the seam structurally or it reaches no DDL at all — which is where it was
+   * until `As of 2026-08-24`: the column landed as a plain `tsvector not null` and the first insert
+   * was a `23502`, because nothing computed it.
+   */
+  readonly generated?: string | undefined;
 }
@@ -26,0 +37,0 @@

src/generate.ts

@@ -16,2 +16,4 @@ // Single responsibility: turn an entity snapshot into a timestamped, reversible migration.
 import { type ConstraintPlans, foreignKeyPlan, foreignKeysOf, type Plan } from './foreign-key-plan';
+import type { Regeneration } from './generated-column';
+import { generatedClause, isGenerated, regenerate } from './generated-column';
 import { declaredMethod, indexMethodOf, indexMethodSql } from './index-method';
@@ -58,3 +60,6 @@ import {
 function columnClause(column: ColumnDescriptionLike): string {
-  const parts = [`"${column.column}"`, sqlType(column.kind)];
+  // The generation clause sits directly after the type, and `generatedClause` refuses the pairs
+  // Postgres has no column for. Every other part below is unchanged and unreachable for a
+  // generated column: it may carry no default, and `hasDefault` is what the refusal reads.
+  const parts = [`"${column.column}"`, `${sqlType(column.kind)}${generatedClause(column)}`];
   const expression = defaultExpression(column);
@@ -111,2 +116,5 @@ if (expression !== null) parts.push(`default ${expression}`);
           position: index + 1,
+          // Only when one was declared — absent stays absent, so no snapshot written before this
+          // field existed gains a key and no app's sidecar regenerates over a fact already true.
+          ...(column.generated === undefined ? {} : { generated: column.generated }),
         }));
@@ -203,5 +211,10 @@ return {
   plan: Plan,
-): void {
+): Regeneration {
   const wanted = sqlType(column.kind);
-  if (recorded.dataType === wanted) return;
+  // A generated column moves by its own rules — see `generated-column.ts`. Asked whenever EITHER
+  // side is one, because becoming generated and ceasing to be are both changes with a statement.
+  if (isGenerated(column) || recorded.generated !== undefined) {
+    return regenerate(table, column, wanted, recorded, plan);
+  }
+  if (recorded.dataType === wanted) return 'unchanged';
   const alter = (type: string): string =>
@@ -212,2 +225,3 @@ `alter table "${table}" alter column "${column.column}" type ${type} ` +
   plan.down.push(alter(recorded.dataType));
+  return 'altered';
 }
@@ -266,6 +280,11 @@
   const added = new Set<string>();
+  // A column `regenerate` had to replace outright: `add column` implies no index, so every index
+  // over it has to be stated again even though its own definition never moved.
+  const rebuilt = new Set<string>();
   for (const column of entity.columns) {
     const recorded = existing.get(column.column);
     if (recorded !== undefined) {
-      retypeColumn(entity.table, column, recorded, plan);
+      if (retypeColumn(entity.table, column, recorded, plan) === 'rebuilt') {
+        rebuilt.add(column.column);
+      }
       continue;
@@ -276,3 +295,8 @@ }
     // leave the agent the exact follow-up rather than a migration that fails at 3am.
-    const nullable = column.notNull && defaultExpression(column) === null;
+    //
+    // A GENERATED column is the exception and not a special case of it: the database computes it
+    // for every existing row inside the same `add column`, so it lands NOT NULL and populated in
+    // one statement — measured. Emitting it nullable would leave a `-- backfill` comment naming a
+    // step nobody can perform, since a generated column cannot be written to.
+    const nullable = column.notNull && !isGenerated(column) && defaultExpression(column) === null;
     const clause = nullable ? columnClause({ ...column, notNull: false }) : columnClause(column);
@@ -292,3 +316,5 @@ plan.up.push(`alter table "${entity.table}" add column ${clause};`);
     const recorded = indexed.get(index.name);
-    if (recorded !== undefined) {
+    // A rebuilt column took its indexes down with it, so this one is CREATED rather than compared:
+    // `redefineIndex` sees a definition that never moved and would emit nothing at all.
+    if (recorded !== undefined && !index.columns.some((column) => rebuilt.has(column))) {
       redefineIndex(entity.table, index, recorded, plan);
@@ -295,0 +321,0 @@ continue;

src/introspect.ts

@@ -17,2 +17,11 @@ // Single responsibility: read the live schema out of `information_schema` / `pg_catalog` into a
   readonly position: number;
+  /**
+   * The generation expression, as the SNAPSHOT spells it. Absent for an ordinary column and absent
+   * for every row this module reads out of the live catalog — deliberately: Postgres stores its own
+   * rewriting of the expression (`COALESCE(title, ''::text)` for `coalesce("title", '')`), so a
+   * catalog value could never compare equal to a generated one, and drift would report a correct
+   * database forever. Both sides of the diff that DOES read it — `x db gen`'s — are generated
+   * spellings, which is the same rule `IndexDescription.where` states one field down.
+   */
+  readonly generated?: string | undefined;
 }
@@ -19,0 +28,0 @@

No alert changes

Improved metrics

Total package byte prevSize

404645

3.15%

Number of package files

48

2.13%

Lines of code

5851

2.63%

Dependency changes

• + Added

  @ultimat3/core@13.0.0
  (transitive)

• - Removed

  @ultimat3/core@12.0.0
  (transitive)

• Updated

  @ultimat3/core@13.0.0