DT
Drizzle Team•13mo ago
chronos

Correct way of handling upserts in MySql

Hey folks! I am using MySql and cannot use onConflictDoUpdate. In leu of that I am wondering if this is a correct way of doing upserts 
const updated = await db
  .update(...)
  .set(...)
  .where(eq(..))

// If no rows updated, create new row
if (updated[0].affectedRows === 0) {
  await db.insert(...).values(...)
}
const updated = await db
  .update(...)
  .set(...)
  .where(eq(..))

// If no rows updated, create new row
if (updated[0].affectedRows === 0) {
  await db.insert(...).values(...)
}
In my use case this record is going to be created once and then updated several times.
3 Replies
D1e
D1e•13mo ago
Have just started using drizzle and my first take was this: 
const result = await db
        .insert(examples)
        .values(input)
        .onDuplicateKeyUpdate({
          set: { ...input },
        });

const updated = {
  id: result[0].insertId || (input.id as number),
};
return updated;
const result = await db
        .insert(examples)
        .values(input)
        .onDuplicateKeyUpdate({
          set: { ...input },
        });

const updated = {
  id: result[0].insertId || (input.id as number),
};
return updated;
chronos
chronosOP•13mo ago
Oh cool, I seemed to have missed onDuplicateKeyUpdate. Ill try that and see. Thanks! Thank you so much this works perfectly!
D1e
D1e•13mo ago
Awesome:) It is a little bit oversimplified, but I guess as long as you do simple data update operations and validate your input schemas with something like zod, it should be fairly safe.

Did you find this page helpful?