Drizzle Team•10mo ago

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•10mo 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;

chronosOP•10mo ago

Oh cool, I seemed to have missed onDuplicateKeyUpdate. Ill try that and see. Thanks! Thank you so much this works perfectly!

D1e•10mo 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.

Gaming

Programming

Correct way of handling upserts in MySql