Prepared queries .prepare() name parameter optional in v1.0
The `name` parameter for `.prepare()` method is now optional when creating prepared queries.
Drizzle · PostgreSQL · all subjects
pg-core/query-api
254 notes in this subject, read out of this brain and free to use. This is page 5 of 5.
SQLcommenter example for v1.0
Example of SQLcommenter in v1.0: `db.select().from(users).comment('my_first_tag');` produces `select "id", "name" from "users" /*my_first_tag*/`.
SQLcommenter support added in v1.0
SQLcommenter support allows adding custom tags to queries via `.comment()` method. Tags are appended as SQL comments at the end of each query. Example: `db.select().from(users).comment('my_first_tag')` produces `select "id", "name" from "users" /*my_first_tag*/`.
pgView WITH options
pgView supports a .with() method for setting CHECK VIEW options. Supported options: checkOption (values like 'cascaded' or 'local'), securityBarrier (boolean), securityInvoker (boolean). Example: pgView('name').with({ checkOption: 'cascaded', securityBarrier: true, securityInvoker: true }).as(...)
pgMaterializedView index and storage options
pgMaterializedView supports .using(method) to specify an index method (e.g., 'btree'), .with(options) for storage parameters like fillfactor, toastTupleTarget, autovacuumEnabled, .tablespace(name) to specify a custom tablespace, and .withNoData() to create the view without initial data. Options are set as key-value pairs in the .with() object.
View column schema inference with query builders
When views are declared using inline or standalone query builders (not raw SQL), Drizzle automatically infers the view columns schema from the query. This eliminates the need to manually declare column types and constraints.
Parameters inlined in raw SQL views
When using raw sql operators in view definitions, all parameters inside the query will be inlined directly, not replaced by $1, $2, etc. placeholders.
Difference between regular and materialized views
PostgreSQL and CockroachDB support both regular views and materialized views. Materialized views persist the results in a table-like form, so query results are returned directly from the materialized view rather than being reconstructed by executing the query against underlying base tables. Regular views execute the query dynamically each time they are accessed.
pgView declaration with inline query builder
Use pgView(name).as((qb) => ...) to declare a regular view with an inline query builder. The view columns schema will be automatically inferred from the query. Example: export const userView = pgView("user_view").as((qb) => qb.select().from(user));
pgMaterializedView declaration with inline query builder
Use pgMaterializedView(name).as((qb) => ...) to declare a materialized view with an inline query builder. The view columns schema will be automatically inferred from the query. Example: export const newYorkers = pgMaterializedView('new_yorkers').as((qb) => qb.select().from(users).where(eq(users.cityId, 1)));
View declaration with standalone QueryBuilder
Create a QueryBuilder instance and pass its constructed query to pgView().as() or pgMaterializedView().as(). The syntax and column schema inference work identically to inline query builders. Example: const qb = new QueryBuilder(); export const userView = pgView("user_view").as(qb.select().from(user));
View declaration with raw SQL
When declaring views with raw SQL syntax not supported by the query builder, use pgView(name, schema).as(sql`...`) or pgMaterializedView(name, schema).as(sql`...`). You must explicitly specify view columns schema when using raw SQL. Column definitions use the same syntax as table definitions (e.g., serial('id').primaryKey(), text('name').notNull()).
Declaring existing views with .existing()
Use .existing() on pgView or pgMaterializedView to mark a view as already existing in the database. When using .existing(), drizzle-kit will not generate a CREATE VIEW statement in migrations. You must still explicitly specify the view columns schema. Example: export const trimmedUser = pgView("trimmed_user", { id: serial("id"), name: text("name") }).existing();
Refresh materialized views at runtime
Materialized views can be refreshed in application code using db.refreshMaterializedView(viewName). Additional options: .concurrently() refreshes the view without locking it, .withNoData() refreshes without populating the view with data. Example: await db.refreshMaterializedView(newYorkers); await db.refreshMaterializedView(newYorkers).concurrently(); await db.refreshMaterializedView(newYorkers).withNoData();