GraphQL & Distributed SQL Tips and Tricks – Aug 10, 2020
Tags: yugabytedb, postgres, hasura, graphql
Published: 2020-08-12 23:24:22

Welcome to this week’s tips and tricks blog where we explore both beginner and advanced topics on how to combine GraphQL and YugabyteDB to develop scalable APIs and services.

First things first, for those of you who might be new to either GraphQL or distributed SQL.

# What’s GraphQL?
GraphQL is a query language for your API, and a server-side runtime for executing queries by using a type system you define for your data. GraphQL allows for fetching, modifying, and subscribing to real-time data updates. Although GraphQL isn’t tied to any specific database or storage engine and is instead backed by your existing code and data, YugabyteDB is a perfect complement to GraphQL, giving you horizontal scalability and global data distribution in a single system. Use cases for GraphQL include microservices and mobile apps. Popular open source GraphQL projects include [Hasura](https://github.com/hasura) and [Apollo](https://github.com/apollographql).

# What’s YugabyteDB?
[YugabyteDB](https://github.com/yugabyte/yugabyte-db) is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. YugabyteDB is PostgreSQL wire compatible and supports GraphQL along with advanced RDBMS features like stored procedures, triggers and UDFs.

Got questions? Make sure to ask them in our [YugabyteDB Slack channel](https://www.yugabyte.com/slack). Ok, let’s dive in…

# Can I use Hasura’s scalar computed fields with YugabyteDB?

Yes. A computed field in Hasura that has an associated SQL function and returns a base type is considered a scalar computed field. To illustrate how this can be done in a distributed SQL database, we’ll be using the [Northwind](https://docs.yugabyte.com/latest/sample-data/northwind/) sample database running on YugabyteDB in our example. The `employees` table has two varchar columns: `first_name` and `last_name`.

Let’s define an SQL function called `employees_full_name`:

```sql
CREATE FUNCTION employees_full_name(employees_row employees)
RETURNS TEXT AS $
  SELECT employees_row.first_name || ' ' || employees_row.last_name
$ LANGUAGE sql STABLE;
```

Next, in the Hasura console let’s add a computed field called `full_name` to the `employees` table using the `employees_full_name` function.

![computed fields hasura](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/1-Hasura-console-a-computed-field-to-a-table-using-the-employees_full_name-function.png)

In the Hasura console let’s add a computed field called `full_name` to the `employees` table

Now we can query the `employees` table and retrieve the values for `full_name`.

![hasura graphql query](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/2-GraphQL-console-employees-table-query.png)

# Does YugabyteDB support Hasura’s Event Triggers so I can be alerted when new data is inserted into a table?

Yes. Hasura has built in support for event triggers which work with YugabyteDB. In this example we’ll trigger an alert to Slack via Zapier whenever a new product is inserted into the `products` table of the Northwind sample database.

The first thing to do is within the Hasura console, navigate to the _Events_ tab and click on the _Create Trigger_ button. Name the trigger `new_product_email` and make sure it will go off when inserts are made to the `products` table.

![Hasura Event Triggers and Distributed SQL with YugabyteDB](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/3-Hasura-Event-Triggers-and-YugabyteDB-Distributed-SQL.png)

Note that in the example above, I am using a Zapier catch webhook that ultimately posts the raw body of the message to Slack. You can obviously have different types of webhooks that do things like send email, post to a spreadsheet, and more. For other options, check out Hasura’s [“Create Event Trigger”](https://hasura.io/learn/graphql/hasura/custom-business-logic/5-create-event-trigger/) tutorial.

Next, insert a row into the `products` table.

![insert a row into the products table distributed sql yugabytedb graphql example](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/4-insert-a-row-into-the-products-table-Hasura-GraphQL-YugabyteDB-example.png)

We should soon see a message like the one shown below post to Slack.

![example of message yugabytedb distributed sql hasura graphql tips and tricks](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/5-see-a-message-like-the-one-shown-yugabytedb-distributedsql-graphql-example.png)

And if we go to _Events > Processed Events_ in the Hasura console, we should see a record of the event trigger.

![Navigate to Processed Events in the Hasura console yugabytedb distributed sql tips and tricks](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/6-Processed-Events-in-the-Hasura-console-yugabytedb-graphql-tips-and-tricks-1024x282.png)

# How can I convert a SQL CONCAT into a GraphQL query that Hasura can use?

In the Northwind sample database we have a table called `territories` with the following snippet of data.

![northwind sample database graphql distributed sql tips tricks](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/7-northwind-sample-table-called-territories-graphql-distributed-sql-tutorial.png)

Let’s say we want to create a new identifier that concantcates `territory_id` and `region_id`. For example:

```sql
select
	CONCAT(territory_id, '-', region_id) as territory_region_id
from
	territories;
```
![tips and tricks graphql hasura distributed sql](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/8-create-a-new-identifier-hasura-graphql-distributed-sql-yugabyte-tips-and-tricks.png)

Unfortunately, in GraphQL you can’t natively `CONCAT` strings. What we can do however is create a view in YugabyteDB and have Hasura track it. For example, within the Hasura console go to _Data > SQL_ and create the following view. (Make sure you check the “Track this” option.)

![hasura graphql distributed sql yugabytedb tips and tricks concat](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/9-nativate-to-Data-then-SQL-hasura-graphql-distributed-sql-tips-and-tricks.png)

Now we can perform a simple select on the `territory_region_id_view` with the Hasura console and retrieve the concatenated results.

![perform a simple select on the territory_region_id_view with the Hasura console and retrieve the concatenated results](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/10-perform-a-simple-select-with-the-Hasura-console-and-retrieve-the-concatenated-results-1024x729.png)

# How can I configure row-level permissions in YugabyteDB using Hasura?

Let’s say we want to limit the access of a given employee so they can only view rows in the `customers` table where the `country` column equals ‘Brazil’. For example:

![query example hasura graphql yugabytedb distributed sql tips tricks](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/11-brazil-query-hasura-graphql-distributed-sql-example.png)

In Hasura, to limit the access to this subset of the rows in the customers table we can use it’s row-level permissions functionality. Row-level permissions are essentially boolean expressions that, when evaluated against any row, determine access to it. These permissions are constructed from the values in columns, session variables and static values to build this boolean expression.

The first thing we need to do is define our access control rules on customers. In the Hasura console navigate to the _Data > Customers > Permissions_ tab. Set up the permissions as shown below.

![define our access control rules permissions hasura console](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/define-our-access-control-rules-permissions-hasura-console-distributed-sql-example-1024x676.png)

Notice that our role has been named `brazil_sales_rep`, our `select` permissions have a custom check that equals `{"country":{"_eq":"Brazil"}}`, and we’ve gone ahead and permitted access to all the columns in the `customers` table.

Now, let’s return to the GraphQL tab and set the following Keys:

![Return to the GraphQL tab and set the following _Keys_ distributed sql yugabytedb tips tricks](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/GraphQL-set-Keys-distributed-sql-yugabytedb-tips-tricks-1024x248.png)

Finally, let’s execute a query against the customers table as `brazil_sales_rep`.

![execute a query hasura graphql yugabytedb tips](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/08/execute-a-query-hasura-graphql-yugabytedb-database-tips-tricks.png)

Notice that the result set is limited to those companies whose `country` equals "Brazil".