Modeling 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

Documentation Explorer

⚲

No Schema Available

• fetch a list of articles with their author:

GraphiQL

Query Variables

Request Headers

Documentation Explorer

⚲

No Schema Available

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

Documentation Explorer

⚲

No Schema Available

• insert articles with their author where the author might already exist (assume unique name for author):

GraphiQL

Query Variables

Request Headers

Documentation Explorer

⚲

No Schema Available

Note

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