Views
Drizzle ORM에서 뷰를 선언하는 방법은 여러 가지가 있습니다.
생성해야 하는 뷰를 선언할 수도 있고, 데이터베이스에 이미 존재하는 뷰를 선언할 수도 있습니다.
인라인 query builder 문법, standalone query builder, 그리고 raw sql 연산자를 사용하여 뷰 문을 선언할 수 있습니다.
인라인 또는 standalone query builder로 뷰를 생성할 때는 뷰 컬럼 스키마가 자동으로 추론되지만,
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');
컬럼의 일부만 필요한 경우 query builder에서 .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';
standalone query builder를 사용하여 뷰를 선언할 수도 있으며, 동일한 방식으로 작동합니다:
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';
raw SQL로 뷰 선언하기
query builder에서 지원하지 않는 문법을 사용하여 뷰를 선언해야 하는 경우,
sql 연산자를 직접 사용하고 뷰 컬럼 스키마를 명시적으로 지정할 수 있습니다.
// regular view
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)}`);
// materialized view
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"),
});
// regular view
export const trimmedUser = pgView("trimmed_user", {
id: serial("id"),
name: text("name"),
email: text("email"),
}).existing();
// materialized view는 차이가 없지만, 일관성을 위해 사용할 수 있습니다
export const trimmedUser = pgMaterializedView("trimmed_user", {
id: serial("id"),
name: text("name"),
email: text("email"),
}).existing();
Materialized 뷰
공식 문서에 따르면 PostgreSQL과 CockroachDB는 regular 뷰와
materialized 뷰를 모두 가지고 있습니다.
PostgreSQL과 CockroachDB의 Materialized 뷰는 뷰처럼 rule 시스템을 사용하지만, 결과를 테이블과 유사한 형태로 저장합니다.
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";
애플리케이션 런타임에서 materialized 뷰를 새로고침할 수 있습니다:
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";
애플리케이션 런타임에서 materialized 뷰를 새로고침할 수 있습니다:
await db.refreshMaterializedView(newYorkers);
await db.refreshMaterializedView(newYorkers).concurrently();
await db.refreshMaterializedView(newYorkers).withNoData();
확장 예제
쿼리 내의 모든 파라미터는 $1, $2 등으로 대체되지 않고 인라인 처리됩니다.
// regular view
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`);
});
// materialized view
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`);
});
// regular view
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`);
});
// materialized view
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`);
});
