Created
February 1, 2022 07:05
-
-
Save preetjdp/bda20d897ea866ca231e966c58dbc19c to your computer and use it in GitHub Desktop.
Custom Upsert
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import { Instance, Model, Transaction, WhereOptions } from "sequelize"; | |
| /** | |
| * A custom function to upsert the data in the table | |
| * | |
| * @param model The model of the | |
| * @param values The value to be upserted with | |
| * @param condition The condition to match the record | |
| * @param transaction The transaction that this is a part of | |
| * | |
| * @returns boolean Indicating weather the upsert was successful | |
| * | |
| * @reference https://github.com/sequelize/sequelize/blob/7b174eb12d8813d338a36a531acd791d58aeeb09/lib/dialects/abstract/query-generator.js#L184 | |
| */ | |
| export const customUpsert = async <T extends SequelizeStatic.Model<K, U>, U, K>( | |
| model: T, | |
| values: U, | |
| condition: WhereOptions<U>, | |
| transaction: Transaction | |
| ): Promise<boolean> => { | |
| try { | |
| //@ts-expect-error The model does have a field `QueryGenerator` but is it not typesafe | |
| // reference https://stackoverflow.com/a/59557541/10839520 | |
| const insertSql: string = model.QueryGenerator.insertQuery( | |
| model.getTableName(), | |
| values, | |
| //@ts-expect-error The model does have a field rawAttributes, but it is not typesafe | |
| model.rawAttributes | |
| ); | |
| const strippedInsertSQL = insertSql.slice(0, -1); | |
| //@ts-expect-error The model does have a field `QueryGenerator` but is it not typesafe | |
| // reference https://stackoverflow.com/a/59557541/10839520 | |
| const updateSql: string = model.QueryGenerator.updateQuery( | |
| model.getTableName(), | |
| values, | |
| null, | |
| null, | |
| //@ts-expect-error The model does have a field rawAttributes, but it is not typesafe | |
| model.rawAttributes | |
| ); | |
| /** | |
| * The update query is of format: `UPDATE <table> SET <field> = <value>`, | |
| * We split it on SET to get the part after the first occurrence SET ie, <field> = <value>. | |
| * | |
| * After split, the splitUpdateQuery holds: `["UPDATE <table>", " <field> = <value>"]`]` | |
| */ | |
| const splitUpdateQuery = splitOnFirst(updateSql, "SET"); | |
| const updateQueryNew = "UPDATE SET" + splitUpdateQuery[1]; | |
| const conflictKeys = Object.keys(condition).join(","); | |
| const finalQuery = | |
| strippedInsertSQL + ` ON CONFLICT (${conflictKeys}) DO ` + updateQueryNew; | |
| await sequelize.query(finalQuery, { | |
| type: sequelize.QueryTypes.INSERT, | |
| transaction | |
| }); | |
| return true; | |
| } catch (error) { | |
| logger.errLog("[customUpsert helper] Error upserting the data", error); | |
| throw error; | |
| } | |
| }; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment