Drizzle ORMで複数行をUpsertする

#drizzle #typescript #sqlite

SQLiteの場合こんな感じ。

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

引用元

https://github.com/drizzle-team/drizzle-orm/issues/1728