DT
Drizzle Team•3w ago
AndréLB

Upsert on foreign key changes serial ID

      const employeeStoresRes = await tx
        .insert(employeeStore)
        .values(employeeIDStoreID)
        .onConflictDoNothing()
        .returning({ storeID: employeeStore.storeID })

export const employeeStore = pgTable(
  'employeeStore',
  {
    employeeStoreID: serial().$type<EmployeeStoreID>().primaryKey(),
    storeID: integer()
      .$type<StoreID>()
      .references(() => stores.storeID, {
        onDelete: 'cascade',
      })
      .notNull(),
    employeeID: integer()
      .$type<EmployeeID>()
      .references(() => employees.employeeID, {
        onDelete: 'cascade',
      })
      .notNull(),
    ...dbDates,
  },
  (employeeStore) => {
    return {
      unique: unique('unique_employeeStore').on(employeeStore.storeID, employeeStore.employeeID),
    }
  },
)

export const employeeStoreRelations = relations(employeeStore, ({ one, many }) => ({
  employees: one(employees),
  stores: one(stores),
  employeeCheckin: many(employeeCheckin),
}))

export const employeeCheckin = pgTable(
  'employeeCheckin',
  {
    employeeCheckinID: serial().$type<EmployeeCheckinID>().primaryKey(),
    employeeStoreID: integer()
      .$type<EmployeeStoreID>()
      .references(() => employeeStore.employeeStoreID, {
        onDelete: 'cascade',
      }),
    employeeCheckedIn: timestamp({ mode: 'date' }),
    employeeCheckedOut: timestamp({ mode: 'date' }),
  },
  (employeeCheckin) => {
    return {
      index: index('checkinIndex').on(employeeCheckin.employeeCheckedIn),
    }
  },
)
      const employeeStoresRes = await tx
        .insert(employeeStore)
        .values(employeeIDStoreID)
        .onConflictDoNothing()
        .returning({ storeID: employeeStore.storeID })

export const employeeStore = pgTable(
  'employeeStore',
  {
    employeeStoreID: serial().$type<EmployeeStoreID>().primaryKey(),
    storeID: integer()
      .$type<StoreID>()
      .references(() => stores.storeID, {
        onDelete: 'cascade',
      })
      .notNull(),
    employeeID: integer()
      .$type<EmployeeID>()
      .references(() => employees.employeeID, {
        onDelete: 'cascade',
      })
      .notNull(),
    ...dbDates,
  },
  (employeeStore) => {
    return {
      unique: unique('unique_employeeStore').on(employeeStore.storeID, employeeStore.employeeID),
    }
  },
)

export const employeeStoreRelations = relations(employeeStore, ({ one, many }) => ({
  employees: one(employees),
  stores: one(stores),
  employeeCheckin: many(employeeCheckin),
}))

export const employeeCheckin = pgTable(
  'employeeCheckin',
  {
    employeeCheckinID: serial().$type<EmployeeCheckinID>().primaryKey(),
    employeeStoreID: integer()
      .$type<EmployeeStoreID>()
      .references(() => employeeStore.employeeStoreID, {
        onDelete: 'cascade',
      }),
    employeeCheckedIn: timestamp({ mode: 'date' }),
    employeeCheckedOut: timestamp({ mode: 'date' }),
  },
  (employeeCheckin) => {
    return {
      index: index('checkinIndex').on(employeeCheckin.employeeCheckedIn),
    }
  },
)
The problem is that that even when doing nothing the employeeStoreID is being updated on the employeeStore table. This means the employeeCheckin table has an outdated value for employeeStoreID. I use employeeStoreID as a foreign key in many places so it won't be an easy refactor to have a composite key.
2 Replies
TOSL
TOSL•3w ago
explicitly specify a conflict target and see if that helps
Mario564
Mario564•3w ago
@AndréLB What's the query that's causing this issue? Nothing can be updating by "doing nothing", there has to be a query being run that's causing this issue If there's not a query in your TS/JS codebase that's causing the issue, have you verified that there aren't any DB triggers causing this?

Did you find this page helpful?