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

(Aliased) Count in raw query returns string instead of integer for the count column #2383

Closed
Roam-Cooper opened this Issue Oct 2, 2014 · 11 comments

Comments

4 participants
@Roam-Cooper

Roam-Cooper commented Oct 2, 2014

When not binding the results to an orm model and therefore using raw: true,

A SELECT count(id) AS "countName" FROM "TABLE_NAME";
Results in the count being returned as a string, rather than an integer in Sequelize 1.7.9

@Roam-Cooper

This comment has been minimized.

Show comment
Hide comment
@Roam-Cooper

Roam-Cooper Oct 2, 2014

Just tested it with count(id) without an alias as well and it still returns the count as a string. I'm sure that max, min and avg functions would have the same effect.

Roam-Cooper commented Oct 2, 2014

Just tested it with count(id) without an alias as well and it still returns the count as a string. I'm sure that max, min and avg functions would have the same effect.

@mickhansen

This comment has been minimized.

Show comment
Hide comment
@mickhansen

mickhansen Oct 3, 2014

Contributor

What dialect? It's likely a dialect connector issue rather than a sequelize issue. Postgres returns them as strings because of a large number issue in javascript afair.

Contributor

mickhansen commented Oct 3, 2014

What dialect? It's likely a dialect connector issue rather than a sequelize issue. Postgres returns them as strings because of a large number issue in javascript afair.

@Roam-Cooper

This comment has been minimized.

Show comment
Hide comment
@Roam-Cooper

Roam-Cooper Oct 4, 2014

Sorry, I should have mentioned that in the OP. Using Postgres. Can you recommend a fix that doesn't involve having to iterate over the results and parse the value, or do you think that's the only solution?

You're right, it's not a Sequelize issue. :)

Solved by making a small change in the file lib/dialects/postgres/connector-manager.js

I commented out the line:
this.pg.types.setTypeParser(20, String);
And changed it to:
this.pg.types.setTypeParser(20, 'text', parseInt)

As mentioned in brianc/node-postgres#427

Roam-Cooper commented Oct 4, 2014

Sorry, I should have mentioned that in the OP. Using Postgres. Can you recommend a fix that doesn't involve having to iterate over the results and parse the value, or do you think that's the only solution?

You're right, it's not a Sequelize issue. :)

Solved by making a small change in the file lib/dialects/postgres/connector-manager.js

I commented out the line:
this.pg.types.setTypeParser(20, String);
And changed it to:
this.pg.types.setTypeParser(20, 'text', parseInt)

As mentioned in brianc/node-postgres#427

@mickhansen

This comment has been minimized.

Show comment
Hide comment
@mickhansen

mickhansen Oct 4, 2014

Contributor

@Roam-Cooper yeah i recall seeing that before, it's not something we can generaly fix in Sequelize but perhaps it's something we should expose so people could call it easily without changing the sequelize code.

Contributor

mickhansen commented Oct 4, 2014

@Roam-Cooper yeah i recall seeing that before, it's not something we can generaly fix in Sequelize but perhaps it's something we should expose so people could call it easily without changing the sequelize code.

@Roam-Cooper

This comment has been minimized.

Show comment
Hide comment
@Roam-Cooper

Roam-Cooper Oct 5, 2014

Yeah, it'd be great to have a setting in Sequelize for it as my modified files won't really work as we push our app to AWS EB, which installs fresh modules for us (so not including my changes).

I would have preferred that the PG guys keep it as int and then convert to string only when the value surpasses the postive value threshold for JS integers.

Roam-Cooper commented Oct 5, 2014

Yeah, it'd be great to have a setting in Sequelize for it as my modified files won't really work as we push our app to AWS EB, which installs fresh modules for us (so not including my changes).

I would have preferred that the PG guys keep it as int and then convert to string only when the value surpasses the postive value threshold for JS integers.

@janmeier

This comment has been minimized.

Show comment
Hide comment
@janmeier

janmeier Oct 5, 2014

Member

In your own models file you should be able to require pg and do the changes mentioned in the issue you linked

Member

janmeier commented Oct 5, 2014

In your own models file you should be able to require pg and do the changes mentioned in the issue you linked

@Roam-Cooper

This comment has been minimized.

Show comment
Hide comment
@Roam-Cooper

Roam-Cooper Oct 5, 2014

In my models file? Could you please clarify? Because I only ever require Sequelize, I don't believe I have access to the pg that Sequelize uses unless I modify Sequelize code, which I can not do.

Roam-Cooper commented Oct 5, 2014

In my models file? Could you please clarify? Because I only ever require Sequelize, I don't believe I have access to the pg that Sequelize uses unless I modify Sequelize code, which I can not do.

@janmeier

This comment has been minimized.

Show comment
Hide comment
@janmeier

janmeier Oct 6, 2014

Member

pg exports a singleton object, so require('pg') in your code will return the same object that sequelize has access to. I just tested it, and doing require('pg').defaults.parseInt8 = true outside of sequelize code works fine

Member

janmeier commented Oct 6, 2014

pg exports a singleton object, so require('pg') in your code will return the same object that sequelize has access to. I just tested it, and doing require('pg').defaults.parseInt8 = true outside of sequelize code works fine

@Roam-Cooper

This comment has been minimized.

Show comment
Hide comment
@Roam-Cooper

Roam-Cooper Oct 10, 2014

Oh, that's awesome then! Thanks a tonne for your help, guys. :)

Roam-Cooper commented Oct 10, 2014

Oh, that's awesome then! Thanks a tonne for your help, guys. :)

@kikar

This comment has been minimized.

Show comment
Hide comment
@kikar

kikar Aug 30, 2017

Same thing is happening to me with Mysql/MariaDB, how to fix this?

kikar commented Aug 30, 2017

Same thing is happening to me with Mysql/MariaDB, how to fix this?

@kikar

This comment has been minimized.

Show comment
Hide comment
@kikar

kikar Aug 30, 2017

I've tried dialectOptions:

new Sequelize('mysql://root@localhost:3306/database', { dialectOptions: { supportBigNumbers: true }})

Following Sequelize docs and mysql docs, but still now working.

kikar commented Aug 30, 2017

I've tried dialectOptions:

new Sequelize('mysql://root@localhost:3306/database', { dialectOptions: { supportBigNumbers: true }})

Following Sequelize docs and mysql docs, but still now working.

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