UPSERT operation not working for SQLite/Turso

Hey! So I'm trying to do a BULK UPSERT for multiple rows with a

.returning()

.returning()

and its not working. I expect:

UPDATE: rows which include the PKs
```
"id"
```
```
"id"
```

INSERT: rows which DO NOT include the PKs

"id"

"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();

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" } 
]

[
{ 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" } 
]

[
{ 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!

Mykhailo•5/28/24, 12:43 PM

Hey @mrk! You try to update your

id

id

instead of

name

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}`) },
  })

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

mrkOP•5/28/24, 12:53 PM

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

mrkOP•5/28/24, 12:54 PM

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•5/28/24, 1:47 PM

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.

MMykhailo Hmm, as I know there is no option to exclude column in sql, that's why we have t...

mrkOP•5/28/24, 2:09 PM

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•5/28/24, 2:24 PM

Super, thank you!

mrkOP•5/28/24, 3:08 PM

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

"id"

"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•5/29/24, 10:08 AM

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

UPSERT operation not working for SQLite/Turso

Similar Threads

Similar Threads

Similar Threads