Drizzle Team•14mo ago

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•14mo 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•14mo 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•14mo ago

Curious what does insertid return when using onduplicatekey if there is a duplicate?

Gaming

Programming

id on transaction conflict