Prisma1: Nested upsert in create mutations (correct: nested connectOrCreate)

[marktani]

Feature Request

What feature are you missing?

A nested upsert mutation inside create mutations.

How could this feature look like in detail? Tradeoffs?

Consider this datamodel:

type Album {
  id: ID! @unique
  title: String!
  artist: Artist!
}

type Artist {
  id: ID! @unique
  name: String! @unique
  works: [Album!]!
}

createAlbum accepts an input type artist that looks like this:

type ArtistCreateOneWithoutWorksInput {
  create: ArtistCreateWithoutWorksInput
  connect: ArtistWhereUniqueInput
}

There is no upsert mutation. Adding this would add convenience, while not polluting the API, in my opinion.

Workarounds

Check existence before mutation

One current workaround is to check the existence of the to-be connected node before running the mutation. This is how you can do so in your application resolver (semi pseudo code):

async createAlbum(parent, { title, authorName }, ctx, info) {
  const artistExists = await ctx.db.exists.Artist({
    name: authorName
  })
  if (!artistExists) {
    return ctx.db.mutation.createAlbum(
      {
        data: {
          title,
          artist: { create: { name: authorName } }
        }
      },
      info
    )
  } else {
    return ctx.db.mutation.createAlbum(
      {
        data: {
          title,
          artist: { connect: { name: authorName } }
        }
      },
      info
    )
  }
},

Upsert from the other side of the relation

You can run the following mutation:

mutation {
  upsertArtist(
    where: {
      name: "unique artist name"
    }
    update: {
      works: {
        create: {
          title: "album title"
        }
      }
    } 
    create: {
      name: "unique artist name"
      works: {
        create: {
          title: "album title"
        }
      }
    }
  ) {
    id
  }
}

[LawJolla]

I've run into this exact pain point and would love an upsert method.

[williamluke4]

@marktani Has there been any progress on this?

[marktani]

Hey @williamluke4, there has not been any work on this feature.
However, the second workaround I mentioned above is now available (starting 1.12.x).

[berstend]

It's worth mentioning that in the top-level update mutation upsert can be used for nested relations, which reduces the risk of abandoned children in the database.

This would be desirable for e.g. one-to-one relationships:

type GeoEntry {
  name: String
}

type Geo {
  id: ID! @unique
  ip: String! @unique

  country: GeoEntry @relation(name: "GeoEntryCountry", onDelete: CASCADE)
}

mutation {
  upsertGeo(
    where: {
      ip: "8.8.8.8"
    }
    update: {
      country: {
        upsert: {
          update: {
            name: "Country3"
          }
          create: {
            name: "Country2"
          }
        }
      }
    }
    create: {
      ip: "8.8.8.8"
      country: {
        create: {
          name: "Country1"
        }
      }
    }
  ) {
    ip
    country {
      name
    }
  }
}

[Dorkside]

Any update on this?
Nested upserts would be great on my API though I'm quite sure I could simplify my logic to avoid the usecase.

[mavilein]

We implemented this a few weeks ago. Closing therefore.

[samburgers]

@mavilein is there any code example of how to implement a nested upsert? thanks

[mavilein]

@samburgers : There is a page about nested mutations in our docs. Have you checked this one? https://www.prisma.io/docs/prisma-graphql-api/reference/mutations-qwe2/#nested-mutations

[nolandg]

@mavilein I'm confused when you say you've implemented this a few weeks ago. The OP is talking about nested upserts within create mutations. When I read through the docs and look at the generated schema, there seems to only be nested upserts within update or upsert mutations.

ie. When creating a node, I want to upsert a connected node. Is this possible yet? Thanks!

[mavilein]

@nolandg : Ah i misunderstood this issue then. Thanks for bringing this to our attention 🙏 . That original feature request does not make sense though with our semantics. An upsert in our API either update the connected node or create a new node and connect it.
So i guess what we really want is an connectOrCreate.

[do4gr]

We are thinking about implementing this soon, so here is a draft of what it could look like:

type User {
  id: ID! @unique
  name: String
  post: [Post!]!  
}

type Post {
  id: ID! @unique
  title: String  @unique
  content: String
  author: User!
}


