GraphQL with Postgres views and materialized views
Tags: graphql, postgres, sql, tutorial
Published: 2019-12-19 13:47:05

---
title: GraphQL with Postgres views and materialized views
published: true
date: 2018-08-14 00:00:00 UTC
tags: graphql, postgres, sql, tutorial
canonical_url: https://blog.hasura.io/postgres-views-and-materialized-views-with-graphql-fd75680888b8/
---

### Postgres views are awesome. You can link them to regular tables using relationships and then make a single nested query to fetch related data.

![](https://blog.hasura.io/content/images/downloaded_images/postgres-views-and-materialized-views-with-graphql-fd75680888b8/1-aaBWLnKdOp_6YPzltA21xA.png)

## TL;DR

Here is a summary of what is covered in this post

- **Introduction to views — ** Views are basically virtual tables. There are a lot of advantages to using them.
- **What makes working with views on Hasura GraphQL engine special?**  — The [Hasura GraphQL engine](https://github.com/hasura/graphql-engine) is an open source tool that lets you setup your own GraphQL server over Postgres in minutes. Using the `graphql-engine`, you get the ability to add views as relationships to tables or other views. This makes it extremely simple to fetch all the required data with a single query.
- **Example use-case ** — We put everything we discussed in the earlier sections to build a backend for a blog engine where authors can post articles and other authors can upvote this article. We try to model the database in a way that lets us get all of the data with a single query.

## What are views in SQL?

A view is a named query.

Let’s say that you have a complex query that you do not want to repeat everywhere, you can create a view over this query. Creating a view gives the query a name and now you can SELECT from this view as you would from an ordinary table.

You can use views to represent joined tables or a subset of a table, selecting only the required columns and rows from a table.

Views can be used in almost any place a real table can be used and are very common in SQL database designs.

### Why are views useful?

- Helps encapsulate the details of the structure of your tables behind a consistent interface. It also lets you structure your data in an intuitive way.
- Provides more readability since a named query can set the context much faster than a raw SQL query. You can use a view instead of littering your client code base with complex queries.
- Views have separate permissions and hence can be used to restrict access to the tables such that users are only allowed to see specific rows and columns.
- Avoid making multiple queries and performing complex calculations on the client by specifying the logic in the DB. Querying a view is also much faster than making multiple queries on your client and then crunching the data.

Since views are not REAL tables, you can only perform SELECT queries on them.

Now that we know what views are and why they’re awesome. Let’s take a look at how Hasura makes working with them even better!

## Working with views on the Hasura GraphQL Engine

The Hasura GraphQL engine can be used with any Postgres. You just have to provide a Postgres connection and you instantly get:

- Instant GraphQL APIs to store and retrieve data from tables and views.
- Ability to add a relationship between a view and a table. You can then query the table (or the view) and get the respective data from both the table as well as the view.

## Example use-case

Let’s build a backend for a **blog engine** to see everything mentioned above in action.

### Feature set

- We will have a list of authors and each author will have articles that they can publish.
- Every article can be “upvoted” by other authors.
- We want the ability to fetch the list of articles by an author along with the total upvotes received by each article, in one query.

### Execution overview

- An `author` table to store information about the authors.
- An `article` table which stores the content of each article.
- The upvotes will be tracked in the `upvote` table.
- A `article_upvotes_count` view which sums up all of the “upvotes” an article has received. Add an object relationship from the article table to the view.
- Fetch the articles (along with the upvotes) for an author using the GraphQL APIs provided by Hasura.

Now that we know what needs to be done, let’s get started.

## Getting GraphQL APIs over Postgres

We will use the Hasura GraphQL engine for instantly getting GraphQL APIs over Postgres. Click on the button below to deploy the GraphQL engine to Heroku’s free tier.

[![Hasura on Heroku](https://www.herokucdn.com/deploy/button.svg)](https://heroku.com/deploy?template=https://github.com/hasura/graphql-engine-heroku)<figcaption>Click this button to deploy the GraphQL engine to Heroku</figcaption>

This will deploy the `graphql-engine` to Heroku. You might need to create a Heroku account if you don’t have one. The `graphql-engine` will be running at `https://your-app.herokuapp.com` (replace `your-app` with your heroku app name).

## Console

The Graphql engine comes with an admin UI called the `Console` . You can use the `Console` to build the backend for your application.

It is running at the `/console` endpoint of your `graphql-engine` URL, which is, in this case, `https://your-app.herokuapp.com/console`. The landing page of the `console` looks something like this:

![](https://blog.hasura.io/content/images/downloaded_images/postgres-views-and-materialized-views-with-graphql-fd75680888b8/1-CuBaxCvX-UlNLsSCBRx0uA.png)

## Creating tables

Head to the `Data` tab and click on `Create Table` to create a new table.

Let’s start with the `author` table

- **`user_id`** Integer _Primary Key_
- **`username`** Text

The `article` table

- **`id`** Integer (auto increment) _Primary Key_
- **`author_id`** Integer
- **`title`** Text
- **`content`** Text
- **`created_at`** Timestamp _Default now()_

`upvote` table

- **`user_id`** Integer
- **`article_id`** Integer
- _Compound Primary Key: `user_id` and `article_id`_

## Creating a view

Now that we have our tables created, let’s create our view which shows the total upvotes for each article.

The SQL statement to create this view will be

```
CREATE VIEW article_upvotes_count AS 
SELECT article_id, COUNT(user_id) as total_upvotes
FROM upvote
GROUP BY article_id;
```

To run this SQL statement, head to the `Data` tab and click on `SQL` from the panel on the left.

![](https://blog.hasura.io/content/images/downloaded_images/postgres-views-and-materialized-views-with-graphql-fd75680888b8/1-9QCnxNt_xEKALycn_V9vwQ.png)

## Fetching data from view

Fetching data from a view or a table is the same, in this case to fetch a list of articles and the total upvotes for each article:  

```
query {
  article_upvotes_count {
    article_id
    total_upvotes
  }
}
```

_Fetching total upvotes for all articles_

Alternatively, you can also add this view as a relationship to the `article` table and fetch the `article` details as well the total upvotes in one query.

## Creating a relationship between a table and a view

Head to the `Data` tab and click on `article`. Click on the `Relationship` tab and hit the `Add a manual relationship` button.

In the form that comes up, select the following:

![](https://blog.hasura.io/content/images/downloaded_images/postgres-views-and-materialized-views-with-graphql-fd75680888b8/1-NiaL44f5i6Vs57clAh2YYw.png)

Now, you can query the `article` table for all articles by an author along with the `total_upvotes` for each article.  

```
query fetch_articles_by_author {
  article (where: { author_id: 1 } order_by: ["-upvotes.total_upvotes"]) {      
    id
    author_id
    title
    content
    created_at
    upvote { 
      total_upvotes
    }  
  }
}
```

_**Note** : The `order_by` condition is used to list the articles ordered by the number of upvotes it has received. `-` sign is to list them in descending order and `+` will be for ascending._

And with this we have built a simple backend to a blog engine.