Generated ColumnsSupported

Generated columns are table columns whose values are computed automatically from an expression. They're computed at write time (stored) or read time (virtual), ensuring data consistency without application-level calculations.

Creating a Generated Column#

Use generatedColumnDdl from @zmdb/sql/schema-objects to generate the DDL. The function accepts a GeneratedColumn definition with the column name, SQL type, and expression.

import { postgres } from '@zmdb/postgres';
import { generatedColumnDdl } from '@zmdb/sql/schema-objects';

const genCol = {
  name: 'full_name',
  type: 'VARCHAR(255)',
  expression: "first_name || ' ' || last_name",
  stored: true,
};

const ddl = generatedColumnDdl(genCol, postgres);
console.log(ddl);

"full_name" VARCHAR(255) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED

Common Use Cases#

Computed Timestamps#

Track elapsed time or derive timestamps from other columns.

const auditLogDef = {
  name: 'duration_ms',
  type: 'INTEGER',
  expression: 'EXTRACT(EPOCH FROM (completed_at - started_at)) * 1000',
  stored: true,
};

"duration_ms" INTEGER GENERATED ALWAYS AS (EXTRACT(EPOCH FROM (completed_at - started_at)) * 1000) STORED

JSON Extraction#

Extract values from JSON columns into dedicated fields for indexing or querying.

const jsonExtractionDef = {
  name: 'user_email',
  type: 'VARCHAR(255)',
  expression: "(payload->>'user')::text",
  stored: true,
};

"user_email" VARCHAR(255) GENERATED ALWAYS AS ((payload->>'user')::text) STORED

Arithmetic Expressions#

Precompute values that are frequently queried but expensive to calculate.

const totalPriceDef = {
  name: 'total_price',
  type: 'NUMERIC(10,2)',
  expression: 'unit_price * quantity',
  stored: true,
};

"total_price" NUMERIC(10,2) GENERATED ALWAYS AS (unit_price * quantity) STORED

Leaving them out of the declaration#

Declare the base columns and stop there. A generated column has no property, which is exactly how it stays out of CreateDTO and UpdateDTO:

import type { Numeric, PrimaryKey, Serial, Sql, Table } from '@zmdb/core/tags';

export interface Order extends Table<'orders'> {
  id: number & Sql<'integer'> & Serial & PrimaryKey;
  unit_price: number & Sql<'numeric'> & Numeric<10, 2>;
  quantity: number & Sql<'integer'>;
  // total_price is generated — it lives in the migration, not here
}

If you need to _read_ it through a typed path, declare a second interface over a view — see Virtual Entities.

Querying Generated Columns#

Generated columns can be selected like regular columns. They're computed automatically, so you don't need to do anything special in your queries.

import { createQueryCompiler, trustedTable } from '@zmdb/sql';
import { postgres } from '@zmdb/postgres';

const compiler = createQueryCompiler(postgres);

const query = compiler.selectFrom(trustedTable('orders')).select(['id', 'unit_price', 'quantity', 'total_price']).compile();

console.log(query.text);

SELECT "id", "unit_price", "quantity", "total_price" FROM "orders"

Dialect Support#

Related#

• Indexes & Constraints — index generated columns for performance
• Schema Declaration — declaring tables with all column types
• Views — virtual tables that can also compute values