# Data types and codecs

> Choose application representations for PostgreSQL values without losing precision or hiding conversions.

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.

## Choose a field representation

| Field                         | Application value                            |
| ----------------------------- | -------------------------------------------- |
| `v.string()`                  | `string`                                     |
| `v.boolean()`                 | `boolean`                                    |
| `v.number()`                  | Finite `number`, stored as double precision  |
| `v.int64()`                   | Safe integer `number`                        |
| `v.int64({ mode: 'bigint' })` | Exact `bigint` within the stored int64 range |
| `v.uuid()`                    | UUID `string`                                |
| `v.date()`                    | Calendar-date string, such as `'2026-10-21'` |
| `v.timestamp()`               | `Date` representing an instant               |
| `v.json()`                    | JSON-safe data                               |

## Keep large integers exact

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:

**Aggregate return types**

| Operation              | Result                                              |
| ---------------------- | --------------------------------------------------- |
| Sum in bigint mode     | `bigint`; the sum may exceed the stored int64 range |
| Average in bigint mode | Decimal string                                      |
| Average in number mode | `number`                                            |

Custom field codecs do not transform aggregate results. For example, summing a number-backed custom class returns a number, not an instance of that class.

## Make date and timezone choices explicit

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

```ts filename="db/schema.ts"
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.

> **Tip: A calendar day is not an instant**
>
> `v.date({ mode: 'date' })` uses the UTC calendar day. Formatting that Date in another timezone can display a different day. Keep the default calendar-date string when you don't need an instant.

### Preserve finer timestamp precision

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

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

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

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

## Store structured JSON

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

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

| You need                                        | Use                               |
| ----------------------------------------------- | --------------------------------- |
| A validated object or array                     | `v.object(...)` or `v.array(...)` |
| A shape declared through the JSON constructor   | `v.json({ ... })`                 |
| JSON-safe data without a fixed shape            | `v.json()`                        |
| A TypeScript type without validating its fields | `v.json<MyType>()`                |

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.

## Query JSON paths and arrays

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

```ts filename="server/profiles.ts"
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:

| Value at `settings.theme` | Exists | Is null |
| ------------------------- | ------ | ------- |
| Missing                   | No     | Yes     |
| `null`                    | Yes    | Yes     |
| `'dark'`                  | Yes    | No      |

For example, find profiles where no theme was provided:

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

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

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

## Use a codec for a different application value

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:

```ts
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](#keep-large-integers-exact) 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](https://hulla.dev/docs/db/functions.md). SSR handoffs also need to follow your framework's serialization rules.