Is there a Kysely workaround for bulk updates in a single table?

[MuhammadAli-208]

I'm searching for a Kysley query to perform a bulk update of my table data.

[koskimas]

Yes, you can do a update set from query.

[MuhammadAli-208]

this was the solution I was looking for

db.insertInto('person')
  .values(person)
  .onConflict((oc) => oc
    .column('id')
    .doUpdateSet((eb) => ({
      first_name: eb.ref('excluded.first_name'),
      last_name: eb.ref('excluded.last_name')
    }))
  )

[bernhardw]

Yes, you can do a update set from query.

Does anyone have an example? I'm trying to do the same.

[JortsEnjoyer0]

Yes, you can do a update set from query.

You got any more of them there examples buster? Not sure how you'd do these with kysely syntax.

[igalklebanov]

update tmp
 set age = data_table.age
from
(
  select 'keith'::text as name, 44::integer as age
 union
  select 'leslie', 50
 union
  select 'bexley', 10
 union
  select 'casey', 12
) as data_table
where
 tmp.name = data_table.name;

https://www.postgresql.org/message-id/AANLkTi=Xy9Q7BXTy19EDbsG3YWEL46mS-FJ6VFLH+xfu@mail.gmail.com

import { sql } from "kysely";

const updates = [
  { name: "keith", age: 44 },
  { name: "leslie", age: 50 },
  { name: "bexley", age: 10 },
  { name: "casey", age: 12 },
];

await db
  .updateTable("tmp")
  .from(
    updates
      .slice(1)
      .reduce(
        (qb, update) => {
          return qb.union(
            db.selectNoFrom([
              sql<string>`${update.name}::text`.as("name"),
              sql<number>`${update.age}::integer`.as("age"),
            ]),
          );
        },
        db.selectNoFrom([
          sql<string>`${updates[0].name}::text`.as("name"),
          sql<number>`${updates[0].age}::integer`.as("age"),
        ]),
      )
      .as("data_table"),
  )
  .set((eb) => ({
    age: eb.ref("data_table.age"),
  }))
  .whereRef("tmp.name", "=", "data_table.name")
  .execute();

https://kyse.link/vCVPW

[vincerubinetti]

this was the solution I was looking for

db.insertInto('person')
  .values(person)
  .onConflict((oc) => oc
    .column('id')
    .doUpdateSet((eb) => ({
      first_name: eb.ref('excluded.first_name'),
      last_name: eb.ref('excluded.last_name')
    }))
  )

I'm confused as to why you need the eb.ref part, when the docs say you can just do { first_name } (which I tried and it doesn't work). Also is there any way to do this generically, without naming each column? All I'm trying to do is upsert rows by the id column in a single statement.

Edit: Cleanest solution I've found so far is this:

async (people: Person[]) => {
  await db
    .insertInto("person")
    .values(people)
    .onConflict((oc) =>
      oc.column("id").doUpdateSet((eb) => {
        const keys = Object.keys(people[0]!) as (keyof Person)[];
        return Object.fromEntries(keys.map((key) => [key, eb.ref(key)]));
      })
    )
    .execute();
};

Related discussion on Drizzle: drizzle-team/drizzle-orm#1728

[koskimas]

@vincerubinetti

const first_name = "foo bar baz"

...

doUpdateSet({ first_name })

the above will se first_name to have the value "foo bar baz".

doUpdateSet(eb => ({ first_name: eb.ref("excluded.first_name")  }))

the above will set first_name to the value behind the column reference excluded.first_name. That value is the first_name of the row you tried to insert.

[koskimas]

Related discussion on Drizzle: drizzle-team/drizzle-orm#1728

How is that drizzle discussion in any way related to this 😕 Drizzle is another library that has absolutely nothing to do with Kysely.

[stevegoossens]

Edit: Cleanest solution I've found so far is this:

async (people: Person[]) => {
  await db
    .insertInto("person")
    .values(people)
    .onConflict((oc) =>
      oc.column("id").doUpdateSet((eb) => {
        const keys = Object.keys(people[0]!) as (keyof Person)[];
        return Object.fromEntries(keys.map((key) => [key, eb.ref(key)]));
      })
    )
    .execute();
};

I was also looking for a general way to upsert without having to specify every key-value. This works great, I just tweaked it to resolve a couple issues:

• the non-null assertion is not type safe
• the reference is ambiguous

async (people: Person[]) => {
  if (people.length === 0) {
    return;
  }
  await db
    .insertInto("person")
    .values(people)
    .onConflict((oc) =>
      oc.column("id").doUpdateSet((eb) => {
        const keys = Object.keys(people[0]) as (keyof Person)[];
        return Object.fromEntries(keys.map((key) => [key, eb.ref(`excluded.${key}`)]));
      })
    )
    .execute();
};