Here's where I'm currently at: If I use onConflictDoNothing, the plan is to insert a new record into the database. However, 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 without throwing an error.
.onConflictDoNothing({
target: [userId, newSettingInfo.provider],
where: sql`${settingInfo.apiKey} != ${newSettingInfo.apiKey}`,
})
I encountered a 500 error code that reads:
NeonDbError: syntax error at or near "where"
at execute
I suspect this error occurs because a where clause cannot be used in the do nothing option.
So, I tried using 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, then the existing record is updated with the new apiKey. If they are equal, the insert operation is skipped without any errors being thrown.
.onConflictDoUpdate({
target: [settingInfo.userId, settingInfo.provider],
set: {
apiKey: newSettingInfo.apiKey,
},
where: sql`${settingInfo.apiKey} != ${newSettingInfo.apiKey}`,
})
The error message I received for this attempt was:
NeonDbError: there is no unique or exclusion constraint matching the ON CONFLICT specification
at execute
}
It seems like I am close to the solution but missing something crucial. My goal is to avoid having duplicate entries for api keys.