[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...

[ItzDerock]

Sharing my snippet for sqlite -- sets all the rows except for the ones defined in the second argument:

export function conflictUpdateAllExcept<
  T extends SQLiteTable,
  E extends (keyof T["$inferInsert"])[],
>(table: T, except: E) {
  const columns = getTableColumns(table);
  const updateColumns = Object.entries(columns).filter(
    ([col]) => !except.includes(col as keyof typeof table.$inferInsert),
  );

  return updateColumns.reduce(
    (acc, [colName, table]) => ({
      ...acc,
      [colName]: sql.raw(`excluded.${table.name}`),
    }),
    {},
  ) as Omit<Record<keyof typeof table.$inferInsert, SQL>, E[number]>;
}


// usage:
await db
  .insert(column) // column: SQLiteTableWithColumns<...>
  .values(values) // values: (typeof column.$inferInsert)[]
  .onConflictDoUpdate({
    set: conflictUpdateAllExcept(column, ["id"]),
    target: column.id,
  });

Should be fully type-safe too, you'll get intellisense results for the except array as well as an error if you put a column that doesn't exist.

[austinm911]

@tjapa @capaj I thought I had run into this before when trying out using postgres schemas and trying to drop all tables across different schemas, but getTableColumns doesn't return the prefixed table name, correct? So if you have tables schema1.table_name then getTableColumns will just return table_name. So I wasn't able to figure out at the time how to use sql.raw and inject the schema name in there. I'm guessing this might be an issue doing upserts if they are on different schemas?

[madc]

Sharing my snippet for sqlite -- sets all the rows except for the ones defined in the second argument:

export function conflictUpdateAllExcept<
  T extends SQLiteTable,
  E extends (keyof T["$inferInsert"])[],
>(table: T, except: E) {
  const columns = getTableColumns(table);
  const updateColumns = Object.entries(columns).filter(
    ([col]) => !except.includes(col as keyof typeof table.$inferInsert),
  );

  return updateColumns.reduce(
    (acc, [colName, table]) => ({
      ...acc,
      [colName]: sql.raw(`excluded.${table.name}`),
    }),
    {},
  ) as Omit<Record<keyof typeof table.$inferInsert, SQL>, E[number]>;
}


// usage:
await db
  .insert(column) // column: SQLiteTableWithColumns<...>
  .values(values) // values: (typeof column.$inferInsert)[]
  .onConflictDoUpdate({
    set: conflictUpdateAllExcept(column, ["id"]),
    target: column.id,
  });

Should be fully type-safe too, you'll get intellisense results for the except array as well as an error if you put a column that doesn't exist.

This works like a charm, thanks for sharing!

[tjapa]

@tjapa @capaj I thought I had run into this before when trying out using postgres schemas and trying to drop all tables across different schemas, but getTableColumns doesn't return the prefixed table name, correct? So if you have tables schema1.table_name then getTableColumns will just return table_name. So I wasn't able to figure out at the time how to use sql.raw and inject the schema name in there. I'm guessing this might be an issue doing upserts if they are on different schemas?

Maybe you can get the schema using the getTableConfig function.
I saw the function returns object with a field called schema.

[WarlockJa]

Sharing my snippet for sqlite -- sets all the rows except for the ones defined in the second argument:

export function conflictUpdateAllExcept<
  T extends SQLiteTable,
  E extends (keyof T["$inferInsert"])[],
>(table: T, except: E) {
  const columns = getTableColumns(table);
  const updateColumns = Object.entries(columns).filter(
    ([col]) => !except.includes(col as keyof typeof table.$inferInsert),
  );

  return updateColumns.reduce(
    (acc, [colName, table]) => ({
      ...acc,
      [colName]: sql.raw(`excluded.${table.name}`),
    }),
    {},
  ) as Omit<Record<keyof typeof table.$inferInsert, SQL>, E[number]>;
}


// usage:
await db
  .insert(column) // column: SQLiteTableWithColumns<...>
  .values(values) // values: (typeof column.$inferInsert)[]
  .onConflictDoUpdate({
    set: conflictUpdateAllExcept(column, ["id"]),
    target: column.id,
  });

Should be fully type-safe too, you'll get intellisense results for the except array as well as an error if you put a column that doesn't exist.

@ItzDerock this is awesome! Thank you for sharing this.

[niklasravnsborg]

import { getTableColumns, SQL, sql, Table } from 'drizzle-orm'

export function conflictUpdateAllExcept<
  T extends Table,
  E extends (keyof T['$inferInsert'])[],
>(table: T, except: E) {
  const columns = getTableColumns(table)
  const updateColumns = Object.entries(columns).filter(
    ([col]) => !except.includes(col as keyof typeof table.$inferInsert),
  )

  return updateColumns.reduce(
    (acc, [colName, table]) => ({
      ...acc,
      [colName]: sql.raw(`excluded.${table.name}`),
    }),
    {},
  ) as Omit<Record<keyof typeof table.$inferInsert, SQL>, E[number]>
}

@ItzDerock Awesome, thank you! This also works for Postgres as far as I tested. I just replaced SQLiteTable with the generic Table.

[adamsullovey]

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...

@sp88011 I had a similar problem and had to use some more quotes to resolve it. If I use ItzDerock's example from #1728 (comment)

This line

      [colName]: sql.raw(`excluded.${table.name}`),

needs to be

      [colName]: sql.raw(`excluded."${table.name}"`),

If I understand it right, PostgreSQL will convert the identifiers like column names to lowercase unless double quoted.

[bitofbreeze]

These helper functions have been really useful, but they break with the new snake_case feature. table.name is still the titlecase version and you get a no such column: excluded.titleCase error. Any idea how to add compatibility for that?

[lxia1220]

These helper functions have been really useful, but they break with the new snake_case feature. table.name is still the titlecase version and you get a no such column: excluded.titleCase error. Any idea how to add compatibility for that?

you can do with internal things

(db.dialect.casing as CasingCache).getColumnCasing(schema.table.column)

[MickL]

Seriously it should be part of the ORM. An ORM should take away the need to write complicated helper functions. This issue is open for a year now, you basically just need to take one of the snippets from above and place it into Drizzle.

Personally I would like to write:

this.db
      .insert(products)
      .values(newProducts)
      .onConflictDoUpdate({ target: products.id });

And expect existing items get overwritten.

Also be aware that it gets even annoying then that we currently need to repeat ALL values: The values also need to be written by the name of the table column:

this.db
      .insert(products)
      .values(newProducts)
      .onConflictDoUpdate({ target: products.id }, set {
          name: sql`excluded.name`,
          nameShort: sql`excluded.name_short`,
          subTitle: sql`excluded.sub_title`,
     });

[paulorumor]

In Postgres for a more dynamic approach we got it working like this:

if (itemsToUpsert.length > 0) {
    const setObject = Object.keys(itemsToUpsert[0]).reduce((acc, key) => {
        // Convert camelCase keys to snake_case for database compatibility,
        // this is specially necessary if you have relationships
        const columnName = key.replace(/[A-Z]/g, (letter) => `_${letter.toLowerCase()}`);
        acc[columnName] = sql.raw(`excluded."${columnName}"`);
        return acc;
    }, {} as Record<string, any>);

    const result = await trx
        .insert(schema)
        .values(itemsToUpsert) 
        .onConflictDoUpdate({
            target: schema.id,
            set: setObject,
        })
        .returning({
            id: schema.id,
            name: schema.name,
        });
    return result;
}

[SSTPIERRE2]

@paulorumor I was struggling to get an upsert multiple rows implementation working with my tables with camelCase columns for a while, it was converting them to all lowercase I suppose because drizzle expects snake case 🤔 When I saw your post I tried putting double quotes around the column name like so excluded."${columnName}" and now it works, thanks!