Drizzle Team•15mo ago

Replace on Insert

I can see that there is a .ignore() option added to insert, but is there an equivalent .replace() or someway to flag it in the code? I'm doing an insert many by passing through an array of values (e.g. await db.insert(ActivityTable).values(values);) which is effectively an over write of existing data as well as new data

4 Replies

machour•15mo ago

Still discovering Drizzle, but I think you're looking for onConflictDoUpdate() to make an upsert Check this: https://github.com/drizzle-team/drizzle-orm/blob/ee82b36acc3da94cc3a2f1a5c18f76e6476b1508/drizzle-orm/src/pg-core/README.md#upsert-insert-with-on-conflict-statement

Zen•15mo ago

Sorry I wasn't clear - I'm using mysql-core which doesn't have the onConflictDoUpdate chain though I'm again reminded how postgres is the superior database!

machour•15mo ago

just discovered that all drivers don't implement all methods 😅 well, using MySQL that would be a REPLACE INTO .. I see a onDuplicateKeyUpdate for MySQL tho

Zen•15mo ago

yup the REPLACE is what I'm used to using in SQL. I've never used ON DUPLICATE KEY UPDATE in SQL before, I'll check it out now - hopefully that's what I need! for reference onDuplicateKeyUpdate worked! My final code looked something like this:

db.insert(TestingTable)
  .values(values)
  .onDuplicateKeyUpdate({
    set: {
      fizz: sql`values(fizz)`,
      buzz: sql`values(buzz)`,
      fizzBuzz: sql`values(fuzz_buzz)`,
      updatedAt: sql`values(updated_at)`,
    },
  });

db.insert(TestingTable)
  .values(values)
  .onDuplicateKeyUpdate({
    set: {
      fizz: sql`values(fizz)`,
      buzz: sql`values(buzz)`,
      fizzBuzz: sql`values(fuzz_buzz)`,
      updatedAt: sql`values(updated_at)`,
    },
  });

Gaming

Programming

Replace on Insert