Hasura, GraphQL Backend in the Cloud- Part 2
Tags: graphql, hasura
Published: 2019-07-01 00:08:30

In Part One, you were introduced to the Hasura GraphQL Engine and shown how to deploy and use Hasura on Heroku.

In this instalment, we will continue where we left off and move on to explore the extensions Hasura offers for the standard GraphQL queries and mutations. At the end of this part, we will set the ground for Part Three and learn more about Hasura Authorization and Dynamic Access Control.

So far, you have learned how Hasura auto-generates queries and mutations of the GraphQL schema from your Postgres schema model. Also, it generates a range of possible queries that could be used. For instance, depending on the Primary Key of a table, it generates a query field that accepts an ID and returns a single object based on that ID input parameter. In addition, Hausra detects relationships between database tables and generates those as nested objects respectively.

The recipe query field that is auto-generated by Hasura is as shown. The rest of the query and mutation fields can be checked on Hasura app deployed on Heroku.

```js
recipe(limit: Int offset: Int order_by: [recipe_order_by!] where: recipe_bool_exp): [recipe!]!
fetch data from the table: "recipe"
```
The recipe mutation field is auto-generated as well, and its results are as shown too. The details of the object types created by Hasura can be checked on Hasura app deployed on Heroku.

```js
insert_recipe(objects: [recipe_insert_input!]! on_conflict: recipe_on_conflict): recipe_mutation_response
insert data into the table: "recipe"
```
## GraphQL Mutations

We will explore a few mutations to demonstrate the different extensions Hasura adds to GraphQL standard language.

## Insert Single record

Let’s start by inserting a new ingredient:

```js
mutation insertSaltIngredient {
  insert_ingredient(objects: [{name: "Salt"}]) {
    returning {
      name
    }
  }
}
```

Hasura adds the **objects** type that can hold a single or multiple data records for insertion.

The mutation above creates the **Salt** ingredient and returns the name of the newly inserted ingredient.

```js
{
  "data": {
    "insert_ingredient": {
      "returning": [
        {
          "name": "Salt"
        }
      ]
    }
  }
}
```
## Insert Bulk of records,

To insert ingredient records in bulk you can pass multiple data records inside the **objects** type to the mutation **insertIngredients**:

```js
mutation insertIngredients {
  insert_ingredient(objects: [{name: "White sugar"}, {name: "Garlic"}]) {
    returning {
      name
    }
  }
}
```
## Insert a record with a nested object record at once

You can insert a new record together with a nested object all at once. For instance, I can write a mutation to insert a new **recipe** object labeled Pizza together with a nested object, of type **recipe_ingredients,** in a single mutation.

The assumption here is that the **ingredient** object already exists in the database.

```js
mutation insertRecipe {
  insert_recipe(objects: [{name: "Pizza", description: "Preparing a lovely Margartia Pizza", vegeterian: true, recipe_ingredients: {data: [{ingredient_id: 3, quantity: 2, comments: "Medium tomatoes"}]}}]) {
    returning {
      name
    }
  }
}
```
With a single mutation, you are able to insert a new record in the **recipe** table together with a record in the many-to-many relationship table, the **recipe_ingredient**.

If the ingredient you are inserting inside the recipe_ingredient doesn’t exist in the database, Hasura GraphQL mutations allows you to insert a new **recipe**, together with a new **recipe_ingredient** and a new **ingredient**, all done at once in the same query and handled totally by Hasura!

Check the mutation below:

```js
mutation insertRecipe {
  insert_recipe(objects: [{name: "Pizza", description: "Preparing a lovely Margartia Pizza", vegeterian: true, recipe_ingredients: {data: [{ingredient: {data: {name: "Tomato"}}, quantity: 2, comments: "Medium tomatoes"}]}}]) {
    returning {
      name
    }
  }
}
```
The mutation is inserting a new Pizza recipe together with a new Tomato ingredient and finally inserting the relationship record between the two objects as a new recipe_ingredient object relationship.

## Upsert a record in Postgres database with GraphQL

