[Suggestion] Could `BigIntColumn` return native `BigInt` values?

[farpeter98]

Problem

Currently the BigInt SQL type is converted into JS Number values in the driver - the former is a 64 bit integer, the latter is a 64 bit float, so there's a possible precision loss there.

I looked through the wiki and the previous issues and found that specifically for this the setUseNumericString option can be set to make the driver return strings instead - however, this applies to all number conversions, which, while not deal breaking, is a bit of a nuisance.

Suggestion

Couldn't we simply use JS BigInt values? I tried the modification locally, this got me the desired behaviour.

diff --git a/cpp/include/js/columns/big_int_column.h b/cpp/include/js/columns/big_int_column.h
index 8edaeb34..0d3a9a3c 100644
--- a/cpp/include/js/columns/big_int_column.h
+++ b/cpp/include/js/columns/big_int_column.h
@@ -24,7 +24,7 @@ class BigIntColumn : public Column {
   }
 
   inline Napi::Object ToNative(Napi::Env env) override {
-    return Napi::Number::New(env, value).As<Napi::Object>();
+    return Napi::BigInt::New(env, value).As<Napi::Object>();
   }
 
  private:

Test Results

Full test suite passes with the change applied (Windows, Node v22.13.0, ODBC Driver 18, Trusted Connection):

  695 passing (3m)
  2 pending

The 2 pending tests are:

• multiple-errors.test.js → "non trusted invalid user" — explicitly marked with it.skip().
• encrypt-verification.test.js → "should verify Always Encrypted is actually working..." — skipped because I used trusted connection without encryption

Breaking Change Considerations

This is a breaking change for consumers that perform arithmetic or comparisons with Number on BIGINT columns (e.g. row.id + 1 becomes a TypeError when row.id is a BigInt). Possible mitigations:

1. Major version bump — ship as part of the next major release.
2. Opt-in flag — add a connection/query option like { bigIntAsNative: true } (default false) so users can migrate at their own pace; flip the default in the next major.

[farpeter98]

I wasn't sure if there's a specific reason for this so I thought I'd open an issue first, but if this gets the green light I'm glad to go ahead and put together a pull request with either mitigation option

[TimelordUK]

yes this does seem a sensible suggestion, i will look to release shortly ill have to ensure we are consist across the whole library, ill report back, i think initialy we have another opt in flag somewhat like the asstring flag and for all current users the behaviour will be as it is today

[TimelordUK]

please see

https://github.com/TimelordUK/node-sqlserver-v8/releases/tag/v5.2.0

and there is a new test file

❯ npx mocha test/bigint.test.js


  bigint return type
    default (legacy Number) return path
      ✔ returns small BIGINT as Number
      ✔ returns Number.MAX_SAFE_INTEGER exactly
      ✔ LOSES PRECISION for 2^53 + 1 (silent corruption — the bug)
    per-query bigint_as_native flag
      ✔ returns small BIGINT as native BigInt
      ✔ preserves precision at 2^53 + 1
      ✔ preserves precision at SQL BIGINT max (2^63 - 1)
      ✔ preserves precision at SQL BIGINT min (-2^63)
      ✔ does not affect plain INT columns
      ✔ does not affect DECIMAL columns whose value happens to be whole
    per-connection setUseBigIntAsNative
      ✔ returns BigInt for every BIGINT query once enabled
      ✔ reverts to Number when disabled mid-session
      ✔ per-query flag overrides connection default
    precedence: numeric_string overrides bigint_as_native
      ✔ per-query: numeric_string wins when both are set
      ✔ connection-level: numeric_string wins when both are set
    parameter binding accepts BigInt
      ✔ binds a BigInt literal as a BIGINT parameter
      ✔ round-trips a max-precision value via the result row
      ✔ binds a BigInt typed via sql.BigInt() wrapper
    JSON serialisation caveat
      ✔ JSON.stringify throws on a row with a BigInt column
      ✔ users can stringify by adding a BigInt-aware replacer
    bulk insert with BigInt values
      ✔ round-trips BigInt values exceeding 2^53 through bulk insert (218ms)
    stored procedure with bigint parameter
      ✔ accepts a BigInt parameter and returns the echoed value
      ✔ binds BigInt via direct EXEC with ? parameter
    TVP with bigint column
      ✔ round-trips BigInt values through a bigint TVP column (50ms)


  23 passing (481ms)

[farpeter98]

Oh that was quick, thank you!