onDuplicateKeyUpdate
i'm having problems using
onDuplicateKeyUpdate
,
doing it like
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 with44 Replies
fkkkkkk 🤣
i found the way
so i just did
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
using this one
now doing this will update my lastupdated but not the messageI believe you need to use
target
to let it know where is the conflictso 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
propertySQL Insert - DrizzleORM
Drizzle ORM | %s
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
thingdo
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
well it can't be, it has to be an object
the above code will work, its exactly what im doing in my code
that's the problem i am facing now, how do i get the current value being inserted
So value is an array?
take drizzle out of the equation; how would you get the current value ?
the problem of doing this is, i would have to loop and it would cause several transactions instead of just 1
this doesnt seem like a drizzle issue
yup
array of entries
I don't think that's possible
put the loop inside the transaction
like this
You have to do it one by one like @mr_pablo_85 says
i think it's doing something like
in raw sql so will drizzle batch those tx or will it be 1 tx per entry?
in raw sql so will drizzle batch those tx or will it be 1 tx per entry?
You do all your inserts inside a transaction
like this in the docs?
Well yes, except in a loop
ohhh i'll try it thanks
i'm getting a
ReferenceError: <property> is not defined
error
nvm fixed it
now this one works
@angelelz i was inserting 100 items and just hit the connection limit... any way to solve this one?Connection limit?
The transaction should be using only one connection
when i use for loop
when using
.map
It seems like you're creating several transactions?
I've seen a similar issue before:
https://discord.com/channels/1043890932593987624/1148695514821435443
ohh yeah i forgot 🤣 i set it in a setTimeout inside a loop
Lol what?
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
Big brain lol
hahaha the previous method worked but the onDuplicateKey is a problem
now onDuplicateKey is working using transactions but i'm getting connection limit exceeded 🤣
Why do you need a setTimeout again?
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
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
i actually did an incremental timeout, so starting with 1 ms then +15000 each loop 🤣
600 request to be made every 15 secs is 2.5 hours?
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 🤣
Man I hate microservices
well there are still pros and cons hahaha
@--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
which code are we talking about?
all goodd sorry for the hassle, i moved to a promise.all solution instead
didn't end up needing a transaction
thanks anyway!
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
yep ran into the exact same issue😅