mrk Comments - Answer Overflow

mrk

Explore posts from servers

•Created by mrk on 5/24/2024 in #help

UPSERT operation not working for SQLite/Turso

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>);
}

9 replies

DTDrizzle Team

•Created by mrk on 5/24/2024 in #help

UPSERT operation not working for SQLite/Turso

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 👍

9 replies

DTDrizzle Team

•Created by mrk on 5/24/2024 in #help

UPSERT operation not working for SQLite/Turso

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

9 replies

DTDrizzle Team

•Created by mrk on 5/24/2024 in #help

UPSERT operation not working for SQLite/Turso

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

9 replies

Gaming

Programming