Levitybot
Levitybot
DTDrizzle Team
Created by nu on 2/13/2025 in #help
How to handle multiple unique constraints with onConflictDoUpdate?
I am also stuck with this. I think (not sure) the array is for composite unique constraints, not multiple individual unique constraints where both have to be independently unique. The only solution I know of at the moment is to basically do a select on the table first. If you get a result then you know its not unique and can do an update using the select results primary key. This isn't ideal because of the extra database call. If you find a better way let me know.
// Some data
const userData = {
name: "Nu",
email: "nu@example.com"
}

// Probably in a insertOrUpdate function
// First, check for conflicts manually
const existingUsers = await db
.select() // should probably limit fields here if on userTable
.from(userTable)
.where(
or(
eq(userTable.name, userData.name),
eq(userTable.email, userData.email)
)
);

if (existingUsers.length > 0) {
// If conflict exists, update instead of insert
const [updatedUser] = await db
.update(userTable)
.set(userData)
.where(eq(userTable.id, existingUsers[0].id))
.returning();
return updatedUser;
}

// Otherwise, insert new record
const [user] = await db
.insert(userTable)
.values(userData)
.returning();
return user;
// Some data
const userData = {
name: "Nu",
email: "nu@example.com"
}

// Probably in a insertOrUpdate function
// First, check for conflicts manually
const existingUsers = await db
.select() // should probably limit fields here if on userTable
.from(userTable)
.where(
or(
eq(userTable.name, userData.name),
eq(userTable.email, userData.email)
)
);

if (existingUsers.length > 0) {
// If conflict exists, update instead of insert
const [updatedUser] = await db
.update(userTable)
.set(userData)
.where(eq(userTable.id, existingUsers[0].id))
.returning();
return updatedUser;
}

// Otherwise, insert new record
const [user] = await db
.insert(userTable)
.values(userData)
.returning();
return user;
5 replies
DTDrizzle Team
Created by scape on 1/27/2025 in #help
foreignKey function options
Hi scape, I'm very new to Drizzle, but this is what I'm currently working with, hope it helps export const location = pgTable( 'location', { id: serial('id').primaryKey(), name: text('name').notNull(), companyId: integer('company_id').notNull() }, (t) => [ foreignKey({ name: 'company_has_locations_fk', columns: [t.companyId], foreignColumns: [company.id] }).onDelete('cascade') ] ); export const locationRelations = relations(location, ({ one }) => ({ company: one(company, { fields: [location.companyId], references: [company.id], relationName: 'company_has_locations' }) })); export type Location = typeof location.$inferSelect;
2 replies