https://www.prisma.io/ logo
Join Slack
Powered by
so if we're not sure if the record exists then sho...
# orm-help
a
so if we're not sure if the record exists then should we provide the same object to 
create
and 
update
r
Yes.
a
Should we also provide the 
data
property in the 
create
?
The below example doesn't have the 
'data
property in 
create
Copy code
const updatedOrCreatedUser = await prisma.upsertUser({
  where: {
    email: '<mailto:alice@prisma.io|alice@prisma.io>',
  },
  update: {
    role: 'ADMIN',
  },
  create: {
    name: 'Alice',
    email: '<mailto:alice@prisma.io|alice@prisma.io>',
    role: 'ADMIN',
  },
})
r
No 
data
is not required
a
okay. In 
where
I have a condition field and in 
update
should i have that field as well?
r
Only if you want to update the field. Otherwise it’s not necessary
a
I'm getting this error even though I have the field 
idReferral
in where, update and create
Copy code
Unknown arg `idReferral` in where.idReferral for type ResponsiblePersonWhereUniqueInput.
Argument idReferral for create.idReferral is missing.
r
idReferral
is unique and can be used in 
where
?
a
no it's not unique
it is a reference to id in another table
r
upsert
requires a unique field from the same model. A reference is not allowed.
a
but that reference is implicit and isn't explicitly mentioned in the schema
r
Could you share that part of your schema?
a
Copy code
model ResponsiblePerson {
  id                     Int       @id @default(autoincrement())
  firstName              String    @db.VarChar(150)
  street                 String    @db.VarChar(150)
  city                   String    @db.VarChar(150)
  state                  String    @db.VarChar(150)
  zip                    String    @db.VarChar(150)
  phone                  String    @db.VarChar(150)
  email                  String    @db.VarChar(150)
  relationshipToResident String    @db.VarChar(150)
  lastName               String    @db.VarChar(150)
  country                String    @db.VarChar(150)
  responsibleFor         String    @db.VarChar(60)
  idReferral             Int

}
r
idReferral
is neither unique nor an id so cannot be used in the 
where
condition.
👍 1
a
Thanks for the clarification.
👍 1
In this case id is the only unique field. so for new entry, the id field would be undefined in the where condition.
r
That’s fine, you can use 
0
if you’re using Int id’s if you know that there’s no record.
a
One last. should we provide the id to the 
create
method. I didn't provide the id to the 
create
and it created a new entry.
r
Yes, if you don’t the 
id
will be generated automatically.
a
I mean it's creating a new entry with new id though I provide the Id in the where clause.
r
If the 
id
is set as default (like 
autoincrement
) then it will create a new entry with the autogenerated id.
a
So there should be an another unique filed apart from the autoincrement id field
r
I’m not getting what you’re trying to add in 
create
. Could you elaborate on that?
a
I want to do an upsert. if the id exists in the table, it should 
update
if the id isn't present then it should 
create
the entry.
id is the primary key and set to autoincrement
r
Yes so then you do not need to pass the 
id
in 
create
and 
update
. You only need to pass it in 
where
.
👍 1
a
Now I'm getting this error. I didn't provide the id on the create and update but only in the where.
Copy code
PrismaClientKnownRequestError2 [PrismaClientKnownRequestError]: 
Invalid `prisma.preAdmission1.upsert()` invocation:


  Unique constraint failed on the fields: (`idReferral`)
    at cb (C:\Users\ArunKadari\Documents\projects\cradleAPI\node_modules\@prisma\client\runtime\index.js:35101:17)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async Promise.all (index 1)
    at async exports.postOne (C:\Users\ArunKadari\Documents\projects\cradleAPI\controllers\preAdmission1AssesmentController.js:90:21) {
  code: 'P2002',
  clientVersion: '2.23.0',
  meta: { target: [ 'idReferral' ] }
r
idReferral
is unique? If so, have you assigned the same value in 
create
?
a
yes
If i remove the unique contraint on idReferral it's creating multiple entries with different ids
This is what I'm doing
Copy code
var data = req.body
    delete data.id

prisma.preAdmission1.upsert({ 
      where:{
        id: req.body.id ? req.body.id : 0
      },
      update: data,
      create: data
      
       });
r
Then you wouldn’t be able to create a new record with the same 
idReferral
.
a
my task is simple if 
id
exists 
update
. if no id 
create
r
But if you pass the same 
idReferral
in create, then it will give a unique constraint error.
a
I'm not concerned about idReferral. I want to check on id only and only update that specific row
there should be only 1 id and 1 idReferral in that model.
I mean unique id and unique idReferral
r
Could you share your schema and what 
data
are you passing in 
update
and 
create
?
a
I'm crying 😭
I don't know why I should worry about idReferral if I'm checking on the id and trying to update that specific record with the id.
r
I’m pretty sure it’s an issue with 
idReferral
that you’re passing in 
create
. The unique constraint error is causing that.
I would suggest not passing the entire data instead just pass the fields that you want to create/update.
a
in the update should i pass idReferral?
r
Do you want to update it? If not, then no. Also updating a unique field should not conflict with other values in that table. Which is also why you could get the above error.
a
I now removed the unique constraint
And this is what I'm doing. data doesn't contain the id field.
Copy code
upsert({ 
      where:{
        id: req.body.id ? req.body.id : 0
      },
      update: req.data,
      create: req.data
       })
r
If you have removed the unique constraint then you shouldn’t face the above error now.
a
Agree. but it's creating multiple entries.
It doesn't seem to be check for the where condition
r
Does the 
id
exist in the database that you’re passing in 
where
?
a
Yes
r
Is it possible to send the snippet and schema just of the required models, I would like to try it out.
a
First I tried to create and tried to update the same. But it created a new entry.
r
I would need a reproduction to test this.
a
Seems like the confusion is with what data we should pass to update and create
should we pass the id to update?
r
If the 
id
passed in 
where
is present in the table, then it will always go to 
update
. 
id
is not required to be passed to 
update
.
Follow these steps: 1. Add this to your 
PrismaClient
instance
Copy code
const prisma = new PrismaClient({
  log: ['query'],
})
2. First create the data and check if that’s created successfully 3. Then using 
upsert
pass the same 
id
created above to 
where
4. Send me the logs of the queries that were run in steps 2 and 3
a
sure
where are the logs saved?
Copy code
`Create`

prisma:query INSERT INTO "public"."PreAdmission1" ("dietryRestrictions","dietryRestrictionDescription","recentChangeInF      ake","recentChangeInFooodIntake","recentChangeInFoodIntakeDescription","eatsLessThan3MealsAday","mealsDescription","mouthOrTeethProblem","mouoodIntsToEatWithOthers","recthOrTeethProblemDescription","likesToEatWithOthers","recentlyLostOrGainedWeight","lossOrGain","numberOfPounds","consumeethProisionImpaired","visionsAlcohol","alcoholFrequency","usesDrugs","drugsFrequency","isVisionImpaired","visionImpairmentDetails","isHearingImpairlcohol"isCongnitiveImpaired"ed","hearingImpairmentDetails","isSpeechImpaired","speechImpairmentDetails","hasDentures","isCongnitiveImpaired","congnmentDergery","accidents","isitiveImpairmentDetails","canParticipateInSocialActivities","listOfSocialActivities","healthHistory","majorIllness","sur"canPa,$2,$3,$4,$5,$6,$7,$8,gery","accidents","isHospitalizedInLast5Years","durationOfHospitalization","descriptionOfHospitalization","idReferral",zedInLblic"."PreAdmission1"."overallHealth") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$5,$6,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36) RETURNING "public"."PreAdmission1"."id"        





