GraphQL with Postgres views and materialized views

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

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 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.

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!

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.

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.

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.

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).

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:

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

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.

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.

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:

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.