# Database

> How Voltro talks to Postgres — the schema DSL, the query builder, mixins, migrations, and the reactive engine's relationship to all of it.



---

<!-- source: en/database/overview.md -->
## Overview

_How Voltro talks to Postgres — the schema DSL, the query builder, mixins, migrations, and the reactive engine's relationship to all of it._

Voltro's data layer is opinionated. One database (Postgres), one connection layer (`@effect/sql`), one schema DSL (built into the framework). No second ORM, no second query builder, no codegen step you have to remember to run.

This section covers everything about how the framework reads + writes data.

## The shape of it

```text
┌───────────────────────────────────────────────────────────┐
│  Schema DSL  (apps/api/database/*.entity.ts)              │
│   table(...) + columns + mixins                           │
└────────────────┬──────────────────────────────────────────┘
                 │  voltro migrate
                 ▼
┌───────────────────────────────────────────────────────────┐
│  Postgres                                                 │
└────────────────┬──────────────────────────────────────────┘
                 │
                 ▼
┌───────────────────────────────────────────────────────────┐
│  Runtime data layer  (ctx.store inside every executor)    │
│   - select/insert/update/delete builders                  │
│   - dependency tracking for subscriptions                 │
│   - tenant-mixin AND-merging                              │
└───────────────────────────────────────────────────────────┘
```

You declare tables once. The migrator produces SQL; the runtime gives you a typed `ctx.store`; the reactive engine watches the same rows.

**Every table is reactive by default.** There is nothing to opt into — this is a
reactive framework, so a capability you had to remember to switch on is one half
your tables would not have. Writes emit change events, subscriptions stay live,
and on a multi-instance deployment the change reaches the other instances too.

`.nonReactive()` turns it off for one table, on every dialect: no subscriber
fires, locally or across instances. The write still happens — this is about
notification, never persistence. Use it for a genuinely hot table nobody
subscribes to (an append-only event log, a metrics sink); never for one a query
reads, because that subscription would never fire. `voltro dev` warns if you do.

## A first table

```ts
// apps/api/database/notes.entity.ts
import {
  table, id, text, integer, timestamp,
} from '@voltro/database'
import { tenant } from '@voltro/plugin-multitenancy'

export const notes = table('notes', {
  id:        id(),
  title:     text(),
  body:      text(),
  views:     integer().default(0),
  createdAt: timestamp().default('now'),
  updatedAt: timestamp().default('now').onUpdate('now'),
})
  .with(tenant())   // adds tenantId + scoping — mixins chain via .with(), not spread
```

After `voltro migrate`:

- A `notes` table exists in Postgres with the columns above + tenant column from the mixin.
- A typed `ctx.store.select('notes')` etc. is available everywhere.
- Subscriptions reading `notes` get auto-scoped to the caller's tenant.

## What's in this section

### Schema basics
- [Column types](/docs/database/columns) — every column function, their SQL type, modifiers
- [Mixins](/docs/database/mixins) — `tenant()`, `audit()`, `softDelete()`, writing your own
- [Indexes](/docs/database/indexes) — single-column, composite, partial, expression, GiST, HNSW
- [Migrations](/docs/database/migrations) — the migration story end-to-end, including production

### Specialized columns
- [Enums (`dbEnum`)](/docs/database/enums) — postgres-native ENUM types
- [Generated columns](/docs/database/generated-columns) — DB-computed values
- [Arrays + intervals](/docs/database/arrays-intervals) — native postgres types + cross-dialect codec
- [JSON columns](/docs/database/json) — typed `jsonb`, indexing, paths
- [Vector columns](/docs/database/vectors) — pgvector, embedding, ANN search, HNSW
- [PostGIS](/docs/database/postgis) — geography/geometry for location-aware apps
- [Full-text search](/docs/database/full-text-search) — tsvector + GIN + `.matching()`

### Reading data
- [Query builder](/docs/database/query-builder) — `select`, `where`, aggregates, raw SQL escape hatch
- [Joins & relations](/docs/database/joins) — explicit joins, eager loading, the runtime's tracking
- [Aggregations](/docs/database/aggregations) — `count` / `sum` / `groupBy` / `having` + window functions
- [Sub-queries](/docs/database/sub-queries) — `inSubquery` / `exists` predicates
- [Set operations](/docs/database/set-operations) — `union` / `intersect` / `except`
- [DISTINCT + DISTINCT ON](/docs/database/distinct) — dedup + one-per-group
- [Self-joins](/docs/database/self-joins) — `.as(alias)` + `.innerJoin(table, alias, on)`
- [Recursive CTEs](/docs/database/recursive-cte) — `WITH RECURSIVE` for tree walks
- [SQL views](/docs/database/views) — `view(name, columns, select)` read-only named SELECTs

### Writing data
- [Transactions](/docs/database/transactions) — how `ctx.store` works inside mutations + workflows
- [Bulk operations](/docs/database/bulk-operations) — `updateMany` / `upsert` / `insertIgnore`

## Conventions enforced by the framework

| | Why |
|---|---|
| One table per `database/*.entity.ts` file + a `database/index.ts` barrel | Discovery walks `*.entity.ts` / `*.schema.ts` / `schema.ts`; the entity-per-file model is the primary convention. |
| All columns NOT NULL by default; `.nullable()` to opt in | Three-valued logic is the source of half of all SQL bugs. The default is the safer one. |
| `id` columns default to **TypeID** (`<prefix>_<ulid>`), not serial integers | Sortable by time, URL-safe, branded to the table type, no leaking sequential row counts. |
| Timestamps are `timestamptz` | Always UTC, never naive. The wire format is ISO 8601. |
| Mixins are chained via `.with(mixin())` | Keeps the table declaration readable + composable; the dep resolver dedups shared mixins. |