Handling of bigint and decimal types

[uki00a]

Summary

deno_mysql currently treats MySQL's bigint and decimal as the JavaScript's Number.

However, these types cannot be accurately represented by the Number.

The following code reproduces this problem:

mod.ts:

import { Client } from "https://deno.land/x/mysql@1.9.1/mod.ts";

const client = await new Client().connect({
  hostname: "localhost",
  port: 3306,
  username: "test",
  db: "test",
  password: "test",
});

await client.query(`DROP TABLE IF EXISTS test`);
await client.query(`CREATE TABLE test (
  id int(11) NOT NULL AUTO_INCREMENT,
  bigint_column bigint NOT NULL,
  decimal_column decimal(65,30) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4`);

const bigint = "9223372036854775807";
const decimal = "0.012345678901234567890123456789";
await client.execute(`
  INSERT INTO test (bigint_column, decimal_column)
  VALUES (${bigint}, ${decimal})`);

console.log(await client.query(`SELECT * FROM test`));

$ deno run --allow-net ./mod.ts
INFO connecting localhost:3306
INFO connected to localhost
[
 { id: 1, bigint_column: 9223372036854776000, decimal_column: 0.012345678901234568 }
]

• deno@v0.42.0
• deno_mysql@v1.9.1

Possible solutions

• Always treat bigint and decimal as String (probably the simplest solution).
• Provide options that change the handling of these types (like node-mysql).
  • node-mysql provides supportBigNumbers and bigNumberStrings options.

These are just ideas, so any feedback would be appreciated 😄

Ref

• https://dev.mysql.com/doc/refman/5.7/en/numeric-type-syntax.html
• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number

[magichim]

I think, it is necessary point! 😀

[magichim]

In sql-builder, BigInt number is possible to solve it as follow.

if (val >= Number.MAX_SAFE_INTEGER) {
  return `${BigInt(val)}`;
}

However, '0.012345678901234567890123456789' Long decimal number is a little complicated...