Drizzle Team•9mo ago

Emulate upsert operation in mySQL

Hi guys, I'm a little new to drizzle / SQL. Is the following method a good way of generically emulating an upsert for a MySQL db? Are there any issues with this strategy?

import { Table, getTableColumns, sql } from "db";
/**
 *
 * Helper to get the SQL "set" value for 'onDuplicateKeyUpdate' for any given table.
 *
 */
export const getUpsertValues = (table: Table) => {
  const col_names = getTableColumns(table);

  type ResType = {
    [K in keyof typeof col_names]: any;
  };
  let res: ResType = {};

  Object.keys(col_names).map((col_name) => {
    const column = col_names[col_name];

    res[col_name] = sql`VALUES(${column.name})`
      ? sql`VALUES(${column.name})`
      : sql`${column.name}`;
  });

  return res;
};




// Insert operation that behaves like an upsert 

const table = someDrizzleMySqlTable
await db
      .insert(table)
      .values(valuesToUpsert)
      .onDuplicateKeyUpdate({
          set : {...getUpsertValues(table)}
        })

import { Table, getTableColumns, sql } from "db";
/**
 *
 * Helper to get the SQL "set" value for 'onDuplicateKeyUpdate' for any given table.
 *
 */
export const getUpsertValues = (table: Table) => {
  const col_names = getTableColumns(table);

  type ResType = {
    [K in keyof typeof col_names]: any;
  };
  let res: ResType = {};

  Object.keys(col_names).map((col_name) => {
    const column = col_names[col_name];

    res[col_name] = sql`VALUES(${column.name})`
      ? sql`VALUES(${column.name})`
      : sql`${column.name}`;
  });

  return res;
};




// Insert operation that behaves like an upsert 

const table = someDrizzleMySqlTable
await db
      .insert(table)
      .values(valuesToUpsert)
      .onDuplicateKeyUpdate({
          set : {...getUpsertValues(table)}
        })

Thanks

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Emulate upsert operation in mySQL