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

NestJS SQL

This entry is part 143 of 177 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 column.

articlesTable.ts

Unfortunately, looks intimidating, so let’s break it down. The generic type accepts three arguments:

  • is the data type we receive when selecting articles from the database,
  • is the type we need to provide when creating a new article,
  • 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 . 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

Selecting articles from yesterday

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

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

articles.repository.ts

Creating the view

We can simplify the above code by creating the view.

20240127213806_add_articles_from_yesterday_view.ts

We also need to add to our interface.

database.ts

We can now use the view in our queries.

articles.repository.ts

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 command that returns the execution plan.

We can see that selecting the contents of the 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

Thanks to calling the function, the 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.

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

Materialized views can be useful when we want to cache the results of some complex queries. For example, we could refresh the 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 command.

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

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 >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments