Drizzle Team•4mo ago

UPSERT operation not working for SQLite/Turso

Hey! So I'm trying to do a BULK UPSERT for multiple rows with a .returning() and its not working. I expect: - UPDATE: rows which include the PKs "id" - INSERT: rows which DO NOT include the PKs "id" the code is as follows (I've followed the docs and tried some variants):

const data [
  // this row IS NOT updated (from "Untitled1" to "John")
  { id: "nhdbw1xklex5", name: "John" },
  // this row IS properly inserted
  { name: "Unknown2" } 
]
const result = await db
  .insert(users)
  .values(data)
  .onConflictDoUpdate({
    target: users.id,
    set: { id: sql.raw(`excluded.${user.id.name}`) },
  })
  .returning();

const data [
  // this row IS NOT updated (from "Untitled1" to "John")
  { id: "nhdbw1xklex5", name: "John" },
  // this row IS properly inserted
  { name: "Unknown2" } 
]
const result = await db
  .insert(users)
  .values(data)
  .onConflictDoUpdate({
    target: users.id,
    set: { id: sql.raw(`excluded.${user.id.name}`) },
  })
  .returning();

I would expect results being:

[
  { id: "nhdbw1xklex5", name: "John" },
  { id: "<nanoid-here>", name: "Untitled2" } 
]

[
  { id: "nhdbw1xklex5", name: "John" },
  { id: "<nanoid-here>", name: "Untitled2" } 
]

and I keep getting (the first row is unchanged / not updated):

[
  { id: "nhdbw1xklex5", name: "Untitled1" },
  { id: "<nanoid-here>", name: "Untitled2" } 
]

[
  { id: "nhdbw1xklex5", name: "Untitled1" },
  { id: "<nanoid-here>", name: "Untitled2" } 
]

Am I doing something wrong? Thanks! Any help is greatly appreciated!

7 Replies

Mykhailo•4mo ago

Hey @mrk! You try to update your id instead of name. You should do smth like this:

.onConflictDoUpdate({
    target: users.id,
    set: { name: sql.raw(`excluded.${user.name.name}`) },
  })

.onConflictDoUpdate({
    target: users.id,
    set: { name: sql.raw(`excluded.${user.name.name}`) },
  })

mrk•4mo ago

Hey! Thanks for the reply! Hmm I understand what you mean, but what I'm trying to achieve is to run an update with ALL fields EXCEPT "id" field, so not just "name"... Is that possible? That is why I was attempting to "exclude" the "id" field I also saw somewhere the possibility of doing something like the following:

const { id, ...rest } = data;
const result = await db
  .insert(users)
  .values({ id, ...rest })
  .onConflictDoUpdate({
    target: users.id,
    set: rest,
  })
  .returning();

const { id, ...rest } = data;
const result = await db
  .insert(users)
  .values({ id, ...rest })
  .onConflictDoUpdate({
    target: users.id,
    set: rest,
  })
  .returning();

But that wouldn't work for bulk upserts right @Mykhailo ? Which is what I'm trying to do...

Mykhailo•4mo ago

Hmm, as I know there is no option to exclude column in sql, that's why we have to specify the columns we want to update. In guide we have custom function which can help to build upsert query, you can write smth like this but for excluding columns. https://orm.drizzle.team/learn/guides/upsert Also, we will add your case as an example in this guide with custom function for excluding columns.

Drizzle ORM - Upsert Query

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

mrk•4mo ago

Thanks, makes sense! Adding it as en example would be great! I think many people might stumble with this use case. I'll try getting it to work and post the example here for the record 👍

Mykhailo•4mo ago

Super, thank you!

mrk•4mo ago

So this is what I ended up doing (a very slight adaptation of the example currently in the docs), simply removing the PK, "id" in this case:

const columns = [
  ...new Set(rows.flatMap(Object.keys))
].filter((col) => col !== pk);
const { id, ...rest } = data;
const result = await db
  .insert(table)
  .values({ id, ...rest })
  .onConflictDoUpdate({
    target: table[pk],
    set: buildConflictUpdateColumns(table[pk], columns),
  })
  .returning();

const columns = [
  ...new Set(rows.flatMap(Object.keys))
].filter((col) => col !== pk);
const { id, ...rest } = data;
const result = await db
  .insert(table)
  .values({ id, ...rest })
  .onConflictDoUpdate({
    target: table[pk],
    set: buildConflictUpdateColumns(table[pk], columns),
  })
  .returning();

with

function buildConflictUpdateColumns<
  T extends SQLiteTable,
  Q extends keyof T["_"]["columns"],
>(
  table: T,
  columns: Q[],
) {
  const cls = getTableColumns(table);
  return columns.reduce((acc, column) => {
    const colName = cls[column].name;
    acc[column] = sql.raw(`excluded.${colName}`);
    return acc;
  }, {} as Record<Q, SQL>);
}

function buildConflictUpdateColumns<
  T extends SQLiteTable,
  Q extends keyof T["_"]["columns"],
>(
  table: T,
  columns: Q[],
) {
  const cls = getTableColumns(table);
  return columns.reduce((acc, column) => {
    const colName = cls[column].name;
    acc[column] = sql.raw(`excluded.${colName}`);
    return acc;
  }, {} as Record<Q, SQL>);
}

Mykhailo•4mo ago

Thank you @mrk! I will test it and update guide then

Gaming

Programming

UPSERT operation not working for SQLite/Turso