error updating an instance because of integer id treated as string

Open
#11 5 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
35/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
javascript
Domain
databases

Research direction

Start with the Sequelize model definition and the update and association paths described in the report, then inspect the generated SQL and the integer values returned from CockroachDB. Reproduce the failing updates and verify that the database receives the exact id value without an unsupported integer-to-string comparison or association mismatch.

Written by the indexing model from the issue text.

Description

When I do an update from its instance

User.findOne({ where: { email: "[email protected]" } }).then(user => {
  user.update({ password })
})

It triggers an error saying

SequelizeDatabaseError: unsupported comparison operator: <int> = <string>

Because it tries to do this query

UPDATE "user" SET "name"='My name',"reset_at"='2017-07-28 07:04:52.688 +00:00',"updated_at"='2017-07-28 07:04:52.706 +00:00' WHERE "id" = '265875070405378049'

Notice quotes between id '265875070405378049' where it should be an integer instead of string. I have the same error when I am trying to update the association such as

user.getProfile().then(profile => {
  profile.update({ name })
})

The closest workaround I found is from this comment by setting require('pg').defaults.parseInt8 = true. I do get an integer id when doing the above query, but the weird thing is I do not get the right id.

For example if my id in db is 265875070405378049 the query produces something like ... WHERE "id" = 265875070405378050. Most of the time it uses an integer +/- 1 of the actual id. Same thing going on when I create a record with association. the User.id will be something like 265923186502860801 but the user_id in Profile will be like 265923186502860800

For the record my model is as simple as

const User = sequelize.define('user', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: Sequelize.STRING,
}, {
  underscored: true,
  constraints: false,
  timestamps: true
})
Dominant language
JavaScript
Stars
54
Forks
18
PR merge metrics
No merged PRs in 30d

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

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.

More from cockroachdb/sequelize-cockroachdb

All issues in cockroachdb/sequelize-cockroachdb

Similar issues

More JavaScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.