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?
Thanks
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
