sequelize / sequelize/sequelize

Support custom upsertKeys for bulkCreate

Open
#13,605 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
TypeScript
Stars
30.4k
Forks
4.3k
Avg merge
1d 6h
Merged PRs (30d)
68

Description

Issue Creation Checklist

Feature Description

Is your feature request related to a problem? Please describe.

I am trying to perform a bulk update of rows where I already know the PK, and each row needs to be updated with a different value. The only way to do this is with a bulkCreate while specifying updateOnDuplicate. One of the fields I would like to update is part of the set of unique keys, so the default functionality of using the unique keys fails for this case and returns a unique key error (tries to insert instead).

Describe the solution you'd like

The bulkCreate (and upsert by extension) should accept a set of keys to use as the uniqueness constraint for the updateOnDuplicate logic.

const people = await Person.bulkCreate(updates, {
  updateOnDuplicate: ['emailAddress', 'name'],
  upsertKeys: ['emailAddress']
});
Why should this be in Sequelize

This adds the ability to configure a core aspect of an updateOnDuplicate query, and therefore should be supported by the raw query generator

Describe alternatives/workarounds you've considered
  • manually passing in upsertKeys to query call without this PR (overwritten internally)
  • changing the upsertKeys field on the options to a custom getter/setter to prevent from being overwritten (still overwritten internally)

Feature Request Checklist

Is this feature dialect-specific?
  • No. This feature is relevant to Sequelize as a whole.
  • Yes. This feature only applies to the following dialect(s): Same dialects as updateOnDuplicate; MySQL, MariaDB, SQLite >= 3.24.0 & Postgres >= 9.5
Would you be willing to implement this feature by submitting a Pull Request?
  • Yes, I have the time and I know how to start.
  • Yes, I have the time but I don't know how to start, I would need guidance.
  • No, I don't have the time, although I believe I could do it if I had the time...
  • No, I don't have the time and I wouldn't even know how to start.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by tracing the bulkCreate and upsert entry points, then follow updateOnDuplicate into the raw query generator. Compare how upsert keys are handled for MySQL, MariaDB, SQLite, and PostgreSQL; done means the requested upsertKeys option is preserved and used for the supported dialects.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, mariadb, mysql, postgresql, sql, sqlite, typescript
Domain
backend, databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
28/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.