ContextQMD
Back to Drizzle Orm

Views

src/content/docs/views.mdx

latest16.0 KB
Original Source

import Tab from '@mdx/Tab.astro'; import Tabs from '@mdx/Tabs.astro'; import IsSupportedChipGroup from '@mdx/IsSupportedChipGroup.astro'; import Callout from '@mdx/Callout.astro'; import Section from '@mdx/Section.astro';

Views

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'SQLite': true, 'MySQL': true, 'SingleStore': false, 'MSSQL': true, 'CockroachDB': true }} /> 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

<Tabs items={['PostgreSQL', 'MySQL', 'SQLite', 'MSSQL', 'CockroachDB']}> <Tab> <Section> ```ts filename="schema.ts" copy {13-14} 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")));
  ```
  ```sql
  CREATE VIEW "user_view" AS SELECT * FROM "user";
  CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
  ```
</Section>
</Tab> <Tab> <Section> ```ts filename="schema.ts" copy {13-14} 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")));
  ```
  ```sql
  CREATE VIEW "user_view" AS SELECT * FROM "user";
  CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
  ```
</Section>
</Tab> <Tab> <Section> ```ts filename="schema.ts" copy {13-14} 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")));
  ```
  ```sql
  CREATE VIEW "user_view" AS SELECT * FROM "user";
  CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
  ```
</Section>
</Tab> <Tab> <Section> ```ts filename="schema.ts" copy {13-14} 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")));
  ```
  ```sql
  CREATE VIEW [user_view] AS (SELECT * FROM "user");
  CREATE VIEW [customers_view] AS (SELECT * FROM "user" WHERE "role" = 'customer');
  ```
</Section>
If you need a subset of columns you can use `.select({ ... })` method in query builder, like this:
<Section> ```ts {4-6} export const customersView = mssqlView("customers_view").as((qb) => { return qb .select({ id: user.id, name: user.name, email: user.email, }) .from(user); }); ``` ```sql CREATE VIEW [customers_view] AS (SELECT "id", "name", "email" FROM "user" WHERE "role" = 'customer'); ``` </Section> </Tab> <Tab> <Section> ```ts filename="schema.ts" copy {13-14} 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")));
  ```
  ```sql
  CREATE VIEW "user_view" AS SELECT * FROM "user";
  CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
  ```
</Section>
</Tab> </Tabs>

You can also declare views using standalone query builder, it works exactly the same way: <Tabs items={['PostgreSQL', 'MySQL', 'SQLite', 'MSSQL', 'CockroachDB']}> <Tab> <Section> ```ts filename="schema.ts" copy {3, 15-16} 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")));
  ```
  ```sql
  CREATE VIEW "user_view" AS SELECT * FROM "user";
  CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
  ```
</Section>
</Tab> <Tab> <Section> ```ts filename="schema.ts" copy {3, 15-16} 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")));
  ```
  ```sql
  CREATE VIEW "user_view" AS SELECT * FROM "user";
  CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
  ```
</Section>
</Tab> <Tab> <Section> ```ts filename="schema.ts" copy {3, 15-16} 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")));
  ```
  ```sql
  CREATE VIEW "user_view" AS SELECT * FROM "user";
  CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
  ```
</Section>
</Tab> <Tab> <Section> ```ts filename="schema.ts" copy {3, 15-16} 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")));
  ```
  ```sql
  CREATE VIEW [user_view] AS (SELECT * FROM "user");
  CREATE VIEW [customers_view] AS (SELECT * FROM "user" WHERE "role" = 'customer');
  ```
</Section>
</Tab> <Tab> <Section> ```ts filename="schema.ts" copy {3, 15-16} 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")));
  ```
  ```sql
  CREATE VIEW "user_view" AS SELECT * FROM "user";
  CREATE VIEW "customers_view" AS SELECT * FROM "user" WHERE "role" = 'customer';
  ```
</Section>
</Tab> </Tabs>

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.

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

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.

ts
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 won't make any difference, yet you can use it for consistency
export const trimmedUser = pgMaterializedView("trimmed_user", {
  id: serial("id"),
  name: text("name"),
  email: text("email"),
}).existing();

Materialized views

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': false, 'SQLite': false, 'MSSQL': false, 'Cockroach': true }} />

According to the official docs, PostgreSQL and CockroachDB have both regular and materialized views.

Materialized views in PostgreSQL and CockroachDB use the rule system like views do, but persist the results in a table-like form. {/* This means that when a query is executed against a materialized view, the results are returned directly from the materialized view, like from a table, rather than being reconstructed by executing the query against the underlying base tables that make up the view. */}

<Tabs items={['PostgreSQL', 'CockroachDB']}> <Tab>

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

You can then refresh materialized views in the application runtime:

ts
await db.refreshMaterializedView(newYorkers);

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

await db.refreshMaterializedView(newYorkers).withNoData();
</Tab> <Tab> <Section> ```ts filename="schema.ts" copy const newYorkers = cockroachMaterializedView('new_yorkers').as((qb) => qb.select().from(users).where(eq(users.cityId, 1))); ``` ```sql CREATE MATERIALIZED VIEW "new_yorkers" AS SELECT * FROM "users"; ``` </Section>

You can then refresh materialized views in the application runtime:

ts
await db.refreshMaterializedView(newYorkers);

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

await db.refreshMaterializedView(newYorkers).withNoData();
</Tab> </Tabs>

Extended example

<Callout type="info" emoji="ℹ️"> All the parameters inside the query will be inlined, instead of replaced by `$1`, `$2`, etc. </Callout>

<Tabs items={['PostgreSQL', 'CockroachDB']}> <Tab>

ts
// 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`);
  });
</Tab> <Tab> ```ts copy // 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); });

</Tab>
</Tabs>