GraphQL schema on Postgres with foreign keys and without foreign keys

While modeling a graph using a relational database system, tables can be considered as nodes and the links between nodes are often derived as foreign key constraints over the columns of these tables. The Hasura GraphQL Engine lets you query data by creating relationships using single-column foreign keys, multi-column foreign keys or even without foreign keys on a Postgres database.

Single-column foreign key

Let’s look at an example. The schema contains two tables, user and address.

user_id column in address table is a foreign key to id column of user table. Once we have a schema like this, Hasura GraphQL Engine console suggests relationships over these columns.

Relationship prompts from the Hasura GraphQL Engine console

When we track these relations, the derived GraphQL schema contains the relation names and both tables can be queried in a single query:

Querying over array relationships

Here, addresssByuserId is an “array relationship” suggested by console for the foreign key we created. It’s an array because a user can have many addresses. A “reverse” “object relationship” called userByuserId will be added to address table. It’s an object since there will be only one user for an address.

The object relationship to user from address table

Relationships can have custom names if they are added manually instead of relying on console to auto-generate them. Checkout the docs for more options in customizing and creating relationships.

Relations without foreign keys

In some cases you might want to be flexible in defining relations between tables (nodes), without constraining it with a foreign key first. Hasura GraphQL Engine allows creating relationships manually, without a constraint.

Let’s look at a book-author schema. We have a table with lot of books, but we don’t have author details for all authors. If we add a foreign key for author column in book table to the name column in author table, we‘ll be required to create an entry in author table for all authors for whom we have books for.

Instead, if we do not add a foreign key, we are free to add books for which authors are not in the author table. Using Hasura, we can define a relationship over required columns manually, thereby enabling GraphQL queries over those extra info.

Adding a relationship manually for author_details

Once we add the relationship, we can make a single query to get all books and the corresponding author details, if it’s present.

Fetching author details without a foreign key

In the above example, we can see that author_details are present for William Shakespeare and Anne Frank, but not for Dan Brown, since there is no entry for Dan Brown in the author table.

On the other hand, by defining an “array relationship” from author table to book table, we can query all books for an author. Here is how we can create an array relationship using console:

Adding an array relationship called books

Once we crate this relationship called books on the author table, we can write a GraphQL query that can fetch all books for authors. Here is an example query for getting all books by William Shakespeare:

Getting all books for an author using an array relationship

• Book-author data taken from Wikipedia