# database.columnTypes

> Postgres-native ENUM types via dbEnum() — cheap ADD VALUE migrations, full type-narrowing. Falls back to CHECK constraints on other dialects.



---

<!-- source: en/database/enums.md -->
## Enums (dbEnum)

_Postgres-native ENUM types via dbEnum() — cheap ADD VALUE migrations, full type-narrowing. Falls back to CHECK constraints on other dialects._

`dbEnum('name', [values])` declares a postgres-native ENUM type
that's reusable across columns and tables. The literal value union
narrows the row type at compile time; cheap `ALTER TYPE ... ADD
VALUE` migrations replace CHECK-rewrite pain.

## Quick start

```ts
import { dbEnum, id, table } from '@voltro/database'

export const orderStatus = dbEnum('order_status', ['pending', 'paid', 'shipped'] as const)

export const orders = table('orders', {
  id:     id(),
  status: orderStatus.column().default('pending'),
})

// row.status is typed `'pending' | 'paid' | 'shipped'`, not plain string
```

## Why this exists

Voltro has two ways to enforce a closed value set on a text column:

1. **`text().oneOf(['a', 'b'])`** — emits a CHECK constraint.
   Cross-dialect, works on every backend, but ADD VALUE is a CHECK
   rewrite (expensive on a 10M-row table).
2. **`dbEnum('name', [...])`** — postgres-native ENUM type.
   `ALTER TYPE ... ADD VALUE 'new_value'` is O(1).

Use `dbEnum` when:
- You're on postgres AND
- The value set is reasonably stable but might grow occasionally AND
- You want the value set self-documenting in the schema

Use `oneOf` when:
- You're on multiple dialects (the framework's cross-dialect fallback
  for `dbEnum` is the same CHECK constraint, but `oneOf` keeps the
  source code aligned with reality).
- The value set is highly stable (the cheap ADD VALUE doesn't help).

## Reuse across tables

```ts
export const status = dbEnum('item_status', ['draft', 'published', 'archived'] as const)

export const articles = table('articles', { id: id(), status: status.column() })
export const videos   = table('videos',   { id: id(), state:  status.column() })
```

The `CREATE TYPE` DDL emits exactly once — the framework dedups by
enum name across all schema tables. Defining the same name with
different value sets throws at DDL time.

## Defaults

```ts
status: orderStatus.column().default('pending')
```

Default must be one of the declared values — TypeScript narrows the
`.default(...)` parameter to the literal union.

## ADD VALUE migrations

```ts
// Day 1
export const orderStatus = dbEnum('order_status', ['pending', 'paid', 'shipped'] as const)

// Day 30 — add a value
export const orderStatus = dbEnum('order_status', ['pending', 'paid', 'shipped', 'returned'] as const)
```

`voltro db plan` detects the new value and emits an `add-enum-value`
op:

```sql
ALTER TYPE "order_status" ADD VALUE 'returned';
```

On postgres this is O(1) — no table rewrite. On
mysql/mariadb/mssql/sqlite/turso (CHECK fallback) the planner emits a
CHECK-recreate, which is a brief lock but doesn't rewrite the table.

## Renaming values

A value rename looks identical to a drop+add in a naive value-set
diff — and a drop+add would orphan every row already storing the old
label. So a rename needs explicit INTENT, exactly like a column rename
needs `.renamedFrom('oldName')`. Declare it with `declareEnumRename`,
placed next to the `dbEnum(...)` whose value array now carries the new
label:

```ts
import { dbEnum, declareEnumRename } from '@voltro/database'

// The enum now spells the NEW value.
export const orderStatus = dbEnum('order_status', ['awaiting', 'paid', 'shipped'] as const)

// Record that 'pending' was renamed to 'awaiting'.
declareEnumRename('order_status', { from: 'pending', to: 'awaiting' })
```

The migrator then emits a guarded, data-preserving rename — it fires
only when the OLD label still exists and the NEW one doesn't, so
re-runs are no-ops:

```sql
DO $$
DECLARE type_oid oid;
BEGIN
  SELECT oid INTO type_oid FROM pg_type WHERE typname = 'order_status';
  IF type_oid IS NOT NULL
     AND EXISTS (SELECT 1 FROM pg_enum WHERE enumtypid = type_oid AND enumlabel = 'pending')
     AND NOT EXISTS (SELECT 1 FROM pg_enum WHERE enumtypid = type_oid AND enumlabel = 'awaiting') THEN
    IF current_setting('server_version_num')::int >= 140000 THEN
      EXECUTE format('ALTER TYPE %I RENAME VALUE %L TO %L', 'order_status', 'pending', 'awaiting');
    ELSE
      UPDATE pg_enum SET enumlabel = 'awaiting' WHERE enumtypid = type_oid AND enumlabel = 'pending';
    END IF;
  END IF;
END $$;
```

- **Postgres 14+** uses the native `ALTER TYPE … RENAME VALUE 'old' TO
  'new'` — cheap, in-place, no table rewrite.
- **Postgres 13 and below** falls back to a data-preserving
  `pg_enum.enumlabel` catalog update — every row keeps its value, the
  label is simply re-spelled.
- **Non-postgres dialects** store enums as text + a CHECK constraint,
  so the new value list lands in the recreated CHECK on its own — no
  dedicated rename DDL is needed.

Keep the `declareEnumRename(...)` declaration until the rename has
applied in every environment you care about, then remove it (the new
value is by then the only label the enum knows).

## Cross-dialect

| Dialect          | Emits                                                            |
|------------------|------------------------------------------------------------------|
| postgres         | `CREATE TYPE "name" AS ENUM (...)` + `<col> name` (native)       |
| mysql / mariadb  | `<col> ENUM('a','b','c')` (native enum column type)              |
| mssql / sqlite   | `<col> NVARCHAR(255) / TEXT` + `CHECK (col IN ('a','b','c'))` (fallback) |

The postgres path is the cheap one; the rest treat enums as
text + CHECK behind the scenes.

## When NOT to use

- **You're on mysql or sqlite primarily** — the postgres-only benefit
  doesn't apply. Use `text().oneOf([...])` for consistency.
- **You need value-level metadata** (descriptions, sort orders) —
  enums are just strings. Build a lookup table.
- **The value set changes weekly** — every change is still a schema
  migration. For high-churn lists, store the values in a table.

## See also

- [Columns](/docs/database/columns) — `text().oneOf([...])` for the
  cross-dialect CHECK form
- [Migrations](/docs/database/migrations/operation-classes) — the
  planner's classification for enum changes



---

<!-- source: en/database/generated-columns.md -->
## Generated columns (DB-level computed)

_Columns the DB engine computes from other columns. Distinct from `.computed()` which runs at INSERT in the app._

`.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

```ts
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):

