Nest.js Tutorial

Using views with the Drizzle ORM and PostgreSQL

Marcin Wanago
JavaScriptNestJSSQL

Our SQL queries can become more complex as the application we develop grows. To deal with that, PostgreSQL implements views that act as virtual tables with rows and columns from which we can select. Additionally, we can create a cached version of our data using materialized views that store our virtual tables in the database. Unlike regular views, materialized views need to be refreshed to reflect changes in the underlying data.

In this article, we explore the idea of views using Drizzle ORM, PostgreSQL, and NestJS.

Views with the Drizzle ORM

Previously, in this series, we created a schema for storing the articles.

database-schema.ts
1import {
2  serial,
3  text,
4  integer,
5  pgTable,
6  timestamp,
7} from 'drizzle-orm/pg-core';
8 
9export const articles = pgTable('articles', {
10  id: serial('id').primaryKey(),
11  title: text('title').notNull(),
12  paragraphs: text('paragraphs').array().notNull(),
13  authorId: integer('author_id')
14    .references(() => users.id)
15    .notNull(),
16  scheduledDate: timestamp('scheduled_date', {
17    withTimezone: true,
18  }),
19});
20 
21// ...
22 
23export const databaseSchema = {
24  articles,
25};
If you want to learn more about managing dates with the Drizzle ORM, check out API with NestJS #159. Date and time with PostgreSQL and the Drizzle ORM

Selecting articles scheduled today

To get all of the articles scheduled for today with PostgreSQL, we can use the CURRENT_DATE variable.

1SELECT * FROM articles
2WHERE DATE(scheduled_date) = CURRENT_DATE;

We need to use raw SQL code to implement it with the Drizzle ORM.

articles.service.ts
1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { sql } from 'drizzle-orm';
5 
6@Injectable()
7export class ArticlesService {
8  constructor(private readonly drizzleService: DrizzleService) {}
9 
10  getScheduledForToday() {
11    return this.drizzleService.db
12      .select()
13      .from(databaseSchema.articles)
14      .where(
15        sql`DATE(${databaseSchema.articles.scheduledDate}) = CURRENT_DATE`,
16      );
17  }
18 
19  // ...
20}

Creating a view

Instead of the above, we can create a view using the pgView function.

database-schema.ts
1import {
2  serial,
3  text,
4  integer,
5  pgTable,
6  timestamp,
7  pgView,
8} from 'drizzle-orm/pg-core';
9import { sql } from 'drizzle-orm';
10 
11export const articles = pgTable('articles', {
12  id: serial('id').primaryKey(),
13  title: text('title').notNull(),
14  paragraphs: text('paragraphs').array().notNull(),
15  authorId: integer('author_id')
16    .references(() => users.id)
17    .notNull(),
18  scheduledDate: timestamp('scheduled_date', {
19    withTimezone: true,
20  }),
21});
22 
23export const articlesScheduledForToday = pgView(
24  'articles_scheduled_for_today',
25).as((queryBuilder) => {
26  return queryBuilder
27    .select()
28    .from(articles)
29    .where(sql`DATE(${articles.scheduledDate}) = CURRENT_DATE`);
30});
31 
32// ...
33 
34export const databaseSchema = {
35  articles,
36  articlesScheduledForToday,
37  addresses,
38  users,
39  usersAddressesRelation,
40  articlesRelations,
41  categories,
42  categoriesArticles,
43  categoriesArticlesRelations,
44  categoriesRelations,
45  products,
46};

Unfortunately, the Drizzle Kit does not yet support views. Because of that, we need to add the --custom flag when generating the migration and write it ourselves.

1npx drizzle-kit generate --name create-articles-scheduled-for-today-view --custom
0009_create-articles-scheduled-for-today-view.sql
1CREATE VIEW articles_scheduled_for_today AS
2  SELECT * FROM articles
3  WHERE DATE(scheduled_date) = CURRENT_DATE;

To run the above SQL code, we need to execute our migrations.

1npx drizzle-kit migrate

Thanks to that, we can use the view to simplify our code.

articles.service.ts
1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4 
5@Injectable()
6export class ArticlesService {
7  constructor(private readonly drizzleService: DrizzleService) {}
8 
9  getScheduledForToday() {
10    return this.drizzleService.db
11      .select()
12      .from(databaseSchema.articlesScheduledForToday);
13  }
14 
15  // ...
16}

Materialized views

It’s crucial to notice that views resemble tables, but they are not stored in our database by default. We can demonstrate this using the EXPLAIN command, which shows the execution plan.

1EXPLAIN SELECT * FROM articles_scheduled_for_today;

When we select data from the articles_scheduled_for_today view, the database queries all articles and applies the filters to find matching results. We can modify this behavior by using the pgMaterializedView function to create a materialized view instead.

database-schema.ts
1import {
2  serial,
3  text,
4  integer,
5  pgTable,
6  timestamp,
7  pgMaterializedView,
8} from 'drizzle-orm/pg-core';
9import { sql } from 'drizzle-orm';
10 
11export const articles = pgTable('articles', {
12  id: serial('id').primaryKey(),
13  title: text('title').notNull(),
14  paragraphs: text('paragraphs').array().notNull(),
15  authorId: integer('author_id')
16    .references(() => users.id)
17    .notNull(),
18  scheduledDate: timestamp('scheduled_date', {
19    withTimezone: true,
20  }),
21});
22 
23export const articlesScheduledForToday = pgMaterializedView(
24  'articles_scheduled_for_today',
25).as((queryBuilder) => {
26  return queryBuilder
27    .select()
28    .from(articles)
29    .where(sql`DATE(${articles.scheduledDate}) = CURRENT_DATE`);
30});
31 
32// ...
33 
34export const databaseSchema = {
35  articles,
36  articlesScheduledForToday,
37};

We also need to take it into account when creating a migration.

0009_create-articles-scheduled-for-today-view.sql
1CREATE MATERIALIZED VIEW articles_scheduled_for_today AS
2  SELECT * FROM articles
3  WHERE DATE(scheduled_date) = CURRENT_DATE;

Now, PostgreSQL will store a table with articles scheduled for today in the database thanks to articles_scheduled_for_today being a materialized view. Therefore, the database does not need to filter all articles every time we fetch them.

1EXPLAIN SELECT * FROM articles_scheduled_for_today;

A key point to remember is that materialized views don’t update automatically, which means they can contain stale data. To keep it up to date, we need to refresh it manually.

1REFRESH MATERIALIZED VIEW articles_scheduled_for_today;

Besides using raw SQL to achieve this, we can use the Drizzle ORM.

1await this.drizzleService.db.refreshMaterializedView(
2  databaseSchema.articlesScheduledForToday
3);

Materialized views can come in handy when caching the results of complex queries. For example, we could refresh the articles_scheduled_for_today view once a day at midnight to avoid running it often. To do that, we could set up a cron job, for example.

If you want to know more about cron, check out API with NestJS #25. Sending scheduled emails with cron and Nodemailer

Summary

Views are useful for simplifying complex queries and making them easier to manage. They can be particularly helpful in scenarios like transitioning from old tables to new ones. For example, if you’re phasing out an old table, a view can temporarily replace it. Additionally, views can be used to grant users access to specific data without giving them direct access to the underlying tables. Materialized views, on the other hand, are valuable for caching data, which is especially beneficial when working with large datasets that are accessed frequently. Thanks to all of the above, views and materialized views can make managing your database easier and more efficient.