onConflictDoUpdate array

Hi, is it possible to upsert/update an array?

await db.insert(organizations).values(organizationsResponse.organizations).onConflictDoUpdate({
    target:[organizations.id],
    set:{
      name:organizationsResponse.organizations.map(org=>org.name)
      
    }
  });

await db.insert(organizations).values(organizationsResponse.organizations).onConflictDoUpdate({
    target:[organizations.id],
    set:{
      name:organizationsResponse.organizations.map(org=>org.name)
      
    }
  });

I would want it to do it for all and not one by one

13 Replies

Dan•2y ago

How would you write it in SQL?

Antebios•2y ago

I ended up doing one by one. If you want the actual syntax I do this:

async addUserProfile(userProfile: NewUserProfile) {
    console.debug('useUsers.ts ==> addUserProfile ==> userProfile: ', userProfile);
    const result = await this.db.insert(tblUserProfiles).values(userProfile)
      .onConflictDoUpdate(
        {
          target: [tblUserProfiles.userId],
          set: {
            firstName: userProfile.firstName,
            lastName: userProfile.lastName,
            email: userProfile.email,
            phoneNumber: userProfile.phoneNumber,
            cellPhoneNumber: userProfile.cellPhoneNumber,
            addressLine1: userProfile.addressLine1,
            addressLine2: userProfile.addressLine2,
            city: userProfile.city,
            state: userProfile.state,
            zipCode: userProfile.zipCode,
            lastUpdateAt: new Date().toUTCString()
          }
        }
      ).returning();

async addUserProfile(userProfile: NewUserProfile) {
    console.debug('useUsers.ts ==> addUserProfile ==> userProfile: ', userProfile);
    const result = await this.db.insert(tblUserProfiles).values(userProfile)
      .onConflictDoUpdate(
        {
          target: [tblUserProfiles.userId],
          set: {
            firstName: userProfile.firstName,
            lastName: userProfile.lastName,
            email: userProfile.email,
            phoneNumber: userProfile.phoneNumber,
            cellPhoneNumber: userProfile.cellPhoneNumber,
            addressLine1: userProfile.addressLine1,
            addressLine2: userProfile.addressLine2,
            city: userProfile.city,
            state: userProfile.state,
            zipCode: userProfile.zipCode,
            lastUpdateAt: new Date().toUTCString()
          }
        }
      ).returning();

QueaOP•2y ago

thanks yea that will be easier

Mr Redactle•2y ago

Late to the party but what you want is

set: { firstName: sql`EXCLUDED.first_name`, ... }

set: { firstName: sql`EXCLUDED.first_name`, ... }

For some reason it didn't work when I used templated column names after the EXCLUDED.

Dan•2y ago

for templated column name, you need to use sql.identifier(name) like this: sql`excluded.${sql.identifier('first_name')}` to take the name from the actual Drizzle column, you can write like this: sql.identifier(tblUserProfiles.firstName.name)

Mr Redactle•2y ago

Very helpful. Do you think you could update the docs so that there is an array example? The upsert examles there are trivial.

Dan•2y ago

what do you mean by array example?

Mr Redactle•2y ago

Upserting multiple records where db.insert().values() take an array param rather than a single object.

Dan•2y ago

db.insert().values(items) it takes an array

MAST•2y ago

But can we set the value for onConflictDoUpdate based on the value that had a conflict when the values is an array?

alex•14mo ago

@MAST did you figure this out by any chance?

MAST•14mo ago

@alex you can do something like this:

onConflictDoUpdate({ title: sql`EXCLUDED.title` })

onConflictDoUpdate({ title: sql`EXCLUDED.title` })

or like this:

sql`excluded.${sql.identifier('first_name')}`

sql`excluded.${sql.identifier('first_name')}`

alex•14mo ago

Works like a charm, thank you

Gaming

Programming

onConflictDoUpdate array