id on transaction conflict

hey all, using mysql (planetscale). when a transaction conflict happens over an insert with the same 'serial' id i believe that mysql will resolve this by changing one of the inserts to the next available. my concern is that within a transaction i want to reference the returned id to use as a reference. will this id match the 'new' id after the conflict has resolved, or will it reference the original? here is some code to better explain maybe:
async function test() {
await db.transaction(async (tx) => {
const insertRecord = await tx.insert(record).values({
foo: "bar",
});

await tx
.insert(user)
.values({
foo: "bar",
recordId: parseInt(insertRecord.insertId) // is insertId ok on conflict?
});
});
}
async function test() {
await db.transaction(async (tx) => {
const insertRecord = await tx.insert(record).values({
foo: "bar",
});

await tx
.insert(user)
.values({
foo: "bar",
recordId: parseInt(insertRecord.insertId) // is insertId ok on conflict?
});
});
}
This is my first time trying to make something production ready so want to make sure all situations are covered, any help is appreciated thank you!
3 Replies
Angelelz
Angelelz16mo ago
The default behavior of Mysql innoDB is to throw an error if you attempt to insert with a duplicate key If recordID has an unique index in the user table, and you attempt to insert a duplicate, and error will be thrown from the database and the transaction will be rolled back
Angelelz
Angelelz16mo ago
If this is not the behavior you want, you may want to look into the on duplicate key update clause: https://orm.drizzle.team/docs/insert#on-duplicate-key-update
SQL Insert - DrizzleORM
Drizzle ORM | %s
DivMode
DivMode15mo ago
Curious what does insertid return when using onduplicatekey if there is a duplicate?
Want results from more Discord servers?
Add your server