How to upsert new record in Prisma without an ID?

Question

I'm using Prisma (https://www.prisma.io) as ORM. I want to check for duplicates when store data and, if not exists, create a new record.

I thought I could do that with upsert method provided by Prisma and available in the generated client, but the where clause of that method only works with id (or @unique fields), but if the record doesn't exist, there isn't any id to provide.

I provide an example of the problem.

datamodel.prisma

type System {
  id: ID! @unique
  performances: [SystemPerformance!]! @relation(name: "PerformanceBySystem" onDelete: CASCADE)
  name: String! @unique
}

type SystemPerformance {
  id: ID! @unique
  system: System! @relation(name: "PerformanceBySystem")
  date: DateTime!
  perf1: Float
  perf2: Float
}

seed.js

const { prisma } = require('./generated/prisma-client');
async function main(){
  await prisma.createSystem({
    name: 's1',
  });
  await prisma.createSystem({
    name: 's2',
  });
  await prisma.createSystem({
    name: 's3',
  });
}
main();

After creation there is a database with three Systems without performances. I'm trying to insert a new SystemPerformance if there aren't any that have same date and same System. I have tried

const { prisma } = require('./prisma/generated/prisma-client');

const perf = await prisma.upsertSystemPerformance({
       where: {
         system: {name: 's1'},
         date: "2019-03-12T00:01:06.000Z"
       },
       update: {
         perf1: 13.45,
         perf2: 18.93
       },
       create: {
        system: {
            connect: { name: 's1' }
        },
        date: "2019-03-12T00:01:06.000Z",
        perf1: 13.45,
        perf2: 18.93
       }
})

But an exception is thrown:

UnhandledPromiseRejectionWarning: Error: Variable '$where' expected value of type 'SystemPerformanceWhereUniqueInput!' but got: {"system":{"name":'s1'},"date":"2019-03-12T00:01:06.000Z"}. Reason: 'system' Field 'system' is not defined in the input type 'SystemPerformanceWhereUniqueInput'

The only solution I have found is check for existence and then update or create, but I wanted to do it with upsert.

let check = await prisma.$exists.SystemPerformance({
            system: {name: 's1'},
            date: "2019-03-12T00:01:06.000Z"
        });
let perfo;
if (check){
  const sysPerf = await prisma.systemPerformances({where:{system: {name: 's1'}, date: "2019-03-12T00:01:06.000Z"}})
            .$fragment(`
            {
                id
            }
            `);
  perfo = await prisma.updateSystemPerformance({
    where: {id: sysPerf[0].id},
            data: {
              perf1: 13.45,
              perf2: 18.93
            }
   })
}
else {
  perfo = await prisma.createSystemPerformance({
    system: {
      connect: { name: 's1' }
    },
    date: "2019-03-12T00:01:06.000Z",
    perf1: 13.45,
    perf2: 18.93
  }
})

Is there a way to do that with upsert?

Tadeo

Upsert accepts only unique fields as input in where clause. I fear you will have to go with workaround where you query record and then use upsert or update / create depending upon whether record exists. – Tory

I was afraid of that. I asked if there was a better solution. Thank you for your answer. – Giveaway

One dirty hack that I am using, and it works for me is using a sentinal value for id that doesn't exist: ` where: { id: id ?? "does not exist" }` – Data

Answer 1

The fields in where need to be unique.

If you can make some field, let's say date @unique (date: DateTime! @unique), and use that for your where in the upsert, I think it would work (tested on my local)

Zoospore

Answer 2

If you are still down here without an answer, I used a combination from @Antoine's answer and another SO answer:

model Likes {
  id         String     @id @unique @default(uuid())
  user_id    String
  tag        String
  auth_user  AuthUser   @relation(references: [id], fields: [user_id], onDelete: Cascade)

  @@unique([user_id, tag], name: "user_id_tag")  // <-- this is the unique constraint
  @@index([user_id])
  @@map("likes")
}

Then I was able to upsert via the following:

prisma.likes.upsert({
    where: {
        user_id_tag: { // <-- And this bit is important
            user_id: user.userId,
            tag: tag.tag
        }
    },
    update: {},
    create: tag
})

Streamy

in this getting error while data not exist return ConnectorError(ConnectorError { user_facing_error: None, kind: RecordDoesNotExist, transient: false }) insted of create record any idea ? – Farny

Answer 3

where: {
    id: sysPerf[0].id ? sysPerf[0].id : 0
},

Eleneeleni

Your answer could be improved by adding more information on what the code does and how it helps the OP. – Katti

It works on mariadb – Cardew

Answer 4

If using Mongo you can add an ObjectId using bson-objectid package:

import ObjectId from "bson-objectid";

providers: {
        upsert: data.item?.map((item: Prisma.ItemCreateInput) => ({
          where: {
            id: item.id || ObjectId().toString(),
          },
          update: {
            // ...data
          },
          create: {
            // ...data
          },
        })),
      },
}

Otherwise you will need to provide your own uuid or a unique identifier that will be passed on.

See also https://github.com/prisma/prisma/issues/6718

Trousseau