Optimizing your GraphQL API with Postgres

This blogpost introduces Postgres and some of its most popular features that you can use to enhance your Hasura GraphQL API.

• About Postgres
• Postgres features
  • Postgres constraints
  • Postgres views
  • Postgres functions
  • Postgres triggers
  • Postgres indexes
• Conclusion

Postgres (or PostgreSQL) is a general-purpose object-relational database management system that uses and extends the SQL language. Postgres is free and open source and has been developed continuously over the last 30 years. Postgres strives to be reliable, robust and performant.

Postgres supports advanced data types and advanced performance optimization, features that are otherwise only available in expensive commercial database systems.

Postgres has some features that can be used to extend your Hasura GraphQL API. They are described in the following.

Postgres constraints

Postgres constraints are used to define rules for columns in a database table. They ensure that no invalid data is entered into the database.

There are different types of constraints that can be used with Postgres:

Primary key constraints

A PRIMARY KEY is used to identify each row of a table uniquely.

Example: Identify the author’s id as the primary key of the authors table:

CREATE TABLE authors(
  id INT PRIMARY KEY,
  name           TEXT    NOT NULL
);

Foreign key constraints

A foreign key constraint specifies that the values in a column must match the values appearing in a row of another table. Foreign key constraints are used to create relationships between tables.

Example: Define the author_id in the articles table as a foreign key to the id column in the authors table:

CREATE TABLE authors(
  id SERIAL PRIMARY KEY,
  name           TEXT    NOT NULL,
  email          TEXT    UNIQUE
);

CREATE TABLE articles(
  id SERIAL PRIMARY KEY,
  title          TEXT    NOT NULL,
  author_id INTEGER,
  FOREIGN KEY (author_id) REFERENCES authors (id)
);

Not-null constraints

A not-null constraint allows you to specify that a column’s value cannot be null.

Example: Validate that an author’s name cannot be null:

CREATE TABLE authors(
  id SERIAL PRIMARY KEY,
  name           TEXT    NOT NULL
);

Unique constraints

Unique constraints prevent database entries with a duplicate value of the respective column.

Example: Validate that an author’s email is unique:

CREATE TABLE authors(
  id SERIAL PRIMARY KEY,
  name           TEXT    NOT NULL,
  email          TEXT    UNIQUE
);

Check constraints

Check constraints allow you to specify a Boolean expression for one or several columns. This Boolean expression must be satisfied (equal to true) by the column value for the object to be inserted.

Example: Validate that an author’s rating is between 1 and 10:

CREATE TABLE authors(
  id SERIAL PRIMARY KEY,
  name           TEXT    NOT NULL,
  rating         INT     NOT NULL CHECK(rating > 0 AND rating <= 10)
);

Postgres constraints & Hasura

Postgres constraints will help avoid writing custom logic code for basic data validation in Hasura.

Most Postgres constraints (primary key, foreign key, not-null and unique constraints) can be added to Hasura natively when creating tables.

Postgres check constraints can be used as a form of data validation in Hasura and can be added as described here.

Postgres views

A Postgres view is a virtual table in Postgres. It represents the result of a query to one or more underlying tables in Postgres. Views are used to simplify complex queries since these queries are defined once in the view, and can then be directly queried via the same.

Standard views

Example 1: View with authors whose rating is larger than 6:

CREATE VIEW popular_authors AS
  SELECT name, rating
  FROM authors
  WHERE rating > 6;

The created view can now be queried as follows:

SELECT name, rating from popular_authors;

Example 2: View with authors ordered by their rating:

CREATE VIEW authors_ordered_by_rating AS
    SELECT name, rating
    FROM authors
    ORDER BY rating;

The created view can now be queried as follows:

SELECT name, rating from authors_ordered_by_rating;

Materialized views

Compared to the standard view described above, materialized views do store data physically in the database. Materialized views are used if data from complex queries needs to be accessed quickly.

Example: Materialized view with authors whose rating is larger than 6 and who are active, ordered by rating:

CREATE MATERIALIZED VIEW popular_active_authors AS
    SELECT name, rating
    FROM authors
    WHERE rating > 6 AND is_active = TRUE
    ORDER BY rating;

The created materialized view can now be queried as follows:

SELECT name, rating from popular_active_authors;

Refreshing materialized views

Materialized views don’t always have the most recent data. Since the result of a query is stored in a materialized view like in a cache, you need to make sure to refresh it periodically:

