Modelling one-to-one table relationships¶

Introduction¶

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

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

author (
  id SERIAL PRIMARY KEY,
  name TEXT
)

passport_info (
  id SERIAL PRIMARY KEY,
  owner_id INT NOT NULL
  passport_number TEXT
  ...
)

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

• an author can have one passport_info
• a passport_info has one owner

Step 1: Set up a table relationship in the database¶

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

1. Adding a foreign key constraint from the passport_info table to the author table using the owner_id and id columns of the tables respectively
2. Adding a unique constraint to the owner_id column for the passport_info table

This will ensure that the value of the owner_id column in passport_info table is present in the id column of the author table and there will be only one row with a particular owner_id.

Step 2: Set up GraphQL relationships¶

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

• Object relationship, passport_info from the author table using id -> passport_info :: owner_id
• Object relationship, owner from the passport_info table using owner_id -> author :: id

Query using one-to-one relationships¶

We can now:

• fetch a list of authors with their passport_info:

  query {
    author {
      id
      name
      passport_info {
        id
        passport_number
      }
    }
  }
  

  query { author { id name passport_info { id passport_number } } }

  { "data": { "author": [ { "id": 1, "name": "Justin", "passport_info": { "id": 1, "passport_number": "987456234" } }, { "id": 2, "name": "Beltran", "passport_info": { "id": 2, "passport_number": "F0004586" } } ] } }

• fetch a list of passport_infos with their owner:

  query {
    passport_info {
      id
      passport_number
      owner {
        id
        name
      }
    }
  }
  

  query { passport_info { id passport_number owner { id name } } }

  { "data": { "passport_info": [ { "id": 1, "passport_number": "987456234", "owner": { "id": 1, "name": "Justin" } }, { "id": 2, "passport_number": "F0004586", "owner": { "id": 2, "name": "Beltran" } } ] } }

Insert using one-to-one relationships¶

We can now:

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

mutation upsertPassportInfoWithOwner {
  insert_passport_info(objects: [
    {
      passport_number: "X98973765",
      owner: {
        data: {
          name: "Kelly"
        },
        on_conflict: {
          constraint: owner_name_key,
          update_columns: [name]
        }
      },
    }
  ]) {
    returning {
      passport_number
      owner {
        name
      }
    }
  }
}