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--OP•16mo 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•16mo ago

I believe you need to use target to let it know where is the conflict

--MON--OP•16mo 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

Angelelz•16mo ago

It should: https://orm.drizzle.team/docs/insert#onconflict-and-upsert-insert-or-update

SQL Insert - DrizzleORM

Drizzle ORM | %s

--MON--OP•16mo 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•16mo ago

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--OP•16mo 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•16mo 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--OP•16mo ago

that's the problem i am facing now, how do i get the current value being inserted

Angelelz•16mo ago

So value is an array?

mr_pablo•16mo ago

take drizzle out of the equation; how would you get the current value ?

--MON--OP•16mo ago

the problem of doing this is, i would have to loop and it would cause several transactions instead of just 1

mr_pablo•16mo ago

this doesnt seem like a drizzle issue

--MON--OP•16mo ago

yup array of entries

Angelelz•16mo ago

I don't think that's possible

mr_pablo•16mo ago

put the loop inside the transaction

--MON--OP•16mo 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•16mo ago

You have to do it one by one like @mr_pablo_85 says

--MON--OP•16mo 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•16mo ago

You do all your inserts inside a transaction

--MON--OP•16mo 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•16mo ago

Well yes, except in a loop

--MON--OP•16mo 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•16mo ago

Connection limit? The transaction should be using only one connection

--MON--OP•16mo 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•16mo ago

It seems like you're creating several transactions? I've seen a similar issue before: https://discord.com/channels/1043890932593987624/1148695514821435443

--MON--OP•16mo ago

ohh yeah i forgot 🤣 i set it in a setTimeout inside a loop

Angelelz•16mo ago

Lol what?

--MON--OP•16mo 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•16mo ago

Big brain lol

--MON--OP•16mo 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•16mo ago

Why do you need a setTimeout again?

--MON--OP•16mo 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•16mo 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--OP•16mo ago

i actually did an incremental timeout, so starting with 1 ms then +15000 each loop 🤣

Angelelz•16mo ago

600 request to be made every 15 secs is 2.5 hours?

--MON--OP•16mo 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•16mo ago

Man I hate microservices

--MON--OP•16mo ago

well there are still pros and cons hahaha

!Stan_---__-•10mo 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--OP•10mo ago

which code are we talking about?

!Stan_---__-•10mo 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--OP•10mo 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_---__-•10mo ago

yep ran into the exact same issue😅

Gaming

Programming

onDuplicateKeyUpdate