DT
Drizzle Team•2y ago
kinsyu

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
kinsyu
kinsyuOP•2y 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
Mykhailo•2y 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.

Did you find this page helpful?