`update`


prisma:query INSERT INTO "public"."PreAdmission1" ("dietryRestrictions","dietryRestrictionDescription","recentChangeInFoodIntake","recentChangeInFoodIntakeDescription","eatsLessThan3MealsAday","mealsDescription","mouthOrTeethProblem","mouthOrTeethProblemDescription","likesToEatWithOthers","recentlyLostOrGainedWeight","lossOrGain","numberOfPounds","consumesAlcohol","alcoholFrequency","usesDrugs","drugsFrequency","isVisionImpaired","visionImpairmentDetails","isHearingImpaired","hearingImpairmentDetails","isSpeechImpaired","speechImpairmentDetails","hasDentures","isCongnitiveImpaired","congnitiveImpairmentDetails","canParticipateInSocialActivities","listOfSocialActivities","healthHistory","majorIllness","surgery","accidents","isHospitalizedInLast5Years","durationOfHospitalization","descriptionOfHospitalization","idReferral","overallHealth") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36) RETURNING "public"."PreAdmission1"."id"
r
Usually Prisma checks if the value is present in the database when you do an upsert, but in this case it's showing insert for both create and update. There's only an insert log for both of them? Also what version of Prisma are you using?
a
"@prisma/client": "^2.23.0",
r
Could you update 
prisma
and 
@prisma/client
to the latest and check? Given this schema:
Copy code
model User {
  id   Int    @id @default(autoincrement())
  name String
}
This query produces the following output:
Copy code
await prisma.user.upsert({
    where: { id: 1 },
    create: { name: 'user 1' },
    update: { name: 'user 1' },
  })
It seems that different query is running rather than 
upsert
8 Views
Open in Slack
PreviousNext
Powered by