Orchid ORM
Stand With Ukraine

Appearance

Views ​

Define PostgreSQL regular views with BaseTable.View and materialized views with BaseTable.MaterializedView when you want a view to be a first-class ORM object. Views are configured next to tables, queried through db.$views, can participate in relations, and can be included in generated migrations.

ts
import { orchidORM, setGrants } from 'orchid-orm';
import { BaseTable, sql } from './base-table';
import { UserTable } from './user.table';

export class MonthlySalesView extends BaseTable.View {
  schema = 'analytics';
  readonly name = 'monthly_sales';

  securityInvoker = true;
  checkOption = 'LOCAL' as const;

  columns = this.setColumns((t) => ({
    id: t.integer(),
    userId: t.integer(),
    total: t.decimal(),
  }));

  sql = sql`
    SELECT
      row_number() over () AS id,
      "userId",
      sum(total) AS total
    FROM sale
    GROUP BY "userId"
  `;

  relations = {
    user: this.belongsTo(() => UserTable, {
      columns: ['userId'],
      references: ['id'],
    }),
  };

  grants = setGrants([
    {
      to: 'reporting_user',
      privileges: ['SELECT'],
    },
  ]);
}

Add the view classes to the first orchidORM options argument:

ts
export const db = orchidORM(
  {
    databaseURL: process.env.DATABASE_URL,
    views: {
      monthlySales: MonthlySalesView,
    },
  },
  {
    user: UserTable,
  },
);

Configured views are exposed under $views by the key from the views object:

ts
const rows = await db.$views.monthlySales
  .select('userId', 'total')
  .where({ userId: 1 })
  .order({ total: 'DESC' });

nameInDb ​

name is the query-facing view alias. It is used for $views query typing and qualified column names, and by default it is also the database view name.

Set nameInDb when the view has a different name in the database:

ts
export class ActiveUserView extends BaseTable.View {
  readonly name = 'activeUser';
  readonly nameInDb = 'active_users';

  columns = this.setColumns((t) => ({
    id: t.integer(),
    firstName: t.text(),
  }));

  sql = sql`SELECT id, "firstName" FROM "user" WHERE active = true`;
}

await db.$views.activeUser.select('activeUser.firstName');
// SELECT "activeUser"."firstName" FROM "active_users" "activeUser"

When snakeCase is enabled on the base table and nameInDb is not set, Orchid derives the database view name from name:

ts
export const BaseTable = createBaseTable({
  snakeCase: true,
});

export class ActiveUserView extends BaseTable.View {
  readonly name = 'activeUser';

  columns = this.setColumns((t) => ({
    id: t.integer(),
    firstName: t.text(),
  }));

  sql = sql`SELECT id, first_name FROM "user" WHERE active = true`;
}

await db.$views.activeUser.select('activeUser.firstName');
// SELECT "activeUser"."first_name" FROM "active_user" "activeUser"

Materialized views support nameInDb in the same way:

ts
export class MonthlySalesMaterializedView extends BaseTable.MaterializedView {
  readonly name = 'monthlySales';
  readonly nameInDb = 'sales_by_month';

  columns = this.setColumns((t) => ({
    userId: t.integer(),
    total: t.decimal(),
  }));

  sql = sql`SELECT "userId", sum(total) AS total FROM sale GROUP BY "userId"`;
}

An explicit nameInDb is used as-is and is not changed by snakeCase. Use the existing schema property for schema qualification; nameInDb is only the view or materialized view name inside that schema. Generated migrations use nameInDb for CREATE VIEW and CREATE MATERIALIZED VIEW, while runtime queries keep using name as the query alias.

For split ORM setup, pass both tables and views to bundleOrchidORM:

ts
import { bundleOrchidORM } from 'orchid-orm';

export const orm = bundleOrchidORM({
  tables: {
    user: UserTable,
  },
  views: {
    monthlySales: MonthlySalesView,
  },
});

read-only by default ​

First-class views are read-only by default. Read queries are available, but mutation methods are unavailable at the TypeScript level:

ts
await db.$views.monthlySales.select('userId', 'total');

await db.$views.monthlySales.create({ userId: 1, total: '10' });
//                         ^ TypeScript error: views are read-only by default.

using query-builder to define views ​

Use init(db) when you want to build the view with the query builder. The db argument is the configured ORM instance, so it has your tables and views:

ts
import { orchidORM } from 'orchid-orm';
import { BaseTable } from './base-table';
import { SaleTable } from './sale.table';
import { UserTable } from './user.table';

export class MonthlySalesView extends BaseTable.View {
  readonly name = 'monthly_sales';

  columns = this.setColumns((t) => ({
    userId: t.integer(),
    total: t.decimal(),
  }));

  init(db: typeof appDb) {
    this.query = db.sale
      .select({
        userId: 'userId',
        total: (q) => q.sum('total'),
      })
      .group('userId');
  }
}

export const appDb = orchidORM(
  {
    databaseURL: process.env.DATABASE_URL,
    views: {
      monthlySales: MonthlySalesView,
    },
  },
  {
    sale: SaleTable,
    user: UserTable,
  },
);

During db g, Orchid compiles this.query to SQL and bind values for createView. It does not execute the query. Runtime reads still use db.$views.<key>.

Set readonly readOnly = false only when you are sure PostgreSQL accepts writes for the view, for example for a simple updatable view or a view with suitable triggers. Orchid does not validate view updatability at runtime; PostgreSQL remains responsible for accepting or rejecting the mutation.

ts
export class ActiveUserView extends BaseTable.View {
  readonly name = 'active_user';
  readonly readOnly = false;
  readonly checkOption = 'CASCADED';

  columns = this.setColumns((t) => ({
    id: t.integer().primaryKey(),
    name: t.text(),
    active: t.boolean(),
  }));

