mssql/views

9 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Declaring views with inline query builder syntax

Views can be declared with an inline query builder syntax using `mssqlView().as()`. The view name is passed as a string, and the `.as()` method receives a callback function that takes a query builder and returns a select query. View columns schema is automatically inferred from the query builder. Example: `export const userView = mssqlView("user_view").as((qb) => qb.select().from(user));`

Declaring views with standalone query builder

Views can also be declared using a standalone QueryBuilder instance imported from drizzle-orm/mssql-core. Create a new QueryBuilder with `new QueryBuilder()`, then pass the built query to `.as()` method. The syntax is identical to inline query builder except the query builder is instantiated separately. Example: `const qb = new QueryBuilder(); export const userView = mssqlView("user_view").as(qb.select().from(user));`

Declaring views with raw SQL

When using syntax not supported by the query builder, declare views with the `sql` operator and explicitly specify the view columns schema as a second argument to `mssqlView()`. The schema object contains column definitions that match the view's actual columns. Example: `export const newYorkers = mssqlView("new_yorkers", { id: int().primaryKey(), name: nvarchar({ length: 256 }).notNull(), cityId: int("city_id").notNull(), }).as(sql`select * from ${users} where ${eq(users.cityId, 1)}`)`. All parameters in the query are inlined rather than parameterized.

Automatic schema inference with query builder views

When views are created using either inline or standalone query builders, the view columns schema is automatically inferred from the query. This means you do not need to manually specify column definitions. This automatic inference only works with query builder syntax, not with raw SQL views.

Schema inference not supported with raw SQL views

When declaring views using raw SQL operators, view columns schema must be explicitly declared. Automatic schema inference does not work with raw SQL syntax, so you must define all column types in the schema object passed to mssqlView().

Declaring existing database views

To reference a view that already exists in the database and has read-only access, use the `.existing()` configuration method on the view declaration. When `.existing()` is used, drizzle-kit will ignore this view and will not generate a CREATE VIEW statement in migrations. Example: `export const trimmedUser = mssqlView("trimmed_user", { id: int("id"), name: nvarchar("name", { length: 256 }), email: nvarchar("email", { length: 256 }), }).existing();`

View configuration options with .with() method

Views can be configured using the `.with()` method which accepts an options object with the following properties: encryption (boolean), schemaBinding (boolean), viewMetadata (boolean), and checkOption (boolean). These options control how the view is created in SQL. Example: `.with({ encryption: true, schemaBinding: false, viewMetadata: true, checkOption: true })`

Using CTEs (Common Table Expressions) in views

Views can use CTEs through the query builder's `.$with()` and `.with()` methods. Define a CTE using `qb.$with('cteName').as(query)`, then reference it in the main query with `.with(cte).select().from(cte)`. This allows complex views with multiple subqueries. Parameters in the CTE query are inlined in the resulting SQL.

Parameter inlining in view queries

All parameters inside view queries are inlined rather than parameterized. This applies to both raw SQL views and query builder views. Parameters in placeholders and filter conditions will be embedded directly into the generated SQL CREATE VIEW statement.

Give your agent this brain