视图
你可以通过多种方式在 Drizzle ORM 中声明视图。
你可以声明需要创建的视图，或者声明已存在于数据库中的视图。
你可以使用内联 query builder 语法、独立的 query builder 和原始的 sql 操作符来声明视图。
当使用内联或独立的查询构建器创建视图时，视图列的架构将自动推断，
但如果使用 sql，则必须显式声明视图列的架构。
声明视图
import { pgTable, pgView, serial, text, timestamp } from "drizzle-orm/pg-core";
export const user = pgTable("user", {
id: serial(),
name: text(),
email: text(),
password: text(),
role: text().$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';
import { text, mysqlTable, mysqlView, int, timestamp } from "drizzle-orm/mysql-core";
export const user = mysqlTable("user", {
id: int().primaryKey().autoincrement(),
name: text(),
email: text(),
password: text(),
role: text().$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';
import { integer, text, sqliteView, sqliteTable } from "drizzle-orm/sqlite-core";
export const user = sqliteTable("user", {
id: integer().primaryKey({ autoIncrement: true }),
name: text(),
email: text(),
password: text(),
role: text().$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';
import { mssqlTable, mssqlView, int, text, timestamp } from "drizzle-orm/mssql-core";
export const user = mssqlTable("user", {
id: int(),
name: text(),
email: text(),
password: text(),
role: text().$type<"admin" | "customer">(),
createdAt: timestamp("created_at"),
updatedAt: timestamp("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 [user_view] AS (SELECT * FROM "user");
CREATE VIEW [customers_view] AS (SELECT * FROM "user" WHERE "role" = 'customer');
如果你需要一个列的子集，你可以在查询构建器中使用 .select({ ... }) 方法，如下所示：
export const customersView = mssqlView("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');
import { cockroachTable, cockroachView, int4, text, timestamp } from "drizzle-orm/cockroach-core";
export const user = cockroachTable("user", {
id: int4(),
name: text(),
email: text(),
password: text(),
role: text().$type<"admin" | "customer">(),
createdAt: timestamp("created_at"),
updatedAt: timestamp("updated_at"),
});
export const userView = cockroachView("user_view").as((qb) => qb.select().from(user));
export const customersView = cockroachView("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';
你也可以使用 独立的查询构建器 声明视图，它的工作方式完全相同：
import { pgTable, pgView, serial, text, timestamp, QueryBuilder} from "drizzle-orm/pg-core";
const qb = new QueryBuilder();
export const user = pgTable("user", {
id: serial(),
name: text(),
email: text(),
password: text(),
role: text().$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';
import { text, mysqlTable, mysqlView, int, timestamp, QueryBuilder } from "drizzle-orm/mysql-core";
const qb = new QueryBuilder();
export const user = mysqlTable("user", {
id: int().primaryKey().autoincrement(),
name: text(),
email: text(),
password: text(),
role: text().$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';
import { integer, text, sqliteView, sqliteTable, QueryBuilder } from "drizzle-orm/sqlite-core";
const qb = new QueryBuilder();
export const user = sqliteTable("user", {
id: integer().primaryKey({ autoIncrement: true }),
name: text(),
email: text(),
password: text(),
role: text().$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';
import { int, text, mssqlView, mssqlTable, QueryBuilder } from "drizzle-orm/mssql-core";
const qb = new QueryBuilder();
export const user = mssqlTable("user", {
id: integer().primaryKey(),
name: text(),
email: text(),
password: text(),
role: text().$type<"admin" | "customer">(),
createdAt: integer("created_at"),
updatedAt: integer("updated_at"),
});
export const userView = mssqlView("user_view").as((qb) => qb.select().from(user));
export const customerView = mssqlView("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');
import { cockroachTable, cockroachView, int4, text, timestamp, QueryBuilder} from "drizzle-orm/cockroach-core";
const qb = new QueryBuilder();
export const user = cockroachTable("user", {
id: int4(),
name: text(),
email: text(),
password: text(),
role: text().$type<"admin" | "customer">(),
createdAt: timestamp("created_at"),
updatedAt: timestamp("updated_at"),
});
export const userView = cockroachView("user_view").as(qb.select().from(user));
export const customersView = cockroachView("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';
使用原始 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(),
name: text(),
email: text(),
password: text(),
role: text().$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 和 CockroachDB 具有 常规
和 物化 视图。
PostgreSQL 和 CockroachDB 中的物化视图像视图一样使用规则系统，但以表格形式持久化结果。
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();
const newYorkers = cockroachMaterializedView('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`);
});
// 常规视图
const newYorkers = cockroachView('new_yorkers')
.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 = cockroachMaterializedView('new_yorkers')
.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`);
});
