Drizzle Team•5mo ago

How to dynamically set a row value using data from a JS object? (onConflictDoUpdate)

I am: - Checking if a row already exists - If it doesn't: Insert the data array's object - If it exists: Update the price column - The issue is I'm not sure how to set a row to the relevant object in my data array

const data = [
  {organisationId: "ABCD", accountId: "001", price: "120000"},
  {organisationId: "EFGH", accountId: "002", price: "59000"},
];

await db
  .insert(myTable)
  .values(data)
  .onConflictDoUpdate({
    target: [myTable.organisationId, myTable.financialAccountId],
    set: data[0], // Putting [0] works but how to dynamically set the value to the relevant object in data array?
  });

const data = [
  {organisationId: "ABCD", accountId: "001", price: "120000"},
  {organisationId: "EFGH", accountId: "002", price: "59000"},
];

await db
  .insert(myTable)
  .values(data)
  .onConflictDoUpdate({
    target: [myTable.organisationId, myTable.financialAccountId],
    set: data[0], // Putting [0] works but how to dynamically set the value to the relevant object in data array?
  });

10 Replies

Sillvva•5mo ago

https://orm.drizzle.team/learn/guides/upsert

Drizzle ORM - Upsert Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

xvx•5mo ago

Sorry which part of that?

A Dapper Raccoon•5mo ago

To upsert multiple rows in one query in PostgreSQL and SQLite you can use sql operator and excluded keyword. excluded is a special reference that refer to the row that was proposed for insertion, but wasn’t inserted because of the conflict. This is how you can do it:
// ...
await db
 .insert(users)
 .values(values)
 .onConflictDoUpdate({
   target: users.id,
   set: { lastLogin: sql.raw(`excluded.${users.lastLogin.name}`) },
 });
// ...
await db
 .insert(users)
 .values(values)
 .onConflictDoUpdate({
   target: users.id,
   set: { lastLogin: sql.raw(`excluded.${users.lastLogin.name}`) },
 });

xvx•5mo ago

Thanks I implemented it. Trying to fix there is no unique or exclusion constraint matching the ON CONFLICT specification error now

Sillvva•5mo ago

Do you have a unique constraint on organisationId and financialAccountId?

xvx•5mo ago

@Sylvain For financialAccountId yes it's the primaryKey. It isn't for organisationId but I tried removing that from target and it still persists

Sillvva•5mo ago

Hmm, it should work so long as the conflict target is unique by its definition/constraints. The values you're inserting also includes the financialAccountId?

xvx•5mo ago

yep, the values object:

{
      organisationId: '7b28dcbd-1f0c-4c00-b176-2e03356c6c3e',
      financialAccountId: '970374f4-f594-41c1-a838-2c3a930e320b',
      financialAccountName: 'x',
      financialAccountClass: 'revenue',
      financialAccountType: 'revenue',
      price: '19994.12'
    },
    {
      organisationId: '7b28dcbd-1f0c-4c00-b176-2e03356c6c3e',
      financialAccountId: '2588eeb2-07bf-45cc-aebc-df3776dbf5ed',
      financialAccountName: 'y',
      financialAccountClass: 'revenue',
      financialAccountType: 'revenue',
      price: '95448.05'
    },
    {
      organisationId: '7b28dcbd-1f0c-4c00-b176-2e03356c6c3e',
      financialAccountId: '6f665285-27cf-45e2-a16a-6ab31be6a874',
      financialAccountName: 'z',
      financialAccountClass: 'revenue',
      financialAccountType: 'revenue',
      price: '64717.97'
    }

{
      organisationId: '7b28dcbd-1f0c-4c00-b176-2e03356c6c3e',
      financialAccountId: '970374f4-f594-41c1-a838-2c3a930e320b',
      financialAccountName: 'x',
      financialAccountClass: 'revenue',
      financialAccountType: 'revenue',
      price: '19994.12'
    },
    {
      organisationId: '7b28dcbd-1f0c-4c00-b176-2e03356c6c3e',
      financialAccountId: '2588eeb2-07bf-45cc-aebc-df3776dbf5ed',
      financialAccountName: 'y',
      financialAccountClass: 'revenue',
      financialAccountType: 'revenue',
      price: '95448.05'
    },
    {
      organisationId: '7b28dcbd-1f0c-4c00-b176-2e03356c6c3e',
      financialAccountId: '6f665285-27cf-45e2-a16a-6ab31be6a874',
      financialAccountName: 'z',
      financialAccountClass: 'revenue',
      financialAccountType: 'revenue',
      price: '64717.97'
    }

The schema looks like it's properly constrained to me:

export const financialAccountTable = pgTable("financial_account", {
  organisationId: uuid("organisation_id")
    .notNull()
    .references(() => organisationTable.organisationId, { onDelete: "cascade" }),
  financialAccountId: uuid("financial_account_id")
    .primaryKey()
    .unique()
    .notNull(),
  financialAccountName: text("financial_account_name").notNull(),
  financialAccountType: text("financial_account_type")
  .notNull(),
  financialAccountClass: text("financial_account_class")
  .notNull(),
  price: decimal("price").notNull(),
  customId: uuid("custom_id")
    .unique(),
  customName: text(
    "custom_name",
  ).unique(),
});

export const financialAccountTable = pgTable("financial_account", {
  organisationId: uuid("organisation_id")
    .notNull()
    .references(() => organisationTable.organisationId, { onDelete: "cascade" }),
  financialAccountId: uuid("financial_account_id")
    .primaryKey()
    .unique()
    .notNull(),
  financialAccountName: text("financial_account_name").notNull(),
  financialAccountType: text("financial_account_type")
  .notNull(),
  financialAccountClass: text("financial_account_class")
  .notNull(),
  price: decimal("price").notNull(),
  customId: uuid("custom_id")
    .unique(),
  customName: text(
    "custom_name",
  ).unique(),
});

Sillvva•5mo ago

Yeah, I can't see any issue with that. I can't think of anything else off the top of my head, unfortunately.

benjick•3mo ago

Did you manage to solve this?

Gaming

Programming

How to dynamically set a row value using data from a JS object? (onConflictDoUpdate)