```ts
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](/docs/database/full-text-search))
  or any other indexed access pattern.

`{ stored: false }` (the default):

```ts
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

```ts
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](/docs/database/full-text-search) — the
framework's `.fullTextIndex()` helper builds this exact pattern
for you in one chain.

### Numeric derived column

```ts
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

```ts
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:

```ts
// 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](/docs/database/columns) — `.computed(row => ...)` and
  `.default(() => ...)` for the app-side variants
- [Indexes](/docs/database/indexes#expression) — `.expressionIndex()`
  for indexing a generated column
- [Full-text search](/docs/database/full-text-search) — the FTS
  pattern uses STORED tsvector generated columns



---

<!-- source: en/database/arrays-intervals.md -->
## Arrays + intervals

_Postgres-native array() and interval() columns. App-side codec keeps the API portable across mysql/mariadb/mssql/sqlite/turso._

`array(elementType)` and `interval()` ship native Postgres types
with transparent fallbacks for the other dialects. Your code reads
and writes JS arrays / strings; the framework handles the per-
dialect serialization.

## Arrays

```ts
import { array, text, integer, id, table } from '@voltro/database'

export const posts = table('posts', {
  id:     id(),
  title:  text(),
  tags:   array(text()),                // ReadonlyArray<string>
  scores: array(integer()),             // ReadonlyArray<number>
})

// Insert — pass a regular JS array
await ctx.store.insert('posts', {
  title: 'voltro is great',
  tags:  ['voltro', 'effect', 'pg'],
  scores: [10, 20, 30],
})

// Read — get a regular JS array back
const rows = await ctx.store.query(database.posts.descriptor)
rows[0]?.tags     // → ['voltro', 'effect', 'pg']
```

The element type drives both the row type AND the per-dialect
storage:

| Element        | Postgres column | Fallback dialects   |
|----------------|-----------------|---------------------|
| `array(text())`  | `TEXT[]`        | JSON / NVARCHAR(MAX) / TEXT |
| `array(integer())` | `INTEGER[]`   | JSON / NVARCHAR(MAX) / TEXT |
| `array(boolean())` | `BOOLEAN[]`   | JSON / NVARCHAR(MAX) / TEXT |
| `array(timestamp())` | `TIMESTAMPTZ[]` | JSON / NVARCHAR(MAX) / TEXT |

### App-side codec

On non-postgres dialects, the framework's storeMiddleware
transparently:

- **Write**: `JSON.stringify(array)` before binding to the SQL
  statement. Native JSON serialisation, preserves types correctly.
- **Read**: `JSON.parse(string)` on the way out. Defensive — pre-
  array values (some drivers do this themselves) pass through;
  null / undefined pass through; malformed JSON leaves the raw
  string (logs no error so caller can debug).

Postgres is a no-op — the driver binds arrays natively, no codec
needed.

### Defaults

```ts
tags: array(text()).default([])
```

The literal `[]` becomes `'{}'::text[]` on postgres + `'[]'` (JSON
empty array) on the fallbacks. Use `[]` rather than `'{}'` even
though postgres accepts the latter — the JS-array form keeps the
schema portable.

### Querying

Array containment operators are in the query builder:

```ts
import { arrayHas, arrayContains, arrayOverlaps } from '@voltro/database'

queryFor(database.posts).where(arrayHas('tags', 'effect'))           // 'effect' ∈ tags
queryFor(database.posts).where(arrayContains('tags', ['effect', 'ts'])) // tags ⊇ both
queryFor(database.posts).where(arrayOverlaps('tags', ['effect', 'go'])) // shares ≥1
```

On postgres these compile to the native `= ANY(col)` / `@>` / `&&`
operators. On the other dialects, where arrays are stored as JSON,
they lower to a JSON-containment check (`JSON_CONTAINS` on mysql/mariadb,
`json_each` on sqlite, `OPENJSON` on mssql) — so the same query is
portable. Postgres is fastest here (a GIN index on the array column
accelerates `@>`/`&&`); the JSON paths scan.

For fallback dialects (JSON-shaped storage), index-friendly
querying is even harder — the framework recommends extracting the
array contents to a separate join table if you need filtering by
contents.

## Intervals

Time durations — SLA deadlines, rate-limit windows, "expires-in":

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

export const tickets = table('tickets', {
  id:           id(),
  title:        text(),
  slaDeadline:  interval(),         // postgres native, others fall back
})

// Insert — string form on postgres, ms-number on others
await ctx.store.insert('tickets', {
  title:       'investigate logs',
  slaDeadline: '4 hours',           // postgres-native literal
})
```

| Dialect          | Storage   | Input form                                |
|------------------|-----------|-------------------------------------------|
| postgres         | `INTERVAL` | string (any pg interval literal: '1 day', '45 minutes', etc.) |
| mysql / mariadb  | `BIGINT`  | number of milliseconds                    |
| mssql            | `BIGINT`  | number of milliseconds                    |
| sqlite           | `INTEGER` | number of milliseconds                    |

For portability across dialects you'd need a per-dialect codec; the
framework doesn't ship one in v1. Apps that target multiple
dialects with intervals should store milliseconds-as-BIGINT
explicitly via `integer()` and convert at read time.

### Querying

Postgres lets you compute against intervals natively:

```sql
WHERE "createdAt" + "slaDeadline" < NOW()
```

The query builder doesn't have a typed wrapper for this — use
sub-query helpers or drop to raw SQL.

## When NOT to use

- **You're not on postgres** — the array fallback (JSON-shaped
  storage) is correct but slow for any non-trivial query. If you
  need cross-dialect array support with index-friendly access,
  use a join table (the `manyToMany` mixin pattern).
- **Intervals on mysql/mssql/sqlite** — the BIGINT-ms fallback
  works but loses the postgres-native arithmetic. For cross-
  dialect interval semantics, store milliseconds in `integer()`
  and do the math in JS.
- **Heavy spatial work** — see [PostGIS](/docs/database/postgis)
  instead of trying to roll your own geometry-as-array column.

## See also

- [Columns](/docs/database/columns) — the regular column types you
  pass to `array(...)`
- [PostGIS](/docs/database/postgis) — for location-aware apps,
  the spatial types are better than arrays of coordinates
- [JSON](/docs/database/json) — `json<T>()` for arbitrary nested
  structures (arrays are special-cased; JSON is the general form)



---

<!-- source: en/database/full-text-search.md -->
## Full-text search

_.fullTextIndex() + .matching() for tsvector / FULLTEXT INDEX search across columns. Cross-dialect per-engine where available._

`.fullTextIndex(name, columns, options?)` declares an FTS index on
one or more text columns. `.matching('indexName', 'query')` on the
query builder runs the search.

## Quick start

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

export const posts = table('posts', {
  id:    id(),
  title: text(),
  body:  text(),
}).fullTextIndex('postSearch', ['title', 'body'], {
  config:  'english',
  weights: { title: 'A', body: 'B' },
})

// Query
const hits = await ctx.store.query(
  queryFor(database.posts).matching('postSearch', 'voltro effect').descriptor,
)
```

## What the framework emits

### Postgres — tsvector + GIN

Postgres' canonical pattern:

```sql
ALTER TABLE "posts"
  ADD COLUMN IF NOT EXISTS "postSearch_tsv" tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce("title", '')), 'A') ||
    setweight(to_tsvector('english', coalesce("body",  '')), 'B')
  ) STORED;

CREATE INDEX IF NOT EXISTS "postSearch" ON "posts" USING GIN ("postSearch_tsv");
```

The framework adds the synthetic `<index>_tsv` STORED tsvector
column + a GIN index. `.matching(...)` queries compile to:

```sql
"postSearch_tsv" @@ plainto_tsquery('voltro effect')
```

### MySQL / MariaDB — FULLTEXT INDEX

```sql
CREATE FULLTEXT INDEX `postSearch` ON `posts` (`title`, `body`);
```

`.matching(...)` queries compile to:

```sql
MATCH("title", "body") AGAINST('voltro effect' IN NATURAL LANGUAGE MODE)
```

The `IN NATURAL LANGUAGE MODE` is mysql's default-tier ranking —
relevance-weighted, no Boolean operators. For Boolean mode
(`+voltro +effect`), use the raw SQL escape hatch.

### SQLite — native FTS5

The migrator creates a native FTS5 virtual table named
`<table>_<index>_fts` plus three triggers that keep it in lockstep
with the base table on every INSERT / UPDATE / DELETE:

```sql
CREATE VIRTUAL TABLE IF NOT EXISTS "posts_postSearch_fts"
  USING fts5("title", "body", row_id UNINDEXED, tokenize='porter');
-- + AFTER INSERT / UPDATE / DELETE triggers syncing row_id = posts.id
```

`row_id` carries the base row's string `id` (FTS5's integer rowid
can't hold a TypeID). `.matching(...)` queries compile to:

```sql
"id" IN (SELECT "row_id" FROM "posts_postSearch_fts" WHERE "posts_postSearch_fts" MATCH 'voltro effect')
```

`tokenize='porter'` gives stemming comparable to postgres' `'english'`
config + mysql's natural-language mode.

### MSSQL — ranked LIKE fallback

MSSQL native full-text needs a server-managed `CREATE FULLTEXT
CATALOG` (operations work, not always available). Rather than require
that, the framework emits a **portable ranked-LIKE search** at query
time — no catalog, no `CONTAINS`, works on every MSSQL install. The
migrator emits no FTS DDL (every base column is already present) and
notes the fallback at boot:

```
[voltro:migrate] full-text index 'postSearch' on 'posts':
  mssql uses a ranked LIKE search at query time (no native FTS catalog required).
```

`.matching(...)` compiles to a per-column case-insensitive LIKE
disjunction:

```sql
(LOWER("title") LIKE LOWER('%voltro effect%') ESCAPE '\'
  OR LOWER("body") LIKE LOWER('%voltro effect%') ESCAPE '\')
```

This is substring matching, not tokenised FTS — it won't stem or rank
on term frequency the way postgres/mysql/sqlite do. It's the honest
portable path; for true server FTS on MSSQL set up a catalog + use raw
SQL with `CONTAINS`.

## Configuring per-language

```ts
.fullTextIndex('postSearch', ['title', 'body'], {
  config:  'german',                        // postgres tsvector config
  weights: { title: 'A', body: 'B', tags: 'C' },
})
```

`config` defaults to `'english'`. Postgres ships configs for
~20 languages; install additional ones via `CREATE TEXT SEARCH
CONFIGURATION` if needed. The framework emits the config name
verbatim into the `to_tsvector(...)` call.

`weights` is ignored on non-postgres dialects (no per-column
weight concept in FULLTEXT INDEX).

## Multi-column with per-column weights

```ts
.fullTextIndex('postSearch', ['title', 'body', 'tags'], {
  weights: { title: 'A', body: 'B', tags: 'C' },
})
```

Postgres combines them in the generated tsvector — a query that
matches `title` ranks higher than one that only matches `tags`.

## Relevance ranking — `.rankBy()`

Chain `.rankBy()` after `.matching(...)` to surface a relevance score
column AND order results best-match-first — on every dialect:

```ts
queryFor(database.posts)
  .matching('postSearch', input.query)
  .rankBy()                                  // adds a `rank` column + ORDER BY rank DESC
  .limit(20)
```

Each returned row carries a numeric `rank` (higher = better). The
score expression is per-dialect, but the API is identical:

| Dialect          | Score expression                          |
|------------------|-------------------------------------------|
| postgres         | `ts_rank(<tsvCol>, plainto_tsquery(...))` |
| mysql / mariadb  | `MATCH(cols) AGAINST(...)` score          |
| sqlite           | `-bm25(<fts>)` (negated so higher = better) |
| mssql            | count of columns whose `LOWER LIKE` the query |

Options:

- `.rankBy({ alias: 'score' })` — name the column `score` instead of `rank`.
- `.rankBy({ order: false })` — surface the score column WITHOUT forcing
  `ORDER BY` (combine with your own `.orderBy(...)`).

```ts
// Score column, but sort by recency with relevance as a secondary key:
queryFor(database.posts)
  .matching('postSearch', input.query)
  .rankBy({ order: false })
  .orderBy('createdAt', 'desc')
  .limit(20)
```

On postgres the `ts_rank` reads the same stored tsvector column the
GIN index covers, so ranking stays index-accelerated.

## Querying

`.matching('indexName', 'query')` is composable with other chain
methods:

```ts
queryFor(database.posts)
  .where(eq('orgId', oid))                  // pre-filter
  .matching('postSearch', input.query)       // FTS
  .orderBy('createdAt', 'desc')              // tie-breaker
  .limit(20)
```

Multiple `.matching()` calls override — FTS is a single-string
concept, no AND-merge across queries.

## Limitations

- **MSSQL is a ranked LIKE fallback, not tokenised FTS.** It matches
  substrings (`LOWER(col) LIKE '%query%'`) and ranks by per-column
  hit count — no stemming, no term-frequency weighting. For true
  server FTS on MSSQL, set up a `CREATE FULLTEXT CATALOG` and use raw
  SQL with `CONTAINS`.
- **Boolean / phrase queries on postgres** — `.matching(...)` uses
  `plainto_tsquery` which strips operators. For Boolean
  (`+voltro -effect`) drop to `store.raw` with `to_tsquery(...)`:
  ```ts
  import { sql } from '@voltro/database/sql'
  await ctx.store.raw!<{ id: string }>(sql`
    SELECT id FROM posts
    WHERE "postSearch_tsv" @@ to_tsquery(${input.query})
  `, { dependsOn: ['posts'] })
  ```
  The query string binds as a parameter; `to_tsquery` parses its
  operators (`+`, `-`, `&`, `|`, `<->`).
- **Reactivity** — FTS subscriptions ARE reactive on postgres (the
  tsvector column is a regular column the dispatcher tracks). On
  mysql/mariadb the MATCH AGAINST predicate isn't bucketed into
  the matcher's index — subscriptions re-evaluate on every write
  to the source columns.

## When NOT to use

- **Trigram / fuzzy match** — postgres' `pg_trgm` extension is a
  different access pattern. Install + use raw SQL.
- **Vector search** — see [Vector columns](/docs/database/vectors)
  for embeddings-based semantic search.
- **Substring search on a small table** — `LIKE '%term%'` is fine
  up to ~100k rows. Don't carry the FTS overhead for tiny tables.

## See also

- [Generated columns](/docs/database/generated-columns) — the FTS
  pattern is "generated tsvector + GIN expression index"
- [Indexes](/docs/database/indexes) — `.expressionIndex({ kind: 'gist' })`
  for the GIN/GiST plumbing under FTS
- [Vector columns](/docs/database/vectors) — for embedding-based
  semantic search (different problem from keyword FTS)



---

<!-- source: en/database/postgis.md -->
## PostGIS (geography / geometry)

_Postgres-native geography/geometry columns, geometry constructors, spatial predicates (ST_DWithin, ST_Within, ST_Contains, ST_Intersects, ST_Buffer, bbox), ST_Distance projection + <-> KNN ordering for location-aware apps. Postgres only._

The `@voltro/plugin-postgis` package adds first-class PostGIS columns
and spatial operators to the Voltro schema DSL. **Postgres only.**
PostGIS is a postgres extension; other dialects have no portable
equivalent, so schema emission (`voltro migrate` / auto-migrate)
FAILS LOUD with a `postgres-only` error on non-postgres backends,
and spatial predicates throw in the SQL compiler.

## Install + enable

```bash
pnpm add @voltro/plugin-postgis
```

On postgres, the migrator emits a one-time
`CREATE EXTENSION IF NOT EXISTS postgis;` alongside the first spatial
column's DDL. If your database user lacks the privilege to create
extensions, run that statement once per database as a superuser
before migrating.

## Geography vs geometry

| Type | Coordinate system | Distance calculation | When to use |
|---|---|---|---|
| `geography(...)` | Spheroidal (curves with the earth) | Great-circle | Global apps, WGS-84 lat/lon (SRID 4326). Easy mental model. |
| `geometry(...)` | Planar / projected | Straight-line in the SRID's frame | Local apps with projected coords (UTM zone, state plane). Faster + works with non-spherical analysis. |

If you're unsure: pick `geography` with SRID 4326. It's the
default mental model for "GPS coordinates" and the distance
calculations are correct for any global scale.

## Declare a location column

```ts
import { geography, point } from '@voltro/plugin-postgis'
import { id, text, table, queryFor } from '@voltro/database'

export const venues = table('venues', {
  id:       id(),
  name:     text(),
  location: geography('Point', 4326),   // lon/lat
})
```

Insert a row using the `point()` helper to build the EWKT literal:

```ts
await ctx.store.insert('venues', {
  name:     'Café Mockup',
  location: point(8.682, 50.110, 4326),   // Frankfurt
})
```

The literal serialises as `SRID=4326;POINT(8.682 50.11)`, which
postgres parses into the geography column via implicit cast.

## Query within a radius

```ts
import { ST_DWithin, point } from '@voltro/plugin-postgis'
import { queryFor } from '@voltro/database'

const nearMe = await ctx.store.query(
  queryFor(venues)
    .where(ST_DWithin('location', point(8.68, 50.11), 1000))  // 1 km
    .descriptor,
)
```

`ST_DWithin(column, point, meters)` is the canonical "find rows
within X distance" predicate. PostGIS handles the great-circle math.

## Query inside a polygon

```ts
import { ST_Intersects, polygon } from '@voltro/plugin-postgis'
import { queryFor } from '@voltro/database'

const cityCenter = polygon([
  [[8.65, 50.10], [8.72, 50.10], [8.72, 50.13], [8.65, 50.13], [8.65, 50.10]],
])

const inside = await ctx.store.query(
  queryFor(venues)
    .where(ST_Intersects('location', cityCenter))  // venue location ∈ polygon
    .descriptor,
)
```

Every predicate takes the **column first**, then the WKT geometry:

- `ST_DWithin(column, geom, meters)` — within a metric radius.
- `ST_Within(column, geom)` — the column's geometry is fully inside
  `geom` (the right shape for "point column inside a polygon").
- `ST_Contains(column, geom)` — the column's geometry fully contains
  `geom` (e.g. a region `boundary` column containing a point).
- `ST_Intersects(column, geom)` — any shared point.
- `ST_Buffer(column, geom, meters)` — the column intersects `geom`
  grown by `meters` (a metric buffer; shape-aware "within N metres").
- `bboxOverlaps(column, geom)` — the `&&` bounding-box overlap
  operator: cheap, GiST-index-only, approximate. Build the box with
  `envelope(minLon, minLat, maxLon, maxLat, srid)`.

Spatial predicates work in one-shot `ctx.store.query` reads only;
they throw in the in-memory reactive matcher, so keep them out of
subscriptions.

## Geometry constructors

`point` / `lineString` / `polygon` / `multiPoint` /
`multiLineString` / `multiPolygon` build EWKT literals; `geoJson`
lowers a GeoJSON geometry to the same EWKT (SRID 4326 by the GeoJSON
spec). Coordinates are `[lon, lat]` (x before y) throughout.

```ts
import { point, lineString, polygon, multiPolygon, geoJson } from '@voltro/plugin-postgis'

const route = lineString([[8.68, 50.11], [8.69, 50.12]], 4326)
const zones = multiPolygon([[[[8.6, 50.1], [8.7, 50.1], [8.7, 50.2], [8.6, 50.1]]]], 4326)
const fromGeoJson = geoJson({ type: 'Point', coordinates: [8.682, 50.110] })
```

## Surfacing distance + nearest-neighbour ordering

`withDistance(...)` projects `ST_Distance(column, geom)` as a
selectable column (metric metres by default), and `nearestBy(...)`
adds a `<->` KNN `ORDER BY` for indexed nearest-neighbour search.
Both are `QueryTransform`s applied via the builder's `.use(...)` seam
(the same mechanism as `hybridSearch`), so they compose with
`.where(...)` and `.limit(k)`. Postgres-only.

```ts
import { withDistance, nearestBy, ST_DWithin, point } from '@voltro/plugin-postgis'
import { queryFor } from '@voltro/database'

// Distance-as-a-value in metres.
const withMeters = await ctx.store.query(
  queryFor(venues)
    .use(withDistance('location', point(8.68, 50.11), { as: 'meters' }))
    .where(ST_DWithin('location', point(8.68, 50.11), 5000))
    .descriptor,
)

// The 5 nearest venues via the indexed `<->` operator.
const nearest5 = await ctx.store.query(
  queryFor(venues).use(nearestBy('location', point(8.68, 50.11))).limit(5).descriptor,
)
```

`withDistance(column, geom, { as?, useGeography? })` aliases the
distance column (default `distance`); `nearestBy(column, geom,
{ direction? })` leads any explicit `.orderBy(...)`. `<->` is planar
(postgres has no geography `<->`), so KNN ranks by planar distance —
add `withDistance` when you need a metric value.

## Indexes

GiST is the PostGIS-recommended index access method for
geometry/geography columns. The framework's regular `.index([...])`
uses B-tree, which is useless for spatial queries — declare the
index with `kind: 'gist'` on `.expressionIndex()`:

```ts
export const venues = table('venues', {
  id:       id(),
  name:     text(),
  location: geography('Point', 4326),
}).expressionIndex(
  'venues_loc_gist',
  [{ expr: '"location"' }],
  { kind: 'gist' },
)
```

The framework emits:

```sql
CREATE INDEX IF NOT EXISTS "venues_loc_gist"
  ON "venues" USING GIST (("location"));
```

on postgres. On every other dialect the migrator warns + falls back
to a plain B-tree index (spatial queries will then fall back to
sequential scan — there's no portable equivalent of GiST).

Combine `kind: 'gist'` with `where: …` for a partial GiST index:

```ts
.expressionIndex(
  'active_venues_loc_gist',
  [{ expr: '"location"' }],
  { kind: 'gist', where: `"active" = true` },
)
```

## Cross-dialect

PostGIS is a postgres extension. A schema using `geography()` /
`geometry()` fails loud at schema emission when the active dialect
isn't postgres — no silent TEXT fallback, no graceful degradation.
Apps that need spatial queries on
MySQL/MariaDB/MSSQL/SQLite should either:

- Use the dialect's native spatial type (mysql's `POINT`/`GEOMETRY`,
  mssql's `geography`/`geometry`) via a file-based migration + raw SQL
- Store coords as numeric columns + filter app-side (acceptable up to
  ~100k rows)

Voltro doesn't ship a cross-dialect spatial abstraction — the
behavioural gaps are too large to paper over.

## See also

- [Columns](/docs/database/columns) — the regular schema-DSL types
- [Expression indexes](/docs/database/indexes#expression) — the
  framework's index API (`kind: 'gist' | 'gin'` on postgres)
- [PostGIS docs](https://postgis.net/docs/) — the official manual,
  authoritative for every spatial function the framework re-exports



---

<!-- source: en/database/vectors.md -->
## Vector columns

_Vectors and RAG — embedding columns, HNSW indexing, nearestNeighbours queries, the vectorEmbedding() mixin, across all five dialects._

For RAG and semantic search, vectors live in the same table as the rest of your data, in the same transactions, with the same tenant scoping. No separate vector DB. No two-store consistency model.

Vector storage works on **all five dialects** — the float array is always persisted. The HNSW *index* is emitted on **postgres** (via pgvector); MariaDB exposes native `VEC_DISTANCE_*` operators so its nearest-neighbour queries are correct, and the other dialects compute distance by sequential scan. The rule across the board: **portable result everywhere, index-accelerated on postgres**.

## Prerequisites

On postgres, the `vector` extension provides the `VECTOR(n)` type, the distance operators, and the HNSW access method. The framework emits it automatically — `voltro migrate` prepends `CREATE EXTENSION IF NOT EXISTS vector;` to the migration whenever any table declares a `vector(...)` column. MariaDB / MySQL ship vectors natively (no extension); MSSQL / SQLite store the bytes with no extra step.

## Declaring a vector column

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

const docs = table('docs', {
  id:        id(),
  body:      text(),
  embedding: vector(1536),
})
```

The number is the **dimensionality**, persisted on the column so the migration emitter can size the DDL (`VECTOR(1536)` on postgres / MariaDB / MySQL; `VARBINARY(MAX)` on MSSQL; `BLOB` on SQLite):

| Provider | Model | Dimensions |
|---|---|---|
| OpenAI | `text-embedding-3-small` | 1536 |
| OpenAI | `text-embedding-3-large` | 3072 |
| Anthropic | (via Voyage) `voyage-3` | 1024 |
| Cohere | `embed-english-v3.0` | 1024 |

Pick once. Changing dimensionality means rebuilding the entire column. `vector(0)` and non-integer dimensions throw at declaration time.

## Generating embeddings

```ts
import { embed } from '@voltro/ai'

const queryVec = yield* embed('how do mutations work?')
// ReadonlyArray<number> of length 1536 (or whatever your provider returns)

await ctx.store.update('docs', id, { embedding: queryVec })
```

`embed` is an Effect — it runs on the server only (the provider call lives in `@voltro/ai`, never a browser bundle). For most apps you don't write this code at all — use the [`vectorEmbedding()` mixin](#auto-embedding-via-vectorembedding) instead and the runtime embeds on write for you.

## ANN search

```ts
ctx.store.select('docs')
  .nearestNeighbours('embedding', queryVec, { distance: 'cosine' })
  .limit(5)
  .all()
```

Each result row carries a synthetic `distance` field. Available distance metrics:

| Metric | Operator (pgvector) | When |
|---|---|---|
| `cosine` *(default)* | `<=>` | Normalised text embeddings — most common. |
| `l2` | `<->` | Euclidean. Image embeddings often need this. |
| `inner` | `<#>` | Inner product. Useful for some recommendation models. |

The choice **must match** the index's opclass (see HNSW below), or the index can't accelerate the search.

On MariaDB / MySQL the same query compiles to `VEC_DISTANCE_COSINE(...)` / `VEC_DISTANCE_EUCLIDEAN(...)`. On MSSQL / SQLite there's no portable distance operator — the query still returns rows (sequential scan) but unranked; the migrate-time warning flags that the dialect has no ANN acceleration.

## HNSW index

Without an index, `nearestNeighbours` is a sequential scan — fine for ≤10k rows, painful beyond. Indexes are declared **at the table level** (there is no column-level `.index()` modifier). Add an HNSW index over the vector column:

```ts
const docs = table('docs', {
  id:        id(),
  body:      text(),
  embedding: vector(1536),
}).index('docsEmbeddingHnsw', ['embedding'], { kind: 'hnsw' })
```

With explicit tuning:

```ts
const docs = table('docs', {
  id:        id(),
  body:      text(),
  embedding: vector(1536),
}).index('docsEmbeddingHnsw', ['embedding'], {
  kind: 'hnsw',
  kindOptions: {
    hnsw: {
      m:              24,         // links per node — higher = better recall + more memory (default 16)
      efConstruction: 128,        // index build effort — higher = better quality + slower build (default 64)
      distance:       'cosine',   // selects the opclass; MUST match query distance (default 'cosine')
    },
  },
})
```

The `distance` metric selects the pgvector opclass (`cosine` → `vector_cosine_ops`, `l2` → `vector_l2_ops`, `inner` → `vector_ip_ops`). Set `opclass` directly to override (e.g. `halfvec_cosine_ops` for a half-precision column). Declaring `kind: 'hnsw'` on a non-vector column, on multiple fields, or on an expression throws at declaration time.

The HNSW DDL is postgres-only; on MariaDB / MySQL / MSSQL / SQLite the index is skipped with a migrate-time warning (a btree on a vector is meaningless). MariaDB queries still run correctly via its native `VEC_DISTANCE_*` operators — just without index acceleration.

## Per-query effort

```ts
ctx.store.select('docs')
  .nearestNeighbours('embedding', queryVec)
  .efSearch(80)                              // search effort (default: 40)
  .limit(10)
  .all()
```

Higher `efSearch` = better recall, slower query. Sweet spot is usually 40-100; benchmark on your data. (pgvector `hnsw.ef_search`.)

## Tenant scoping + vectors

If the table has the `tenant()` mixin, vector searches stay scoped — they only consider rows in the caller's tenant. The runtime AND-merges the tenant filter before the ANN order/limit, so cross-tenant rows never enter the candidate set. No cross-tenant leakage via similarity search.

```ts
const docs = table('docs', {
  id:        id(),
  body:      text(),
  embedding: vector(1536),
})
  .with(tenant())
  .index('docsEmbeddingHnsw', ['embedding'], { kind: 'hnsw' })

// Cross-tenant searches IMPOSSIBLE — the runtime injects the tenant filter.
```

## Auto-embedding via `vectorEmbedding()`

Most RAG apps want: write text → embedding gets generated automatically. The mixin adds the vector column, its HNSW index, and the re-embed behaviors in one line:

```ts
import { vectorEmbedding } from '@voltro/database'

const docs = table('docs', {
  id:   id(),
  body: text(),
}).with(vectorEmbedding({
  from:       'body',
  model:      'text-embedding-3-small',
  dimensions: 1536,
}))
```

Behaviour:

- On INSERT, the runtime computes the embedding from `body` + writes it into the `embedding` column (override the column name with `as`).
- On UPDATE of `body`, the embedding is recomputed.
- The mixin also contributes the HNSW index (pass `index: false` to skip it, `distance` to pick the metric).
- A query helper `nearestNeighbours(queryString, k)` embeds the string for you:

  ```ts
  ctx.store.select('docs')
    .nearestNeighbours('how do mutations work?', 5)
    .all()
  ```

The runtime injects `@voltro/ai`'s `embed` into the mixin's write hook — the embedding API calls are billed to your provider account and traced by `@voltro/plugin-audit`.

### Backfilling pre-existing rows

The mixin only embeds rows written *after* it's in place. For rows that already existed (or after switching embedding model), seed them with the CLI:

```bash
voltro embeddings backfill docs --text body --vector embedding [--model text-embedding-3-small] [--batch 50] [--dry-run]
```

It selects rows whose vector column is empty (or, with `--model-field`/`--model`, embedded under a different model), embeds them in batches via `@voltro/ai`, and writes the vectors back. `--dry-run` reports what *would* be embedded without writing.

## Hybrid search

For best results, combine vector similarity with full-text search (FTS):

```ts
import { hybridSearch } from '@voltro/database'

ctx.store.select('docs').use(hybridSearch({
  vector: { col: 'embedding', query: 'how to deploy' },
  fts:    { indexName: 'docsBody', query: 'how to deploy' },
  alpha:  0.7,                                // 0=pure FTS, 1=pure vector
})).limit(10).all()
```

The FTS clause narrows the candidate set (lexical recall); the vector clause ranks those candidates by embedding distance (semantic ordering). `alpha` weights how much the vector ranking dominates when the two rank signals are fused (Reciprocal Rank Fusion). Beats pure vector on most knowledge-base style retrievals.

## Storage cost

A 1536-dim float32 vector is ~6 KB per row. With HNSW overhead it's roughly 9-10 KB.

- 10k docs → ~100 MB
- 1M docs → ~10 GB

For very large corpora, downcast to half-precision — `vector(1536, { precision: 'half' })` emits pgvector's `HALFVEC(n)`, half the bytes, marginally lower recall. (MariaDB / MySQL have no half type — the emitter upcasts to float32 there.)

## When NOT to use postgres / pgvector

- **Billions of vectors.** Postgres + pgvector tops out around 10-50M vectors with reasonable latency. Beyond that, Qdrant / Weaviate / Pinecone.
- **Frequent re-embedding of entire corpus.** A separate vector DB is easier to wipe + rebuild than a column.
- **Multi-tenant where each tenant has their own embedding model.** A vector column pins one dimensionality.

For most B2B SaaS, postgres + pgvector is more than enough + you get transactional + tenant-scoped + same-backup-line semantics for free.