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
bloberenober15mo ago
you can pass an array to .values()
sevenwestonroads
sevenwestonroads15mo ago
But what should I pass to the set property ?
bloberenober
bloberenober15mo ago
what would you pass in a raw SQL query?
sevenwestonroads
sevenwestonroads15mo 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
bloberenober15mo ago
you can do the same with drizzle { name: sql`excluded.name` }
sevenwestonroads
sevenwestonroads15mo ago
Perfect ! Thank you
Jan Vorwerk
Jan Vorwerk6mo 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
Angelelz6mo ago
Please add a feature request to GH so we can keep track
Jan Vorwerk
Jan Vorwerk6mo ago
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
fawwaz5mo 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
Jan Vorwerk4mo 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)