Variable behavior for .onConflict[X] method of .insert?
Let's say that I create an array of insert statements along the lines of db.insert(targetTable).values(targetValues) and, on a conflict, I want different behavior based on why there was a conflict.
For instance, if the conflict arises due to the
id field having a unique constraint that would be violated by the entry, I would want to update, like:
db.insert(targetTable).values(targetValues).onConflictDoUpdate({ target: targetTable.id, set: targetValues })
However, if a different field of the insert has a unique violation, I’d want to do nothing like:
db.insert(targetTable).values(targetValues).onConflictDoNothing({ target: targetTable.anotherUniqueField })
And a third field has a foreign key constraint that would be violated by the insert. I would imagine that would look like:
db.insert(targetTable).values(targetValues).onConflictDoNothing({ target: targetTable.fieldWForeignConstraint })
What I’d REALLY like to be able to do is something of my choosing on a conflict like logging the skipped entry to a file. Something like:
db.insert(targetTable).values(targetValues).onConflict((conflictObject) => {
if(conflictObject.field === “id”){
conflictObject.update({ target: targetTable.id, set: targetValues })
}
if(conflictObject.field === “anotherUniqueField” {
// write to file here
console.log("this entry was skipped do to a conflict on the field: "anotherUniqueField", targetValues);
// do nothing
return;
}
if(conflictObject.field === “fieldWForeignConstraint” {
// write to file here
console.log("this entry was skipped do to a conflict on the field: “fieldWForeignCosntraint”", targetValues);
// do nothing
return;
}
})
Is anything like this possible? Can the .onConflict[X] methods be chained maybe? I’ve found that the Drizzle documentation doesn’t have a very thorough description of the API or the methods’ arguments, etc. so I have a hard time knowing what is and isn’t possible with the package.5 Replies
Does anyone happen to have any insight into whether what I'm trying to accomplish is possible or not within the library as it currently exists?
hey, yeah, we've investigated and it does seem onConflict statements were implemented non fully compliant with SQL and we will update it in the next release
https://github.com/drizzle-team/drizzle-orm/pull/4028
GitHub
SQLite onConflict chaining fix by Sukairo-02 · Pull Request #4028 ·...
Fixed SQLite onConflict clauses being overwritten instead of stacked
added related tests
removed unused import
@alexblokh That's great! Two questions:
1. Is there documentation/examples showing how to chain and how it behaves?
2. Is there a way to do custom behavior inside an onConflict method? Something like in my question where I create a log entry before performing the onConflict behavior?
it now behaves exactly like SQL would be, every
onConflict clause just appends this clause to sql statement and you can examine this statement in every query via .toSQL
there's also no extra onConflict business logic apart from one in sql