Drizzle Team•15mo ago

UPSERT many ?

I have a table called crmCompanies containing companies from my client's CRMs. Each day, I receive an array with all of the companies of my client. The thing is: I need to insert a new company in crmCompanies if it doesn't exist otherwise, update all its values. Here's the syntax from the docs (https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/README.md#upsert-insert-with-on-conflict-statement); await db.insert(users).values({ id: 1, name: 'Dan' }).onConflictDoUpdate({ target: users.id, set: { name: 'John' } }); But because I have an array - I'd like to do an UPSERT on the whole array, instead of looping through it with a for loop - if that makes sense. Do you know if / how I could achieve that ? Much appreciated.

11 Replies

bloberenober•15mo ago

you can pass an array to .values()

sevenwestonroads•15mo ago

But what should I pass to the set property ?

bloberenober•15mo ago

what would you pass in a raw SQL query?

sevenwestonroads•15mo ago

This;

INSERT INTO ${crmCompanies} (
      name,
      ...
    ) VALUES ${sql.raw(values)}
    ON CONFLICT (id) DO UPDATE SET
      name = EXCLUDED.name,
      ...,
  `;

INSERT INTO ${crmCompanies} (
      name,
      ...
    ) VALUES ${sql.raw(values)}
    ON CONFLICT (id) DO UPDATE SET
      name = EXCLUDED.name,
      ...,
  `;

(I'm trying to run a RAW SQL query until I figure it out 😉 )

bloberenober•15mo ago

you can do the same with drizzle { name: sql`excluded.name` }

sevenwestonroads•15mo ago

Perfect ! Thank you

Jan Vorwerk•7mo ago

Hello, I am resurecting an old discussion... How would you deal with a systematic update of all fields when you pass in an object? I came up with something like that:

await db
    .insert(crmCompanies)
    .values(values)
    .onConflictDoUpdate({
      target: crmCompanies.id,
      set: Object.assign(
        {},
        ...Object.keys(values[0])
          .filter((k) => k !== "id")
          .map((k) => ({ [k]: sql`excluded.${k}` })),
      ) as Partial<CrmCompanies>,
    });

await db
    .insert(crmCompanies)
    .values(values)
    .onConflictDoUpdate({
      target: crmCompanies.id,
      set: Object.assign(
        {},
        ...Object.keys(values[0])
          .filter((k) => k !== "id")
          .map((k) => ({ [k]: sql`excluded.${k}` })),
      ) as Partial<CrmCompanies>,
    });

Would it not make sense to have this directly available in Drizzle? Thanks!!

Angelelz•7mo ago

Please add a feature request to GH so we can keep track

Jan Vorwerk•7mo ago

Done => https://github.com/drizzle-team/drizzle-orm/issues/1728 Thanks for your reply 🙏

GitHub

[FEATURE]: onConflictDoUpdate() set many · Issue #1728 · drizzle-te...

Describe what you want This is a follow-up of a discussion on discord The idea is that, when inserting an array of object values, it's a bit complex to ask for an update for value which cause a...

fawwaz•6mo ago

I might be wrong as I'm not an sql expert but @Jan Vorwerk shouldn't you catch all the keys instead of just the keys of the first element in the array, something like this?

const saveAll = async (usersData: Array<NewUser | UpdateUser>) => {
  await db
    .insert(users)
    .values(usersData)
    .onConflictDoUpdate({
      target: users.id,
      set: Object.assign(
        {},
        [...new Set(usersData.map((user) => Object.keys(user)).flat())].filter((k) => k !== "id").map((k) => ({ [k]: sql`excluded.${k}` }))
      ) as Partial<UpdateUser>,
    });
};

const saveAll = async (usersData: Array<NewUser | UpdateUser>) => {
  await db
    .insert(users)
    .values(usersData)
    .onConflictDoUpdate({
      target: users.id,
      set: Object.assign(
        {},
        [...new Set(usersData.map((user) => Object.keys(user)).flat())].filter((k) => k !== "id").map((k) => ({ [k]: sql`excluded.${k}` }))
      ) as Partial<UpdateUser>,
    });
};

Jan Vorwerk•5mo ago

@fawwaz , apologies for my very late response... I missed your reply. Honnestly? I really don't know! 😅 That's why, it seems better that the framework deals with this for us (me)

Gaming

Programming

UPSERT many ?