Creating views with PostgreSQL

Creating views with PostgreSQL and TypeORM

December 6, 2021

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.

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

CREATE TABLE users (

id serial PRIMARY KEY,

email text UNIQUE,

is_email_confirmed boolean

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.

CREATE VIEW confirmed_emails AS

SELECT email FROM users

WHERE is_email_confirmed IS true

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

SELECT * from confirmed_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.

EXPLAIN SELECT * from confirmed_emails

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.

CREATE VIEW view_name AS SELECT 'Lorem ipsum'

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.

CREATE VIEW confirmed_emails (email, domain)

AS SELECT email, split_part(email, '@', 2) FROM users

WHERE is_email_confirmed IS true

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.

CREATE VIEW confirmed_emails

AS SELECT email, split_part(email, '@', 2) AS domain FROM users

WHERE is_email_confirmed IS true

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.

CREATE VIEW confirmed_emails AS

SELECT email FROM users

WHERE is_email_confirmed IS true;

-- Perform operations on the confirmed_emails view

DROP VIEW confirmed_emails;

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.

CREATE TEMPORARY VIEW confirmed_emails AS

SELECT email FROM users

WHERE is_email_confirmed IS true;

-- Perform operations on the confirmed_emails view

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.

CREATE TABLE posts (

id serial PRIMARY KEY,

title text,

content text,

created_at timestamptz DEFAULT now()

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.

CREATE MATERIALIZED VIEW posts_from_yesterday AS

SELECT * FROM posts

WHERE created_at < timestamptz '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.

EXPLAIN SELECT * FROM posts_from_yesterday

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.

REFRESH MATERIALIZED VIEW posts_from_yesterday

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.

import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity()

export class User {

@PrimaryGeneratedColumn()

public id: number;

@Column({ unique: true })

public email: string;

@Column({ unique: true })

public isEmailConfirmed: boolean;

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

import { ViewEntity, ViewColumn } from 'typeorm';

@ViewEntity({

expression: 'SELECT email from users WHERE "isEmailConfirmed" IS true'

export class ConfirmedEmails {

@ViewColumn()

email: string;

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.

import { ViewEntity, ViewColumn } from 'typeorm';

@ViewEntity({

expression: 'SELECT email, split_part(email, \'@\', 2) AS domain from users WHERE "isEmailConfirmed" IS true'

export class ConfirmedEmails {

@ViewColumn()

email: string;

import { ViewColumn, ViewEntity } from 'typeorm';

import { ConfirmedEmails } from './confirmedEmails.entity';

@ViewEntity({

expression: `

SELECT domain, COUNT(distinct domain)

FROM confirmed_emails

GROUP BY domain

dependsOn: [ConfirmedEmails]

export class ConfirmedEmailDomains {

@ViewColumn()

domain: string;

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:

import { ViewColumn, ViewEntity } from 'typeorm';

@ViewEntity({

expression: `

SELECT * FROM posts WHERE created_at < timestamptz 'yesterday'

materialized: true

export class PostsFromYesterday {

@ViewColumn()

domain: string;

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

REFRESH MATERIALIZED VIEW posts_from_yesterday

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.

Creating views with PostgreSQL