Generated columns (DB-level computed)

.generatedAs(expression, { stored? }) declares a column whose value the DB engine computes from other columns on the same row. The framework keeps it consistent on INSERT + UPDATE automatically — no app-side stamping needed.

#Quick start

import { text, id, table } from '@voltro/database'

export const people = table('people', {
  id:        id(),
  firstName: text(),
  lastName:  text(),
  fullName:  text().generatedAs(`"firstName" || ' ' || "lastName"`, { stored: true }),
})

// Insert without setting fullName — the DB fills it
await ctx.store.insert('people', { firstName: 'Mario', lastName: 'Lima' })
// Read it back
const rows = await ctx.store.query(database.people.descriptor)
rows[0]?.fullName    // → 'Mario Lima'

A generated column is never caller-supplied. It's omitted from the typed insert payload — insertRow(store, people, { firstName, lastName }) compiles with no fullName, and its type is optional even when the column is NOT nullable and has no default (the DB owns the value). If a loose store.insert passes one anyway, the framework strips it before the INSERT — MariaDB and Postgres both reject an explicit value for a generated column, so this keeps that error from reaching the dialect.

#STORED vs VIRTUAL

{ stored: true } (the rare-but-useful case):

fullName: text().generatedAs(`"firstName" || ' ' || "lastName"`, { stored: true })

• Value is persisted on disk — same storage cost as a regular column.
• Indexable — combine with .expressionIndex('byFullName', ['fullName']) for cheap lookups.
• Required when the column drives full-text search (see Full-text search) or any other indexed access pattern.

{ stored: false } (the default):

fullName: text().generatedAs(`"firstName" || ' ' || "lastName"`)
// stored: false

• Value is recomputed on every read — no disk cost.
• NOT indexable — the DB has nothing physical to index.
• Postgres has no VIRTUAL form; the framework auto-promotes to STORED and logs a console.warn at boot so you know.

Dialect	STORED	VIRTUAL
postgres 12+	GENERATED ALWAYS AS (expr) STORED	not supported → STORED (warns)
mysql 5.7+	GENERATED ALWAYS AS (expr) STORED	GENERATED ALWAYS AS (expr) VIRTUAL
mariadb 10.2+	same as mysql	same as mysql
mssql	AS (expr) PERSISTED	AS (expr) (computed col, not persisted)
sqlite 3.31+	GENERATED ALWAYS AS (expr) STORED	GENERATED ALWAYS AS (expr) VIRTUAL (default)

#Common patterns

#Search-friendly text column

posts: table('posts', {
  id:    id(),
  title: text(),
  body:  text(),
  searchVec: text().generatedAs(
    `to_tsvector('english', "title" || ' ' || "body")`,
    { stored: true },
  ),
}).expressionIndex('posts_search_gin', [{ expr: '"searchVec"' }], { kind: 'gin' })

See Full-text search — the framework's .fullTextIndex() helper builds this exact pattern for you in one chain.

#Numeric derived column

orders: table('orders', {
  id:     id(),
  qty:    integer(),
  price:  integer(),
  total:  integer().generatedAs(`"qty" * "price"`, { stored: true }),
})

Cheap to read (no per-query arithmetic), atomic on update — change qty or price and total updates in the same statement.

#Slug from a title

posts: table('posts', {
  id:    id(),
  title: text(),
  // Postgres: LOWER + replace spaces — pure expression, no functions
  slug:  text().generatedAs(`LOWER(REPLACE("title", ' ', '-'))`, { stored: true }),
})

For complex slug logic (transliteration, deduplication), use .computed(row => ...) instead — it runs in the MutationStore middleware where you have the full JS stdlib.

#Distinct from .computed(row => ...)

The framework has two "derive a column from other columns" hooks. They look similar but solve different problems:

Property	.computed(row => ...)	.generatedAs(expr, ...)
Runs in	App (MutationStore middleware)	DB engine
Can reference subject/tenant context	Yes — the row carries them at stamp time	NO — only same-row columns
Fires on	INSERT only (v1)	INSERT + UPDATE
Indexable	Yes — the column is a regular text()	Only when stored: true
Cross-dialect	Identical on every backend	Per-dialect syntax (framework dispatches)
Value visible to raw SQL?	Yes (after insert)	Yes always

Reach for .computed() when you need subject context, complex JS logic, or the value comes from outside the row.

Reach for .generatedAs() when the value is a pure expression over other columns + you want UPDATE to keep it in sync.

#Suppressed modifiers

The framework suppresses NOT NULL / UNIQUE / DEFAULT on generated columns automatically. The DDL would conflict in some drivers:

// These modifiers are SUPPRESSED on a generated column:
title: text().unique().default('untitled')
  .generatedAs(`LOWER("rawTitle")`, { stored: true })
// Emitted: ... text GENERATED ALWAYS AS (LOWER("rawTitle")) STORED
//                ^^ no NOT NULL, no UNIQUE, no DEFAULT

To enforce uniqueness on a generated column, declare a UNIQUE INDEX on it explicitly via .expressionIndex(name, [...], { ... }).

#When NOT to use

• You need conditional logic (if A then B else C) — most DB-level expressions support this via CASE WHEN, but complex cases land in .computed() more naturally.
• You need context (subject id, tenant id, request metadata) — not visible to a DB-level expression. Use .computed() or .default(() => ...).
• You need it on a different table — generated columns can only reference columns on the SAME row.

#See also

• Columns — .computed(row => ...) and .default(() => ...) for the app-side variants
• Indexes — .expressionIndex() for indexing a generated column
• Full-text search — the FTS pattern uses STORED tsvector generated columns