UPSERT many ?

Ssevenwestonroads4/23/2023
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.
Bbloberenober4/23/2023
you can pass an array to .values()
Ssevenwestonroads4/23/2023
But what should I pass to the set property ?
Bbloberenober4/23/2023
what would you pass in a raw SQL query?
Ssevenwestonroads4/23/2023
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 ๐Ÿ˜‰ )
Bbloberenober4/23/2023
you can do the same with drizzle { name: sql`excluded.name` }
Ssevenwestonroads4/23/2023
Perfect ! Thank you
JVJan Vorwerk12/29/2023
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!!
AAngelelz12/29/2023
Please add a feature request to GH so we can keep track
JVJan Vorwerk12/29/2023
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...
Ffawwaz1/27/2024
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>,
});
};
JVJan Vorwerk2/20/2024
@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)

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
missing type declarations in mysql-core imports (>v0.25.1)after upgrading drizzle-orm to v0.25.1 i get some errors in my schema telling me that `drizzle-orm/mdrizzle-orm@0.25.0 broke edge supportSeems like the ESM Support broke some stuff in the edge runtime land. This my vercel build output wiDoes Drizzle support type CUID?In Prisma i can use: id String @id @default(cuid()) Do we have similar solutions?Get column name in error messagesIs there a way to get column names exactly where the query is failing, for example when the FOREIGN Using Drizzle as a package in Turborepo doesn't work ?I have a monorepo (Turborepo) with a shared package called @mono/database in which I put my drizzle Argument of type 'Database' is not assignable to parameter of type 'D1Database'.I'm trying to use drizzle with cloudflare d1 but getting this error. Any help?drizzle-zod update schema?is there any plan for `createUpdateSchema` from `drizzle-zod`?How to reproduce a Prisma `include` statement for arrays of related entities without SQL?Hello, I'm trying to translate this SQL query to DrizzleORM; ``` SELECT channels.id, channelsmany-to-one selection as arrayHi, Let's say I have a user table, and the user can have multiple profiles. When I select using joiAlias in from() change? (SQLite)Did something change wrt using aliases in `from()`? Until recently I've been using aliases to affectTable definition has 'any' typeI'm defining all of my tables in an `index.ts` file; but on some tables like `visitor_events` I get CTE query of hierarchical dataI'd like to drizzle-ize this query: ```sql WITH RECURSIVE Child(n) AS ( VALUES('...id') createInsertSchema wrong type (drizzle-zod 0.3.1, sqlite))After upgrading to 0.3.1, all of the fields changed to ZodTypeAnyHow do I get the values of an insert inside a transaction?I have this transaction where I create a project and add variables and a history of those variables.Raw sql`` quoting issueHi, I doing a work around until Drizzle supports generated columns. I have a manual script that addsNuxt3 type errorHello, is there somebody who used Drizzle in Nuxt3 project? I have problem with inheriting types froSyntax error mysql migration using composite primary key```sql --> statement-breakpoint CREATE TABLE `member_to_address` ( `member_id` varchar(32) NOT Nwhen using planetscale, using the `.$with()` and `.with()` clauses causes errorIs it not possible to use `$with()` and `with()` with planetscale? it gives the following error: `DaZod prototype mismatchEven though the Zod object constructor name created by Drizzle is ZodObject, it is not an instanceofunrecognized_keysGetting the following error when running generate:pg ``` ZodError: [ { "code": "unrecognized_k