[FEATURE]: onConflictDoUpdate() set many

[janvorwerk]

Describe what you want

This is a follow-up of a discussion on discord

The idea is that, when inserting an array of object values, it's a bit complex to ask for an update for value which cause a conflict. I came up with something like the following

const values: CrmCompanies[] = ... ;

await db
    .insert(crmCompanies)
    .values(values)
    .onConflictDoUpdate({
      target: crmCompanies.id,
      set: Object.assign(
        {},
        ...Object.keys(values[0])
          .filter((k) => k !== "id")
          .map((k) => ({ [k]: sql`excluded.${k}` })),
      ) as Partial<CrmCompanies>,
    });

As you can see, the syntax is not very easy to come up with, and we lose type information. Ideally, I would rather write something such as (not necessarily that exact syntax of course):

const values: CrmCompanies[] = ... ;

await db
    .insert(crmCompanies)
    .values(values)
    .onConflictDoUpdate({
      target: crmCompanies.id,
      set: values,
    });

[janvorwerk]

I realized that I oversimplified my code above... it no longer works.
Given a utility function such as:

function keysFromObject<T extends object>(object: T): (keyof T)[] {
  return Object.keys(object) as (keyof T)[];
}

this code seems to work:

await skDrizzleDb
  .insert(crmCompanies)
  .values(values)
  .onConflictDoUpdate({
    target: crmCompanies.id,
    set: Object.assign(
      {},
      ...keysFromObject(values[0])
        .filter((k) => k !== "id")
        .map((k) => ({ [k]: sql.raw(`excluded.${crmCompanies[k].name}`) })),
    ) as Partial<CrmCompany>,
  });

[lxia1220]

how about like this?

const values: CrmCompanies[] = ... ;

await db
    .insert(crmCompanies)
    .values(values)
    .onConflictDoUpdate({
      target: crmCompanies.id,
      set: ({excluded}) => ({
        value: excluded.value
      }),
    });

[Angelelz]

I haven't looked into this but doesn't this work?

await db
    .insert(crmCompanies)
    .values(values)
    .onConflictDoUpdate({
      target: crmCompanies.id,
      set: {
         value: sql`excluded.value`,
         anotherValue: sql`excluded.anotherValue`,
         ... etc
      },
    });

Or listing all the columns is what you're trying to avoid?

[janvorwerk]

Or listing all the columns is what you're trying to avoid?

Yes, listing all the columns is what I refuse to do because the day I add a new column is when the sh** hits the fan: nothing will complain (no type issue, ...) but one data will be let on the side.

[janvorwerk]

    set: ({excluded}) => ({
        value: excluded.value
      }),

@lxia1220, I don't think that set can be given such a function... can it?

[richard-edwards]

@Angelelz thanks .. that works great .. should be in the docs

    await db.transaction(async (db) => {
      await db.insert(player).values(results)
        .onConflictDoUpdate({ target: player.id, set: { ranking: sql`excluded.ranking` } })
    })

[Angelelz]

This is more like a general SQL knowledge, more that it's drizzle. I believe the docs will have some improvements anyway.

[AidanLaycock]

Hey, is this actually feasible to capture the conflicting row so that I can then set it as something? As I don't think the thread above actually resolves the initial question?

[Angelelz]

Hey, is this actually feasible to capture the conflicting row so that I can then set it as something? As I don't think the thread above actually resolves the initial question?

The feature request is about a convenience more than an actual request. This currently possible, you just need to list all the fields.

[janvorwerk]

The feature request is about a convenience more than an actual request. This currently possible, you just need to list all the fields.

True. Actually, I had several cases where I did not want to update all the fields in case of conflicts... I realize that it can be more subtle than I initially thought.

FWIW, I wrote this little syntaxic sugar that makes it (a bit) easier to read the code:

