Views

There’re several ways you can declare views with Drizzle ORM.

You can declare views that have to be created or you can declare views that already exist in the database.

You can declare views statements with an inline query builder syntax, with standalone query builder and with raw sql operators.

When views are created with either inlined or standalone query builders, view columns schema will be automatically inferred, yet when you use sql you have to explicitly declare view columns schema.

Declaring views

import { int, mssqlTable, mssqlView, nvarchar, datetime2 } from "drizzle-orm/mssql-core";
import { eq } from "drizzle-orm";

export const user = mssqlTable("user", {
  id: int().identity().primaryKey(),
  name: nvarchar({ length: 256 }),
  email: nvarchar({ length: 256 }),
  password: nvarchar({ length: 256 }),
  role: nvarchar({ length: 256 }).$type<"admin" | "customer">(),
  createdAt: datetime2("created_at"),
  updatedAt: datetime2("updated_at"),
});

export const userView = mssqlView("user_view").as((qb) => qb.select().from(user));
export const customersView = mssqlView("customers_view").as((qb) => qb.select().from(user).where(eq(user.role, "customer")));

CREATE VIEW [customers_view] AS select ... from [user] where [user].[role] = 'customer';
CREATE VIEW [user_view] AS select ... from [user];

You can also declare views using standalone query builder, it works exactly the same way:

import { int, mssqlTable, mssqlView, nvarchar, datetime2, QueryBuilder } from "drizzle-orm/mssql-core";
import { eq } from "drizzle-orm";

const qb = new QueryBuilder();

export const user = mssqlTable("user", {
  id: int().identity().primaryKey(),
  name: nvarchar({ length: 256 }),
  email: nvarchar({ length: 256 }),
  password: nvarchar({ length: 256 }),
  role: nvarchar({ length: 256 }).$type<"admin" | "customer">(),
  createdAt: datetime2("created_at"),
  updatedAt: datetime2("updated_at"),
});

export const userView = mssqlView("user_view").as(qb.select().from(user));
export const customersView = mssqlView("customers_view").as(qb.select().from(user).where(eq(user.role, "customer")));

CREATE VIEW [customers_view] AS select ... from [user] where [user].[role] = 'customer';
CREATE VIEW [user_view] AS select ... from [user];

Declaring views with raw SQL

Whenever you need to declare view using a syntax that is not supported by the query builder, you can directly use sql operator and explicitly specify view columns schema.

import { eq, sql } from "drizzle-orm";
import { int, mssqlTable, mssqlView, nvarchar } from "drizzle-orm/mssql-core";

export const users = mssqlTable("users", {
  id: int().identity().primaryKey(),
  name: nvarchar({ length: 256 }),
  cityId: int("city_id"),
});

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)}`);

CREATE VIEW [new_yorkers] AS select * from [users] where [users].[city_id] = 1;

Declaring existing views

When you’re provided with a read only access to an existing view in the database you should use .existing() view configuration, drizzle-kit will ignore and will not generate a create view statement in the generated migration.

import { int, mssqlTable, mssqlView, nvarchar, datetime2 } from "drizzle-orm/mssql-core";

export const user = mssqlTable("user", {
  id: int().identity().primaryKey(),
  name: nvarchar({ length: 256 }),
  email: nvarchar({ length: 256 }),
  password: nvarchar({ length: 256 }),
  role: nvarchar({ length: 256 }).$type<"admin" | "customer">(),
  createdAt: datetime2("created_at"),
  updatedAt: datetime2("updated_at"),
});

export const trimmedUser = mssqlView("trimmed_user", {
  id: int("id"),
  name: nvarchar("name", { length: 256 }),
  email: nvarchar("email", { length: 256 }),
}).existing();

Extended example

All the parameters inside the query will be inlined, instead of being parameterised.

export const newYorkers = mssqlView('new_yorkers')
  .with({
    encryption: true,
    schemaBinding: false,
    viewMetadata: true,
    checkOption: true,
  })
  .as((qb) => {
    const sq = qb.$with('sq').as(
      qb
        .select({
          userId: users.id.as('user_id'),
          cityId: cities.id.as('city_id'),
        })
        .from(users)
        .leftJoin(cities, eq(cities.id, users.homeCity))
        .where(sql`${users.age1} > 18 and ${users.homeCity} = 1`),
    );

    return qb.with(sq).select().from(sq);
  });

CREATE VIEW [new_yorkers]
WITH ENCRYPTION, VIEW_METADATA 
AS
WITH
	[sq] AS (
		SELECT
			[users].[id] AS [user_id],
			[cities].[id] AS [city_id]
		FROM
			[users]
			LEFT JOIN [cities] ON [cities].[id] = [users].[home_city]
		WHERE
			[users].[age1] > 18
			AND [users].[home_city] = 1
	)
SELECT
	[user_id],
	[city_id]
FROM
	[sq]
WITH
	CHECK
OPTION;