feat: add support for bigints (backport of #14485)

[marra85]

Pull Request Checklist

• Have you added new tests to prevent regressions?
• If a documentation update is necessary, have you opened a PR to the documentation repository?
• Did you update the typescript typings accordingly (if applicable)?
• Does the description below contain a link to an existing issue (Closes [BUG] - MSSQL - bulkCreate on BigInt fields fails #15411) or a description of the issue you are solving?
• Does the name of your PR follow our conventions?

Description Of Change

this PR backports #14485 to v6 branch with some exceptions regarding original PR changes

• src/dialects/mssql/query.js L93-L98 because the paramType object changed
• test/integration/sequelize/query.test.js because changes tests that are not related to BigInt support
• test/registerEsbuild.js because increases the node version
• test/unit/query-generator/select-query.test.ts because does not exists in v6

the other changes have been applied.

[WikiRik]

Maybe you can add the test of select-query.test.ts in https://github.com/sequelize/sequelize/blob/v6/test/unit/sql/select.test.js ?

[marra85]

Hello @ephys,

I've revised CI the failures and implemented the suggestions by @WikiRik.

I tried to fix oracle specific issues, since in v7 is not present, from what I could see from stable v6 the data was cast to string before arriving in the run executor, so I applied the same logic from sqlite (stringify bigints).
It seems that it works for .create but not for .bulkCreate, since I'm not familiar with oracle I need some advice.
could you please tell me the best approach (or help me fix it?)

One side note: the ported test from typescript has different values since the sql generated in v6 is slightly different, I've left the original SQL statements to let you decide if they are good values or not.

[ephys]

I believe the problem comes from this:

sequelize/src/dialects/oracle/data-types.js

Lines 302 to 304 in 75b473a

	_getBindDef(oracledb) {
	return { type: oracledb.DB_TYPE_NUMBER };
	}

. It's saying that the bind parameter is a number instead of a string

It looks like oracle set up their own way of passing bind parameter types around (due to this not being available yet). It's called here: https://github.com/sequelize/sequelize/blob/v6/src/dialects/oracle/query-generator.js#L716

I've pinged them on slack to see if they have an opinion on how to solve this

One side note: the ported test from typescript has different values since the sql generated in v6 is slightly different, I've left the original SQL statements to let you decide if they are good values or not.

I saw :) That's perfectly fine, we simply made further changes in v7 that aren't part of v6

[ephys]

I think a good way to fix that would be to update this.options.inbindAttributes here:

sequelize/src/dialects/oracle/query.js

Lines 80 to 90 in 75b473a

	// Since the bigint primitive is not automatically translated
	// we stringify the value as was done previously
	if (_.isPlainObject(parameters)) {
	const newParameters = Object.create(null);
	for (const key of Object.keys(parameters)) {
	newParameters[`${key}`] = stringifyIfBigint(parameters[key]);
	}
	parameters = newParameters;
	} else if (_.isArray(parameters)) {
	parameters = parameters.map(stringifyIfBigint);
	}

[nilabja-bhattacharya]

Hi @marra85,

Can you try iterating over the out parameter list/map and convert the type from DB_TYPE_NUMBER to DB_TYPE_VARCHAR2 when we pass an int > MAX_SAFE_INTEGER. Let me know if it works.

[fzn0x]

Looks good, also want to say to @nilabja-bhattacharya for the review help :)

[marra85]

@ephys I'm glad you approved the PR!

could you share an ETA for the release containing this fix?
it's really important for us :)

PS: about the CI / Postgres 10 (Node 10) (minified aliases) (pull_request) lane I saw that the failure seems to be related to a schema not existing, but I'm not able to reproduce the issue locally, moreover all other PG CI lanes are passing.

[ephys]

Pretty much as soon as the CI gives the greenlight, the release will be published automatically

The CI issue is something we fixed in v7, but fails every now and then in v6. It's just a badly written test, I've just re-run that job :)

[github-actions]

🎉 This PR is included in version 6.27.0 🎉

The release is available on:

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

Your semantic-release bot 📦🚀