Skip to main content
Version: v2.x

Postgres: Extend Schema with Views

What are views?

Postgres Views can be used to expose the results of a custom query as a virtual table. Views are not persisted physically i.e. the query defining a view is executed whenever data is requested from the view.

Hasura GraphQL Engine lets you expose views over the GraphQL API to allow querying them using both queries and subscriptions just like regular tables.

Creating views

Views can be created using SQL which can be run in the Hasura Console:

Allowing permissions on views

In order to generate create, update, and delete permissions for a view, it must be insertable. Otherwise, you'll only be able to generate read permissions (select) for the view.

Tracking views

Views can be present in the underlying Postgres database without being exposed over the GraphQL API. In order to expose a view over the GraphQL API, it needs to be tracked.

While creating views from the Data -> SQL page, selecting the Track this checkbox will expose the new view over the GraphQL API right after creation.

You can track any existing views in your database from the Data -> Schema page:

Track views

Use cases

Views are ideal solutions for retrieving some derived data based on some custom business logic. If your custom logic requires any user input, you should use custom SQL functions instead.

Let's look at a few example use cases for views:

Example: Group by and then aggregate

Sometimes we might want to fetch some data derived by aggregating (avg, min, max, etc.) over a group of rows in a table.

Let’s say we want to fetch the average article rating for each author in the following schema:

author(id integer, name text, city text, email text, phone integer, address text)

article(id integer, title text, content text, rating integer, author_id integer)

A view that averages the rating of articles for each author can be created using the following SQL query:

CREATE VIEW author_average_rating AS
SELECT author_id, avg(rating)
FROM article
GROUP BY author_id

Example: Hide certain fields of a table

Sometimes we might have some sensitive information in a table which we wouldn't want to expose.

Let's say, we want to expose the following author table without the fields email, phone and address:

author(id integer, name text, city text, email text, phone integer, address text)

A view that only exposes the non-sensitive fields of the author table can be created using the following SQL query:

CREATE VIEW author_public AS
SELECT id, name, city
FROM author
Additional Resources

PostgresSQL Views - Learn Tutorial.