Drizzle Team•7mo ago

Upsert Multiple Rows with Conflicts postgres

Is there a way to upsert multiple rows when there could be conflicts on some of the rows? I have this code I tried below but spreading out the userValues in the set doesn't work of course, the usersValue is an array of users that could contain information about new users or existing users with new information. Is there a way to do it all in this one command or do I need to break it down before attempting the db insert?

await db
      .insert(users)
      .values(userValues)
      .onConflictDoUpdate({
        target: users.UserId,
        set: {
          ...userValues,
          updatedAt: new Date(),
        },
      });

await db
      .insert(users)
      .values(userValues)
      .onConflictDoUpdate({
        target: users.UserId,
        set: {
          ...userValues,
          updatedAt: new Date(),
        },
      });

6 Replies

Mykhailo•7mo ago

Hello, @Matt! https://orm.drizzle.team/learn/guides/upsert

Drizzle ORM - Upsert Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Matt•7mo ago

Oh, nice! That's exactly what I'm looking for. Thank you! I'm thinking I'll just need to iterate over the users since this is still having similar issues of just overwriting rows, which makes sense. The userValues can look like this

[
  {
    userId: '1'
    value1: '2'
  },
  {
    userId: '2'
    value1: '1'
    value2: '2'
    value3: '3'
  }
]

[
  {
    userId: '1'
    value1: '2'
  },
  {
    userId: '2'
    value1: '1'
    value2: '2'
    value3: '3'
  }
]

where userId 1 is an update and userId 2 is a new user being inserted. When I run do it like the guide all the other values in userId1 are overwritten with nothing when it should really just be updating value1. Might not be possible without a for loop or something but figured I'd ask one more to see if you have thoughts.

francis•7mo ago

there's a magical table alias you can use, let me find it it's the excluded.column alias here's an example from my codebase:

        await tx
          .insert(auditMessageOrder)
          .values(messageOrders)
          .onConflictDoUpdate({
            target: [auditMessageOrder.surveyResponseId, auditMessageOrder.reportSectionName],
            set: { messageOrder: sql.raw("excluded.message_order") },
          });

        await tx
          .insert(auditMessageOrder)
          .values(messageOrders)
          .onConflictDoUpdate({
            target: [auditMessageOrder.surveyResponseId, auditMessageOrder.reportSectionName],
            set: { messageOrder: sql.raw("excluded.message_order") },
          });

This lets you reuse the values that caused the conflict in the set clause @Mykhailo possibly worth adding to the upsert docs? it's unfortunate there isn't a better API for referencing the excluded alias other than using the sql.raw function

Matt•7mo ago

Yeah, the guide above goes over that but you'll still overwrite cells in a row if it matches a conflict. Like below the row for user 1 would just be an update of the value1 column but the guide would cause columns value2 and value3 to be zero'd out.

const buildConflictUpdateColumns = <
  T extends PgTable | SQLiteTable,
  Q extends keyof T['_']['columns']
>(
  table: T,
  columns: Q[],
) => {
  const cls = getTableColumns(table);
  return columns.reduce((acc, column) => {
    const colName = cls[column].name;
    acc[column] = sql.raw(`excluded.${colName}`);
    return acc;
  }, {} as Record<Q, SQL>);
};

const userValues = [
  {
    userId: '1'
    value1: '2'
  },
  {
    userId: '2'
    value1: '1'
    value2: '2'
    value3: '3'
  }
]

await db
  .insert(users)
  .values(userValues)
  .onConflictDoUpdate({
    target: users.id,
    set: buildConflictUpdateColumns(users, ['value1', 'value2', 'value3']),
  });

const buildConflictUpdateColumns = <
  T extends PgTable | SQLiteTable,
  Q extends keyof T['_']['columns']
>(
  table: T,
  columns: Q[],
) => {
  const cls = getTableColumns(table);
  return columns.reduce((acc, column) => {
    const colName = cls[column].name;
    acc[column] = sql.raw(`excluded.${colName}`);
    return acc;
  }, {} as Record<Q, SQL>);
};

const userValues = [
  {
    userId: '1'
    value1: '2'
  },
  {
    userId: '2'
    value1: '1'
    value2: '2'
    value3: '3'
  }
]

await db
  .insert(users)
  .values(userValues)
  .onConflictDoUpdate({
    target: users.id,
    set: buildConflictUpdateColumns(users, ['value1', 'value2', 'value3']),
  });

Obviously, a simple for loop works but was wondering if I could do it all in one. Maybe just missing something.

for (const user of userValues) {
      await db
        .insert(users)
        .values(user)
        .onConflictDoUpdate({
          target: [users.userId],
          set: { ...user, updatedAt: new Date() },
        });
    }

for (const user of userValues) {
      await db
        .insert(users)
        .values(user)
        .onConflictDoUpdate({
          target: [users.userId],
          set: { ...user, updatedAt: new Date() },
        });
    }

Mykhailo•7mo ago

@Matt a bit busy now to test it, but I will come back later and we will figure out. Btw, as I see, we can improve docs with your case

Matt•7mo ago

No rush at all but thanks Seems like raw SQL does the trick - I'll keep noodling at a cleaner way to build the sql but hey it works.

await db
      .insert(users)
      .values(userValues)
      .onConflictDoUpdate({
        target: users.userId,
        set: {
          updatedAt: new Date(),
          value1: sql.raw(
            `CASE WHEN excluded.value_1 IS NOT NULL AND excluded.value_1 <> 'undefined' THEN excluded.value_1 END`
          ),
          value2: sql.raw(
            `CASE WHEN excluded.value_2 IS NOT NULL AND excluded.value_2 <> 'undefined' THEN excluded.value_2 END`
          ),
          value3: sql.raw(
            `CASE WHEN excluded.value_3 IS NOT NULL AND excluded.value_3 <> 'undefined' THEN excluded.value_3 END`,
          ),
        },
      });

await db
      .insert(users)
      .values(userValues)
      .onConflictDoUpdate({
        target: users.userId,
        set: {
          updatedAt: new Date(),
          value1: sql.raw(
            `CASE WHEN excluded.value_1 IS NOT NULL AND excluded.value_1 <> 'undefined' THEN excluded.value_1 END`
          ),
          value2: sql.raw(
            `CASE WHEN excluded.value_2 IS NOT NULL AND excluded.value_2 <> 'undefined' THEN excluded.value_2 END`
          ),
          value3: sql.raw(
            `CASE WHEN excluded.value_3 IS NOT NULL AND excluded.value_3 <> 'undefined' THEN excluded.value_3 END`,
          ),
        },
      });

Gaming

Programming

Upsert Multiple Rows with Conflicts postgres