---
url: /guide/views.md
description: >-
  Defining and querying PostgreSQL views with defineView, $views, relations,
  grants, refreshes, and generated migrations.
---

# Views

Define PostgreSQL regular views and materialized views with `defineView` 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 } from 'orchid-orm';
import { defineView, sql } from './table-factory';
import { UserTable } from './user.table';

export const MonthlySalesView = defineView(
  'monthly_sales',
  {
    schema: 'analytics',
    securityInvoker: true,
    checkOption: 'LOCAL',
    sql: sql`
      SELECT
        row_number() over () AS id,
        "userId",
        sum(total) AS total
      FROM sale
      GROUP BY "userId"
    `,
  },
  (t) => ({
    id: t.integer(),
    userId: t.integer(),
    total: t.decimal(),
  }),
)
  .relations((monthlySales) => ({
    user: monthlySales('userId').belongsTo(() => UserTable('id')),
  }))
  .grants([
    {
      to: 'reporting_user',
      privileges: ['SELECT'],
    },
  ]);
```

Add the view definitions 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

The first argument of `defineView` 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 const ActiveUserView = defineView(
  'activeUser',
  {
    nameInDb: 'active_users',
    sql: sql`SELECT id, "firstName" FROM "user" WHERE active = true`,
  },
  (t) => ({
    id: t.integer(),
    firstName: t.text(),
  }),
);

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

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

```ts
export const { defineView, sql } = createTableFactory({
  snakeCase: true,
});

export const ActiveUserView = defineView(
  'activeUser',
  { sql: sql`SELECT id, first_name FROM "user" WHERE active = true` },
  (t) => ({
    id: t.integer(),
    firstName: t.text(),
  }),
);

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 const MonthlySalesMaterializedView = defineView(
  'monthlySales',
  {
    materialized: true,
    nameInDb: 'sales_by_month',
    sql: sql`SELECT "userId", sum(total) AS total FROM sale GROUP BY "userId"`,
  },
  (t) => ({
    userId: t.integer(),
    total: t.decimal(),
  }),
);
```

An explicit `nameInDb` is used as-is and is not changed by `snakeCase`.
Use the existing `schema` option 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 the first `defineView` argument 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 `.query(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 { defineView } from './table-factory';
import { SaleTable } from './sale.table';
import { UserTable } from './user.table';

export const MonthlySalesView = defineView('monthly_sales', (t) => ({
  userId: t.integer(),
  total: t.decimal(),
})).query((db: typeof appDb) =>
  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 the query to SQL and bind values for
`createView`. It does not execute the query. Runtime reads still use
`db.$views.<key>`.

Set `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 const ActiveUserView = defineView(
  'active_user',
  {
    readOnly: false,
    checkOption: 'CASCADED',
    sql: sql`SELECT id, name, active FROM "user" WHERE active = true`,
  },
  (t) => ({
    id: t.integer().primaryKey(),
    name: t.text(),
    active: t.boolean(),
  }),
);
```

## materialized views

Use `defineView` with `materialized: true` 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 { defineView, sql } from './table-factory';
import { UserTable } from './user.table';

export const MonthlySalesMaterializedView = defineView(
  'monthly_sales',
  {
    materialized: true,
    schema: 'analytics',
    withData: false,
    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
    `,
  },
  (t) => ({
    userId: t.integer(),
    month: t.date(),
    total: t.decimal(),
  }),
).relations((monthlySales) => ({
  user: monthlySales('userId').belongsTo(() => UserTable('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,
});
```

Materialized views support the table-like view behavior that applies to
read-only queryable relations: columns, schema, computed columns, scopes, soft
delete, grants, and relations. They do 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](/guide/migration-writing#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 const UserTable = defineTable('user', (t) => ({
  id: t.identity().primaryKey(),
  name: t.text(),
})).relations((user) => ({
  monthlySales: user('id').hasMany(() => MonthlySalesView('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

`defineView` supports persistent PostgreSQL `CREATE VIEW` options used by
generated migrations. See the PostgreSQL
[`CREATE VIEW`](https://www.postgresql.org/docs/current/sql-createview.html)
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.

`defineView` 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](/guide/migration-writing#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 definition `schema`, alias, columns, `sql` or `.query(...)`, grants, and supported view options to generate `createView` and `dropView` migrations.
For materialized views, it uses `schema`, alias, columns, `sql`, grants, `withData`, dependencies, and indexes to generate `createMaterializedView`, `dropMaterializedView`, and related index migrations.

Set `generatorIgnore: true` in view options when the view should stay queryable in code but its DDL is managed outside Orchid.
This works the same as [`generatorIgnore.views`](/guide/generate-migrations#generatorignore) in ORM config.
View grants still need [`generatorIgnore.grants`](/guide/generate-migrations#generatorignore).
There is no separate materialized-view ignore option.