Drizzle ORM

Views

Declare a view with its columns and get a row type your whole app can use.

A view declared with explicit columns works like a table: toDrizzle builds the real drizzle view from it, and its row type goes through your routes and your client without drizzle types.

Declaring a View

Give the view its columns, then the query as plain sql. Tables you embed in the query resolve to the real drizzle tables when the view is built.

import * as DZ from '@mionjs/drizzle-orm-pg-core';
import type {InferSelectViewModel} from '@mionjs/drizzle-orm';
import {sql} from '@mionjs/drizzle-orm';
import {createValidateFn} from '@mionjs/run-types';
import {users} from './drizzle-proxy-pg-example.ts';

export const adultUsers = DZ.pgView('adult_users', {
  id: DZ.uuid('id'),
  name: DZ.varchar('name', {length: 100, notNull: true}),
  role: DZ.text('role', {enum: ['admin', 'user'], notNull: true}),
}).as(sql`select id, name, role from ${users} where age >= 18`);

// already in the database: drizzle-kit will not create it, but you still get the row type
export const legacyUsers = DZ.pgView('legacy_users', {
  id: DZ.uuid('id'),
  name: DZ.varchar('name', {length: 100}),
}).existing();

// { id: UUID | null; name: String<{maxLength: 100}>; role: 'admin' | 'user' }
export type AdultUser = InferSelectViewModel<typeof adultUsers>;

export const validateAdultUser = createValidateFn<AdultUser>();

export const checks = [
  validateAdultUser({
    id: '793aff46-42ac-4372-b7fa-c48ba48ed94f',
    name: 'ann',
    role: 'admin',
  }), // true
  validateAdultUser({id: null, name: 'x'.repeat(101), role: 'admin'}), // false: maxLength 100
];

A view's row type comes from InferSelectViewModel, not the table name. Views are read only, so there is no insert or update model. Drizzle splits the names for the same reason: asking for one is caught while you type.

The columns carry the same format types as a table's, so a validator built from the row type checks the same lengths, ranges and shapes. Rows you query have this type too, so a route can return them as is.

Materialized Views

pgMaterializedView takes the same columns and query, plus the storage options PostgreSQL accepts:

export const topTeams = pgMaterializedView('top_teams', {
  id: serial('id'),
  code: varchar('code', {length: 10, notNull: true}),
})
  .using('btree')
  .with({fillfactor: 90})
  .tablespace('custom_tablespace')
  .withNoData()
  .as(sql`select id, code from teams`);

MySQL views take algorithm, sqlSecurity and withCheckOption instead. SQLite views take no options.

Views Built From a Query

Drizzle can also declare a view whose columns come from a query builder:

// this form stays on drizzle
export const userView = pgView('user_view').as((qb) => qb.select().from(user));

Its columns come from drizzle's own query typing, which these packages keep out of your app. Declare those views with drizzle, over your toDrizzle tables, in the file that runs your queries. Their rows keep the formats of the table columns they select. For a view's row type in your app, declare the columns yourself.

Views

Declaring a View

View Row Types

Materialized Views

Views Built From a Query