How do you define an upsert (if exists update else insert) or even an update using graphql?

Question

EDIT: I'm going to give the SQL equivalents here because its the easiest way I can convey what I'm trying to find an equivalent to.

The schema for a person is

 id (system assigned clustered PK, probably a guid, doesnt really matter for this)
 FirstName : string
 LastName : string, required
 EmployeeCode : string, required, Unique

I want to be able to represent a query like this in GraphQL (BEGIN/END ommitted for brevity), and just the UPDATE statement and I need examples for either that shows how to target specific records like the WHERE clause does.

IF EXISTS(SELECT TOP 1 1 FROM person WITH (UPDLOCK) WHERE EmployeeCode = 'ABC123')
   UPDATE person 
      SET FirstName = 'Mike'
          , LastName = 'Jones' 
   WHERE EmployeeCode = 'ABC123
ELSE
   INSERT person (FirstName, LastName, EmployeeCode) 
      VALUES ('Mike', "Hones', 'ABC123')

The GraphQl spec discusses mutations but not what it looks like. The examples only cover the INSERT scenario. The only mention about updates is that they will/must/? be executed consecutively.

Original question -

I want to be able to send a single graphql document to add a record if none exist for the criteria specified, or update (partial or all non ident/non PK/non NK fields) an existing one if the criteria specified match an existing record.

I can do this a few ways in conventional SQL dialects and in ElasticSearch (update by query). But I dont see how its supposed to be specified or described within graphql - or if its even possible.

In the spec and on the web there are some examples for adding records that seem to require things that look like they are "extra" or "nice to have" (like schema/type definitions) to be included in the document. The update examples often have field, argument, and variable names that dont match (like Author.Id and authorId), or are written using a non-graphql specific language like Javascript (same example page as "Author".) Doing this two stage operation from the end client doesnt seem like the correct approach either as only the target data store is going to know which is the correct operation to take.

There will not be a fixed predefined centralized schema for what I'm working on as its a muilti-tenant API and each tenant may have different definitions for a type (for example Tenant A has a Contract entity with 16 fields, but Tenant B has a type of the same name and intended function with 20 field. Both would share the same storage space for Contracts.

Is any what I'm asking about for even possible? If so can an example be shared?

Answer 1

upsert-is-not-a-substitute-for-update

... then it can be a loose interpretation ...

createItem (name it upsertItem if you wish) mutation resolver can insert or update (BE, resolver implementation/storage/DB related decision) with or without strict/explicit input type definitions (required for create, optional for update)

... tenants can have different input types (fields amount) for the same mutation

... in wp-graphql (WordPress) it's even role based - different introspection results, different args for fields, different mutations available ... but it's from dynamic, non-persistent/stateless php character

You can just limit fields usage (per tenant) or type matching (required/not required) in some validation 'layer' inside resolver or in middleware just by throwing errors (when input doesn't match specific usage/tenant - f.e. return 'required error' for some field while all fields in type definitions are optional). When introspection query is blocked (on production) then it's just a documentation problem (not self-describing).

update - example

if the target entity to upsert is a Person with an ID, and First, and Last names, (ID and Last are required)

ID can't be required here ...

UpsertPersonInput with all (ID, First, Last) optional ...

For create: upsertPerson( $input: UpsertPersonInput) {.. with variables required (by validation rules) for creation (First: "some", Last: "Name")

... ID unknown here, created and returned as result

...no ID provided then assuming 'creation mode'

For update: query the same but when provided input variable with ID prop validation works in 'update mode' (at least one other variable - input prop - required, f.e. Last ... or other additional validation rules)

... while only input.ID prop provided > throw some "no required 'First' passed in input arg" (any field) Error ... as it would in separate update mutation and its input type.

Answer 2

There is no freedom when you have to supply all fields in an update or else they are set to NULL when using GraphQL. There is no freedom when you have a table such as below: TABLE1 ( ID NUMBER PK NOT NULL, FIRSTNAME VARCHAR2(20), LASTNAME VARCHAR2(20), COMPANY VARCHAR2(25), STATUS VARCHAR2(10) )

and in Oracle or any other database I could easily update the status of all records meeting conditions UPDATE TABLE1 SET STATUS = 'DONE' WHERE COMPANY = 'XYZ CORP';

but in GraphQL I have to supply the ID in the update or get an error "ID required..." or having to include all the columns within the table That is very inefficient.