Column types

Every column constructor — id, text, integer, boolean, timestamp, json, vector, reference — with their SQL types, defaults, and modifiers.

A column declaration is a single function call returning a ColumnBuilder. You can chain modifiers (.nullable(), .unique(), .default(...), .computed(...), .onUpdate(...)). Indexes are declared at the table level — there is no column-level .index().

import { text } from '@voltro/database'

text()                       // text NOT NULL
text().nullable()            // text NULL
text().unique()              // text NOT NULL UNIQUE
text().default('draft')      // text NOT NULL DEFAULT 'draft'

The complete column constructor list

`id()`

id()                         // PRIMARY KEY — value generated by the runtime

TypeID by default — <prefix>_<26-char-ULID> (Stripe-style), with the prefix auto-derived from the table name. Sortable by creation time, URL-safe, no leaking sequential counts, and branded to its table type at compile time. You don't pass it explicitly on insert; the runtime generates it.

To override the scheme:

import { id } from '@voltro/database'
id()                         // TypeID — `<prefix>_<26-char-ULID>` (default)
id({ prefix: 'org' })        // TypeID with an explicit prefix
id({ scheme: 'ulid' })       // bare ULID — no prefix (don't leak the table type)
id({ scheme: 'numeric' })    // DB-managed BIGSERIAL / AUTO_INCREMENT / IDENTITY
id({ scheme: 'snowflake' })  // 64-bit Snowflake id

Composite primary key

For a natural composite key — a join table keyed on both its FKs, a time-series table keyed on (deviceId, ts) — declare it at the table level with .primaryKey([...]). It replaces the single-column id() PK: the listed columns become the table's key, emitted as PRIMARY KEY (a, b) on every dialect.

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

table('memberships', {
  userId: reference(() => users),
  orgId:  reference(() => orgs),
  role:   text(),
}).primaryKey(['userId', 'orgId'])
// → PRIMARY KEY ("userId", "orgId")

The member columns are type-checked against the row, and the key implies NOT NULL on each. Don't also declare an id() column — the composite key is the key; a stray id() would emit a second primary key and the DDL is rejected (the DSL catches this at declaration time). Two or more columns are required — a single-column key is just id().

`text()`

text()                       // unbounded text NOT NULL (TEXT / LONGTEXT)
text().nullable()            // text NULL
text().oneOf(['a', 'b', 'c'])  // narrows to 'a' | 'b' | 'c' + CHECK (col IN (...))
text().maxLength(191)        // VARCHAR(191) (NVARCHAR on mssql) — bounded + index-able

Plain text() is unbounded — TEXT on Postgres/SQLite, LONGTEXT on MySQL/MariaDB (full TEXT there silently truncates past 64 KB). That's the right default for user prose (article bodies, JSON-as-string blobs).

Reach for .maxLength(n) when the column is a short identifier (a kind, slug, external id, resource discriminator) that participates in a .unique([...]) or composite .index([...]): an unbounded column can only back a HASH long-unique constraint (MariaDB) or a prefix index, whereas a bounded VARCHAR(n) takes a plain BTREE key. It's cross-dialect (VARCHAR(n) on Postgres/MySQL/MariaDB, NVARCHAR(n) on MSSQL, TEXT on SQLite) — unlike raw('varchar(20)'), which hardcodes one dialect's DDL.

For value constraints (length ranges, regex, email, numeric min/max, cross-field invariants), use table-level table().validate(Schema) (see below) — that's the single validation surface, not a pile of per-column modifiers. There are no .minLength() / .pattern() column modifiers by design.

`integer()`

integer()                    // integer NOT NULL
integer().default(0)

32-bit integer. For values past the 32-bit range use bigint(); for money use decimal(p, s) — never a float. Range constraints (>= 0, between) go in table().validate(Schema), not on the column.

`decimal(precision, scale)` / `numeric(precision, scale)`

Fixed-point exact decimal — money, tax rates, anything where floating-point rounding is unacceptable. numeric is an exact alias of decimal (same DDL; Postgres treats NUMERIC and DECIMAL as the same type).

import { decimal, numeric } from '@voltro/database'

decimal(12, 2)               // up to 9_999_999_999.99
numeric(5, 4)                // 0.0000 .. 9.9999  (alias for decimal)
decimal(38, 9).nullable()

Emits NUMERIC(p, s) on Postgres and DECIMAL(p, s) on MySQL / MariaDB / MSSQL; SQLite / Turso have no fixed-point type, so it's NUMERIC affinity storing the value as text (still exact). precision is the total number of significant digits; scale the digits after the point (0 <= scale <= precision, default 0) — both validated at declaration time.

