Created
October 25, 2019 14:38
-
-
Save fnimick/5dd3c6855cea6e000c86d1bc38876958 to your computer and use it in GitHub Desktop.
Sequelize patch to enable unique indices (including partial indices) for ON CONFLICT DO UPDATE with postgres
This file contains 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
diff --git a/node_modules/sequelize/lib/dialects/abstract/query-generator.js b/node_modules/sequelize/lib/dialects/abstract/query-generator.js | |
index d2c865d..b668f38 100755 | |
--- a/node_modules/sequelize/lib/dialects/abstract/query-generator.js | |
+++ b/node_modules/sequelize/lib/dialects/abstract/query-generator.js | |
@@ -302,9 +302,16 @@ class QueryGenerator { | |
if (this._dialect.supports.inserts.updateOnDuplicate && options.updateOnDuplicate) { | |
if (this._dialect.supports.inserts.updateOnDuplicate == ' ON CONFLICT DO UPDATE SET') { // postgres / sqlite | |
// If no conflict target columns were specified, use the primary key names from options.upsertKeys | |
- const conflictKeys = options.upsertKeys.map(attr => this.quoteIdentifier(attr)); | |
const updateKeys = options.updateOnDuplicate.map(attr => `${this.quoteIdentifier(attr)}=EXCLUDED.${this.quoteIdentifier(attr)}`); | |
- onDuplicateKeyUpdate = ` ON CONFLICT (${conflictKeys.join(',')}) DO UPDATE SET ${updateKeys.join(',')}`; | |
+ if (Array.isArray(options.upsertKeys)) { | |
+ const conflictKeys = options.upsertKeys.map(attr => this.quoteIdentifier(attr)); | |
+ onDuplicateKeyUpdate = ` ON CONFLICT (${conflictKeys.join(',')}) DO UPDATE SET ${updateKeys.join(',')}`; | |
+ } else { | |
+ // If upsertKeys is an object, generate an ON CONFLICT WHERE | |
+ const conflictKeys = options.upsertKeys.fields.map(attr => this.quoteIdentifier(attr)); | |
+ const where = this.whereQuery(options.upsertKeys.where); | |
+ onDuplicateKeyUpdate = ` ON CONFLICT (${conflictKeys.join(',')}) ${where} DO UPDATE SET ${updateKeys.join(',')}`; | |
+ } | |
} else { // mysql / maria | |
const valueKeys = options.updateOnDuplicate.map(attr => `${this.quoteIdentifier(attr)}=VALUES(${this.quoteIdentifier(attr)})`); | |
onDuplicateKeyUpdate = `${this._dialect.supports.inserts.updateOnDuplicate} ${valueKeys.join(',')}`; | |
diff --git a/node_modules/sequelize/lib/model.js b/node_modules/sequelize/lib/model.js | |
index 7a9273a..e58296b 100644 | |
--- a/node_modules/sequelize/lib/model.js | |
+++ b/node_modules/sequelize/lib/model.js | |
@@ -2701,9 +2701,21 @@ class Model { | |
if (options.updateOnDuplicate) { | |
options.updateOnDuplicate = options.updateOnDuplicate.map(attr => model.rawAttributes[attr].field || attr); | |
// Get primary keys for postgres to enable updateOnDuplicate | |
- options.upsertKeys = _.chain(model.primaryKeys).values().map('field').value(); | |
- if (Object.keys(model.uniqueKeys).length > 0) { | |
- options.upsertKeys = _.chain(model.uniqueKeys).values().filter(c => c.fields.length === 1).map('column').value(); | |
+ if (options.upsertIndex !== undefined) { | |
+ const upsertIndex = model._indexes.find(index => index.name === options.upsertIndex); | |
+ if (upsertIndex === undefined) { | |
+ throw new Error(`upsertIndex '${options.upsertIndex}' not defined`); | |
+ } | |
+ if (!upsertIndex.unique) { | |
+ throw new Error(`upsertIndex '${options.upsertIndex}' not a unique index`); | |
+ } | |
+ options.upsertKeys = { fields: upsertIndex.fields, where: upsertIndex.where }; | |
+ } | |
+ if (options.upsertKeys === undefined) { | |
+ options.upsertKeys = _.chain(model.primaryKeys).values().map('field').value(); | |
+ if (Object.keys(model.uniqueKeys).length > 0) { | |
+ options.upsertKeys = _.chain(model.uniqueKeys).values().filter(c => c.fields.length === 1).map('column').value(); | |
+ } | |
} | |
} | |
diff --git a/node_modules/sequelize/types/lib/model.d.ts b/node_modules/sequelize/types/lib/model.d.ts | |
index 98bd56e..91ab4ca 100644 | |
--- a/node_modules/sequelize/types/lib/model.d.ts | |
+++ b/node_modules/sequelize/types/lib/model.d.ts | |
@@ -751,6 +751,20 @@ export interface BulkCreateOptions extends Logging, Transactionable { | |
*/ | |
updateOnDuplicate?: string[]; | |
+ /** | |
+ * The name of a unique index to be used for generation of an `ON CONFLICT` | |
+ * clause. | |
+ */ | |
+ upsertIndex?: string; | |
+ | |
+ /** | |
+ * Either an array of database columns that are either primary keys or | |
+ * composite members of a unique key, or an object containing fields and a | |
+ * where clause that represents a partial index. Used for generating an `ON | |
+ * CONFLICT` clause. (Only supported by SQLite >= 3.24.0 & Postgres >= 9.5) | |
+ */ | |
+ upsertKeys?: string[] | { fields: string[], where: WhereOptions }; | |
+ | |
/** | |
* Include options. See `find` for details | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment