视图 (WIP)

⚠️

视图目前只在 drizzle-orm 中实现，drizzle-kit 尚不支持视图。 您可以查询数据库中已有的视图，但是这些视图不会被添加到 drizzle-kit 迁移或 db push 中。

视图声明

使用 Drizzle ORM，您可以通过几种方式声明视图。

您可以声明需要创建的视图，也可以声明数据库中已存在的视图。

您可以使用内联的 查询构建器 语法、独立的查询构建器 语法或原始的 SQL 操作符来声明视图语句。

当使用内联或独立的查询构建器创建视图时，将自动推断视图列的模式，但使用 SQL 语法时，您需要显式声明视图的列模式。

声明视图

PostgreSQL
MySQL
SQLite
schema.ts 
import { pgTable, pgView, serial, text, timestamp } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  email: text("email"),
  password: text("password"),
  role: text("role").$type<"admin" | "customer">(),
  createdAt: timestamp("created_at"),
  updatedAt: timestamp("updated_at"),
});

export const userView = pgView("user_view").as((qb) => qb.select().from(user));
export const customersView = pgView("customers_view").as((qb) => qb.select().from(user).where(eq(user.role, "customer")));
CREATE VIEW "user_view" AS SELECT * FROM "user";
CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
schema.ts 
import { text, mysqlTable, mysqlView, int, timestamp } from "drizzle-orm/mysql-core";

export const user = mysqlTable("user", {
  id: int("id").primaryKey().autoincrement(),
  name: text("name"),
  email: text("email"),
  password: text("password"),
  role: text("role").$type<"admin" | "customer">(),
  createdAt: timestamp("created_at"),
  updatedAt: timestamp("updated_at"),
});

export const userView = mysqlView("user_view").as((qb) => qb.select().from(user));
export const customersView = mysqlView("customers_view").as((qb) => qb.select().from(user).where(eq(user.role, "customer")));
CREATE VIEW "user_view" AS SELECT * FROM "user";
CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
schema.ts 
import { integer, text, sqliteView, sqliteTable } from "drizzle-orm/sqlite-core";

export const user = sqliteTable("user", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name"),
  email: text("email"),
  password: text("password"),
  role: text("role").$type<"admin" | "customer">(),
  createdAt: integer("created_at"),
  updatedAt: integer("updated_at"),
});

export const userView = sqliteView("user_view").as((qb) => qb.select().from(user));
export const customersView = sqliteView("customers_view").as((qb) => qb.select().from(user).where(eq(user.role, "customer")));
CREATE VIEW "user_view" AS SELECT * FROM "user";
CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';

如果您只需要部分列，可以在查询构建器中使用 .select({ ... }) 方法，如下所示：

export const customersView = pgView("customers_view").as((qb) => {
  return qb
    .select({
      id: user.id,
      name: user.name,
      email: user.email,
    })
    .from(user);
});
CREATE VIEW "customers_view" AS SELECT "id", "name", "email" FROM "user" WHERE "role" = 'customer';

您还可以使用 独立的查询构建器 来声明视图，工作方式完全相同：

PostgreSQL
MySQL
SQLite
schema.ts 
import { pgTable, pgView, serial, text, timestamp, QueryBuilder} from "drizzle-orm/pg-core";

const qb = new QueryBuilder();

export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  email: text("email"),
  password: text("password"),
  role: text("role").$type<"admin" | "customer">(),
  createdAt: timestamp("created_at"),
  updatedAt: timestamp("updated_at"),
});

export const userView = pgView("user_view").as(qb.select().from(user));
export const customersView = pgView("customers_view").as(qb.select().from(user).where(eq(user.role, "customer")));
CREATE VIEW "user_view" AS SELECT * FROM "user";
CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
schema.ts 
import { text, mysqlTable, mysqlView, int, timestamp, QueryBuilder } from "drizzle-orm/mysql-core";

const qb = new QueryBuilder();

export const user = mysqlTable("user", {
  id: int("id").primaryKey().autoincrement(),
  name: text("name"),
  email: text("email"),
  password: text("password"),
  role: text("role").$type<"admin" | "customer">(),
  createdAt: timestamp("created_at"),
  updatedAt: timestamp("updated_at"),
});

export const userView = mysqlView("user_view").as(qb.select().from(user));
export const customersView = mysqlView("customers_view").as(qb.select().from(user).where(eq(user.role, "customer")));
CREATE VIEW "user_view" AS SELECT * FROM "user";
CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
schema.ts 
import { integer, text, sqliteView, sqliteTable, QueryBuilder } from "drizzle-orm/sqlite-core";

