Best effort support for JS BigInt in queries

[papandreou]

Pull Request Checklist

Please make sure to review and check all of these items:

• Have you added new tests to prevent regressions?
• Does yarn test or yarn test-DIALECT pass with this change (including linting)?
• Is a documentation update included (if this change modifies existing APIs, or introduces new ones)?
• Did you update the typescript typings accordingly (if applicable)?
• Does the description below contain a link to an existing issue (Closes #[issue]) or a description of the issue you are solving?
• Did you follow the commit message conventions explained in CONTRIBUTING.md?

Description Of Change

• Allow bigint primitives in Model.findByPk
• Do not stringify bigints in SQL queries unless necessary because of limitations in the underlying driver

Background

I'm working on an app that uses Postgres and needs to use the full BIGINT/INT8 range for PK columns. The obvious thing is to use JavaScript BigInts for that to prevent loss of precision when going over Number.MAX_SAFE_INTEGER.

I saw #14296 that aims to address how values from the database get mapped to values in JavaScript.

Even without something like that, I found out that sequelize already has some support for BigInt, and that configuring the pg driver to deserialize INT8 as BigInt actually gets me very far:

const pg = require('pg');

// https://github.com/brianc/node-postgres/issues/378
function nullHandler(parser) {
  return function (val) {
    return val === null ? null : parser(val);
  };
}

pg.types.setTypeParser(pg.types.builtins.INT8, nullHandler(BigInt));
const bigintArrayOid = 1016; // Postgres OID for bigint[]. See SELECT typarray FROM pg_type WHERE typname = 'int8';
const parseBigIntArray = pg.types.getTypeParser(bigintArrayOid);
pg.types.setTypeParser(bigintArrayOid, (a) => parseBigIntArray(a).map(BigInt));

That just left Model.findByPk, as passing a BigInt to that throws:

Argument passed to findByPk is invalid: 123

That was easy to fix (at least for Postgres), it was just a matter of allowing BigInts through in the type check that threw the error.

Then I noticed that when I passed a BigInt to Model.findByPk or into a where object, the number would become a string literal in the resulting SQL. That seemed a bit wasteful, so I tried to fix that also. Getting it to work with all the dialects was a bit tricky, as not all of the underlying drivers support BigInt yet. Thus the "best effort" label. I still think it's worth it to try to DTRT when a BigInt is passed into a query.

[ephys]

Thank you for this PR, I'll set some time aside this friday/weekend to review this

[ephys]

Very nice, only a few notes. Thank you very much for your work on this!

[ephys]

I really appreciate you taking this off my backlog, thank you! :)

[github-actions]

🎉 This PR is included in version 7.0.0-alpha.13 🎉

The release is available on:

• npm package (@release-v7 dist-tag)
• GitHub release

Your semantic-release bot 📦🚀