Multiple `onConflictDoUpdate()`
Is there a cleaner way to do this? I tried
case and where in the sql statements in one vs chaining, but that didn't work
const stmt = db.insert(characters).values(chars)
.onConflictDoUpdate({
target: characters.id,
set: {
name: sql`excluded.name`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
where: sql`excluded.name is not null`
})
.onConflictDoUpdate({
target: characters.id,
set: {
level: sql`excluded.level`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
where: sql`excluded.level is not null`
})
.onConflictDoUpdate({
target: characters.id,
set: {
factionId: sql`excluded.faction_id`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
where: sql`excluded.faction_id is not null`
})
.onConflictDoUpdate({
target: characters.id,
set: {
foreground: sql`excluded.foreground`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
where: sql`excluded.foreground is not null`
})
.onConflictDoUpdate({
target: characters.id,
set: {
background: sql`excluded.background`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
where: sql`excluded.background is not null`
})
const stmt = db.insert(characters).values(chars)
.onConflictDoUpdate({
target: characters.id,
set: {
name: sql`excluded.name`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
where: sql`excluded.name is not null`
})
.onConflictDoUpdate({
target: characters.id,
set: {
level: sql`excluded.level`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
where: sql`excluded.level is not null`
})
.onConflictDoUpdate({
target: characters.id,
set: {
factionId: sql`excluded.faction_id`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
where: sql`excluded.faction_id is not null`
})
.onConflictDoUpdate({
target: characters.id,
set: {
foreground: sql`excluded.foreground`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
where: sql`excluded.foreground is not null`
})
.onConflictDoUpdate({
target: characters.id,
set: {
background: sql`excluded.background`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
where: sql`excluded.background is not null`
})
2 Replies
seems like trying to clean this up with a
.$dynamic() doesn't work for .onConflictUpdate
current solution, though I want to be able to input the table column names in a type safe way
const stmt = db.insert(characters).values(chars)
.onConflictDoUpdate({
target: characters.id,
set: {
name: sql`COALESCE(EXCLUDED.name, characters.name)`,
guildId: sql`COALESCE(EXCLUDED.guild_id, characters.guild_id)`,
level: sql`COALESCE(EXCLUDED.level,characters.level)`,
factionId: sql`COALESCE(EXCLUDED.faction_id,faction_id)`,
foreground: sql`COALESCE(EXCLUDED.foreground,characters.foreground)`,
background: sql`COALESCE(EXCLUDED.background,characters.background)`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
})
const stmt = db.insert(characters).values(chars)
.onConflictDoUpdate({
target: characters.id,
set: {
name: sql`COALESCE(EXCLUDED.name, characters.name)`,
guildId: sql`COALESCE(EXCLUDED.guild_id, characters.guild_id)`,
level: sql`COALESCE(EXCLUDED.level,characters.level)`,
factionId: sql`COALESCE(EXCLUDED.faction_id,faction_id)`,
foreground: sql`COALESCE(EXCLUDED.foreground,characters.foreground)`,
background: sql`COALESCE(EXCLUDED.background,characters.background)`,
updatedAt: sql`CURRENT_TIMESTAMP`
},
})
Calling the same method more than once is not supported. Your workaround is the way to go.