Matt
Matt
Explore posts from servers
TTCTheo's Typesafe CultDTDrizzle Team
PostsComments
DTDrizzle Team
Created by Matt on 3/27/2024 in #help
Upsert Multiple Rows with Conflicts postgres
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`,
          ),
        },
      });
13 replies
DTDrizzle Team
Created by Matt on 3/27/2024 in #help
Upsert Multiple Rows with Conflicts postgres
No rush at all but thanks
13 replies
DTDrizzle Team
Created by Matt on 3/27/2024 in #help
Upsert Multiple Rows with Conflicts postgres
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() },
        });
    }
13 replies
DTDrizzle Team
Created by Matt on 3/27/2024 in #help
Upsert Multiple Rows with Conflicts postgres
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']),
  });
13 replies
DTDrizzle Team
Created by Matt on 3/27/2024 in #help
Upsert Multiple Rows with Conflicts postgres
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.
13 replies
DTDrizzle Team
Created by Matt on 3/27/2024 in #help
Upsert Multiple Rows with Conflicts postgres
Oh, nice! That's exactly what I'm looking for. Thank you!
13 replies