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_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:
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.
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
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
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