Auto convert to JS `BigInt` when numbers are not safe integer

[unknown1337]

Bug report

Describe the bug

A clear and concise description of what the bug is.
type overflow bigint, seems that the client lib cannot handle bigint which causes some unexpected behaviour (overflow?)

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. SQL
   create table test ( myfield int8 ); insert into test (myfield) values (23849208278400758)
2. JS

import { createClient } from '@supabase/supabase-js'
const supabaseUrl: string = process.env.SUPABASE_URL ?? ''
const supabaseKey: string = process.env.SUPABASE_KEY ?? '' 
supabase = createClient(supabaseUrl, supabaseKey)
  console.log(
    await supabase
      .from('test')
      .select('myfield')
      .eq('myfield', '23849208278400758') // quotes->string as the number wont fit in 32bit mem);
  ); 

Expected behavior

expected result to be 23849208278400758 or "23849208278400758" (automatic type conversion to prevent overflows
observed result 23849208278400760

Screenshots

System information

• OS: [Windows]
• Browser (if applies chrome
• Version of supabase-js: 1.35.6"

Additional context

note: causes difficult to find issues
note: root cause of issue unknown (dep?).
quasar v2

[soedirgo]

See also #273:

This is because numbers in JS are float64, so if your number is more than Number.MAX_SAFE_INTEGER === 9007199254740991 it won't be represented correctly.
An alternative is to cast it to text: supabase.from(table_name).select('id, num::text') and manually convert them to BigInt.

[soedirgo]

Repurposing this issue to do auto-conversion to JS BigInt when numbers are !Number.isSafeInteger(x)

[bensalilijames]

Postgrest is returning the number correctly in the fetch response:

[{"id":349506210296369153}]

But it loses precision when the response.text() goes through JSON.parse in this library:

[{ id: 349506210296369150 }]

This is the offending line:

postgrest-js/src/PostgrestBuilder.ts

Line 95 in 878034b

data = JSON.parse(body)

Would you be open to adopting a different JSON parser that can transform ints bigger than MAX_SAFE_INTEGER or smaller than MIN_SAFE_INTEGER into BigInts or strings? For example, json-bigint?

It could be an opt-in feature per-request with a flag on PostgrestBuilder, though it seems safer out the box for the default behaviour to be to return correct values rather than truncated (wrong!) ones.

(Note, eventually this should become possible in the web platform itself without having to use a whole external JSON parser, instead using just reviver! see https://github.com/tc39/proposal-json-parse-with-source)

More conversation here: PostgREST/postgrest#498

I imagine the supabase gen types command would need to be updated to change the generated TypeScript types to number | string or number | BigInt also.

Hope that helps!

[soedirgo]

Would you be open to adopting a different JSON parser that can transform ints bigger than MAX_SAFE_INTEGER or smaller than MIN_SAFE_INTEGER into BigInts or strings?

Yes, but we consider this a breaking change so this will have to wait until the next major version.

The TC39 proposal looks interesting - thanks for sharing!

[bradennapier]

Ideally you would decide if you want to get bigint back always or number | bigint if you are going to offer both.

[afk-mario]

Just want to point out that if you cast a bigint to text as recommended in #273 (comment) you end up breaking TS as seen in #370

[jens-f]

I just stumbled over this issue as well when calling functions defined in Postgres that expect and return bigint numbers.

E.g. we have this function

CREATE OR REPLACE FUNCTION public.number_test(n bigint) RETURNS bigint
LANGUAGE SQL IMMUTABLE
RETURNS NULL ON NULL INPUT
AS $$ SELECT n; $$;

Which supabase gen types --lang typescript turned into this definition.

      number_test: {
        Args: {
          n: number
        }
        Returns: number
      }

When calling this, the numbers get silently changed:

await supabaseClient.rpc('number_test', { n: 8603657889541918976 })).data

// received: 8603657889541919000

Investigating options

For a test I manually switched the definition to n: number|bigint

and then called it like this:

await supabaseClient.rpc('number_test', { n: 8603657889541918976n })).data

but that just leads to the error

    TypeError: Do not know how to serialize a BigInt
        at JSON.stringify (<anonymous>)

I then manually changed the definition to n: number|string and called it this way

await supabaseClient.rpc('number_test', { n: "8603657889541918976" })).data

This works on the sender side.

But we also need another means of receiving the returned bigint values as text. Adding a .select to await supabaseClient.rpc('number_test', { n: "8603657889541918976" }).select(1 as text) doesn't work since there are no named columns and hence this fails.

I also tried using .select(::text) which doesn't fail but then also just gets turned back into a number by JSON.parse.

Proposal

In order to address all of this, here's my proposal:

1. Would you guys consider extending the supabase js client so that users can provide their own JSON.stringify and JSON.parse(body) implementations? This could be passed in as options when creating the supabase client or alternatively in the options parameter of the .rpc() call

2. In addition, would you consider extending the type generator with a new flag int8as

supabase gen types --lang typescript --int8as <numberAndBigint | numberAndText | bigint | text>

that essentially writes out all bigint columns as Typescript definitions number|bigint, number|text,
bigint, or text -> depending on what format is best for the user.

This way the user has full (typesafe) control over data being serialized or deserialized and it shouldn't cause any breaking issues for users who don't run into this problem.