sequelize / sequelize/sequelize
BulkCreate with updateOnDuplicate option generates bad SQL Query
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
- I understand that my issue will be automatically closed if I don't fill in the requested information
- I have read the contribution guidelines
Bug Description
Having a model with an UUIDv4 Primary Key and trying to run model.bulkCreate with the updateOnDuplicate option enabled generates a bad SQL query for that primary key.
Found that around this line is the bug. For some reason it is returning the raw uuidv4 instead of an string.
Reproducible Example
const User = sequelize.define('User', {
user_id: {
type: DataTypes.UUIDV4,
primaryKey: true,
autoIncrement: true,
},
first_name: {
type: DataTypes.STRING,
},
last_name: {
type: DataTypes.STRING,
},
});
async multiUpsertUser(users) {
await User.bulkCreate(users,
{
updateOnDuplicate: ['firstName', 'lastName'],
}
)
}
Input (users)
[
{
user_id: null,
owner_id: 'test,
person_id: 'test',
},
{
user_id: '1be8bec3-689e-4c68-918f-5622a09933d9',
first_name: 'new,
last_name: 'last',
}
]
What do you expect to happen?
The generated query expected should have a valid UUID:
INSERT INTO "users" ("user_id","first_name","last_name")
VALUES (DEFAULT,'test','test'),
('1be8bec3-689e-4c68-918f-5622a09933d9','new','last')
ON CONFLICT ("user_id")
DO UPDATE SET "first_name"=EXCLUDED."first_name","last_name"=EXCLUDED."last_name"
RETURNING "user_id","first_name","last_name";
Note that the id is a plain text and not a string.
What is actually happening?
INSERT INTO "users" ("user_id","first_name","last_name")
VALUES (DEFAULT,'test','test'),
(1be8bec3-689e-4c68-918f-5622a09933d9,'new','last')
ON CONFLICT ("user_id")
DO UPDATE SET "first_name"=EXCLUDED."first_name","last_name"=EXCLUDED."last_name"
RETURNING "user_id","first_name","last_name";
The user_id is not a valid id
Environment
- Sequelize version: 6.32.0
- Node.js version: 16.20.1
- If TypeScript related: TypeScript version: 4.9.3
- Database & Version: PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit
- Connector library & Version: pg@8.9.0, pg-pool@3.6.0
Would you be willing to resolve this issue by submitting a Pull Request?
- Yes, I have the time and I know how to start.
- Yes, I have the time but I will need guidance.
- No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
- No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.
Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with packages/core/src/dialects/abstract/query-generator.js around line 372 and reproduce the bulkCreate case using updateOnDuplicate and the UUIDv4 primary key against PostgreSQL. Compare the generated SQL with the expected query, then verify that the UUID is emitted as a valid quoted value rather than a raw token.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- node.js, postgresql, typescript
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 48/100