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•17mo 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•17mo 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•17mo 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•17mo ago

what do you mean by array example?

Mr Redactle•17mo ago

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

Dan•17mo ago

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

MAST•17mo ago

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

alex•13mo ago

@MAST did you figure this out by any chance?

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

Works like a charm, thank you

Gaming

Programming

onConflictDoUpdate array