Upserting a record in the database is to execute a single mutation statement such that, when the record already exists in the database, it gets updated. While, if the record doesn’t exist in the database, it gets created. The upsert operation is based on constraints that we will cover next .

Let’s explore how Hasura converted the table **ingredient** into a GraphQL mutation statement with all the related object types.

```js
insert_ingredient(objects: [ingredient_insert_input!]!on_conflict: ingredient_on_conflict): ingredient_mutation_response
insert data into the table: "ingredient"
```
The **insert_ingredient** mutation accepts both **objects** and **on_conflict** input parameters. We have seen how to use the **objects** parameter to pass the actual data to be inserted inside the database. The **on_conflict** parameter is basically used to upgrade an insert statement into an **upsert** statement to help Hasura make a decision when there is a conflict in the data being inserted.

The **ingredient_on_conflict** object is defined as an object with the following properties:

```js
constraint: ingredient_constraint!
update_columns: [ingredient_column!]
```
The **ingredient_constraint** object is defined as a constraint on the Primary Key of the table ingredients:

```js
Ingredient_pkey
unique or primary key constraint
```
When a new ingredient is inserted into the database:

A conflict occurs on the constraint defined in **constraint** property:n this case, the **ingredient_constraint**. It means we are trying to insert a new record with a Primary Key of ID that already exists in the database table.

Hasura checks the **update_columns** property.

If **update_columns** property is omitted in the **ingredient_constraint**, it updates the record found in the table with all the columns included in the **objects** parameter.

```js
mutation insertSaltIngredient {
  insert_ingredient(objects: [{id: 2, name: "Eggs"}], on_conflict: {constraint: ingredient_pkey}) {
    returning {
      id
      name
    }
  }
}
```
If the property exists, Hasura will perform an update operation and update only those columns listed in the **update_columns** array.

```js
mutation insertSaltIngredient {
  insert_ingredient(objects: [{id: 2, name: "Eggs"}], on_conflict: {constraint: ingredient_pkey, update_columns: [name]}) {
    returning {
      id
      name
    }
  }
}
```

On the other hand, if the **update_columns** property exists and is set to a value of an empty array, the insert operation is ignored.

```js
mutation insertSaltIngredient {
  insert_ingredient(objects: [{id: 2, name: "Eggs"}], on_conflict: {constraint: ingredient_pkey, update_columns: []}) {
    returning {
      id
      name
    }
  }
}
```

## Update a record using a where operator

Hasura offers the **where** operator to filter out records to be updated in the mutation. The mutation below updates the ingredient record with **id = 2** and sets its name to Eggs.

```js
mutation {
  update_ingredient(where: {id: {_eq: 2}}, _set: {name: "Eggs"}) {
    returning {
      id
      name
    }
  }
}
```
## Update a record using a where operator on nested object property

An update mutation can also be used to update a record filtered by a property on one of its nested object properties.
```js
mutation {
  update_recipe(where: {food_category: {id: {_eq: 2}}}, _set: {name: "Update based on food_category"}) {
    returning {
      id
      name
      food_category_id
    }
  }
}
```
The mutation above loops through all recipe records in the database and updates their name column value to “Update based on food_category” where the food_category’s **id = 2.** And the result is:

```js
{
  "data": {
    "update_recipe": {
      "returning": [
        {
          "id": 4,
          "name": "Update based on food_category",
          "food_category_id": 2
        },
        {
          "id": 5,
          "name": "Update based on food_category",
          "food_category_id": 2
        }
      ]
    }
  }
}
```
## Update a record property of type jsonb

