Matt Comments - Answer Overflow

The main idea is to get the TenantUrl and Secret Token to show without the rerender and I meant to put sheet for what's shown in the video... the drawer only shows on mobile

17 replies

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

Gaming

Programming