Does onConflictDoUpdate work with composite primary keys?

I have a model defined below:

export const userDevices = pgTable(
  'user_devices',
  {
    id: text('id').notNull(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, {
        onDelete: 'cascade',
      }),
      ...
  },
  (table) => ({
    idUserIdPk: primaryKey(table.id, table.userId),
  }),
);

export const userDevices = pgTable(
  'user_devices',
  {
    id: text('id').notNull(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, {
        onDelete: 'cascade',
      }),
      ...
  },
  (table) => ({
    idUserIdPk: primaryKey(table.id, table.userId),
  }),
);

I want to upsert a row into this table but I'm not sure how to set target to the composite primary key. Is this possible?

return this.drizzleService.db
      .insert(userDevices)
      .values(data)
      .onConflictDoUpdate({
        target: /* What to do here? */,
        set: data,
      });

return this.drizzleService.db
      .insert(userDevices)
      .values(data)
      .onConflictDoUpdate({
        target: /* What to do here? */,
        set: data,
      });

2 Replies

tmcw•2y ago

try something like this?

target: [table.id, table.userId]

target: [table.id, table.userId]

NoahhOP•2y ago

Seems like that worked! I have yet to actually run it but the types don't disagree :)

Gaming

Programming

Does onConflictDoUpdate work with composite primary keys?