-1

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?

StingyJack
  • 19,041
  • 10
  • 63
  • 122
  • no conditions/sorting/filtering etc in query possible ... why do you want to do that? ... why describing (in docs/specs) conditional mutation behaviour (dependent on passed variable set) is not enough ???????? – xadm Dec 17 '20 at 16:14
  • @xadm - I want that because I dont want to update the last name field on all person records to be the same value, just the one. Because I cant find a coherent example anywhere, I've asked if someone here can share an example of what the GraphQL document would look like to do a simple update that targets specific record(s) and if possible an upsert for the same.. – StingyJack Dec 17 '20 at 16:49
  • I wrote how it can work .... `Last` alone doesn't pass validation step in resolver (not enough args for create, no ID arg for update) ... doesn't affect all records, no any DB actions fired, error thrown, where doubts? ... it's not query alone, it's query+variables+rules in resolver (and validator before action) – xadm Dec 17 '20 at 16:58
  • @xadm - I'm looking for a complete example of the graphQL payload sent over the network from a client to a server for either a full upsert or just an update. Giving me only bits and pieces of that does not help because I do not know the correct way to assemble them to make a request. – StingyJack Dec 17 '20 at 17:27
  • just try to build a PoC? – xadm Dec 17 '20 at 17:40
  • @xadm - if I cant compose the syntax for an update or upsert, how can I continue to build a PoC demonstrating how GraphQL can work for what we need? – StingyJack Dec 17 '20 at 17:47

3 Answers3

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.

xadm
  • 8,219
  • 3
  • 14
  • 25
  • thanks, if the target entity to upsert is a Person with an ID, and First, and Last names, (ID and Last are required), and the update was to fix a misspelled last name and the field to match on was ID, what would the graphql actually it look like? The sytax of this is where I'm struggling. – StingyJack Dec 16 '20 at 20:26
  • Sorry, I meant that ID and Last Name cannot be null on a stored record and should have given a different unique match field. In reality there would be the system "ID" field that we can ignore for now, and then some other combination fields that would make a record unique in its collection. In this example that could be an EmployeeCode field. In which case it _would_ be required as its not generated when null and would be the field to match on to determine update or insert.Whats the GraphQL actually look like for that operation? I appreciate the time you are taking to try to explain this – StingyJack Dec 16 '20 at 21:17
  • no any difference, still input type contains all possible fields as optional ... it's up to you how you construct validation rules for create and update modes (what subfield/prop existence determines mode, ... and for each of tenant - ignore or throws when passed not supported prop/subfield) – xadm Dec 16 '20 at 21:34
  • Perhaps I should rephrase the problem - _GraphQL does not appear to have any kind of `WHERE` clause that I can use to identify the records to affect_ I dont actually think this is true, but I cant find an example of a GraphQL mutation that does an update thats using any kind of predicate filtering. – StingyJack Dec 17 '20 at 03:11
  • no any kind of sorting/filtering/etc. in specs ... not a query role to determine how resolver should work ... it's data-driven ... you CAN interpret (f.e. validate mode by) variables, you CAN resolve (work in create or update mode) by variables ... it's implementation detail ... you're defining what input data/args can mean ... WHERE clause to work on what? DB/file/remote service/datasource? – xadm Dec 17 '20 at 06:02
  • `UPDATE person SET LastName = 'Jones' WHERE EmployeeCode = 'ABC123'` is what I'm trying to find an equivalent for. All I can find are examples for `INSERT person (FirstName, LastName, EmployeeCode) VALUES ('Mike', "Hones', 'ABC123')` – StingyJack Dec 17 '20 at 15:23
  • if there is no any support then **you have to construct it from input data** in resolver (when required) ... did you explored any graphql API with filtering/sorting/conditions? tons of them ... still, it hides implementation details – xadm Dec 17 '20 at 15:29
0

The answer is... that you don't. There is no syntax or grammar for specifying or expressing an update behavior in the GraphQL other than the blanket idea of a resolver, for which you will have to entirely create your own syntax. For me that resolves to an incomplete query language.

Why this gap continues to exist is currently beyond me, but for the purposes of this question the answer is a disappointing "No" when it comes to updating.

halfer
  • 19,824
  • 17
  • 99
  • 186
StingyJack
  • 19,041
  • 10
  • 63
  • 122
  • Apples to oranges ... compare it to REST ... no syntax? probably never will be ... own syntax? not exactly ... input type for conditions from many vendors are similar, tree structured `where` (do some research) ... yes, it's not standarized (in specs) NOW but it doesn't stop people ... but it's up to you what this `where` means in your API ... suprisely they're creating similar solutions, adapted to environment/methodology and its possibilities (not sticking to older, well known solutions) ... sadly you don't feel it/don't see power in this freedom ... bad for you, not graphql – xadm Dec 21 '20 at 15:06
  • @xadm - I totally appreciate you sticking with this and owe you at _least_ one beer. I'm only trying to compare this against other object or data/type (aka graphs) query languages/functions and thats where I find this to be deficient. If you really think its not, then please share a complete example of the graphql payload that would be needed to send to a server that satisfies at least the update of the last name for an existing person record based on matching by employeecode (as outlined in the OP), without using any custom syntax. – StingyJack Dec 21 '20 at 19:01
0

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.

Eric Lichtman
  • 1