export function conflictUpdateSet<TTable extends PgTable>(
  table: TTable,
  columns: (keyof TTable["_"]["columns"] & keyof TTable)[],
): PgUpdateSetSource<TTable> {
  return Object.assign(
    {},
    ...columns.map((k) => ({ [k]: sql.raw(`excluded.${(table[k] as Column).name}`) })),
  ) as PgUpdateSetSource<TTable>;
}

Here is how I use it:

     tx
      .insert(skSession)
      .values(sessions)
      .onConflictDoUpdate({
        target: [skSession.session],
        set: conflictUpdateSet(skSession, [
          "startTimestamp",
          "stats",
          // ... all colums to update here
        ]),
      });

Not sure if it's the cleanest solution... Drizzle internals are a bit obscure to me.

[capaj]

@drizzle-team I wish this conflictUpdateSet was included in postgres/sqlite packages to make it easier to do upserts.
Upserts should be encouraged. If you will not add the conflictUpdateSet please at least document this in

https://orm.drizzle.team/docs/insert#on-conflict-do-nothing

actually I wish there was a section in the docs

https://orm.drizzle.team/docs/insert#on-conflict-do-update which is IMHO a more common usecase than on-conflict-do-nothing, certainly when making APIs

also here is the helper for sqlite: https://gist.github.com/capaj/270a4acaf649cc1242fc09e993c95f50

[dBianchii]

+1 to this. Would be great if drizzle could have a way to facilitate this

[dBianchii]

This took me forever.
I am using Mysql. I had to do this:

function allSetValues(values: Record<string, unknown>[]) {
  return Object.assign(
    {},
    ...Object.keys(values[0]!).map((k) => {
      return { [k]: sql.raw(`values(${k})`) }; //Needs to be raw because otherwise it will have 3 string chunks!
    }),
  ) as Record<string, unknown>;
}

//Usage:
await db
      .insert(schema.devPartners)
      .values(devPartners)
      .onDuplicateKeyUpdate({
        set: allSetValues(devPartners),
      });

This was overly complicated for me to just have an upsert. Maybe I am a tad bit too spoiled by prisma

[tjapa]

I made this function to generate the set with all columns except the ones with default values and merge with the current row values.

import { sql } from 'drizzle-orm'
import { PgUpdateSetSource, PgTable } from 'drizzle-orm/pg-core'
import { getTableColumns } from 'drizzle-orm'
import { getTableConfig } from 'drizzle-orm/pg-core'

export function conflictUpdateSetAllColumns<TTable extends PgTable>(
  table: TTable,
): PgUpdateSetSource<TTable> {
  const columns = getTableColumns(table)
  const { name: tableName } = getTableConfig(table)
  const conflictUpdateSet = Object.entries(columns).reduce(
    (acc, [columnName, columnInfo]) => {
      if (!columnInfo.default) {
        // @ts-ignore
        acc[columnName] = sql.raw(
          `COALESCE(excluded.${columnInfo.name}, ${tableName}.${columnInfo.name})`,
        )
      }
      return acc
    },
    {},
  ) as PgUpdateSetSource<TTable>
  return conflictUpdateSet
}

[capaj]

nice one @tjapa, will update my gist too in case anyone would land there looking for it from search engine

[richard-edwards]

Love it @tjapa ! 🥇

Does anyone have a library started that has all these neat little extras that we all need?

Here's a quick code snippet on how to apply it just in case anyone comes across this:

export async function updatePoolTeamPlayers(input: PoolTeamPlayer[]): Promise<void> {
  await db.insert(pool_team_player).values(input)
    .onConflictDoUpdate({ target: pool_team_player.id, set: conflictUpdateSetAllColumns(pool_team_player) })
}

[sp88011]

I'm using camelCase to name my columns, for example:

const user = pgTable("user", {
 firstName: varchar("firstName")
//...

seems like these helper functions seem to ignore (?) this so I get errors like:

column.firstname does not exist

Is there some workaround or will I have to rename all my columns...