The row type is string, not number — on purpose. A JS number is an IEEE-754 double and silently loses precision past ~15-17 significant digits (0.1 + 0.2 !== 0.3), so a big DECIMAL round-tripped through a JS number would corrupt. The value carries as a string end-to-end. Do arithmetic with a decimal library (or in SQL), never JS +.

MSSQL ceiling: the tedious driver parses a DECIMAL into a JS number on read, so a decimal whose full value exceeds ~15-17 significant digits loses low-order precision on read (the database stores it exactly; Postgres / MySQL / MariaDB / SQLite are all lossless). On MSSQL, project a large decimal via a CAST(col AS VARCHAR) raw fragment, or keep the value inside the JS-double range. bigint() is unaffected on every dialect.

`bigint()`

64-bit integer — Snowflake ids, byte counters, large sequence numbers, anything past the 32-bit integer() range or the JS-safe-integer ceiling (2^53 − 1).

import { bigint } from '@voltro/database'

bigint()                     // BIGINT (INTEGER on sqlite/turso — both 64-bit)
bigint().default('0')

The row type is string (not number) so a value larger than Number.MAX_SAFE_INTEGER survives intact. Parse with BigInt(value) when you need to compute on it.

`boolean()`

boolean()                    // boolean NOT NULL
boolean().default(false)

`timestamp()`

timestamp()                  // timestamptz NOT NULL — always UTC
timestamp().default('now')   // server-side now()
timestamp().onUpdate('now')  // auto-bump on every write

Always timestamptz (timestamp with time zone). Plain timestamp (without tz) is a footgun across deployments in different zones.

`date()`

date()                       // date — no time component

For calendar dates (birthdays, anniversaries) where wall-clock semantics matter regardless of viewer's timezone.

`interval()`

interval()                   // interval — durations

Use Effect's Duration.toIso when writing; reads come back as ISO 8601 strings (PT1H30M).

`json<T>()`

Typed JSONB column. The type parameter flows to ctx.store.select(...) so reads are narrowed without manual casts.

import { json } from '@voltro/database'

interface NotePrefs {
  readonly fontSize: 'sm' | 'md' | 'lg'
  readonly collapsed: ReadonlyArray<string>
}

const notes = table('notes', {
  id:    id(),
  prefs: json<NotePrefs>().default({ fontSize: 'md', collapsed: [] }),
})

See JSON columns for indexing + path queries.

`vector(dimensions)`

vector(1536)                 // pgvector vector(1536)

The HNSW index is table-level — there is no column-level .index('hnsw'). Declare it as .index('docsEmbeddingHnsw', ['embedding'], { kind: 'hnsw' }). Requires the pgvector extension on postgres (the framework emits CREATE EXTENSION IF NOT EXISTS vector automatically). See Vector columns for the full RAG story.

`reference(() => table, options?)`

Foreign key to another table's id column. Pass a thunk returning the target table (not a string name) — singular reference, not references.

import { reference } from '@voltro/database'

const messages = table('messages', {
  id:       id(),
  threadId: reference(() => threads, { onDelete: 'cascade' }),
  authorId: reference(() => users, { onDelete: 'setNull' }).nullable(),
})

`onDelete`	SQL
`'restrict'` (default)	`ON DELETE RESTRICT`
`'cascade'`	`ON DELETE CASCADE`
`'setNull'`	`ON DELETE SET NULL` (requires `.nullable()`)
`'noAction'`	`ON DELETE NO ACTION`

Every FK column gets a B-tree index by default (index: true); opt out with reference(() => buckets, { index: false }) for tiny lookup tables.

`dbEnum(name, values)`

Postgres-native enum. Declare it once, then build a column from it with .column(); the migrator creates the type + the column.

import { dbEnum } from '@voltro/database'

const NoteStatus = dbEnum('note_status', ['draft', 'published', 'archived'])

const notes = table('notes', {
  status: NoteStatus.column().default('draft'),
})

You get TypeScript autocomplete on where('status', '=', 'published') — typos fail the type check.

`raw<T>(ddl)`

The escape hatch for any column type the DSL doesn't model:

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

const events = table('events', {
  id:         id(),
  occurredAt: raw<Date>('timestamptz NOT NULL'),
  geo:        raw<string>('geography(Point, 4326)'),
})

The TypeScript row shape comes from the generic parameter; the DDL fragment is emitted verbatim on every dialect — the framework does no translation, so you own dialect-portability of the text.

Because the fragment already encodes the type, nullability, and default, the fragment is the single source of truth: chaining .nullable(), .default(), or .unique() on a raw(...) column throws at declaration time (two sources would drift). Encode NULL / DEFAULT / UNIQUE directly in the fragment instead.

Universal modifiers

These chain on every column type:

