onDuplicateKeyUpdate

i'm having problems using onDuplicateKeyUpdate, doing it like
// values is an array of entries
await db.insert(myTable).values(values).onDuplicateKeyUpdate({ set: {values } })
// values is an array of entries
await db.insert(myTable).values(values).onDuplicateKeyUpdate({ set: {values } })
causes an error TypeError: Cannot read properties of undefined (reading 'name') and doing it with set: { id: myTable.id } causes an error TypeError: Converting circular structure to JSON my goal is to update the whole entry/row when there's a conflict in the primary key which based on the name onDuplicateKeyUpdate gets triggered with
44 Replies
--MON--
--MON--OP15mo ago
fkkkkkk 🤣 i found the way so i just did
await db.insert(myTable).values(values).onDuplicateKeyUpdate({ set: { id: sql`${myTable.id}` } })
await db.insert(myTable).values(values).onDuplicateKeyUpdate({ set: { id: sql`${myTable.id}` } })
and do the same as id: sql`${myTable.id} for the rest of the columns new problem doing this seems to update the value to the previous one making the update worthless, but it does help in not throwing an error when inserting several batches of data now let's say i have
const values = [
{id: 1, message: "hello", lastUpdated: new Date()},
{id: 2, message: "hi", lastUpdated: new Date()},
{id: 3, message: "haha", lastUpdated: new Date()},
]
const values = [
{id: 1, message: "hello", lastUpdated: new Date()},
{id: 2, message: "hi", lastUpdated: new Date()},
{id: 3, message: "haha", lastUpdated: new Date()},
]
using this one
await db.insert(myTable).values(values).onDuplicateKeyUpdate({ set: { id: sql`${myTable.id}`, message: sql`${myTable.message}`, lastUpdated: new Date() } })
await db.insert(myTable).values(values).onDuplicateKeyUpdate({ set: { id: sql`${myTable.id}`, message: sql`${myTable.message}`, lastUpdated: new Date() } })
now doing this will update my lastupdated but not the message
Angelelz
Angelelz15mo ago
I believe you need to use target to let it know where is the conflict
--MON--
--MON--OP15mo ago
so how do i get the value being inserted to be used on the onDuplicateKeyUpdate set does that property exist? cause intellisense only shows set and no target or where property
--MON--
--MON--OP15mo ago
yeah that's the old method which does not exist in the version i am using which is version ^0.28.6 this one checks using the sql's ON DUPLICATE KEY UPDATE thing
mr_pablo
mr_pablo15mo ago
do
await db.insert(myTable)
.values({id: id, name: name, etc})
.onDuplicateKeyUpdate({
set: {name: name, etc}
})
await db.insert(myTable)
.values({id: id, name: name, etc})
.onDuplicateKeyUpdate({
set: {name: name, etc}
})
--MON--
--MON--OP15mo ago
so the only problem i now have is how do i get the current value being inserted and pass it on the set as i said, the value i am inserting is an ARRAY or Values
mr_pablo
mr_pablo15mo ago
well it can't be, it has to be an object the above code will work, its exactly what im doing in my code
--MON--
--MON--OP15mo ago
that's the problem i am facing now, how do i get the current value being inserted
Angelelz
Angelelz15mo ago
So value is an array?
mr_pablo
mr_pablo15mo ago
take drizzle out of the equation; how would you get the current value ?
--MON--
--MON--OP15mo ago
the problem of doing this is, i would have to loop and it would cause several transactions instead of just 1
mr_pablo
mr_pablo15mo ago
this doesnt seem like a drizzle issue
--MON--
--MON--OP15mo ago
yup array of entries
Angelelz
Angelelz15mo ago
I don't think that's possible
mr_pablo
mr_pablo15mo ago
put the loop inside the transaction
--MON--
--MON--OP15mo ago
const values = [
{id: 1, message: "hello", lastUpdated: new Date()},
{id: 2, message: "hi", lastUpdated: new Date()},
{id: 3, message: "haha", lastUpdated: new Date()},
]
const values = [
{id: 1, message: "hello", lastUpdated: new Date()},
{id: 2, message: "hi", lastUpdated: new Date()},
{id: 3, message: "haha", lastUpdated: new Date()},
]
like this
Angelelz
Angelelz15mo ago
You have to do it one by one like @mr_pablo_85 says
--MON--
--MON--OP15mo ago
i think it's doing something like
INSERT (),(),() INTO myTable
INSERT (),(),() INTO myTable
in raw sql so will drizzle batch those tx or will it be 1 tx per entry?
Angelelz
Angelelz15mo ago
You do all your inserts inside a transaction
--MON--
--MON--OP15mo ago
like this in the docs?
await db.transaction(async (tx) => {
await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));
await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew'));
});
await db.transaction(async (tx) => {
await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));
await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew'));
});
Angelelz
Angelelz15mo ago
Well yes, except in a loop
--MON--
--MON--OP15mo ago
ohhh i'll try it thanks i'm getting a ReferenceError: <property> is not defined error nvm fixed it
const values = [
{id: 1, message: "hello", lastUpdated: new Date()},
{id: 2, message: "hi", lastUpdated: new Date()},
{id: 3, message: "haha", lastUpdated: new Date()},
]

await db.transaction(async (tx) => {

for(let i = 0; i < values.length; i++){

const value = values.at(i)

await tx.insert(myTable)
.values(values)
.onDuplicateKeyUpdate({ set:
{ id: value.id, message: value.message, lastUpdated: value.lastUpdated } })
}

});

//or

await db.transaction(async (tx) => {

values.map(async(value)=>
await tx.insert(myTable)
.values(values)
.onDuplicateKeyUpdate({ set:
{ id: value.id, message: value.message, lastUpdated: value.lastUpdated } })
)

});
const values = [
{id: 1, message: "hello", lastUpdated: new Date()},
{id: 2, message: "hi", lastUpdated: new Date()},
{id: 3, message: "haha", lastUpdated: new Date()},
]

await db.transaction(async (tx) => {

for(let i = 0; i < values.length; i++){

const value = values.at(i)

await tx.insert(myTable)
.values(values)
.onDuplicateKeyUpdate({ set:
{ id: value.id, message: value.message, lastUpdated: value.lastUpdated } })
}

});

//or

await db.transaction(async (tx) => {

values.map(async(value)=>
await tx.insert(myTable)
.values(values)
.onDuplicateKeyUpdate({ set:
{ id: value.id, message: value.message, lastUpdated: value.lastUpdated } })
)

});
now this one works @angelelz i was inserting 100 items and just hit the connection limit... any way to solve this one?
Angelelz
Angelelz15mo ago
Connection limit? The transaction should be using only one connection
--MON--
--MON--OP15mo ago
when i use for loop
DatabaseError: 'target: mydb.-.primary: vttablet: rpc error: code = ResourceExhausted
desc = transaction pool connection limit exceeded (CallerID: ...)'
DatabaseError: 'target: mydb.-.primary: vttablet: rpc error: code = ResourceExhausted
desc = transaction pool connection limit exceeded (CallerID: ...)'
when using .map
DatabaseError: 'target: mydb.-.primary: vttablet: rpc error: code = ResourceExhausted
desc = transaction pool connection limit exceeded (CallerID: ...)'
DatabaseError: 'target: mydb.-.primary: vttablet: rpc error: code = ResourceExhausted
desc = transaction pool connection limit exceeded (CallerID: ...)'
Angelelz
Angelelz15mo ago
It seems like you're creating several transactions? I've seen a similar issue before: https://discord.com/channels/1043890932593987624/1148695514821435443
--MON--
--MON--OP15mo ago
ohh yeah i forgot 🤣 i set it in a setTimeout inside a loop
Angelelz
Angelelz15mo ago
Lol what?
--MON--
--MON--OP15mo ago
cause i'm logging the data from a Rest Api with a 15 second per request basically for loop setTimeout 15 sec fetch transaction so i'm basically creating 600+ set timeouts
Angelelz
Angelelz15mo ago
Big brain lol
--MON--
--MON--OP15mo ago
hahaha the previous method worked but the onDuplicateKey is a problem now onDuplicateKey is working using transactions but i'm getting connection limit exceeded 🤣
Angelelz
Angelelz15mo ago
Why do you need a setTimeout again?
--MON--
--MON--OP15mo ago
i'm trying to migrate it to setInterval now the reason is the REST endpoint has a 15 second per request rate limit so to prevent 429 error i used setTimeout in 15 sec interval
Angelelz
Angelelz15mo ago
Well, you'll need to create the next timeout inside the one before. That way is one after the other Man, those free tier are killing us
--MON--
--MON--OP15mo ago
i actually did an incremental timeout, so starting with 1 ms then +15000 each loop 🤣
Angelelz
Angelelz15mo ago
600 request to be made every 15 secs is 2.5 hours?
--MON--
--MON--OP15mo ago
it's a community api for another project which i am using to fetch game data and use that data for the app i am building 100 items 1 request 100 database entries per 15 sec 600+ loops yeah about 2.8 hrs 🤣
Angelelz
Angelelz15mo ago
Man I hate microservices
--MON--
--MON--OP15mo ago
well there are still pros and cons hahaha
!Stan_---__-
!Stan_---__-9mo ago
@--MON-- sry for the ping, did you figure out the solution to the 'transaction pool connection limit exceeded'? I'm following your example line by line basically and am getting the same. To note: I don't have a setTimeout like your case and i have at most 20 queries in my transaction
--MON--
--MON--OP9mo ago
which code are we talking about?
!Stan_---__-
!Stan_---__-9mo ago
all goodd sorry for the hassle, i moved to a promise.all solution instead didn't end up needing a transaction thanks anyway!
--MON--
--MON--OP9mo ago
cause this one i think will cause that tx pool connection limit error since this is generating several actions in a single transaction batch which only allows for a limited amount of actions yeah that's how i did it too, i moved it out of the db.transaction and used async then for looped those inserts also when i used this with i think planetscale the concern why i was doing it was useless i wanted to do the batched tx so it would only be using a single tx which in the per tx model would be cheap but planetscale uses per write model so it was useless and had to go back to that async for loop method
!Stan_---__-
!Stan_---__-9mo ago
yep ran into the exact same issue😅
Want results from more Discord servers?
Add your server