Views | OKModel

views is a list of view() and materializedView() from okmodel/view. You declare the output columns and the query. The query is stored as written. schema() does not check it against the tables, except for tenancy. An omitted list, an empty list, and a value that is not a list add no view.

db.views is the read handle. The client name is the camelCase of the view name, so open_notes is openNotes. Every output column is decoded as text. Each field is string | null. A plain view has find, one, count, and exists. A materialized view also has refresh().

columns, query are required on the SQL form. The builder form is view(name, (q) => q.from(table)). A column list projects those fields. Omit it to select every field. .where(sql...) appends that text. .tenancy(global("reason")) opts out. The builder form is public only. It has no schema option. materializedView() stays the SQL form. On the SQL form, schema defaults to public. tenancy opts a view out with global("reason").

view

view() stores the query. find needs a limit, or .all(reason).

view.ts

import { schema, sql, table, t } from "okmodel/pg";
import { view } from "okmodel/view";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const openNotes = view("openNotes", {
  columns: [
    { name: "id", type: "bigint" }, 
    { name: "title", type: "text" }, 
  ],
  query: sql`select id, title from notes`, 
});

export default schema({
  tables: [notes],
  views: [openNotes], 
});

materialized

materializedView() is created with no data. Populate is a later plan step. Omitted refresh still populates, without concurrently. db.views.noteCounts.refresh() runs refresh materialized view "noteCounts". The call sets app.unscoped for that statement and is not tenant-scoped.

materialized.ts

import { connect } from "okmodel/pg/postgresjs";
import { schema, sql, table, t } from "okmodel/pg";
import { materializedView } from "okmodel/view";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const noteCounts = materializedView("noteCounts", {
  columns: [{ name: "n", type: "bigint" }],
  query: sql`select count(*)::bigint as n from notes`,
});

const app = schema({
  tables: [notes],
  views: [noteCounts],
});

export async function reload(url: string) {
  await using db = connect(url, { schema: app });
  await db.views.noteCounts.refresh(); 
  return db.views.noteCounts.find({ limit: 1 });
}

OKM1111

A driver that cannot reserve a connection is Refresh needs a driver that can reserve a connection.

concurrently

refresh: "concurrently" plans refresh materialized view concurrently once the view has been populated. The first populate is not concurrent. Postgres rejects concurrently until a unique index exists, so the declaration needs one. Any other refresh value is refused.

concurrently.ts

import { schema, sql, table, t } from "okmodel/pg";
import { materializedView } from "okmodel/view";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const noteCounts = materializedView("noteCounts", {
  columns: [{ name: "title", type: "text" }],
  query: sql`select title from notes`,
  indexes: [{ columns: ["title"], unique: true }], 
  refresh: "concurrently", 
});

export default schema({
  tables: [notes],
  views: [noteCounts],
});

OKM1822

Materialized view noteCounts refreshes concurrently and has no unique index.

OKM1120

Another refresh value is Materialized view noteCounts refresh must be "concurrently".

OKM1020

An index column the view does not declare is Index on noteCounts names column missing, which the materialized view does not have.

tenancy

A view that reads a tenant table has to expose the tenant key, or pass global("reason"). The reason is stored for inspect() and is not part of the catalog hash. Under rlsTenancy, a view that exposes the key is created with (security_invoker = true).

tenancy.ts

import { columnTenancy, global } from "okmodel/tenancy";
import { schema, sql, table, t } from "okmodel/pg";
import { view } from "okmodel/view";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const openNotes = view("openNotes", {
  columns: [
    { name: "id", type: "bigint" },
    { name: "title", type: "text" },
    { name: "tenantId", type: "uuid" }, 
  ],
  query: sql`select id, title, "tenantId" from notes`,
});

const shared = view("shared", {
  columns: [{ name: "n", type: "bigint" }],
  query: sql`select count(*)::bigint as n from notes`,
  tenancy: global("counts are shared"), 
});

export default schema({
  tables: [notes],
  tenancy: columnTenancy({ key: "tenantId", type: "uuid" }),
  views: [openNotes, shared],
});

OKM1820

A tenant view that hides the key is View openNotes reads a tenant table and does not expose tenantId.

OKM1020

Anything other than global("reason") is View openNotes tenancy must be global("reason"). Two output columns that camelCase to one field are View column tenant_id repeats tenantId.

OKM1023

The same client name as a table is View notes uses the client name notes. The same SQL name as a table is View notes uses the name of a table.

schema

schema is the install schema. The default is public.

install.ts

import { schema, sql, table, t } from "okmodel/pg";
import { view } from "okmodel/view";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const openNotes = view("openNotes", {
  schema: "app", 
  columns: [
    { name: "id", type: "bigint" },
    { name: "title", type: "text" },
  ],
  query: sql`select id, title from notes`,
});

export default schema({
  tables: [notes],
  views: [openNotes],
});

together

A tenant view exposes tenantId. A materialized view opts out, refreshes concurrently, and is read after refresh().

together.ts

import { connect } from "okmodel/pg/postgresjs";
import { columnTenancy, global } from "okmodel/tenancy";
import { schema, sql, table, t } from "okmodel/pg";
import { materializedView, view } from "okmodel/view";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const openNotes = view("openNotes", {
  columns: [
    { name: "id", type: "bigint" },
    { name: "title", type: "text" },
    { name: "tenantId", type: "uuid" },
  ],
  query: sql`select id, title, "tenantId" from notes`,
});

const noteCounts = materializedView("noteCounts", {
  columns: [{ name: "title", type: "text" }],
  query: sql`select title from notes`,
  indexes: [{ columns: ["title"], unique: true }],
  refresh: "concurrently",
  tenancy: global("counts are shared"),
});

const app = schema({
  tables: [notes],
  tenancy: columnTenancy({ key: "tenantId", type: "uuid" }),
  views: [openNotes, noteCounts],
});

export async function reload(url: string, tenantId: string) {
  await using db = connect(url, { schema: app });
  await db.views.noteCounts.refresh();
  return db.for({ tenantId }).views.openNotes.find({ limit: 1 }); 
}