How to Setup a Computed Field in Hasura

Why Computed Fields

There are times where adding a second table as a SQL View makes sense. In the context of Hasura, this generally applies to when you need to apply permissions. You cannot currently use computed fields in permission access, however you can control which role can access which computed field.

Our example will contain a users table and a posts table. We will add a post_count to the users table. The user won't need access to the posts aggregate calls for other users, we can leverage a computed field to add and allow any user to see post_count of other users.

Additionally you can query by computed field. So in our case you could find all users that had more than 0 posts. This would not be possible with aggregate.

Overall computed fields allow you to extend a table schema where a user may not have permissions to access the underlying values, or you need to query/sort by the computed field.

The Data Structure

The initial data structure will be for demonstration purposes. We have a users table with an id and username. The posts table will contain some basic information, and hold a reference to the user_id who owns the post.

users
- id
- username

posts
- id
- title
- body
- user_id

Creating a SQL Function

Lets walk through the structure of this SQL function. The first

CREATE OR REPLACE FUNCTION

We want to create a function if it doesn't exist, otherwise we'll replace it. The benefit of this is future migrations become easier when you need to replace existing functions especially if they already existed prior to using Hasura.

CREATE OR REPLACE FUNCTION users_posts_count(user_row users)

Now we name the function called users_posts_count name it whatever yow want. Then we name the variable for accessing the data row the function will be called with. We called it user_row since the function will get called with an individual user row. Finally we need to specify the table that this function will apply to. In our case it will be on the users table.

RETURNS BIGINT AS

Now we need to specify the type of value that this will return. Remember this is going into a field, so we need to specify a consistent return type. In our case it will return a BIGINT.

$$
  SELECT count(1) FROM posts WHERE user_id = user_row.id;
$$

The $$ just outline where the SQL function will go. Our query will select the count of all posts where user_id is equal to the user_row.id that got passed in.

LANGUAGE sql STABLE;

Finally we specify that it's a STABLE function. Basically meaning it's not going to modify the database when it is called. This allows for Postgres to optimize this function.

The full function together looks like this.

CREATE OR REPLACE FUNCTION users_posts_count(user_row users)
RETURNS BIGINT AS $$
  SELECT count(1) FROM posts WHERE user_id = user_row.id;
$$ LANGUAGE sql STABLE;

Once our function is all ready we need to apply the function as a migration to the DB. This migration will then additionally be saved off for applying to your other environments.

Add the Field

Go to the users table, visit the Computed Fields section and give the field a name. Our field name will be post_count. Then select our function we created from the dropdown. And click Save.

Query and Sort

query GetUsers {
  users(where: { post_count: { _gte: 0 } }) {
    post_count
    id
    username
  }
}

query GetUsers {
  users(order_by: { post_count: asc }) {
    post_count
    id
    username
  }
}

Ending

Computed fields add a ton of flexibility and possibilities without having to add more tables, or views. Further more as they are invoked with a single row the SQL queries that need to be written can be simpler to write rather than complex GROUP BY queries.