API with NestJS #160. Using views with the Drizzle ORM and PostgreSQL

August 5, 2024

This entry is part 160 of 172 in the API with NestJS

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

import {

serial,

text,

integer,

pgTable,

timestamp,

} from 'drizzle-orm/pg-core';

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

paragraphs: text('paragraphs').array().notNull(),

authorId: integer('author_id')

.references(() => users.id)

.notNull(),

scheduledDate: timestamp('scheduled_date', {

withTimezone: true,

}),

});

// ...

export const databaseSchema = {

articles,

};

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.

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

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

articles.service.ts

import { Injectable } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { sql } from 'drizzle-orm';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

getScheduledForToday() {

return this.drizzleService.db

.select()

.from(databaseSchema.articles)

.where(

sql`DATE(${databaseSchema.articles.scheduledDate}) = CURRENT_DATE`,

);

}

// ...

}

Creating a view

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

database-schema.ts

import {

serial,

text,

integer,

pgTable,

timestamp,

pgView,

} from 'drizzle-orm/pg-core';

import { sql } from 'drizzle-orm';

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

paragraphs: text('paragraphs').array().notNull(),

authorId: integer('author_id')

.references(() => users.id)

.notNull(),

scheduledDate: timestamp('scheduled_date', {

withTimezone: true,

}),

});

export const articlesScheduledForToday = pgView(

'articles_scheduled_for_today',

).as((queryBuilder) => {

return queryBuilder

.select()

.from(articles)

.where(sql`DATE(${articles.scheduledDate}) = CURRENT_DATE`);

});

// ...

export const databaseSchema = {

articles,

articlesScheduledForToday,

addresses,

users,

usersAddressesRelation,

articlesRelations,

categories,

categoriesArticles,

categoriesArticlesRelations,

categoriesRelations,

products,

};

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.

1	npx drizzle-kit generate --name create-articles-scheduled-for-today-view --custom

0009_create-articles-scheduled-for-today-view.sql

CREATE VIEW articles_scheduled_for_today AS

SELECT * FROM articles

WHERE DATE(scheduled_date) = CURRENT_DATE;

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

1	npx drizzle-kit migrate

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

articles.service.ts

import { Injectable } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

getScheduledForToday() {

return this.drizzleService.db

.select()

.from(databaseSchema.articlesScheduledForToday);

}

// ...

}

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.

1	EXPLAIN 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

import {

serial,

text,

integer,

pgTable,

timestamp,

pgMaterializedView,

} from 'drizzle-orm/pg-core';

import { sql } from 'drizzle-orm';

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

paragraphs: text('paragraphs').array().notNull(),

authorId: integer('author_id')

.references(() => users.id)

.notNull(),

scheduledDate: timestamp('scheduled_date', {

withTimezone: true,

}),

});

export const articlesScheduledForToday = pgMaterializedView(

'articles_scheduled_for_today',

).as((queryBuilder) => {

return queryBuilder

.select()

.from(articles)

.where(sql`DATE(${articles.scheduledDate}) = CURRENT_DATE`);

});

// ...

export const databaseSchema = {

articles,

articlesScheduledForToday,

};

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

0009_create-articles-scheduled-for-today-view.sql

CREATE MATERIALIZED VIEW articles_scheduled_for_today AS

SELECT * FROM articles

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.

1	EXPLAIN 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.

1	REFRESH MATERIALIZED VIEW articles_scheduled_for_today;

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

await this.drizzleService.db.refreshMaterializedView(

databaseSchema.articlesScheduledForToday

);

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.

Series Navigation<< API with NestJS #159. Date and time with PostgreSQL and the Drizzle ORMAPI with NestJS #161. Generated columns with the Drizzle ORM and PostgreSQL >>