Modifier	What it does
`.nullable()`	Drops the `NOT NULL` constraint.
`.unique()`	Adds a unique constraint. Optional `{ dedup }` migration strategy.
`.default(value)`	Default value. Literal, `'now'` for `now()`, OR a callback — see Default with callback.
`.computed(row => ...)`	Computed-from-row value, app-side at write-time. See Computed columns.
`.onUpdate(value)`	Auto-bump on UPDATE (timestamps).
`.generatedAs(expr, { stored })`	DB-level generated column — see Generated columns.
`.oneOf([...])`	Closed value set (text only) — narrows the type + emits a `CHECK`.
`.maxLength(n)`	Bounds a text column → `VARCHAR(n)` / `NVARCHAR(n)` (text only). For index-ability — see the `text()` section.
`.check(expr)`	Raw SQL `CHECK` on the column — DB-enforced. Chainable for multiple. Any column type. See DB-level checks.

DB-level checks

Two layers enforce row invariants, and they're complementary:

.check(expr) (column) / table().check(name, expr) (table) — emit a SQL CHECK constraint. DB-enforced: the rule holds no matter which client writes the row (a raw psql, another service, a buggy migration) — defense in depth. expr is raw SQL emitted verbatim, so YOU own its cross-dialect portability (stick to standard operators + unquoted column names; avoid dialect-specific functions / regex). CHECK is enforced on Postgres / MySQL 8+ / MariaDB / MSSQL / SQLite.
```
table('bookings', {
  id:       id(),
  seats:    integer().check('seats > 0'),
  startsAt: timestamp(),
  endsAt:   timestamp(),
})
  .check('booking_window', 'startsAt < endsAt')   // cross-column → table level
```
table().validate(Schema) (below) — app-level effect/Schema validation, run before the INSERT/UPDATE. Use it for the rich stuff a portable CHECK can't express: regex / email / structured strings, length ranges, numeric bounds, cross-field rules with custom typed errors. It's the single validation surface — don't reach for a pile of .min() / .max() / .pattern() column modifiers (there aren't any, by design).

Rule of thumb: .check() when the DATABASE must guarantee it; validate(Schema) for everything else. There is no .comment() / .index() column modifier — indexes are table-level.

Default with callback

default() accepts three forms:

status:        text().default('pending')              // literal — lands in DDL DEFAULT
createdAt:     timestamp().default('now')              // server-now — lands in DDL DEFAULT
correlationId: text().default(() => crypto.randomUUID())  // app-side factory

The literal + 'now' forms emit DEFAULT in the table DDL — the database fills in the value when the row omits the column. The callback form is evaluated app-side at insert time by the MutationStore wrapper, BEFORE the row reaches the database. Use it for per-insert dynamic values the DB can't produce:

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

export const sessions = table('sessions', {
  id:            id(),
  correlationId: text().default(() => crypto.randomUUID()),    // unique per row, JS-generated
  region:        text().default(() => process.env.REGION!),     // env-driven
  startedAt:     timestamp().default('now'),                    // DB-generated, lands in DDL
})

Callback defaults compose with the framework's existing auto-stamps (audit createdAt / updatedAt, tenant tenantId, etc.) — your callback runs after the framework fills its own auto-stamps, so the callback's row argument carries the stamped fields.

Important: callback defaults are NOT emitted in the DDL. Raw SQL inserts that bypass the framework (psql, manual migrations, third-party tooling) won't see the callback fire — only the literal portion of the column declaration lands in CREATE TABLE. For DB-enforced defaults always reachable from raw SQL, use the literal/'now' form.

Computed columns

.computed(row => ...) marks the column as derived from other row fields. The function runs on INSERT (AFTER framework auto-stamps + factory defaults) and again on every UPDATE; its return value overwrites whatever the caller passed for that column (computed values are an opinion of the schema, not a user-overridable input).

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

const slugify = (s: string): string => s.toLowerCase().replace(/\s+/g, '-')

export const posts = table('posts', {
  id:    id(),
  title: text(),
  // slug ALWAYS comes from title — no caller can pass a stale slug
  slug:  text().computed((row) => slugify(String(row.title))),
})

Computed columns recompute on UPDATE too. Every computed column on the table re-runs against the merged post-update row (the patch layered over the existing row), so a derived slug stays in sync when its source title changes — no dependsOn declaration, no explicit recompute in the handler:

// mutations/posts.update.mutation.server.ts — slug is recomputed automatically
const execute = async (input, ctx) =>
  ctx.store.update('posts', input.id, { title: input.title })
  // slug becomes slugify(input.title) on its own; a `slug` in the patch is overwritten

Because all computed columns re-run on every UPDATE, keep the functions pure and cheap. They receive the full merged row, so an unchanged source column the computed value reads is still available.

Table-level validation

