GraphQL schema on Postgres with foreign keys and without foreign keys
Tags: graphql, postgres, webdev, tutorial
Published: 2019-12-19 12:25:39

---
title: GraphQL schema on Postgres with foreign keys and without foreign keys
published: true
date: 2018-08-16 00:00:00 UTC
tags: graphql, postgres, webdev, tutorial
canonical_url: https://blog.hasura.io/graphql-schema-on-postgres-with-foreign-keys-and-without-foreign-keys-95f6b2715478/
---

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`.

{% gist https://gist.github.com/shahidhk/f0316b12972694e0255c747f532be8d3.js %}

`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.

![](https://blog.hasura.io/content/images/downloaded_images/graphql-schema-on-postgres-with-foreign-keys-and-without-foreign-keys-95f6b2715478/1-QZejkm3ukh7gbg_plj5NVQ.png)<figcaption>Relationship prompts from the Hasura GraphQL Engine console</figcaption>

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

![](https://blog.hasura.io/content/images/downloaded_images/graphql-schema-on-postgres-with-foreign-keys-and-without-foreign-keys-95f6b2715478/1-Ez6DgzYJnsKut-ioSjmiyw.png)<figcaption>Querying over array relationships</figcaption>

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.

![](https://blog.hasura.io/content/images/downloaded_images/graphql-schema-on-postgres-with-foreign-keys-and-without-foreign-keys-95f6b2715478/1-zJNUB0v__qzTOFUbJV63bQ.png)<figcaption>The object relationship to user from address table</figcaption>

Relationships can have custom names if they are added manually instead of relying on console to auto-generate them. Checkout [the docs](https://docs.hasura.io/1.0/graphql/manual/schema/relationships.html) 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.

{% gist https://gist.github.com/shahidhk/b1b620c3dcfd68f3de7d9ed8c253ce3d.js %}

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.

![](https://blog.hasura.io/content/images/downloaded_images/graphql-schema-on-postgres-with-foreign-keys-and-without-foreign-keys-95f6b2715478/1-67WaRY-gxKi70q3z-KYhAQ.png)<figcaption>Adding a relationship manually for author_details</figcaption>

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

![](https://blog.hasura.io/content/images/downloaded_images/graphql-schema-on-postgres-with-foreign-keys-and-without-foreign-keys-95f6b2715478/1-CZqLXqVf7LA9VFe5Lq_rjA.png)<figcaption>Fetching author details without a foreign key</figcaption>

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:

![](https://blog.hasura.io/content/images/downloaded_images/graphql-schema-on-postgres-with-foreign-keys-and-without-foreign-keys-95f6b2715478/1-OmqX-Kzm45V0mrj1m7KiXA.png)<figcaption>Adding an array relationship called books</figcaption>

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:

![](https://blog.hasura.io/content/images/downloaded_images/graphql-schema-on-postgres-with-foreign-keys-and-without-foreign-keys-95f6b2715478/1-022i1xh04rD1SttbPFZXwQ.png)<figcaption>Getting all books for an author using an array relationship</figcaption>

- Book-author data taken from Wikipedia