JSON columns

json<T>() declares a jsonb column whose JSON shape is typed by T. The runtime decodes on read + validates on write — typos in your object literals fail the TypeScript check, not at runtime.

#Declaring a typed JSON column

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

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

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

ctx.store.select('notes') returns prefs: NotePrefs — autocomplete works in your IDE, mutation inputs are checked too.

#Storage + validation per dialect

Pass JavaScript values to json() column writes, not JSON.stringify(value). A string such as 'null', '42' or '{"a":1}' remains that exact string; it is not interpreted as a pre-encoded null, number or object. The store encodes the value at the SQL boundary. Keep explicit serialization for text() columns and raw SQL parameters. Existing stored JSON is not rewritten; a value previously stored as the wrong type cannot be reconstructed automatically.

SQLite-family and SQL Server stores apply the same schema decoding to write return values and change images as to queries. Upsert callbacks receive decoded column values; do not parse JSON columns again in a callback or event consumer.

SQL Server schema inspection distinguishes JSON-validity checks from enum membership: literals used to validate JSON are not reported as oneOf values. Low-level ColumnSnapshot.jsonChecks lists recognized JSON checks with their catalog name, validation (value or container), enabled and trusted state. A re-enabled check is not necessarily trusted: SQL Server can enable future-write checking without validating existing rows. Application-specific predicates are not included in this list. jsonValidation summarizes the proven domain (value, container or unverified) separately from catalog names. An active container-only check still prevents scalar writes even if a value check exists alongside it.

For existing SQL Server tables, run voltro db plan and voltro db apply before deploying. reconcile-json-check replaces recognized older or unverified checks with the required validation. The database validates existing rows within the migration transaction; invalid JSON refuses the migration and restores the preceding checks. No values are rewritten. Application-specific checks remain in force and can impose additional restrictions. voltro serve does not apply this migration. If rollback itself fails, the migration error retains both the original DDL failure and the rollback failure; do not infer successful rollback from that error alone. Inspect the database state before retrying. The rolling-deploy assessment treats a verified container-to-value widening as safe. Adding validity enforcement or narrowing to containers requires writers to satisfy the new constraint first.

