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.

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.

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

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.

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.

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.

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

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:

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:

• Book-author data taken from Wikipedia