.validate(Schema) adds a pre-INSERT validation pass. The framework decodes the row against the schema BEFORE the SQL INSERT runs. Decode failure throws a typed TableValidationFailed error you can declare on the mutation's error: channel.

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

export const users = table('users', {
  id:    id(),
  email: text().unique(),
}).validate(Schema.Struct({
  id:    Schema.String,
  email: Schema.String.pipe(Schema.pattern(/^[^@]+@[^@]+$/)),
}))

The validation runs after framework auto-stamps + computed columns — by the time the decoder sees the row, createdAt/updatedAt from audit(), tenantId from tenant(), your .computed() values, and your callback .default() values are all in place. The schema sees the fully-stamped row.

This lets you express constraints the column DSL can't:

Cross-field invariants (startDate < endDate, total === items.reduce(sum))
Format checks beyond oneOf (regex patterns, length bounds, structured strings)
Refinements (Schema.NonEmptyString, Schema.Number.pipe(Schema.between(0, 1)))
Discriminated unions for shape variants

Catch the failure in your mutation handler if you want typed UI feedback:

// mutations/users.create.mutation.ts
import { TableValidationFailed } from '@voltro/runtime'

export const createUser = defineMutation({
  name:   'users.create',
  input:  Schema.Struct({ email: Schema.String }),
  output: Schema.Struct({ id: Schema.String }),
  target: { table: 'users', op: 'insert' },
  error:  TableValidationFailed,
})

Validating UPDATE patches — `.validatePatch(Schema)`

.validate(...) runs on INSERT. For UPDATE patches, add .validatePatch(Schema) — the framework wraps the schema in Schema.partial, so a patch that touches only some columns is valid; every field that IS present must satisfy its rule. A bad patch throws the same typed TableValidationFailed error.

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

export const users = table('users', {
  id:    id(),
  email: text().unique(),
  age:   integer(),
})
  .validate(Schema.Struct({
    email: Schema.String.pipe(Schema.pattern(/^[^@]+@[^@]+$/)),
  }))
  .validatePatch(Schema.Struct({
    age: Schema.Number.pipe(Schema.greaterThanOrEqualTo(0)),
  }))

Pass the same shape you'd pass to .validate(...) — the partial wrapping is applied for you, so don't pre-wrap fields in Schema.optional. The patch decoder runs on the (stamped) patch right before the UPDATE; ctx.store.update('users', id, { age: -5 }) rejects, while ctx.store.update('users', id, { email: 'new@x.io' }) passes (the age rule only fires when age is in the patch).

Specialized column types

Beyond the core text / integer / boolean / timestamp / etc., the framework ships richer types for specific use cases:

Enums (dbEnum) — postgres-native ENUM types with literal-union narrowing, cheap ALTER TYPE ADD VALUE migrations. Falls back to CHECK constraints on other dialects.
Generated columns (generatedAs) — DB-level computed values, kept consistent on UPDATE without app-side stamping. Distinct from .computed() which runs in the MutationStore middleware at INSERT.
Arrays + intervals — postgres-native array(text()) / array(integer()) / interval() with JSON-stringified codec for non-postgres dialects.
Vectors — vector(dim) for embedding- based semantic search via pgvector.
PostGIS — geography() / geometry() from @voltro/plugin-postgis for location-aware apps.
JSON — json<T>() for arbitrary nested data; native JSONB on postgres.

Anti-patterns

serial/bigserial integer ids. Leaks row counts via /users/12345. Use id() (TypeID).
text (or a float) for currency. Use decimal(15, 2) — exact fixed-point, carried as a string.
timestamp without tz. Use timestamp() which is timestamptz.
Storing JSON blobs as text. Use json<T>() for type-safety + JSONB performance.
references('table'). The constructor is reference(() => table) — singular, thunk-arg.
Declaring a timestamp() column as Schema.Number in a descriptor's output. That describes the wire type, not the domain type, so the schema has nothing left to convert and the handler ends up doing it by hand. Declare the field schema instead — timestampMs / timestampMsOrNull from @voltro/database/wire. Do not reach for rowSchema(table) here: a descriptor cannot import a table value without dragging @voltro/database into the browser bundle, and the boot aborts. rowSchema is a server-side row codec.

Column types

#The complete column constructor list

#id()

Composite primary key

#text()

#integer()

#decimal(precision, scale) / numeric(precision, scale)

#bigint()

#boolean()

#timestamp()

#date()

#interval()

#json<T>()

#vector(dimensions)

#reference(() => table, options?)

#dbEnum(name, values)

#raw<T>(ddl)

#Universal modifiers

#DB-level checks

#Default with callback

#Computed columns

#Table-level validation

#Validating UPDATE patches — .validatePatch(Schema)

#Specialized column types

#Anti-patterns