Arrays + intervals

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

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

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:

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":

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:

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 instead of trying to roll your own geometry-as-array column.

#See also

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