Support "Upsert", aka "UpdateOrCreate" mutations

[jesstelford]

Interestingly, these existed in GraphCool, but not in Prisma or OpenCRUD:

input UpdateOrCreatePostInput {
  id: ID # NOTE: Optional - passing this does an update, not passing this does a create
  # ...the rest of UpdatePostInput
}

type Mutation {
  updateOrCreatePost(data: UpdateOrCreatePostInput): Post
}

Greatly simplifies client code by not having to check for IDs, and switch to a different query.

In the case that you're trying to update with an ID that doesn't exist, it would throw an error the same as when doing updatePost(id: "idontexist")

[molomby]

This doesn't strike me as a very common requirement.. I can only imagine this would be useful if the IDs used were somehow relevant to the item, ie. they encoded some information about it. This is pretty unusual.. normally all IDs would be arbitrary and/or random. In this case (random IDs), attempting to update an object that doesn't exist does indicate an error and shouldn't magically succeed.

Am I wrong? Whats a common, real-world example of upserts being useful?

Maybe restoring/reverting data in bulk..?

[jesstelford]

I used it in a couple places in Cete:

• RSVPing. I have a list RSVPs, with user and event Relationships. The first time a user RSVPs to an event, we have to create a new RSVP and link everything up, and set going: true. All following "toggles" of going/not going need to update that RSVP. The code is simplified by getting to use a CreateOrUpdate query - the id is initially null so will create an item, thereafter it's got a value, and so the user can toggle as many times as they like and it'll trigger an update instead of a create.
• The same for creating / editing an event - it's the exact same form, the only different is a new event doesn't have an id yet, so a CreateOrUpdate works great here.
• etc

[molomby]

Huh.. so the interface still "knows" whether it's creating or updating (because it has an id of not) but it's a bit neater because it can hit the same mutation?

I can imagine this causing issues with stale state in the client. Eg, the if the user opened the event in two tabs and RSVP'd in both, wouldn't you get two RSVP items being created?

Maybe it would be more helpful to be able to specify filters other than the ID (almost like the Mongo "bulk" upsert functionality). So for RSVPs, the frontend might do this..

mutation {
  upsertRsvp (
    where: {
      eventId: "262881f59fc4f3e1d7b34c99"
      userId: "7e3d8a3bbda9c6309167dcc4"
    }
    data: {
      isAttending: true
    }
  ) {
    id
    eventId
    userId
    isAttending
  }
}

Not sure how this fits with OpenCRUD/Graphcool but, functionally, it's how I imagine upserts being most useful.

[jesstelford]

@molomby I like that idea! Goes perfectly with https://github.com/keystonejs/keystone-5/issues/699

[molomby]

@molomby writes:

This doesn't strike me as a very common requirement [..]

Yeah, scratch that; this would be super handy.

Definitely worth separating the filters from the data being updated though.

I wonder if this should fit into how we configure multi-field uniqueness in Keystone. Ie. currently, we can specify a single field is unique using the isUnique flag but adding unique constraints across multiple fields must be done behind the scenes, in the DB.

Upserts seem to be useful when you know some information about an item but maybe not whether it exists or not so, implicitly, don't have the items id. (If you have the id you have less of a problem because, race conditions aside, know whether to call create or update). But, the "some" information you know really needs to uniquely identify one item (or one potential item). Without this restriction it gets a lot more complicated -- we end up trying to solve upserts, multi-item updates, multi-item creates, etc. all at the same time.

So... if we give app devs a way of specifying sets of fields that should be unique within the list (eg. user and event in the Rsvp example above), that same config could be used to provision upsert mutations, using the same sets.

Eg. something like this:

keystone.createList('Rsvp', {
   fields: {
      user: {
         type: Relationship,
         ref: 'User',
         isRequired: true,
      },
      event: {
         type: Relationship,
         ref: 'Event',
         isRequired: true,
      },
      isAttending: {
         type: Checkbox,
         defaultValue: false,
      },
   },
   // A made up, multi-field unique constraint configuration
   // The object key is arbitrary; just a handle we use to name the mutation and underlying constraint
   uniqueFieldSets: {
      pair: ['user', 'event'],
   }
});

Could cause this additional GraphQL schema to be created:

type rsvpPairUpsertWhereInput {
   user: ID!
   event: ID!
}
type rsvpPairUpsertInput {
   isAttending: Bool
}
# .. all the other types

type Mutation {
   upsertRsvpPair(
      where: rsvpPairUpsertWhereInput!,
      data: rsvpPairUpsertInput!
   ): rsvpOutputType
   // .. all the other mutations
}

There could of course be multiple sets of unique fields, resulting in multiple upsert mutations for a single list.

[molomby]

I think this is functionality is dependant on unique indexes which, themselves, get a lot more useful when you start dealing with compound indexing. Anyone actioning this should also be across #2304.

[stale]

It looks like there hasn't been any activity here in over 6 months. Sorry about that! We've flagged this issue for special attention. It wil be manually reviewed by maintainers, not automatically closed. If you have any additional information please leave us a comment. It really helps! Thank you for you contribution. :)

[jesstelford]

This may come back once Prisma support is fully baked in Keystone Next, but for now I will close this.

[MurzNN]

Seems Prisma already have the upsert method?
https://www.prisma.io/docs/concepts/components/prisma-client/crud#update-or-create-records
https://www.prisma.io/docs/reference/api-reference/prisma-client-reference/#upsert

[johanneshiry]

@jesstelford Any chance that this will be available in KeyStone Next soon? Or is it available and I just didn't saw it?

[timleslie]

@johanneshiry This isn't available for Keystone Next just yet, but we're it's on our feature list to be planned into future development. I can't give you an ETA on it just yet.

[andy-blum]

Popping in to see if there's any kind of update on this besides it being on the roadmap.

I've currently side-stepped the problem by creating a function in a script to attempt and update and then insert on fail, but I'd love to have a genuine upsert mutation to work with.

My use case is importing data from another system that has it's own set of IDs. I want the external IDs to be the source of truth, but I want the data in my system to make relationships easier to maintain.