GraphQL mutation (insert) with nested, non-tree relationship objects

[Zeta611]

I'm using Relay endpoints generated in Hasura, connected to a PostgreSQL database.

Suppose there are three objects a, b, and c.

• a has one-to-many relationships with bs and cs.
• c has a one-to-one relationship with b. (Exactly one b for each c, but not the other way around.)

This is the essence of the Relay GraphQL schema for these objects:

type a implements Node {
  id: ID!
  bs(...): [b!]!
  bs_connection(...): bConnection!
  cs(...): [c!]!
  cs_connection(...): cConnection!
}

type b implements Node {
  id: ID!
  a: jargon!
  a_id: Int!
  c: translation
  c_id: Int
}

type c implements Node {
  id: ID!
  b: comment
  b_id: Int!
  a: jargon!
  a_id: Int!
}

I want to insert an object a with b and c altogether:

a --→ b
  \   ↑
   -→ c

But if I try the followings:

mutation AttemptMutation1 {
  insert_a_one(object: {bs: {data: {c: {data: {}}}}}) {
    id
  }
}

mutation AttemptMutation2 {
  insert_a_one(object: {cs: {data: {b: {data: {}}}}}) {
    id
  }
}

neither works, emitting errors like

Not-NULL violation. null value in column \"b_id\" of relation \"c\" violates not-null constraint

I'm currently resorting to

1. insert a with b object nested,
2. insert c with b ID, and
3. update b with c ID.
   Here I'm also not sure why step 2 does not set c ID for b automatically.
   This makes 3 GraphQL queries which really hurts the performance.
   Ideally I would like to perform this operation in a single transaction to make it robust.

What is the recommended way to deal with this kind of mutation?

I've originally asked this here.

[TristenHarr]

When you say:

"Here I'm also not sure why step 2 does not set c ID for b automatically."

With your schema, nothing is inherently linking nodes b and c.

Say you were to make fields a_id and c_id nullable in b, and make fields a_id and b_id nullable in c. Then you could perform the mutation you were attempting, but notice how I structure the output.

mutation AttemptMutation1 {
  insert_a_one(object: {bs: {data: {c: {data: {}}}}}) {
    id
    bs {
      a_id
      c_id
      c {
        id
        a_id
        b_id
      }
    }
    cs {
      id
      a_id
      b_id
    }
  }
}

This outputs for example (using auto-incrementing keys to have tested this some):

{
  "data": {
    "insert_a_one": {
      "id": 29,
      "bs": [
        {
          "a_id": 29,
          "c_id": 42,
          "c": {
            "id": 42,
            "a_id": null,
            "b_id": null
          }
        }
      ],
      "cs": []
    }
  }
}

I suspect your data-modeling might be a bit mangled, is there a reason to split B and C into different entities? The closest I can get to something like a single transaction is to set the columns nullable as mentioned above and then run a mutation like this:

mutation AttemptMutation1($a_id: Int = 1, $b_id: Int = 2, $c_id: Int = 3) {
  insert_a_one(object: {bs: {data: {id: $b_id}}, id: $a_id, cs: {data: {id: $c_id, b_id: $b_id}}}) {
    id
    bs {
      id
      a_id
      c_id
      cs {
        id
        a_id
        b_id
      }
    }
    cs {
      id
      a_id
      b_id
    }
  }
  update_b_by_pk(pk_columns: {id: $b_id}, _set: {c_id: $c_id}) {
    id
    a_id
    c_id
  }
}

Which outputs:

{
  "data": {
    "insert_a_one": {
      "id": 1,
      "bs": [
        {
          "id": 2,
          "a_id": 1,
          "c_id": null,
          "cs": [
            {
              "id": 3,
              "a_id": 1,
              "b_id": 2
            }
          ]
        }
      ],
      "cs": [
        {
          "id": 3,
          "a_id": 1,
          "b_id": 2
        }
      ]
    },
    "update_b_by_pk": {
      "id": 2,
      "a_id": 1,
      "c_id": 3
    }
  }
}

Keep in mind the way you have your structure setup will mean that the following statements must hold.

A can have B's and C's

B has an A and a C

C has an A and a B

There are no rules that say that "If B and C are linked, they have to share the same A". If that is a rule, as you seem to want it to be from the way you are expecting step 2 to set C for B automatically, you should probably remodel your data. Does it make sense for B and C to be separate tables? Could you put all the properties from both B and C into a single table "BC" and simplify your schema so that the rules simply becomes:

A can have BC's

BC's have an A

Technically, if Table B requires a non-null foreign key from table C and table C requires a non-null foreign key from table B, you have a complete deadlock situation, because you cannot insert into one table without already having the required reference from the other table.

Hope this helps some! :)

[Zeta611]

Thank you for the comprehensive explanation! I understand that my constraints do not enforce B and C to have same A.

B and C may indeed be stored in the same table, and it will enforce the condition to have the same A, technically.
But in my case it doesn't make much sense (semantically), as B and C have fields not relevant to each other. (Different update_at times, etc.)

I ended up migrating the database to use UUIDs and PKs, and made the client provide UUIDs.
This way, I solved the "deadlock" situation, and only use a single mutation.