Graphql-engine: Upsert mutation with default ids?

[plmercereau]

Hello,
I am looking for a way to avoid defining both an insert and an update mutation in following your guidelines on upsert.
However, in my DB schema, I never ask the client to define the primary key: it is an uuid that is auto-generated on insert by postgres.
Therefore I need:

• on insert, to send all the data, without a primary key field
• on update, to send all the data, with the primary field

What I tried until now looks something like this:

mutation upsert_encounter(
          $id: uuid
          $data: jsonb
        ) {
          insert_encounter(
            objects: [
              {
                id: $id
                data: $data
              }
            ]
            on_conflict: {
              constraint: encounter_pkey
              update_columns: [data]
              }
          ) {
            affected_rows
          }
        }

As I dont provide any $id value on insert, it sends an id set to null, and therefore it raises an error that I am violating the non-null constraint on my primary key...
I found a workaround in my javascript client in replacing id: $id by ${form.id ? 'id: $id' : ''} but I find this pretty ugly, and I then can't separate my graphql code from my javascript code...
Another way I thought is to declare the objects to upsert like this: objects: [$object], but then I have another problem as I intend to upsert nested relations as well...
Any idea on how to make things cleaner?

[0x777]

upsert is for those cases where you do not know whether the data exists in the table or not. But if you do know what the operation is you are better off issuing the appropriate mutation instead of using upsert. In your example it looks like you know what the operation is, i.e, whether to insert or to update so I would suggest you to have two separate mutations and call the appropriate mutation.

However, you can do this as an upsert operation without the switching if you have another unique constraint on the table (other than the primary key constraint).

[plmercereau]

Thanks @0x777 for your reply. I don't understand your last sentence.
But overall it makes sense. The issue in having two mutations every time, one insert and one update, is that it will multiply the complexity of my code. I foresee to have to define mutations for approximately 50 tables, including nested mutations. I initially tried the 2 mutations approach, but the complexity of the code exploded, making it very hard to maintain.
I strongly believe most of the upsert cases are meant to insert or update rows without knowing if th data already exists or not, as per definition upsert is supposed to let the server decide wether this is an insert or an update, and I will be pleased that we could consider this limitation as a missing feature :)

[AakashPat]

Thanks @0x777 for your reply. I don't understand your last sentence.
But overall it makes sense. The issue in having two mutations every time, one insert and one update, is that it will multiply the complexity of my code. I foresee to have to define mutations for approximately 50 tables, including nested mutations. I initially tried the 2 mutations approach, but the complexity of the code exploded, making it very hard to maintain.
I strongly believe most of the upsert cases are meant to insert or update rows without knowing if th data already exists or not, as per definition upsert is supposed to let the server decide wether this is an insert or an update, and I will be pleased that we could consider this limitation as a missing feature :)

I'm with you on this one! Just experienced the exact same issue. I want to reduce the number of mutations and try to combine the insert/update operations using a single mutation. The problem though is that I will have to pass in the primary_key even though it is set to auto-increment integer. I am looking for a way to have that auto generated as it should be in case I send a 'null' value for the ID (Insert). I suppose this could be considered a missing feature.

[0x777]

@AakashPat

I am looking for a way to have that auto generated as it should be in case I send a 'null' value for the ID (Insert)

The server can only have one mapping for a GraphQL null and currently it is SQL NULL. If the field isn't specified, DEFAULT will be used and I don't think SQL DEFAULT makes sense for GraphQL null even if we were to change the mapping.

Why not use uuid as the primary key so that you can generate it on the client side?

[AakashPat]

@AakashPat

I am looking for a way to have that auto generated as it should be in case I send a 'null' value for the ID (Insert)

The server can only have one mapping for a GraphQL null and currently it is SQL NULL. If the field isn't specified, DEFAULT will be used and I don't think SQL DEFAULT makes sense for GraphQL null even if we were to change the mapping.

Why not use uuid as the primary key so that you can generate it on the client side?

Seems like a valid fix, @0x777.

In fact, that's something I've already implemented.

Initially, I was having to maintain the ID as integer, as we were using it to sort things serially in parts of the app.
I have now moved away from that approach and have implemented another column in the table that stores an auto-incremented integer value for every row, which is set to be able to take NULL values.

[marionschleifer]

I'm closing this issue. Feel free to re-open if you'd like to add something 🙂