Skip to content

Commit 496bede

Browse files
wbourne0sdepold
wbourne0
and
sdepold
authored
feat(upsert): add conflictFields option (#13723)
* feat(upsert): add conflictFields option Adds support for the `conflictFields` option to `Model.upsert`. This is used for `options.upsertKeys` in `QueryInterface.prototype.upsert` if provided for specifying the fields used at `ON CONFLICT({fields})` rather than relying on the default logic. * add conflictFields to the right type Co-authored-by: Sascha Depold <[email protected]>
1 parent 98b0f1a commit 496bede

8 files changed

Lines changed: 137 additions & 42 deletions

File tree

‎lib/dialects/abstract/index.js‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -40,7 +40,8 @@ AbstractDialect.prototype.supports = {
4040
inserts: {
4141
ignoreDuplicates: '', /* dialect specific words for INSERT IGNORE or DO NOTHING */
4242
updateOnDuplicate: false, /* whether dialect supports ON DUPLICATE KEY UPDATE */
43-
onConflictDoNothing: '' /* dialect specific words for ON CONFLICT DO NOTHING */
43+
onConflictDoNothing: '', /* dialect specific words for ON CONFLICT DO NOTHING */
44+
conflictFields: false /* whether the dialect supports specifying conflict fields or not */
4445
},
4546
constraints: {
4647
restrict: true,

‎lib/dialects/abstract/query-interface.js‎

Lines changed: 27 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -767,40 +767,42 @@ class QueryInterface {
767767
options = { ...options };
768768

769769
const model = options.model;
770-
const primaryKeys = Object.values(model.primaryKeys).map(item => item.field);
771-
const uniqueKeys = Object.values(model.uniqueKeys).filter(c => c.fields.length > 0).map(c => c.fields);
772-
const indexKeys = Object.values(model._indexes).filter(c => c.unique && c.fields.length > 0).map(c => c.fields);
773770

774771
options.type = QueryTypes.UPSERT;
775772
options.updateOnDuplicate = Object.keys(updateValues);
776-
options.upsertKeys = [];
777-
778-
// For fields in updateValues, try to find a constraint or unique index
779-
// that includes given field. Only first matching upsert key is used.
780-
for (const field of options.updateOnDuplicate) {
781-
const uniqueKey = uniqueKeys.find(fields => fields.includes(field));
782-
if (uniqueKey) {
783-
options.upsertKeys = uniqueKey;
784-
break;
773+
options.upsertKeys = options.conflictFields || [];
774+
775+
if (options.upsertKeys.length === 0) {
776+
const primaryKeys = Object.values(model.primaryKeys).map(item => item.field);
777+
const uniqueKeys = Object.values(model.uniqueKeys).filter(c => c.fields.length > 0).map(c => c.fields);
778+
const indexKeys = Object.values(model._indexes).filter(c => c.unique && c.fields.length > 0).map(c => c.fields);
779+
// For fields in updateValues, try to find a constraint or unique index
780+
// that includes given field. Only first matching upsert key is used.
781+
for (const field of options.updateOnDuplicate) {
782+
const uniqueKey = uniqueKeys.find(fields => fields.includes(field));
783+
if (uniqueKey) {
784+
options.upsertKeys = uniqueKey;
785+
break;
786+
}
787+
788+
const indexKey = indexKeys.find(fields => fields.includes(field));
789+
if (indexKey) {
790+
options.upsertKeys = indexKey;
791+
break;
792+
}
785793
}
786794

787-
const indexKey = indexKeys.find(fields => fields.includes(field));
788-
if (indexKey) {
789-
options.upsertKeys = indexKey;
790-
break;
795+
// Always use PK, if no constraint available OR update data contains PK
796+
if (
797+
options.upsertKeys.length === 0
798+
|| _.intersection(options.updateOnDuplicate, primaryKeys).length
799+
) {
800+
options.upsertKeys = primaryKeys;
791801
}
792-
}
793802

794-
// Always use PK, if no constraint available OR update data contains PK
795-
if (
796-
options.upsertKeys.length === 0
797-
|| _.intersection(options.updateOnDuplicate, primaryKeys).length
798-
) {
799-
options.upsertKeys = primaryKeys;
803+
options.upsertKeys = _.uniq(options.upsertKeys);
800804
}
801805

802-
options.upsertKeys = _.uniq(options.upsertKeys);
803-
804806
const sql = this.queryGenerator.insertQuery(tableName, insertValues, model.rawAttributes, options);
805807
return await this.sequelize.query(sql, options);
806808
}

‎lib/dialects/postgres/index.js‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -51,7 +51,8 @@ PostgresDialect.prototype.supports = _.merge(
5151
},
5252
inserts: {
5353
onConflictDoNothing: ' ON CONFLICT DO NOTHING',
54-
updateOnDuplicate: ' ON CONFLICT DO UPDATE SET'
54+
updateOnDuplicate: ' ON CONFLICT DO UPDATE SET',
55+
conflictFields: true
5556
},
5657
NUMERIC: true,
5758
ARRAY: true,

‎lib/dialects/sqlite/index.js‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,8 @@ SqliteDialect.prototype.supports = _.merge(
3434
'RIGHT JOIN': false,
3535
inserts: {
3636
ignoreDuplicates: ' OR IGNORE',
37-
updateOnDuplicate: ' ON CONFLICT DO UPDATE SET'
37+
updateOnDuplicate: ' ON CONFLICT DO UPDATE SET',
38+
conflictFields: true
3839
},
3940
index: {
4041
using: false,

‎lib/model.js‎

Lines changed: 11 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -2445,16 +2445,17 @@ class Model {
24452445
*
24462446
* **Note** that Postgres/SQLite returns null for created, no matter if the row was created or updated
24472447
*
2448-
* @param {object} values hash of values to upsert
2449-
* @param {object} [options] upsert options
2450-
* @param {boolean} [options.validate=true] Run validations before the row is inserted
2451-
* @param {Array} [options.fields=Object.keys(this.attributes)] The fields to update if the record already exists. Defaults to all changed fields. If none of the specified fields are present on the provided `values` object, an insert will still be attempted, but duplicate key conflicts will be ignored.
2452-
* @param {boolean} [options.hooks=true] Run before / after upsert hooks?
2453-
* @param {boolean} [options.returning=true] If true, fetches back auto generated values
2454-
* @param {Transaction} [options.transaction] Transaction to run query under
2455-
* @param {Function} [options.logging=false] A function that gets executed while running the query to log the sql.
2456-
* @param {boolean} [options.benchmark=false] Pass query execution time in milliseconds as second argument to logging function (options.logging).
2457-
* @param {string} [options.searchPath=DEFAULT] An optional parameter to specify the schema search_path (Postgres only)
2448+
* @param {object} values hash of values to upsert
2449+
* @param {object} [options] upsert options
2450+
* @param {boolean} [options.validate=true] Run validations before the row is inserted
2451+
* @param {Array} [options.fields=Object.keys(this.attributes)] The fields to update if the record already exists. Defaults to all changed fields. If none of the specified fields are present on the provided `values` object, an insert will still be attempted, but duplicate key conflicts will be ignored.
2452+
* @param {boolean} [options.hooks=true] Run before / after upsert hooks?
2453+
* @param {boolean} [options.returning=true] If true, fetches back auto generated values
2454+
* @param {Transaction} [options.transaction] Transaction to run query under
2455+
* @param {Function} [options.logging=false] A function that gets executed while running the query to log the sql.
2456+
* @param {boolean} [options.benchmark=false] Pass query execution time in milliseconds as second argument to logging function (options.logging).
2457+
* @param {string} [options.searchPath=DEFAULT] An optional parameter to specify the schema search_path (Postgres only)
2458+
* @param {Array<string>} [options.conflictFields] Optional override for the conflict fields in the ON CONFLICT part of the query. Only supported in Postgres >= 9.5 and SQLite >= 3.24.0
24582459
*
24592460
* @returns {Promise<Array<Model, boolean | null>>} returns an array with two elements, the first being the new record and the second being `true` if it was just created or `false` if it already existed (except on Postgres and SQLite, which can't detect this and will always return `null` instead of a boolean).
24602461
*/

‎test/integration/model/upsert.test.js‎

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -678,6 +678,89 @@ describe(Support.getTestDialectTeaser('Model'), () => {
678678
});
679679
});
680680
}
681+
682+
if (current.dialect.supports.inserts.conflictFields) {
683+
describe('conflictFields', () => {
684+
// An Abstract joiner table. Unique constraint deliberately removed
685+
// to ensure that `conflictFields` is actually respected, not inferred.
686+
const Memberships = current.define('memberships', {
687+
user_id: DataTypes.INTEGER,
688+
group_id: DataTypes.INTEGER,
689+
permissions: DataTypes.ENUM('admin', 'member')
690+
});
691+
692+
beforeEach(async () => {
693+
await Memberships.sync({ force: true });
694+
695+
await current.queryInterface.addConstraint('memberships', {
696+
type: 'UNIQUE',
697+
fields: ['user_id', 'group_id']
698+
});
699+
});
700+
701+
it('should insert with no other rows', async () => {
702+
const [newRow] = await Memberships.upsert(
703+
{
704+
user_id: 1,
705+
group_id: 1,
706+
permissions: 'member'
707+
},
708+
{
709+
conflictFields: ['user_id', 'group_id']
710+
}
711+
);
712+
713+
expect(newRow).to.not.eq(null);
714+
expect(newRow.permissions).to.eq('member');
715+
});
716+
717+
it('should use conflictFields as upsertKeys', async () => {
718+
const [originalMembership] = await Memberships.upsert(
719+
{
720+
user_id: 1,
721+
group_id: 1,
722+
permissions: 'member'
723+
},
724+
{
725+
conflictFields: ['user_id', 'group_id']
726+
}
727+
);
728+
729+
expect(originalMembership).to.not.eq(null);
730+
expect(originalMembership.permissions).to.eq('member');
731+
732+
const [updatedMembership] = await Memberships.upsert(
733+
{
734+
user_id: 1,
735+
group_id: 1,
736+
permissions: 'admin'
737+
},
738+
{
739+
conflictFields: ['user_id', 'group_id']
740+
}
741+
);
742+
743+
expect(updatedMembership).to.not.eq(null);
744+
expect(updatedMembership.permissions).to.eq('admin');
745+
expect(updatedMembership.id).to.eq(originalMembership.id);
746+
747+
const [otherMembership] = await Memberships.upsert(
748+
{
749+
user_id: 2,
750+
group_id: 1,
751+
permissions: 'member'
752+
},
753+
{
754+
conflictFields: ['user_id', 'group_id']
755+
}
756+
);
757+
758+
expect(otherMembership).to.not.eq(null);
759+
expect(otherMembership.permissions).to.eq('member');
760+
expect(otherMembership.id).to.not.eq(originalMembership.id);
761+
});
762+
});
763+
}
681764
});
682765
}
683766
});

‎types/lib/model.d.ts‎

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -385,8 +385,8 @@ export interface IncludeThroughOptions extends Filterable<any>, Projectable {
385385
*/
386386
as?: string;
387387

388-
/**
389-
* If true, only non-deleted records will be returned from the join table.
388+
/**
389+
* If true, only non-deleted records will be returned from the join table.
390390
* If false, both deleted and non-deleted records will be returned.
391391
* Only applies if through model is paranoid.
392392
*/
@@ -747,6 +747,11 @@ export interface UpsertOptions<TAttributes = any> extends Logging, Transactionab
747747
* Run validations before the row is inserted
748748
*/
749749
validate?: boolean;
750+
/**
751+
* Optional override for the conflict fields in the ON CONFLICT part of the query.
752+
* Only supported in Postgres >= 9.5 and SQLite >= 3.24.0
753+
*/
754+
conflictFields?: (keyof TAttributes)[];
750755
}
751756

752757
/**
@@ -1129,7 +1134,7 @@ export interface ModelValidateOptions {
11291134
* check the value is one of these
11301135
*/
11311136
isIn?: ReadonlyArray<readonly any[]> | { msg: string; args: ReadonlyArray<readonly any[]> };
1132-
1137+
11331138
/**
11341139
* don't allow specific substrings
11351140
*/
@@ -2182,7 +2187,7 @@ export abstract class Model<TModelAttributes extends {} = any, TCreationAttribut
21822187
fields: { [key in keyof M['_attributes']]?: number },
21832188
options: IncrementDecrementOptions<M['_attributes']>
21842189
): Promise<M>;
2185-
2190+
21862191
/**
21872192
* Run a describe query on the table. The result will be return to the listener as a hash of attributes and
21882193
* their types.

‎types/test/upsert.ts‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,5 +41,6 @@ sequelize.transaction(async trx => {
4141
searchPath: 'DEFAULT',
4242
transaction: trx,
4343
validate: true,
44+
conflictFields: ['foo', 'bar']
4445
});
4546
})

0 commit comments

Comments
 (0)