API with NestJS #143. Optimizing queries with views using PostgreSQL and Kysely

January 29, 2024

This entry is part 143 of 168 in the API with NestJS

Some of our SQL queries can become quite complicated. Fortunately, we can create views that act as aliases for the select queries. They have a form of virtual tables with rows and columns that we can select from but can’t insert any data into. We can also use materialized views that store the query results in the database, effectively creating a cached data version. In this article, we learn how to create and use views using PostgreSQL, NestJS, and Kysely.

If you want to know more about the basics of using NestJS with PostgreSQL and Kysely, check out API with NestJS #119. Type-safe SQL queries with Kysely and PostgreSQL

Creating views with Kysely

In the previous parts of this series, when working with Kysely, we’ve created a table for the articles.

Let’s take a closer look at the created_at column.

articlesTable.ts

import { ColumnType, Generated } from 'kysely';

export interface ArticlesTable {

id: Generated<number>;

title: string;

paragraphs: string[];

author_id: number;

scheduled_date?: ColumnType<Date, string | Date, string | Date>;

created_at: ColumnType<Date, string | Date | undefined, string | Date>;

}

Unfortunately, created_at: ColumnType<Date, string | Date | undefined, string | Date> looks intimidating, so let’s break it down. The ColumnType generic type accepts three arguments:

Date is the data type we receive when selecting articles from the database,
string | Date | undefined is the type we need to provide when creating a new article,
string | Date is the data we need to use when updating an existing article.

If you want to know more about handling dates with Kysely, check out API with NestJS #132. Handling date and time in PostgreSQL with Kysely

The most important part above is string | Date | undefined. Providing the creation date when inserting a new article is optional because we set it up to default to the current date.

20231105201749_add_created_at_to_articles.ts

import { Kysely, sql } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.alterTable('articles')

.addColumn('created_at', 'timestamptz', (column) => {

return column.notNull().defaultTo(sql`now()`);

})

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema.alterTable('articles').dropColumn('created_at');

}

Selecting articles from yesterday

To find all articles from yesterday, we can take advantage of the today and yesterday timestamps built into PostgreSQL.

SELECT * FROM articles

WHERE articles.created_at < NOW() - INTERVAL '1 DAY'

AND articles.created_at > NOW() - INTERVAL '2 DAYS'

The most straightforward way of implementing this behavior with Kysely is to use the where function twice.

articles.repository.ts

import { Database } from '../database/database';

import { Article } from './article.model';

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

import { sql } from 'kysely';

@Injectable()

export class ArticlesRepository {

constructor(private readonly database: Database) {}

async getArticlesFromYesterday() {

const databaseResponse = await this.database

.selectFrom('articles')

.where('created_at', '<', sql`NOW() - INTERVAL '1 DAY'`)

.where('created_at', '>', sql`NOW() - INTERVAL '2 DAYS'`)

.selectAll()

.execute();

return databaseResponse.map((articleData) => new Article(articleData));

}

// ...

}

Creating the view

We can simplify the above code by creating the view.

20240127213806_add_articles_from_yesterday_view.ts

import { Migration, sql } from 'kysely';

export const up: Migration['up'] = async (database) => {

await database.schema

.createView('articles_from_yesterday')

.as(

database

.selectFrom('articles')

.selectAll()

.where('created_at', '<', sql`NOW() - INTERVAL '1 DAY'`)

.where('created_at', '>', sql`NOW() - INTERVAL '2 DAYS'`),

)

.execute();

};

export const down: Migration['down'] = async (database) => {

await database.schema.dropView('articles_from_yesterday');

};

We also need to add articles_from_yesterday to our Tables interface.

database.ts

import { ArticlesTable } from '../articles/articlesTable';

import { Kysely } from 'kysely';

import { UsersTable } from '../users/usersTable';

import { AddressesTable } from '../users/addressesTable';

import { CategoriesTable } from '../categories/categoriesTable';

import { CategoriesArticlesTable } from '../categories/categoriesArticlesTable';

import { CommentsTable } from '../comments/commentsTable';

