Data types and codecs

Choose how PostgreSQL values appear in your application. A timestamp can be a Date, for example, while a large integer can stay exact as a bigint.

Each validator has a default representation. Use a mode or codec below when you need a different one.

Use bigint mode when an integer may exceed JavaScript’s safe integer range. The default number mode rejects unsafe reads and writes instead of rounding them.

Stored values must still fit PostgreSQL’s int64 range. Aggregate results have their own return rules:

Use a date for a calendar day, such as a birthday. Use a timestamp for an instant, such as when an event starts:

import { defineTable, v } from '@hulla/db/schema'

const events = defineTable({
  day: v.date(), // Calendar-date string
  startsAt: v.timestamp(), // Date
})

Default timestamps use timezone-aware storage. Their Date values preserve milliseconds.

Preserve finer timestamp precision

Switch a timestamp to string mode when milliseconds aren’t enough:

- startsAt: v.timestamp(),
+ startsAt: v.timestamp({ mode: 'string', precision: 6 }),

startsAt now reads as a string that preserves six fractional digits. Precision defaults to 3 and accepts 0–6. Date mode supports at most 3; incompatible combinations throw instead of losing information.

Store a clock time without a timezone

Use timezone-free storage when a value should keep its date and clock components rather than identify a worldwide instant:

const localTime = v.timestamp({ withTimezone: false })

This mode returns a clock-component string. It does not infer the machine’s local timezone.

Describe the shape when you want nested values checked on reads and writes:

const profiles = defineTable({
  settings: v.object({
    theme: v.string().nullable().optional(),
    tags: v.array(v.string()),
  }),
})

settings is a JSON column. Its theme member may be missing or null; tags must be an array of strings. Undeclared properties throw.

Use .nullable() on the column to allow a null column value. .optional() applies to nested object members, not the column itself.

Other JSON forms serve different needs:

JSON shapes can include nested objects, arrays, and explicit codecs. Column defaults remain available through v.json(...).

JSON updates replace the whole column. Include every member you want to keep; the update does not merge a deep patch into the existing object.

Register profiles in your schema and regenerate. In server code, import the generated database and table references:

import { db } from '../db/_generated/server'
import { profiles } from '../db/_generated/tables'

const dark = await db.select(profiles).where('settings.theme', '=', 'dark')

The path starts at the selected table. Use a generated field reference when a literal property name contains a dot, so it isn’t mistaken for a nested path.

Distinguish missing and null members

A missing member and an explicitly null member are different states. Choose a filter based on which states you want:

For example, find profiles where no theme was provided:

const absent = await db.select(profiles).where('settings.theme', 'not exists')

Use is not null for a present, non-null value. The equality forms = null and != null normalize to the same null checks.

The three-argument form where(path, 'exists', false) is equivalent to where(path, 'not exists').

Match array members

Find profiles with at least one of the supplied tags:

const tagged = await db
  .select(profiles)
  .where(profiles.settings.tags, 'containsAny', ['editor', 'admin'])

Other array operators are contains, containsAll, and containedBy. Negate an operator with a form such as 'not contains', or use callback not(...).

Use callback helpers when a filter combines a calculation with a comparison. This finds profiles with at least two tags:

const withSeveralTags = await db
  .select(profiles)
  .where(({ gte, arrayLength }) => gte(arrayLength(profiles.settings.tags), 2))

A codec converts application values on writes and restores them on reads. For example, this field stores a string while application code uses a bigint:

const counter = v.string().codec({
  encode: (value: bigint) => value.toString(),
  decode: (value) => BigInt(value),
})

counter.encode(42n) // '42'
counter.decode('42') // 42n

Use counter as a field in defineTable. The string validator still checks the encoded value. Both callbacks must be synchronous; null and omitted values follow the field’s rules without passing through the codec.

Storage still determines queries

Whole-value predicates encode the application value before comparing it. SQL text patterns and arithmetic use the stored column type. A bigint stored as a string is still a SQL string; use bigint-mode int64 when you need integer storage.

Inside JSON, conversions must be explicit too. A nested timestamp validator alone does not make a Date JSON-safe; use a JSON-compatible string or number validator with a codec.

Returning values to a browser

A custom class from a direct server read is not automatically a valid browser result. Return a supported serializable value or declare an output schema; see function results. SSR handoffs also need to follow your framework’s serialization rules.