Drizzle Team•8mo ago

onConflictDoUpdate excluded with a dynamic column

Hey, I'm trying to perform an insert where it's possible that the record already exists, therefore I'm using onConflictDoUpdate, the problem I'm running into is that I can't dynamically set the column to update, here's my current approach:

export async function editGroupSettings(
  db: Db,
  option: string,
  groupId: number,
  userId: string,
  value: number,
) {
  await db
    .insert(groupAlertsConfigTable)
    .values({
      groupId: groupId,
      userId: userId,
      [option]: value,
    })
    .onConflictDoUpdate({
      target: [groupAlertsConfigTable.mafiaId, groupAlertsConfigTable.userId],
      set: {
        [option]: sql`EXCLUDED."${option}"`,
      },
      where: and(
        eq(groupAlertsConfigTable.groupId, groupId),
        eq(groupAlertsConfigTable.userId, userId),
      ),
    });
}

editGroupSettings(db, "minMCap", 1, "ry73wkah5ojhl76k", 2);

export async function editGroupSettings(
  db: Db,
  option: string,
  groupId: number,
  userId: string,
  value: number,
) {
  await db
    .insert(groupAlertsConfigTable)
    .values({
      groupId: groupId,
      userId: userId,
      [option]: value,
    })
    .onConflictDoUpdate({
      target: [groupAlertsConfigTable.mafiaId, groupAlertsConfigTable.userId],
      set: {
        [option]: sql`EXCLUDED."${option}"`,
      },
      where: and(
        eq(groupAlertsConfigTable.groupId, groupId),
        eq(groupAlertsConfigTable.userId, userId),
      ),
    });
}

editGroupSettings(db, "minMCap", 1, "ry73wkah5ojhl76k", 2);

I'm getting the following error when executing this

PostgresError: column excluded.$4 does not exist

PostgresError: column excluded.$4 does not exist

2 Replies

kinsyuOP•8mo ago

Here's the schema

export const groupAlertsConfigTable = pgTable(
  "groupAlertConfig",
  {
    id: serial("id").primaryKey(),
    groupId: integer("groupId").notNull(),
    userId: text("userId").notNull(),
    minMembers: integer("minMembers"),
    minMCap: integer("minMCap"),
    maxTime: integer("maxTime"),
  },
  (tbl) => ({
    userFk: foreignKey({
      columns: [tbl.userId],
      foreignColumns: [usersTable.id],
    }),
    groupFk: foreignKey({
      columns: [tbl.groupId],
      foreignColumns: [groupsTable.id],
    }),
    userGroupUniq: unique("unique_group_user").on(tbl.userId, tbl.grupId),
  }),
);

export const groupAlertsConfigTable = pgTable(
  "groupAlertConfig",
  {
    id: serial("id").primaryKey(),
    groupId: integer("groupId").notNull(),
    userId: text("userId").notNull(),
    minMembers: integer("minMembers"),
    minMCap: integer("minMCap"),
    maxTime: integer("maxTime"),
  },
  (tbl) => ({
    userFk: foreignKey({
      columns: [tbl.userId],
      foreignColumns: [usersTable.id],
    }),
    groupFk: foreignKey({
      columns: [tbl.groupId],
      foreignColumns: [groupsTable.id],
    }),
    userGroupUniq: unique("unique_group_user").on(tbl.userId, tbl.grupId),
  }),
);

the idea is to be able to dynamically pass either minMembers, minMcap or maxTime through a parameter to the function, and update that field.

Mykhailo•7mo ago

Hey @kinsyu! We have guide for this case

set: {
   [option]: sql.raw(`excluded.${groupAlertsConfigTable.minMCap.name}`),
},

set: {
   [option]: sql.raw(`excluded.${groupAlertsConfigTable.minMCap.name}`),
},

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.

Gaming

Programming

onConflictDoUpdate excluded with a dynamic column