Drizzle Team•8mo ago

onConflict

if i try onConflictDoNothing the plan is to insert a new record into the database. If a record with the same userId and provider already exists, and the apiKey of the existing record is not equal to the apiKey of the new record, the insert operation is skipped and no error is thrown.

  .onConflictDoNothing({
    target: [userId, newSettingInfo.provider],
    where: sql`${settingInfo.apiKey} != ${newSettingInfo.apiKey}`,
  })

  .onConflictDoNothing({
    target: [userId, newSettingInfo.provider],
    where: sql`${settingInfo.apiKey} != ${newSettingInfo.apiKey}`,
  })

i get a a 500 error code and it reads

NeonDbError: syntax error at or near "where"
    at execute

NeonDbError: syntax error at or near "where"
    at execute

i am pretty certain this is because i cannot use a where clause in the DoNothing option. so i try the onConflictDoUpdate option to insert a new record in the database. If a record with the same userId and provider already exists, and the apiKey of the existing record is not equal to the apiKey of the new record, the existing record is updated with the new apiKey. If they are equal, the insert operation is skipped and no error is thrown.

.onConflictDoUpdate({
    target: [settingInfo.userId, settingInfo.provider],
    set: {
      apiKey: newSettingInfo.apiKey,
    },
    where: sql`${settingInfo.apiKey} != ${newSettingInfo.apiKey}`,
  })

.onConflictDoUpdate({
    target: [settingInfo.userId, settingInfo.provider],
    set: {
      apiKey: newSettingInfo.apiKey,
    },
    where: sql`${settingInfo.apiKey} != ${newSettingInfo.apiKey}`,
  })

and the error i get for this is

NeonDbError: there is no unique or exclusion constraint matching the ON CONFLICT specification
    at execute 
}

NeonDbError: there is no unique or exclusion constraint matching the ON CONFLICT specification
    at execute 
}

so i feel like i am close but i am clearly missing something.

1 Reply

ZynkahOP•8mo ago

I figured it out

Gaming

Programming

onConflict