const qb = new QueryBuilder();

export const user = sqliteTable("user", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name"),
  email: text("email"),
  password: text("password"),
  role: text("role").$type<"admin" | "customer">(),
  createdAt: integer("created_at"),
  updatedAt: integer("updated_at"),
});

export const userView = sqliteView("user_view").as((qb) => qb.select().from(user));
export const customerView = sqliteView("customers_view").as((qb) => qb.select().from(user).where(eq(user.role, "customer")));
CREATE VIEW "user_view" AS SELECT * FROM "user";
CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';

使用原始 SQL 声明视图

当需要使用查询构建器不支持的语法来声明视图时，可以直接使用 sql 操作符，并显式指定视图列的模式。

// 常规视图
const newYorkers = pgView('new_yorkers', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  cityId: integer('city_id').notNull(),
}).as(sql`select * from ${users} where ${eq(users.cityId, 1)}`);

// 材料化视图
const newYorkers = pgMaterializedView('new_yorkers', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  cityId: integer('city_id').notNull(),
}).as(sql`select * from ${users} where ${eq(users.cityId, 1)}`);

声明已存在的视图

如果您只有对数据库中现有视图的只读访问权限，应使用 .existing() 视图配置，drizzle-kit 将忽略并不会在生成的迁移中生成 create view 语句。

export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  email: text("email"),
  password: text("password"),
  role: text("role").$type<"admin" | "customer">(),
  createdAt: timestamp("created_at"),
  updatedAt: timestamp("updated_at"),
});

// 常规视图
export const trimmedUser = pgView("trimmed_user", {
  id: serial("id"),
  name: text("name"),
  email: text("email"),
}).existing();

// 材料化视图不会有任何区别，但是可以用于保持一致性
export const trimmedUser = pgMaterializedView("trimmed_user", {
  id: serial("id"),
  name: text("name"),
  email: text("email"),
}).existing();

材料化视图

PostgreSQL
MySQL
SQLite

根据官方文档，PostgreSQL 提供标准的 常规 和 材料化 视图。

PostgreSQL 中的材料化视图使用与视图相同的规则系统，但将结果以表的形式持久化。

这意味着，当对材料化视图执行查询时，结果直接从材料化视图返回，就像从表中返回一样，而不是通过对构成视图的基础表执行查询重构结果。

Drizzle ORM 原生支持 PostgreSQL 的材料化视图：

schema.ts 
const newYorkers = pgMaterializedView('new_yorkers').as((qb) => qb.select().from(users).where(eq(users.cityId, 1)));
CREATE MATERIALIZED VIEW "new_yorkers" AS SELECT * FROM "users";

您可以在应用程序运行时刷新材料化视图：

await db.refreshMaterializedView(newYorkers);

await db.refreshMaterializedView(newYorkers).concurrently();

await db.refreshMaterializedView(newYorkers).withNoData();

扩展示例

ℹ️

查询中的所有参数都会被内联，而不是使用 $1，$2 等替换。

// 常规视图
const newYorkers = pgView('new_yorkers')
  .with({
    checkOption: 'cascaded',
    securityBarrier: true,
    securityInvoker: true,
  })
  .as((qb) => {
    const sq = qb
      .$with('sq')
      .as(
        qb.select({ userId: users.id, cityId: cities.id })
          .from(users)
          .leftJoin(cities, eq(cities.id, users.homeCity))
          .where(sql`${users.age1} > 18`),
      );
    return qb.with(sq).select().from(sq).where(sql`${users.homeCity} = 1`);
  });

// 材料化视图
const newYorkers2 = pgMaterializedView('new_yorkers')
  .using('btree')
  .with({
    fillfactor: 90,
    toast_tuple_target: 0.5,
    autovacuum_enabled: true,
    ...
  })
  .tablespace('custom_tablespace')
  .withNoData()
  .as((qb) => {
    const sq = qb
      .$with('sq')
      .as(
        qb.select({ userId: users.id, cityId: cities.id })
          .from(users)
          .leftJoin(cities, eq(cities.id, users.homeCity))
          .where(sql`${users.age1} > 18`),
      );
    return qb.with(sq).select().from(sq).where(sql`${users.homeCity} = 1`);
  });
Benchmarks
Extension
Studio
Studio Package
Turso
🚀 Drizzle is giving you 10% off Turso Scaler and Pro for 1 Year 🚀
Payload
Xata
Neon
Nuxt
Hydra
Become a Gold Sponsor