SQL views

A view is a named, server-side SELECT you query by name exactly like a table, but which is never written to. Declare one with view(name, columns, select): columns describes the projected row shape (types the result + drives the read decoder), and select is the raw SELECT body emitted verbatim into CREATE VIEW.

import { view, id, text, boolean, timestamp } from '@voltro/database'

export const activeUsers = view(
  'active_users',
  {
    id:        id(),
    email:     text(),
    active:    boolean(),
    createdAt: timestamp(),
  },
  `SELECT id, email, active, created_at AS "createdAt"
     FROM users
    WHERE deleted_at IS NULL`,
)

The migrator discovers the view alongside your tables and emits it after the base tables it reads from — no ordering wiring needed. Pass it into the schema entity list the same way you pass tables.

#Querying a view

A view is read-only. Query it with queryForView(...), which returns a query with the full .where(...) / .orderBy(...) / .take(...) / .with(...) surface but no mutation path (there's no INSERT/UPDATE/DELETE on a view).

import { queryForView, eq } from '@voltro/database'

const rows = await ctx.store.query(
  queryForView(activeUsers).where(eq('email', someEmail)),
)

The projected columns are decoded to their canonical JS shapes — a boolean() projection comes back a real boolean, a json<T>() projection a parsed object, a decimal() projection a string — the same read codec that runs for tables.

#Idempotent per dialect

CREATE VIEW is emitted idempotently so re-running a migration is a no-op:

Dialect	Emission
Postgres / MySQL / MariaDB	CREATE OR REPLACE VIEW
MSSQL	CREATE OR ALTER VIEW (SQL Server 2016 SP1+)
SQLite / Turso	DROP VIEW IF EXISTS + CREATE VIEW (no CREATE OR REPLACE; a view holds no data, so dropping is free)

#When to use a view

• Collapse a recurring filter/join into a named entity your handlers query directly (active_users, open_orders).
• Expose a stable read shape while the underlying tables evolve.
• Hand a reporting/read path a denormalized projection without duplicating the join logic in every query.

You own the SELECT body's cross-dialect portability — the framework emits it verbatim, the same contract as a raw() column or an expressionIndex(...) expression. Keep to standard SQL, or gate dialect-specific views behind your deployment's known backend.