Skip to main content
Version: v2.x

Modelling one-to-many table relationships

Introduction

A one-to-many relationship between two tables can be established via a foreign key constraint.

Say we have the following two tables in our database schema:

authors (
id SERIAL PRIMARY KEY,
name TEXT
)

articles (
id SERIAL PRIMARY KEY,
author_id INT
title TEXT
...
)

These two tables are related via a one-to-many relationship. i.e:

  • an author can have many articles
  • an article has one author

Step 1: Set up a table relationship in the database

This one-to-many relationship can be established in the database by:

  1. Adding a foreign key constraint from the articles table to the authors table using the author_id and id columns of the tables respectively.

This will ensure that the value of author_id column in the articles table is present in the id column of the authors table.

Step 2: Set up GraphQL relationships

To access the nested objects via the GraphQL API, create the following relationships:

  • Array relationship, articles from authors table using articles :: author_id -> id
  • Object relationship, author from articles table using author_id -> authors :: id

Query using one-to-many relationships

We can now:

  • fetch a list of authors with their articles:
GraphiQL
Query Variables
Request Headers
  • fetch a list of articles with their author:
GraphiQL
Query Variables
Request Headers

Insert using one-to-many relationships

We can now:

  • insert an author with their articles where the author might already exist (assume unique name for author):
GraphiQL
Query Variables
Request Headers
  • insert articles with their author where the author might already exist (assume unique name for author):
GraphiQL
Query Variables
Request Headers
Note

You can avoid the on_conflict clause if you will never have conflicts.