Creating views with PostgreSQL and TypeORM

In PostgreSQL, views act as virtual tables. Although they have rows and columns, we can’t insert any data into them manually. Instead, PostgreSQL runs an underlying query when we refer to the view.

Creating views with PostgreSQL

Let’s create a table of users to use it in a view.

Now, let’s define a view that contains all confirmed emails. To do that, we need to use the  CREATE VIEW command followed by a query that describes our view.

When we select rows from the above view, we see a table of emails.

The crucial thing to understand is that even though views resemble tables, they are not stored in the database. We can easily prove that with the EXPLAIN command that returns the execution plan. With it, it shows us how PostgreSQL scans the table.

Above, we can see that running SELECT * from confirmed_emails caused the database to query all users and filter out those who don’t have their email confirmed.

Managing columns included in the view

By default, PostgreSQL deduces the columns of the view from the query. In the above example, we’ve used SELECT email FROM users, and because of that, our view only has one column called email.

We need to be careful when structuring queries used in our views. Unfortunately, some queries can produce unexpected results.

The above query results in creating a column called ?column?.

To deal with the above problems, we can provide a list of column names we want PostgreSQL to use.

Above, we’re using the split_part function that splits text on a given separator.

PostgreSQL knows that the second column is called the domain, thanks to providing a list of column names.

Another way to achieve the above outcome would be to use the AS keyword.

Temporary views

Views can help simplify queries that would be lengthy and difficult to read otherwise. We need to keep in mind that, by default, PostgreSQL keeps the definitions of all of the views we’ve created. We can use the DROP VIEW command to clean up after our queries.

Fortunately, PostgreSQL allows for a more elegant solution. With the TEMPORARY keyword, we can create views that PostgreSQL automatically drops at the end of the current session.

If our view refers to temporary views or tables, it becomes a temporary view out of the box even without the TEMPORARY keyword.

Materialized views

By default, views don’t persist data into the database. This behavior changes when we create a materialized view.

Let’s create a table of posts that keeps the creation date.

If you want to know more about dates, check out Managing date and time with PostgreSQL and TypeORM

Having the above table, we can create a materialized view that holds all of the posts from yesterday.

Thanks to the above code, the posts_from_yesterday view contains all of the posts from yesterday. PostgreSQL stores a table of yesterday’s posts in the database because posts_from_yesterday is a materialized view. Therefore, we don’t need to filter all posts every time we fetch them.

The crucial thing is that materialized views don’t update automatically. Because of that, there is a risk they contain outdated data. For the materialized view to update, we need to refresh it manually.

Because of that, materialized views might come in handy when we want to cache the results of some complex queries. For example, we could refresh the posts_from_yesterday view once a day to keep it up to date.

Creating views with TypeORM

To create views with TypeORM, let’s define a table of users first.

To define a view with TypeORM, we need to use the @ViewEntity() and @ViewColumn() decorators.

Above, we define the view by passing the expression as a string. Instead, we can also use a query builder.

Configuring dependencies

Our views can refer to other views. To ensure that TypeORM runs migrations in the right order, we can use the dependsOn property.

When we do the above, PostgreSQL counts the domains used by our users.

Materialized views

The official TypeORM documentation doesn’t mention anything about materialized views. However, TypeORM supports materialized views.

To create a materialized view with TypeORM, we need to use the materialized: true flag:

Unfortunately, to refresh the view, we need to use the following query manually:

Summary

In this article, we’ve gone through various types of views both through SQL and TypeORM. Views can come in handy when we have a lot of complex queries and want to make them more readable. Also, we can use views to help us refactor existing tables. For example, if we’re going to deprecate an old table and move to a new one, we can create a view in the place of the old table for some time. Also, we can give access to views to the users while the tables are not accessible directly. All of the above make views a tool worth knowing.