New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Postgres JSONB 'destroy' with 'where' object fails #5092

Closed
ahadinyoto opened this Issue Dec 23, 2015 · 4 comments

Comments

5 participants
@ahadinyoto

ahadinyoto commented Dec 23, 2015

I'm using sequelize 3.15.0, postgres dialect on Mac OS X (El Cap). I have a column with JSONB
type. When destroying by using where object, it throws an exception.

var Page = sequelize.define('Page', {
  content: Sequelize.JSONB
})
Page.create({ name: "Test", content: { title: "Title" } })
.then(() => {
  Page.destroy({ where: { content: { title: "Title" } } })
  .then(() => {
    sequelize.close()
  })
})

It throws.

Unhandled rejection TypeError: val.replace is not a function
    at Object.SqlString.escape (/tmp/myapp/node_modules/sequelize/lib/sql-string.js:56:15)
    at Object.QueryGenerator.escape (/tmp/myapp/node_modules/sequelize/lib/dialects/abstract/query-generator.js:983:22)
    ...

When using netsted key content.title, it also throws an exception though a different one.

Page.destroy({ where: { 'content.title': "Title" } })
Unhandled rejection SequelizeDatabaseError: column "content.title" does not exist
    at Query.formatError (/tmp/myapp/node_modules/sequelize/lib/dialects/postgres/query.js:347:14)
    at null.<anonymous> (/tmp/myapp/node_modules/sequelize/lib/dialects/postgres/query.js:81:19)
    ...

However, using that same 'where' object with findOne or findAll works perfectly as expected.

@mickhansen mickhansen added the bug label Dec 23, 2015

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Dec 23, 2015

Looks like destroy has some legacy code.

@fredericlefeurmou

This comment has been minimized.

fredericlefeurmou commented Dec 31, 2015

+1

1 similar comment
@erichulburd

This comment has been minimized.

erichulburd commented Jan 1, 2016

+1

@zbigg

This comment has been minimized.

zbigg commented Mar 15, 2017

The same code, but with paranoid flag enabled i.e "destroy by setting deletedAt value doesn't work.

Change model declaration to:

var Page = sequelize.define('Page', {
  content: Sequelize.JSONB
}, { paranoid: true} )

and observe exactly same behaviour as in original post:

  • Page.destroy({ where: { content: { title: "Title" } } }) triggers
Unhandled rejection TypeError: val.replace is not a function
    at Object.SqlString.escape (.../node_modules/sequelize/lib/sql-string.js:61:15)
    at Object.escape (.../node_modules/sequelize/lib/dialects/abstract/query-generator.js:978:22)
  • Page.destroy({ where: { 'content.title': "Title" } }) triggers
Unhandled rejection SequelizeBaseError: column "content.title" does not exist
    at Query.formatError (/.../node_modules/sequelize/lib/dialects/postgres/query.js:357:14)
    at Query.<anonymous> (/.../node_modules/sequelize/lib/dialects/postgres/query.js:88:19)

Sequelize version: "sequelize": "^3.30.2",

Should I open new PR, or you prefer creating new one ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment