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

bloberenober•14mo ago

How would you write it in SQL?

Antebios•13mo 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();

Quea•13mo ago

thanks yea that will be easier

Mr Redactle•12mo 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.

bloberenober•12mo 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•12mo ago

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

bloberenober•12mo ago

what do you mean by array example?

Mr Redactle•12mo ago

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

bloberenober•12mo ago

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

MAST•12mo ago

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

alex•9mo ago

@MAST did you figure this out by any chance?

MAST•9mo 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•9mo ago

Works like a charm, thank you

Gaming

Programming

onConflictDoUpdate array