On MySQL and MariaDB, mysql2 already decodes native JSON (including MariaDB's extended JSON metadata). Voltro preserves these decoded values instead of parsing strings a second time. Fallback array columns still have their own text decoder.

json() maps to each engine's native JSON type, and Voltro enforces JSON validity on write, on every dialect — invalid JSON is rejected by the database, not only by TypeScript:

Dialect	Column type	Validated on write
postgres	JSONB (binary, GIN-indexable)	yes (native)
mysql / mariadb	JSON	yes (mariadb's JSON is LONGTEXT + an auto json_valid CHECK)
mssql	NVARCHAR(MAX) + CHECK (ISJSON(col)=1)	yes
sqlite	TEXT + CHECK (json_valid(col))	yes

On postgres the binary JSONB form is what makes path queries + GIN indexing fast; the other engines store JSON as text (mariadb's information_schema reports the JSON column as longtext — that is what the JSON type is there). Either way, the jsonField(...) filters below and write-validation behave identically across all of them, and reads always come back as parsed objects/arrays — never raw strings.

On postgres, prefer jsonb over json (Voltro always emits jsonb): binary storage, GIN-indexable, faster. Plain json only preserves exact byte / whitespace / key-order — never what you want for app data.

#Path filters

Filter on a value inside a json() column with jsonField(column, ...path):

import { jsonField } from '@voltro/database'

database.users.where(jsonField('preferences', 'theme').eq('dark'))
database.events.where(jsonField('payload', 'amount').gt(1000))       // numeric, not lexical
database.docs.where(jsonField('meta', 'tags', 0).eq('urgent'))       // nested key + array index
database.users.where(jsonField('preferences', 'locale').inSet(['de', 'en']))
database.users.where(jsonField('preferences', 'theme').isNotNull())

Path segments are object keys (string) or array indices (number) → $.theme / $.tags[0].

Operators	Compare the extracted value as
eq neq inSet notInSet contains isNull isNotNull	text
gt gte lt lte	number (numeric ordering, not lexical)

The same jsonField(...) expression is portable across every backend — it lowers to each dialect's json accessor: postgres #>>, mysql/mariadb JSON_EXTRACT (+ JSON_UNQUOTE), mssql JSON_VALUE, sqlite / turso json_extract.

Reactive subscriptions filtered by a JSON path stay live, but the matcher treats the leaf as non-indexable: it's re-checked on every change to the table (the same way contains is). For a high-traffic filter, denormalise into a real indexed column — or index the specific path (see Indexing JSON columns below).

Scope. JSON-path filtering is a server-side query-DSL feature (query / mutation handlers). Clients can't yet send JSON-path filters over rpc — that's a deliberate later phase (it needs path allow-listing + validation). For arbitrary expressions the DSL doesn't model, drop to the store.raw escape hatch (see the query builder).

#Indexing JSON columns

#GIN index — broad coverage

A GIN index covers arbitrary containment queries on the whole jsonb document. Declare it with the table-level .index(name, [col], { kind: 'gin' }):

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

const events = table('events', {
  id:      id(),
  payload: json<unknown>(),
})
  .index('events_payload', ['payload'], { kind: 'gin' })

On postgres this emits CREATE INDEX "events_payload" ON "events" USING GIN ("payload"), so any containment query on payload is indexed.

GIN is postgres-only. On mysql / mariadb there is no GIN access method and a btree on a JSON column can't serve containment — the migrator skips the index and warns, pointing you at the single-path approach below. On mssql / sqlite it falls back to a plain btree + a warning. GIN is also large (often 30-50% of the table size for wide JSON); use the next option when you only filter on one specific path.

#Index a JSON path — jsonIndex

When 99% of your queries look like jsonField('payload', 'kind').eq('X'), index just that path with jsonIndex(column, ...path) inside .expressionIndex(...). It mirrors jsonField exactly — same column, same segments, lowered to the same per-dialect accessor — so a filter on that path can use the index:

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

const events = table('events', { id: id(), payload: json<unknown>() })
  .expressionIndex('events_kind',   [jsonIndex('payload', 'kind')])
  .expressionIndex('events_amount', [jsonIndex('payload', 'amount').numeric()])

Text extraction (the default) backs eq / neq / inSet / contains; .numeric() backs the range ops (gt / gte / lt / lte). Because the index expression is byte-identical to the WHERE expression jsonField(...) compiles to, the optimiser actually picks it up.

Dialect support is uneven — this is a hard engine limit, not a Voltro choice. Only some engines can index an expression directly:

Dialect	jsonIndex	Notes
postgres	✓ expression index	((col #>> '{path}'::text[]))
mysql (8.0.13+)	✓ functional index	((JSON_UNQUOTE(JSON_EXTRACT(...))))
sqlite	✓ expression index	(json_extract(col, '$.path'))
mariadb	— skipped + warned	no expression-index support
mssql	— skipped + warned	no expression-index support

On mariadb / mssql the migrator skips a jsonIndex and warns rather than emit DDL the engine rejects. The idiomatic indexed-JSON path there is a generated / computed column (which Voltro already supports), then filter on that column instead of jsonField(...):

table('events', {
  id:      id(),
  payload: json<unknown>(),
  // materialise the path into a real, indexable column
  kind:    text().generatedAs(`JSON_UNQUOTE(JSON_EXTRACT(\`payload\`, '$.kind'))`),
}).index('events_kind', ['kind'])
// query: ctx.store.select('events').where('kind', 'click').all()

For an arbitrary expression the jsonIndex shorthand doesn't model, drop to the raw .expressionIndex(...) form with an { expr } entry:

table('events', { id: id(), payload: json<unknown>() })
  .expressionIndex('events_kind', [{ expr: `(payload->>'kind')` }])

There you own per-dialect correctness (the expr string is emitted verbatim; the postgres ->> form is shown).

#Writing JSON

ctx.store.insert('notes', {
  prefs: { fontSize: 'lg', collapsed: ['archive'] },
})

A plain update writes the WHOLE JSON value. To change one field without rewriting the rest, use patchJson — a server-side in-place JSON merge (no read-modify-write round-trip):

// Merge an object over the top level of the column:
await ctx.store.patchJson('notes', id, 'prefs', { autoSave: true })

// Set a nested path (dot-separated; the column is the first segment):
await ctx.store.patchJson('notes', id, 'prefs.theme', 'dark')

patchJson(table, pk, path, value, additionalPatch?) returns the post-image (or null when the row doesn't exist) and emits an update ChangeEvent so reactive subscribers see the change. The merge is server-side on every dialect — postgres jsonb_set, mysql + mariadb JSON_SET, mssql JSON_MODIFY, and SQLite-family JSON functions. Root objects merge shallowly; nested values replace their target. Missing/non-object parents become objects. Numeric path segments are object keys, not array indices. JSON null stays present.

The optional companion patch writes other columns in the same UPDATE:

await ctx.store.patchJson('notes', id, 'prefs.theme', 'dark', { title: 'Updated note' })

Companions cannot replace primary-key columns or the JSON column itself; undefined is omitted and explicit null is preserved. Request-scoped .onUpdate() and audit values ride in the same assignment, through validation and encryption. The JSON target counts as an explicit write, so its own .onUpdate() cannot replace the document. A failed companion constraint rolls back the JSON change too. No whole-document application snapshot is used.

Escaped SQLite-family object keys use bound comparisons with an in-statement engine probe, so keys are decoded exactly once. Literal backslash-n and a newline remain different keys. Unrelated escaped siblings are preserved too.

SQL Server limitation: the current JSON-column constraint allows object/array roots, not scalar roots (nested scalar values are supported). Ordinary-object tests do not prove scalar-root support.

When you need a full read-modify-write (e.g. computing the new value from the old in JS), read the row, change the object, and write it back inside the mutation's transaction:

const note = await ctx.store.select('notes').where('id', id).one()
await ctx.store.update('notes', id, {
  prefs: { ...note.prefs, autoSave: true },
})

#Validating JSON shape

Two layers of validation apply:

1. JSON validity — that the stored bytes are well-formed JSON — is enforced automatically by the database on every dialect (see Storage + validation per dialect). You don't declare anything.
2. JSON shape — that the value matches your expected structure — is up to you: enforce it at the table level with table().validate(Schema):

import { Schema } from 'effect'

table('notes', { id: id(), prefs: json<NotePrefs>() })
  .validate(Schema.Struct({
    prefs: Schema.Struct({ fontSize: Schema.Literal('sm', 'md', 'lg') }),
  }))

Decode failure throws a typed TableValidationFailed before the INSERT runs. There is no .check() modifier on a JSON column.

#When JSON is the wrong choice

JSON is great for:

• Free-form user-configurable data (preferences, layout configs)
• Sparse extensions (every row has different shape)
• Foreign-system payloads (Stripe webhook bodies, Slack message JSON)

JSON is a footgun for:

• Anything you filter on heavily. Denormalise into real columns — they're cheaper to query, easier to index, simpler to constrain.
• Anything with strict schema. A real column with a NOT NULL + CHECK is stronger than a JSON path constraint.
• Joining / relating to other tables. You can't FK from a JSON path.

Rule of thumb: if you'd write a migration to add a new field, it's a real column. If users add fields without your code changing, it's JSON.

#Anti-patterns

• json<any>() everywhere. Defeats the type-safety. Be specific.
• Putting a foreign key inside JSON. No FK constraint, no cascade, no clean join. Use a real reference(() => table) column.
• Storing big binary as JSON. Use the storage plugin (@voltro/plugin-storage) for blobs > a few KB.