REFRESH MATERIALIZED VIEW popular_active_authors;

Materialized views can be refreshed periodically using Postgres triggers.

Postgres views & Hasura

You can use Postgres views with Hasura to expose a subset of fields in a table, as well for using Postgres clauses like ORDER_BY and GROUP_BY.  

After creating a view, you can expose it over your GraphQL API and query it like a normal table.

See here how to create and expose views in Hasura.

Postgres functions

Postgres functions allow you to customize your database schema by defining a set of operations that can include several statements such as declarations, assignments and conditional workflows. Postgres functions are similar to views but allow more procedural computations and can take arguments.

Example: Searching articles:

We can create the following function that we can call later to search articles based on the input text argument search.

CREATE FUNCTION search_articles(search text)
RETURNS SETOF article AS $$
    SELECT *
    FROM article
    WHERE
      title ilike ('%' || search || '%')
      OR content ilike ('%' || search || '%')
$$ LANGUAGE sql STABLE;

Let’s break this function apart:

• Function name: search_articles
• Parameters: there is one parameter where search is the name and text is the type
• Return type: SETOF article
• Function body: Block from SELECT until the end of the WHERE clause
• Language: The response is returned in the sql language

Postgres functions & Hasura

In Hasura, Postgres functions can be used to either encapsulate custom business logic or extend the built-in SQL functions and operators. Postgres functions can be exposed in Hasura’s GraphQL schema. They are typically used for performing custom business logic in the database.

Refer to this page for more use cases and for instructions on how to create and expose Postgres functions in Hasura.

Postgres triggers

Postgres triggers are used to invoke previously defined Postgres functions before or after a specific database event (e.g. INSERT) occurs.

Example 1: Trigger a Postgres function before an article is inserted or updated:

Let’s say we want to check if an author is active before a corresponding article can be inserted or updated. We can do so with the following Postgres function:

CREATE FUNCTION check_author_active()
    RETURNS trigger AS $BODY$
    DECLARE active_author BOOLEAN;
    BEGIN
    SELECT author.is_active INTO active_author FROM "authors" author WHERE author.id = NEW."author_id";
    IF active_author != TRUE THEN
        RAISE EXCEPTION 'Author must be active';
    END IF;
    RETURN NEW;
    END;
    $BODY$ LANGUAGE plpgsql;

Now we want to have this function executed whenever a new article is about to be inserted or updated. We can create a Postgres trigger as follows:

CREATE TRIGGER insert_article BEFORE INSERT OR UPDATE 
ON "articles" FOR EACH ROW EXECUTE PROCEDURE check_author_active();

If someone now tries to insert an article for an author that is not active, the following error will be thrown:

unexpected : Author must be active

Example 2: Refresh a materialized view when an author gets inserted:

Let’s say we want to refresh a materialized view whenever a new author is inserted.

The following Postgres function refreshes a materialized view:

CREATE FUNCTION refresh_materialized_view()
  RETURNS trigger AS $BODY$
  BEGIN
  REFRESH MATERIALIZED VIEW popular_active_authors;
  RETURN NULL;
  END;
  $BODY$ LANGUAGE plpgsql;

Now, to make sure this function gets called whenever a new author is inserted, we can create the following Postgres trigger:

CREATE TRIGGER update_materialized_view AFTER INSERT 
ON "authors" FOR EACH ROW EXECUTE PROCEDURE refresh_materialized_view();

Postgres triggers & Hasura

Postgres triggers can be used as a form of data validation in Hasura and can be added as described here.

Postgres indexes

Postgres indexes are a way of increasing performance on a column that is queried frequently. The concept is similar to the one of an index in a book. It helps accessing the data you’re looking for more quickly by maintaining additional metadata.

Example: Create an index on the column name in the table authors:

Let’s say the database receives a large number of requests of authors being queried by their name, for example:

SELECT * FROM authors WHERE name = 'J.K. Rowling';

We can now create an index on the name column of the authors table:

CREATE INDEX author_name_index ON authors (name);

Since the database is now able to look up the result of these queries more quickly, the performance of these queries will increase significantly.

Postgres indexes & Hasura

Indexes can be used to optimize query performance in Hasura. Refer to this page for information about query performance and how to add Postgres indexes to Hasura.

We've seen how the Hasura GraphQL API can be extended and improved using some of the Postgres features. For more detail about these features, check out the Postgres documentation.