Drizzle Team•8mo ago

How to do "onConflictDoUpdate" when inserting an array of values?

I want to add multiple rows to my table but it has a unique key, so it has to update the row if there is a conflict. How do I do this with the ORM?

await db
  .insert(items)
  .values(
    chunk.map((item) => ({
      auctionHouseId: Number(auctionHouseId),
      itemId: item.itemId,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [items.itemId, items.auctionHouseId],
    set: {
      // What do I put in here? This is outside of the `map` context
    },
  });

await db
  .insert(items)
  .values(
    chunk.map((item) => ({
      auctionHouseId: Number(auctionHouseId),
      itemId: item.itemId,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [items.itemId, items.auctionHouseId],
    set: {
      // What do I put in here? This is outside of the `map` context
    },
  });

11 Replies

Meexa•8mo ago

These are multiple ~2000 row inserts, so I don't think I want to wrap the map around the insert, that would be 10k+ insert queries

Angelelz•8mo ago

From sqlite docs: https://sqlite.org/lang_upsert.html

Column names in the expressions of a DO UPDATE refer to the original unchanged value of the column, before the attempted INSERT. To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name.

So:

await db
  .insert(items)
  .values(
    chunk.map((item) => ({
      auctionHouseId: Number(auctionHouseId),
      itemId: item.itemId,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [items.itemId, items.auctionHouseId],
    set: {
      yourColumnName: sql`excluded.yourColumnName`
    },
  });

await db
  .insert(items)
  .values(
    chunk.map((item) => ({
      auctionHouseId: Number(auctionHouseId),
      itemId: item.itemId,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [items.itemId, items.auctionHouseId],
    set: {
      yourColumnName: sql`excluded.yourColumnName`
    },
  });

Meexa•8mo ago

Ah yeah, "excluded" is something that copilot suggested but it added sql.excluded which wasnt a thing Nice, thanks, will try this

Angelelz•8mo ago

BTW, yourColumnName should be the name of the column in the database, not the name in js Say a column is name: text("name_column"), your db column name is name_column

Meexa•8mo ago

Got it! I think it's working. Thanks a lot!

ak4zh•8mo ago

I have a similar situation with composite unique index.

itemAutionIndex: uniqueIndex('item_auction_idx').on(items.itemId, items.auctionHouseId)

itemAutionIndex: uniqueIndex('item_auction_idx').on(items.itemId, items.auctionHouseId)

How can I use the index as the target? I tried the above format:

await db
  .insert(items)
  .values(
    chunk.map((item) => ({
      auctionHouseId: Number(auctionHouseId),
      itemId: item.itemId,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [items.itemId, items.auctionHouseId],
    set: {
      yourColumnName: sql`excluded.yourColumnName`
    },
  });

await db
  .insert(items)
  .values(
    chunk.map((item) => ({
      auctionHouseId: Number(auctionHouseId),
      itemId: item.itemId,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [items.itemId, items.auctionHouseId],
    set: {
      yourColumnName: sql`excluded.yourColumnName`
    },
  });

But this results in an error:

error: ON CONFLICT DO UPDATE command cannot affect row a second time
{
  length: 239,
  severity: 'ERROR',
  code: '21000',
  detail: undefined,
  hint: 'Ensure that no rows proposed for insertion within the same command have duplicate constrained values.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'nodeModifyTable.c',
  line: '2564',
  routine: 'ExecOnConflictUpdate'
}

error: ON CONFLICT DO UPDATE command cannot affect row a second time
{
  length: 239,
  severity: 'ERROR',
  code: '21000',
  detail: undefined,
  hint: 'Ensure that no rows proposed for insertion within the same command have duplicate constrained values.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'nodeModifyTable.c',
  line: '2564',
  routine: 'ExecOnConflictUpdate'
}

I have verified that I am not passing any duplicate values in an array.

Angelelz•8mo ago

You have multiple indexes having conflicts and the DB doesn't like it

sigbotai•8mo ago

sqlexcluded.yourColumnName this does not work with postgres. It throws an error: column excluded.yourColumnName does not exist In my case, I am doing:

await db
      .insert(posts)
      .values([
        { id: 2, text: "I am trying to insert something", authorId: 3 },
        { id: 3, text: "I am tired of trying", authorId: 3 },
      ])
      .onConflictDoUpdate({ target: posts.id, set: { text: sql`excluded.text` } })

await db
      .insert(posts)
      .values([
        { id: 2, text: "I am trying to insert something", authorId: 3 },
        { id: 3, text: "I am tired of trying", authorId: 3 },
      ])
      .onConflictDoUpdate({ target: posts.id, set: { text: sql`excluded.text` } })

So, I get the error: column excluded.text does not exist

Angelelz•8mo ago

Maybe your column text in ts is not called the same in the database Can you show your posts table definition

sigbotai•8mo ago

Oh, I have figured it out. I made a mistake in my post table definition

Keroz•7mo ago

I am having this same issue, could you tell me how did you fix it?

Gaming

Programming

How to do "onConflictDoUpdate" when inserting an array of values?