export interface Tables {

articles: ArticlesTable;

articles_from_yesterday: ArticlesTable;

users: UsersTable;

addresses: AddressesTable;

categories: CategoriesTable;

categories_articles: CategoriesArticlesTable;

comments: CommentsTable;

}

export class Database extends Kysely<Tables> {}

We can now use the view in our queries.

articles.repository.ts

import { Database } from '../database/database';

import { Article } from './article.model';

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

@Injectable()

export class ArticlesRepository {

constructor(private readonly database: Database) {}

async getArticlesFromYesterday() {

const databaseResponse = await this.database

.selectFrom('articles_from_yesterday')

.selectAll()

.execute();

return databaseResponse.map((articleData) => new Article(articleData));

}

// ..

}

Materialized views

It is crucial to realize that even though views resemble tables, they are not stored in our database. Let’s prove it using the EXPLAIN command that returns the execution plan.

1	EXPLAIN SELECT * FROM articles_from_yesterday;

We can see that selecting the contents of the articles_from_yesterday view causes the database to query all articles and find the ones matching our filters. We can change this behavior by modifying our migration to create a materialized view.

20231105201749_add_created_at_to_articles.ts

import { Migration, sql } from 'kysely';

export const up: Migration['up'] = async (database) => {

await database.schema

.createView('articles_from_yesterday')

.as(

database

.selectFrom('articles')

.selectAll()

.where('created_at', '<', sql`NOW() - INTERVAL '1 DAY'`)

.where('created_at', '>', sql`NOW() - INTERVAL '2 DAYS'`),

)

.materialized()

.execute();

};

export const down: Migration['down'] = async (database) => {

await database.schema.dropView('articles_from_yesterday');

};

Thanks to calling the materialized() function, the articles_from_yesterday is materialized. This means that PostgreSQL stores a table containing the articles in the memory and does not need to filter them every time we select them.

1	EXPLAIN SELECT * FROM articles_from_yesterday

It’s essential to notice that materialized views don’t update automatically. For it to update, we need to refresh it manually.

1	REFRESH MATERIALIZED VIEW articles_from_yesterday;

Materialized views can be useful when we want to cache the results of some complex queries. For example, we could refresh the articles_from_yesterday every day at midnight to keep it up to date.

Temporary views

Views help simplify queries that are otherwise long and complex. This can be especially useful when writing raw SQL without the help of an ORM or a query builder such as Kysely. We should remember that PostgreSQL automatically stores all the view definitions we create. To remove these views, we can use the DROP VIEW command.

CREATE VIEW articles_from_yesterday AS

SELECT * FROM articles

WHERE articles.created_at < NOW() - INTERVAL '1 DAY'

AND articles.created_at > NOW() - INTERVAL '2 DAYS'

-- Perform operations on the articles_from_yesterday view

DROP VIEW articles_from_yesterday;

Thankfully, PostgreSQL offers a more straightforward solution. We can create temporary views with the TEMPORARY keyword, and PostgreSQL will automatically drop them at the end of the current session.

CREATE TEMPORARY VIEW articles_from_yesterday AS

SELECT * FROM articles

WHERE articles.created_at < NOW() - INTERVAL '1 DAY'

AND articles.created_at > NOW() - INTERVAL '2 DAYS'

-- Perform operations on the articles_from_yesterday view

Summary

Views are helpful when we have complicated queries and need to make them easier to read. They can come in handy in various situations, for example, when changing old tables to new ones. For instance, if we’re getting rid of an old table and switching to a new one, we can use a view as a temporary replacement for the old table. Besides that, we can give access to views to the users who can’t access the tables directly. On the other hand, materialized views can come in handy for caching the data. It can be especially useful when dealing with large datasets or frequently accessed queries.

Thanks to all of the above, views and materialized views in PostgreSQL are valuable tools for simplifying complex queries, transitioning between table structures, and optimizing data access.

Series Navigation<< API with NestJS #142. A video chat with WebRTC and ReactAPI with NestJS #144. Creating CLI applications with the Nest Commander >>