Advantages of Postgres views and using Hasura GraphQL Engine to query them
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 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
article_upvotes
table. - A
article_upvote_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.
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:
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 Keyusername
Text
The article
table
id
Integer (auto increment) Primary Keyauthor_id
Integertitle
Textcontent
Textcreated_at
Timestamp Default now()
article_upvotes
table
user_id
Integerarticle_id
Integeris_upvoted
Boolean Default true- Compound Primary Key:
user_id
andarticle_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_upvote_count AS
SELECT article_id, COUNT(user_id) as total_upvotes
FROM article_upvotes
WHERE is_upvoted = true
GROUP BY article_id;
To run this SQL statement, head to the Data
tab and click on SQL
from the panel on the left.
Note: Ensure that you check the This is a migration
and Track Table
checkbox.
Fetching data from view
TheFetching data from a view or a table is the same, in this case to fetch the total upvotes for a particular article
query fetch_upvotes {
article_upvote_count(where: { article_id: 1 }) {
article_id
total_upvotes
}
}
Fetching total upvotes for an article with id 1
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:
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
upvotes {
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.
In case I have missed anything out or you if you would like to provide feedback on the content in this post, feel free to leave a comment below !