Hasura supports updating records with columns of type [jsonb in Postgres](https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/). You can read more on this topic by following this link: [Using Jsob operators](https://docs.hasura.io/1.0/graphql/manual/mutations/update.html#using-jsonb-operators).

## Delete a record using a where operator

To delete a record in Hasura you can execute the delete mutation and filter on the record to be deleted:

```js
mutation {
  delete_recipe(where: {name: {_like: "%za%"}}) {
    returning {
      id
      name
    }
  }
}
```
The mutation above deletes the recipe with name ends with **za**.

## Delete a record using a where operator on nested object property

You may also delete a record filtered through a nested object property as follows:

```js
mutation {
  delete_recipe(where: {food_category: {name: {_eq: "Desserts"}}}) {
    returning {
      id
      name
    }
  }
}
```
The above mutation deletes a recipe record whose food_category name is “Desserts.”

## GraphQL Queries

Now that we’ve built up some data in our Postgres database, let’s explore queries and what Hasura offers in this regard.

## Simple Query

Let’s start by writing a simple query to retrieve all recipes in the database and return only the **id** and **name** of each recipe:

```js
query getAllRecipes {
  recipe {
    id
    name
  }
}
```

The query above returns the following response:

```js
{
  "data": {
    "recipe": [
  	{
        "id": 1,
        "name": "Hamburger Steak with Onions and Gravy"
  	},
  	{
        "id": 2,
        "name": "Butter!"
  	}
	]
  }
}
```

## Query a record and return nested object fields (object relationship)

A typical GraphQL query would be to search for a record and also return data from its nested objects in an object relationship.

```js
query getAllRecipes {
  recipe(where: {id: {_eq: 1}}) {
    id
    name
    food_category {
      id
      name
    }
  }
}
```

This query is for a recipe whose **id = 1** and returns the id and name of the recipe and the id and name of the food_category.

```js
{
  "data": {
    "recipe": [
      {
        "id": 1,
        "name": "Hamburger Steak with Onions and Gravy",
        "food_category": {
          "id": 5,
          "name": "BBQ and Grilled Beef"
        }
      }
    ]
  }
}
```
## Query a record and return nested object fields (array relationship)

A typical GraphQL query is to search for a record and also return data from its nested objects in an array relationship.

```js
query getAllRecipes {
  recipe(where: {id: {_eq: 6}}) {
    id
    name
    recipe_ingredients {
      ingredient {
        name
      }
      quantity
      comments
    }
  }
}
```

The query above returns the recipe with **id = 6** and also returns all the ingredients defined by the **recipe_ingredients** relationship:

```js
{
  "data": {
    "recipe": [
      {
        "id": 6,
        "name": "Butterhorns",
        "recipe_ingredients": [
          {
            "ingredient": {
              "name": "White sugar"
            },
            "quantity": 2,
            "comments": "teaspoons"
          },
          {
            "ingredient": {
              "name": "Milk"
            },
            "quantity": 1,
            "comments": "cups"
          },
          {
            "ingredient": {
              "name": "Butter, softened"
            },
            "quantity": 1,
            "comments": "cup"
          },
          {
            "ingredient": {
              "name": "Eggs"
            },
            "quantity": 3,
            "comments": "eggs"
          }
        ]
      }
    ]
  }
}
```

## Query a record using the Greater Than operator

Let’s say we want to query for a recipe that serves more than four people. Hasura offers an out of the box list of useful operators that you can use inside the GraphQL queries.

```js
{
  recipe(where: {number_of_servings: {_gt: 4}}) {
    id
    name
    number_of_servings
  }
}
```
The query above makes use of the **_gt** operator to filter out all recipes serving more than four persons and requesting in return the recipe id, name and number of servings columns:

```js
{
  "data": {
    "recipe": [
      {
        "id": 6,
        "name": "Butterhorns",
        "number_of_servings": 16
      }
    ]
  }
}
```
Hasura also offers other related [operators](https://docs.hasura.io/1.0/graphql/manual/queries/query-filters.html#greater-than-or-less-than-operators-gt-lt-gte-lte) like: **_gte**,**_lt** and **_lte**.

## Query a record using the Equality operators

Let’s query the Eggs ingredient using the **_eq** operator.

```js
{
  ingredient(where: {name: {_eq: "Eggs"}}) {
    id
    name
  }
}
```
Likewise, you can use the **_neq** operator to exclude some data.

```js
{
  ingredient(where: {name: {_neq: "Eggs"}}) {
    id
    name
  }
}
```
This query returns all ingredients to exclude Eggs.

## Query a record using the List based Search operators

Hasura brings to the GraphQL table many SQL based operators that powers up the GraphQL queries.

For instance, let's query all recipes that belong to food_category 1 and 2.

```js
{
  recipe(where: {food_category: {id: {_in: [1, 2]}}}) {
    id
    name
    food_category {
      id
    }
  }
}
```

The response is as follows:

```js
{
  "data": {
    "recipe": [
      {
        "id": 4,
        "name": "Cajun Seafood Pasta",
        "food_category": {
          "id": 2
        }
      },
      {
        "id": 5,
        "name": "Chicken Parmesan",
        "food_category": {
          "id": 2
        }
      }
    ]
  }
}
```

Notice how the query returns the food_category id field to verify the query sent?

Likewise, Hasura offers the **_nin** operator which functions in the opposite direction to **_in** operator.

## Query a record using the Text Search or pattern operators

Hasura offers the **_like**, **_nlike**, **_ilike**, **_nilike**, **_similar** and **_nsimilar**. These operators are the counterparts for SQL clauses. The **_ilike** operator is not case sensitive, whereas **_like** and **_similar** operators are case sensitive. In addition, you can use the **_similar** operator to do a pattern match searching.

Let’s query for all recipes that contain **chicken** in their names:

```js
{
  recipe(where: {name: {_ilike: "%chicken%"}}) {
    id
    name
    food_category {
      id
    }
  }
}
```

Let’s run a query for all ingredients whose names start with the letter E or B.

```js
{
  ingredient(where: {name: {_similar: "(E|B)%"}}) {
    id
    name
  }
}
```

Visit [Hasura docs](https://docs.hasura.io/1.0/graphql/manual/queries/query-filters.html#filter-or-check-for-null-values) to check the rest of the operators offered.

## Query all recipes and Sort results

Let’s query all recipes and use Hasura **order_by** operator to sort the results by recipe name in an ascending order.

```js
{
  recipe(order_by: [{name: asc}]) {
    id
    name
  }
}
```

The results are as follows:

```js
{
  "data": {
    "recipe": [
      {
        "id": 6,
        "name": "Butterhorns"
      },
      {
        "id": 4,
        "name": "Cajun Seafood Pasta"
      },
      {
        "id": 5,
        "name": "Chicken Parmesan"
      },
      {
        "id": 1,
        "name": "Hamburger Steak with Onions and Gravy"
      }
    ]
  }
}
```

## Query all recipes and Sort results by a Nested Object field

Let’s say we want to query for all recipes and sort the results by the nested object food_category name.

```js
{
  recipe(order_by: [{food_category: {name: desc}}]) {
    id
    name
    food_category {
      name
    }
  }
}
```

The query searches for all recipes and orders the results by the food_category name on each recipe.

```js
{
  "data": {
    "recipe": [
      {
        "id": 4,
        "name": "Cajun Seafood Pasta",
        "food_category": {
          "name": "Seafood"
        }
      },
      {
        "id": 5,
        "name": "Chicken Parmesan",
        "food_category": {
          "name": "Seafood"
        }
      },
      {
        "id": 6,
        "name": "Butterhorns",
        "food_category": {
          "name": "Bread"
        }
      },
      {
        "id": 1,
        "name": "Hamburger Steak with Onions and Gravy",
        "food_category": {
          "name": "BBQ and Grilled Beef"
        }
      }
    ]
  }
}
```

The recipe with food_category name of **Seafood** comes first in the list before a recipe with food_category of **Bread**.

## Query all recipes and return only the first N records

Hasura enriches GraphQL with a pagination mechanism that you can use to limit the number of records returned from a query.

```js
{
  recipe(limit: 2, order_by: [{food_category: {name: asc}}]) {
    id
    name
    food_category {
      name
    }
  }
}
```

The query orders all recipes by the food_category names in an ascending order and returns only the first 2 records using the **limit** operator.

Let’s say we want to run the same query, however this time, we skip the first record and return the next 2 records.

```js
{
  recipe(limit: 2, offset: 1, order_by: [{food_category: {name: asc}}]) {
    id
    name
    food_category {
      name
    }
  }
}
```

The **offset** operator lets you define the number of records to skip. The **limit** and **offset** operators are very useful when you want to introduce paging into your result sets.

## Mixing multiple operators in the same Query

Let’s write a query to ask for all the recipes that serve more than four persons, skip the first two records, return five records, and order the results by recipe name in a descending order. Phew!

```js
{
  recipe(where: {number_of_servings: {_gt: 4}}, limit: 5, offset: 2, order_by: {name: desc}) {
    id
    name
    number_of_servings
  }
}
```

## GraphQL Subscriptions

A GraphQL subscription is a live query where the client receives an event that a field has changed its value on the server. Hasura has full support for [subscriptions](https://docs.hasura.io/1.0/graphql/manual/subscriptions/index.html).

## Event Triggers

Hasura provides [event triggers](https://docs.hasura.io/1.0/graphql/manual/event-triggers/index.html) for the Postgres tables. You can define your own trigger based on a delete, insert, or update operation. For each trigger, you would also define a **[webhook](https://en.wikipedia.org/wiki/Webhook)** that Hasura will call via a POST request upon a delete, insert, or update event on the database tables.

## Authentication / Access Control

Hasura offers a powerful yet easy to use access control system that gives the developer the upper hand in defining a very granular and fine tuned [access control ](https://docs.hasura.io/1.0/graphql/manual/auth/index.html)strategy to control every field in the GraphQL schema which is eventually a table in the database.

Not only that, Hasura also allows you to utilize **dynamic variables**, that we will discuss further down the line, in the access control strategies to give more preciseness and control.

In addition to controlling queries and mutations on GraphQL schema fields, you can be selective in what table columns can be queried, inserted, updated or deleted.

## Basic Access Control

Let’s modify the recipe table by adding the **created_by** column. This column holds the user who actually inserted the recipe.

Refer back to Part one of this series for more details on how to modify a Postgres table managed by Hasura.

Now that the column is added, lets navigate to the **Permissions** tab on the **recipe** table.

![](https://cdn-images-1.medium.com/max/2694/0*WC6xfzmQH7K7uP4P)

By default, Hasura creates for each and every table an admin **Role**. This role has full access on the table and all of its columns.

To add a custom access control strategy, start by creating a new Role. By default every new role has **no access** to the table operations of insert, select, delete and update.

Enter the name of the new role and click on the pencil icon near each column (insert, select, update and delete) to edit the permissions via an access control form.

![](https://cdn-images-1.medium.com/max/2800/0*cocsqaI8BdrNxOQF)

To give the role **user** select permission on the recipe table, you select the **Without any checks** checkbox.

To give the role **user** select permission on some columns of the recipe table, choose the columns for access, under **With access to columns** section.

You can further customize your access control to compare the ID of the user executing the query against a static value or a value coming from a dynamic variable that Hasura has access to. For this, use the **With custom check** checkbox.

You need to follow the same steps highlighted above to give proper access control on other operations like insert, delete and edit. Otherwise, if left with **no access** this means Hasura will not generate the proper object types, queries and mutations corresponding to the other operations.

![](https://cdn-images-1.medium.com/max/2000/0*eByOhOFnqVsyfbg0)

The above expression reads as follows:

Allow a user to read the data row whose **created_by** column value equals to the value stored in the dynamic session variable **X-HASURA-USER-ID** such that the user belongs to the role user.

When in development mode, you can pass the **X-HASURA-USER-ID** and **X-HASURA-ROLE** variables via the Request Headers. While in production mode, you are advised to hook Hasura with an Authorization/Authentication service that knows how to retrieve the **X-HASURA-USER-ID** and **X-HASURA-ROLE** values to man the access control.

To test it, switch to the API Explorer on Hasura, add the two variables as Request Headers and give them some values for testing.

![](https://cdn-images-1.medium.com/max/2214/0*2u-AgS4BFK43inF5)

You can always add more session variables to build more complex access control strategies. What matters is that Hasura gets access to those variables in order to evaluate your access control expressions.

Hasura online docs has an excellent article on constructing roles and permissions based on several practical scenarios. This article can be found under the [Common roles and auth examples](https://docs.hasura.io/1.0/graphql/manual/auth/common-roles-auth-examples.html#common-roles-and-auth-examples) page.

## Advanced Access Control

When in production, you don’t want to send Hasura the session variables in plain text via the Request Headers. Instead, you should authenticate the users of your client-side app with a public authentication service that generates for them an **Authentication Token** when they sign-in to your app.

When the app sends a request to the Hasura Engine to execute a query or mutation, the **Authentication Token** is included in the request headers.

Hasura then retrieves this token, sends its value to an authorization service to verify it, verify its holder and retrieve all the dynamic session variables from the token so that it can freely evaluate the access control expressions.

For the above workflow to function properly, Hasura Engine needs to be configured with a proper authorization webhook that can call to verify the tokens and retrieve the session variables.

For instance, in Part three of this series, when we build a Vue.js app. The app will make use of Auth0 to authenticate users of the app. Once a user is authenticated, an authentication token is generated. Usually, the app has access to the authentication token as it will send it together with the request to Hasura to include it in the request headers.

Hasura will be configured to call an [Auth0 Webhook](https://github.com/hasura/sample-auth-webhook/blob/master/auth0/auth0Handler.js) (developed and provided for free by Hasura) that would in return connect to auth0, verify the token and make sure it belongs to the user sending the original request and return to Hasura engine two session variables populated with the needed information.

The X-Hasura-User-Id will be populated by the Auth0 User ID.

The X-Hasura-Role will be populated by a default value of **user.**

In case the authorization fails, the response would include only the X-Hasura-Role with a value of **anonymous.**

In Part three we will deploy an instance of the Auth0 webhook on Heroku and configure Hasura to use that webhook to authenticate and authorize requests.

## GraphQL Derived Views

As part of the tight integration between GraphQL and Postgres databases, Hasura allows for the creation of SQL Views on top of Postgres database. Once a view is created, Hasura makes sure to convert the view into a field on the Root Query and defines all type objects needed for this new field.

Hasura allows you to include the views as nested objects on other Root Query fields by letting you define relationships between a view and a table.

Let’s define our first view!

Navigate to the **Data** section of the Hasura Admin Console UI.

Click on the **SQL** section on the left hand side of the page.

![](https://cdn-images-1.medium.com/max/2000/0*BdXNZaZCAGd46pPW)

We can type the SQL View in the **SQL** text area.

In this scenario, we are creating a view to only select the recipe id and name.

Notice the checkbox **Track table** is selected by default? When a view or table is tracked, Hasura will make sure that the view or table appear as a fields on the Root Query of the GraphQL schema type. This means, at any given moment you can ask Hasura to stop tracking a view or table, so their fields on the Root Query simply disappear!

Click on **Run** button to create the view.
Notice how the **Tables** section now lists **recipe_name_id** view as new table?

![](https://cdn-images-1.medium.com/max/2000/0*oizG5n5qeyFU1b6a)

Treat this new table like any other created with Hasura. You can now issue the usual GraphQL queries and mutations against this new field, even embed the **recipe_name_id** as a nested object on any other GraphQL type.

Another benefit of using Views as Tables in Hasura, is that you can select who to give access to and to what. Instead of adding permissions on the recipe table, you can now limit the permissions and apply them directly on the new view.

Check out [Derived data in queries](https://docs.hasura.io/1.0/graphql/manual/queries/aggregations.html) for more information on the topic.

## Conclusion

This article covered most of the useful features in Hasura, enabling you to write GraphQL queries and mutations against your Postgres database. You can always refer back to the [Hasura docs](https://docs.hasura.io/1.0/graphql/manual/index.html) for a complete list of its features.

Part three is next. See you on the other side of this article!

Happy Hasuring!

*This post was written by[ Bilal Haidar](https://www.twitter.com/bhaidar), a mentor with [This Dot](http://thisdot.co/labs).*

*Need JavaScript consulting, mentoring, or training help? Check out our list of services at [This Dot Labs](thisdot.co).*