mutation createOrConnect {createUser(data:{
  name: "Paul", 
  posts:{
    createOrConnect:[
      {
        where: {title: "First"},
        create: {title: "First", content: "Test"}
      },
      {
        where: {title: "Second"},
        create: {title: "AnotherTitle", content: "Test"}
      }   
    ]
  }
  }){id}}

The where would accept any field marked as unique in the datamodel, and the create would require the normal CreateInputType. It is not required to have the same value of the where for the respective field in the create.

In the mutation above we would check for a Post with the specified unique title and set a connection to this one if one is found. If none is found we would create a new one with the provided values and establish a relation to this one.

We will not generated the mutation in cases where it would always error on the connect case. In these cases a simple create should be used.

| Author | Post | Nested CreateOrConnect on Author|
|---|---|---|
|[Posts!]! | [Authors!]! | generate |
|[Posts!]! | Author! | generate |
|[Posts!]! | Author | generate |
| Post! | [Author!]! | generate |
| Post! | Author! | do not generate |
| Post! | Author | do not generate |
| Post | [Author!]! | generate |
| Post | Author! | generate |
| Post | Author | generate |

This way only the create part can error - due to unique violations for example.

Please let us now if you have feedback concerning this proposed implementation.

[saratitan]

This looks great. Are there any updates on this?

[cmoses8626]

Awesome, am also looking for exactly this mutation, proposal looks great

[joshhopkins]

Keep coming up against this. Very much needed!

[Soliseum]

Any updates on createOrConnect? This would be an amazingly helpful feature, thanks either way!
@mavilein @do4gr

[aarshaw]

Any word on this? Desperately needed..

Has anyone found a good temporary workaround?

[saeedseyfi]

This is my temp solution, might help you as well.
I use a helper function that generates create/connect.

defenition

const createOrConnect = async (entity, items) =>
  (await Promise.all(items.map(({ where }) => entity(where))))
    .reduce((result, entityAlreadyExist, i) => {
      if (entityAlreadyExist) {
        if (!result.connect) {
          result.connect = [];
        }
        result.connect.push(items[i].where);
      } else {
        if (!result.create) {
          result.create = [];
        }
        result.create.push(items[i].create);
      }

      return result;
    }, {});

usage

await prisma.createUser({
  name: 'saeed',
  posts: {
    ...await createOrConnect(prisma.post, [
      {
        where: {title: "First"},
        create: {title: "First", content: "Test"}
      },
      {
        where: {title: "Second"},
        create: {title: "AnotherTitle", content: "Test"}
      }
    ])
  }
})

tests

const createOrConnect = require('./createOrConnect'); describe('createOrConnect', () => { let prisma; let prismaEntityResult; beforeEach(() => { let called = 0; prismaEntityResult = []; prisma = { exampleEntity: async () => prismaEntityResult[called++] }; }); it('should be a function', () => { expect(typeof createOrConnect).toBe('function'); }); it('should handle create', async () => { prismaEntityResult.push(undefined); const result = await createOrConnect(prisma.exampleEntity, [ { where: { title: 'First' }, create: { title: 'First', content: 'Test' } } ]); expect(result).toEqual({ create: [{ title: 'First', content: 'Test' }] }); }); it('should handle connect', async () => { prismaEntityResult.push({ id: 1 }); const result = await createOrConnect(prisma.exampleEntity, [ { where: { title: 'First' }, create: { title: 'First', content: 'Test' } } ]); expect(result).toEqual({ connect: [{ title: 'First' }] }); }); it('should handle connect and create', async () => { prismaEntityResult.push(undefined); prismaEntityResult.push({ id: 2 }); const result = await createOrConnect(prisma.exampleEntity, [ { where: { title: 'First' }, create: { title: 'First', content: 'Test' } }, { where: { title: 'Second' }, create: { title: 'Second', content: 'Test' } } ]); expect(result).toEqual({ create: [{ title: 'First', content: 'Test' }], connect: [{ title: 'Second' }] }); }); });

[BoraALAP]

Any news on this?

[pantharshit00]

This is available in Prisma 2 now see: https://github.com/prisma/prisma-client-js/issues/336#issuecomment-642233623