  sql = sql`SELECT id, name, active FROM "user" WHERE active = true`;
}

materialized views ​

Use BaseTable.MaterializedView for PostgreSQL materialized views. They use the same views ORM option and the same $views namespace as regular views, but they are always read-only and store stale data until refreshed.

ts
import { orchidORM, refreshMaterializedView } from 'orchid-orm';
import { BaseTable, sql } from './base-table';
import { UserTable } from './user.table';

export class MonthlySalesMaterializedView extends BaseTable.MaterializedView {
  schema = 'analytics';
  readonly name = 'monthly_sales';
  withData = false;

  columns = this.setColumns((t) => ({
    userId: t.integer(),
    month: t.date(),
    total: t.decimal(),
  }));

  sql = sql`
    SELECT
      "userId",
      date_trunc('month', "createdAt")::date AS month,
      sum(total) AS total
    FROM sale
    GROUP BY "userId", date_trunc('month', "createdAt")::date
  `;

  relations = {
    user: this.belongsTo(() => UserTable, {
      columns: ['userId'],
      references: ['id'],
    }),
  };
}

export const db = orchidORM(
  {
    databaseURL: process.env.DATABASE_URL,
    views: {
      monthlySales: MonthlySalesMaterializedView,
    },
  },
  {
    user: UserTable,
  },
);

const rows = await db.$views.monthlySales.where({ userId: 1 });

await db.$views.monthlySales.create({ userId: 1 });
//                         ^ TypeScript error: materialized views are read-only.

await refreshMaterializedView(db.$views.monthlySales, {
  concurrently: true,
  withData: true,
});

BaseTable.MaterializedView supports the table-like view behavior that applies to read-only queryable relations: columns, schema, computed columns, scopes, soft delete, grants, and relations. It does not support the regular view write opt-in, so declaring readOnly = false is not part of the materialized view API.

withData controls generated CREATE MATERIALIZED VIEW migrations:

  • omit withData to let PostgreSQL use its default.
  • set withData = true for WITH DATA.
  • set withData = false for WITH NO DATA; PostgreSQL creates the materialized view without loading rows, and it cannot be scanned until refreshed with data.

Materialized views do not use regular-view options such as recursive, checkOption, securityBarrier, or securityInvoker. They also do not expose table-only options such as rls, autoForeignKeys, or table primary-key requirements.

Use refreshMaterializedView from orchid-orm to refresh a configured materialized view at runtime. The helper accepts only materialized view query objects, derives the schema-qualified view name from the query, runs REFRESH MATERIALIZED VIEW, and resolves when the refresh completes.

ts
await refreshMaterializedView(db.$views.monthlySales);

await refreshMaterializedView(db.$views.monthlySales, {
  concurrently: true,
  withData: true,
});

await refreshMaterializedView(db.$views.monthlySales, {
  withData: false,
});

concurrently: true emits CONCURRENTLY. withData: true emits WITH DATA, withData: false emits WITH NO DATA, and omitting withData omits the clause. Orchid rejects { concurrently: true, withData: false } before running SQL because PostgreSQL does not allow concurrent refresh with WITH NO DATA. PostgreSQL still validates state-dependent refresh requirements, including the need for an already populated materialized view and a suitable unique index for concurrent refresh.

Create indexes for materialized views in migrations with the same index helpers used for tables. For manual migration examples, see createMaterializedView, dropMaterializedView, refreshMaterializedView.

relations ​

Views support the same relation declaration methods as tables: belongsTo, hasOne, hasMany, and hasAndBelongsToMany. Relations can point from a view to a table, from a table to a view, or between views.

ts
export class UserTable extends BaseTable {
  readonly table = 'user';

  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    name: t.text(),
  }));

  relations = {
    monthlySales: this.hasMany(() => MonthlySalesView, {
      columns: ['id'],
      references: ['userId'],
    }),
  };
}

const users = await db.user.select('name', {
  monthlySales: (q) => q.monthlySales.select('total'),
});

Relation queries preserve the target view's read-only capability. Nested mutations are unavailable for read-only view relations.

view options ​

BaseTable.View supports persistent PostgreSQL CREATE VIEW options used by generated migrations. See the PostgreSQL CREATE VIEW docs for full database semantics.

  • recursive: creates a recursive view.
  • checkOption: 'LOCAL' or 'CASCADED'; asks PostgreSQL to reject writes through the view when rows would not satisfy the view condition. CASCADED also checks dependent views.
  • securityBarrier: enables PostgreSQL's security barrier behavior for views that are used as a security boundary.
  • securityInvoker: when true, PostgreSQL checks underlying table privileges and row-level security policies as the user querying the view instead of the view owner. This is usually the important option for views over RLS-managed tables. Generated view migrations default it to true; set securityInvoker = false only when owner-checked behavior is intentional.

BaseTable.View does not expose table-only options such as rls or autoForeignKeys. For manual migrations, createView also has migration-only options such as createOrReplace, temporary, dropIfExists, and dropMode; see createView, dropView.

generated migrations ​

Views are managed by db g only when they are listed in the ORM views option. If no ORM views are configured, regular database views and materialized views are not loaded or diffed during migration generation.

When views are configured, the migration generator loads both regular and materialized views. For regular views, it uses the view class schema, name, columns, sql or the query assigned in init(db), grants, and supported view options to generate createView and dropView migrations. For materialized views, it uses schema, name, columns, sql, grants, withData, dependencies, and indexes to generate createMaterializedView, dropMaterializedView, and related index migrations.

Set generatorIgnore = true on a view class when the view should stay queryable in code but its DDL is managed outside Orchid. This works the same as generatorIgnore.views in ORM config. View grants still need generatorIgnore.grants. There is no separate materialized-view ignore option.