pgView() - inline query builder syntax
Use pgView(name).as((qb) => ...) to declare a PostgreSQL view with automatic column schema inference. The query builder is passed inline and columns are automatically inferred from the SELECT statement.
Drizzle · PostgreSQL · all subjects
views
13 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
pgView() - standalone query builder syntax
Import QueryBuilder from 'drizzle-orm/pg-core', instantiate it with new QueryBuilder(), and pass the result of qb.select().from(...) to pgView(name).as(). This achieves the same result as inline syntax with automatic schema inference.
Declaring views with raw SQL and explicit schema
When query builder syntax is insufficient, use pgView(name, { columns }).as(sql`...`) to declare a view with raw SQL. You must explicitly specify all view columns with their types and constraints. The same pattern applies to materialized views with pgMaterializedView(name, { columns }).as(sql`...`).
pgMaterializedView() - basic usage
Use pgMaterializedView(name).as((qb) => ...) to declare a PostgreSQL materialized view. Materialized views in PostgreSQL and CockroachDB persist results in a table-like form, so query results are returned directly from the materialized view rather than reconstructed from underlying tables.
Existing views with .existing()
Use .existing() on either pgView() or pgMaterializedView() when declaring a read-only view that already exists in the database. drizzle-kit will not generate a CREATE VIEW statement in migrations.
Refresh materialized views at runtime
Call db.refreshMaterializedView(viewName) to refresh a materialized view. Chain .concurrently() for concurrent refresh or .withNoData() to refresh without data.
pgView() with configuration options
Use .with({ checkOption, securityBarrier, securityInvoker }) on pgView() to set view options. Example: pgView('name').with({ checkOption: 'cascaded', securityBarrier: true, securityInvoker: true }).as(...)
pgMaterializedView() with storage options
pgMaterializedView() supports .using(heapType) for storage type, .with({ options }) for configuration, .tablespace(name) for tablespace, and .withNoData() to create without data. Example: pgMaterializedView('name').using('heap').with({ fillfactor: 90 }).tablespace('custom_tablespace').withNoData().as(...)
Column schema inference with query builders
When declaring views with inline or standalone query builders, view columns schema is automatically inferred from the SELECT statement. When using raw sql, columns must be explicitly declared.
Query parameters inlined in view definitions
When using query builders in view definitions, all parameters inside the query are inlined directly rather than replaced by $1, $2, etc. placeholders.
pgView with WHERE clause example
Example: export const customersView = pgView('customers_view').as((qb) => qb.select().from(user).where(eq(user.role, 'customer'))); generates CREATE VIEW "customers_view" AS (SELECT * FROM "user" WHERE "role" = 'customer');
pgMaterializedView with subqueries example
Example showing materialized view with CTE: export const newYorkers = pgMaterializedView('new_yorkers').as((qb) => { const sq = qb.$with('sq').as(qb.select({...}).from(users).leftJoin(cities, eq(cities.id, users.homeCity)).where(...)); return qb.with(sq).select().from(sq).where(...); });
pgMaterializedView materialized configuration example
Example: pgMaterializedView('new_yorkers_2').using('heap').with({ fillfactor: 90, toastTupleTarget: 0.5, autovacuumEnabled: true }).tablespace('custom_tablespace').withNoData().as(...) creates a materialized view with heap storage, configuration options, custom tablespace, and no initial data.