Bigint IDs result in broken queries and incorrect types

[ozanmakes]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Both with Postgrest.js and generated TypeScript types Bigint types such as IDs are not represented well. With the default number JS representation of Bigint IDs the queries start breaking because of overflowing numbers. This is especially problematic when using a ID format that very frequently results in numbers larger than Number.MAX_SAFE_INTEGER.

Postgrest supports queries such as id::text, however Supabase support for this leaves a lot to be desired. Two issues off the top of my head:

1. API returns incorrect IDs when Bigint values overflow what JS number type allows. This results in scenarios such as this:

   • Create a row and ask it to return all the fields *.
   • Redirect the user to a page such as /items/<id>
   • Get 404 because the ID doesn't exists. It was incorrect because of overflowing number value.

   This can be sort of worked around using the casting syntax. But unfortunately;

2. Postgrest.js doesn't support casting syntax such as id::text. Neither the issue from nearly a year ago https://github.com/supabase/postgrest-js/issues/370 nor my PR https://github.com/supabase/postgrest-js/pull/429 received a response from Supabase team.

3. Generated types don't allow inserting Bigint or String values. Postgrest seem to be happy with passing string values however the types are generated like so:

export interface Database {
  public: {
    Tables: {
      table_one: {
        Row: {
          id: number;
          foreign_id: number | null;
        };
        Insert: {
          id?: number;
          foreign_id?: number | null;
        };
        Update: {
          id?: number;
          foreign_id?: number | null;
        };
        Relationships: [
          {
            foreignKeyName: "table_two_id_fkey";
            columns: ["foreign_id"];
            referencedRelation: "table_two";
            referencedColumns: ["id"];
          },
        ];
      };
    };
  };
}

A query like this results in an error:

    await this.supabase
      .from("table_one")
      .insert({ foreign_id: "2193192389123" });

To Reproduce

1. Create a table with Bigint ids, and columns with Bigint references to other tables with Bigint ids.
2. Insert data with larger ID values than Number.MAX_SAFE_INTEGER
3. Perform standard CRUD operations with JS SDK using TypeScript.
4. Observe the issues such as wrong IDs being returned (runtime bug) and TypeScript errors (compile time bug)

Expected behavior

Supabase detects Bigint values, returns correct responses and generates correct code. Bigints seem to be handled better in Supabase Console with special treatment, however user code is still affected. Some possible fixes:

• Large Bigint values (or all Bigint values) are detected and returned as strings in query results.
• Generated types are updated to include string and/or JS Bigint types.
• Type casts are supported in generated TypeScript types
• String or JS Bigint values are supported when inserting / updating data.

System information

• OS: macOS
• Browser (if applies) Chrome
• Version of supabase-js: 2.36.0
• Version of Node.js: v18.17.1

[stoicskyline]

bigint is also represented as number in TypeScript when running npx supabase gen types.

      books: {
        Row: {
          id: number

Instead, these should be generated with string type or some representation of BigInt

[Slyracoon23]

I have this exact problem. There is no elegant solution that I have come across yet

[Slyracoon23]

We ran into this same issue in our application where we needed to query the database using bigint columns, but didn't want to break all the TypeScript types generated by the Supabase gen types tool.

As a workaround, we generated a computed column that casts the bigint to a string. For example:

ALTER TABLE public.thread_metadata
ADD COLUMN discord_thread_id_str text GENERATED ALWAYS AS (CAST(discord_thread_id AS text)) STORED;

This allows us to query using the discord_thread_id_str column and get back string values that play nicely with the generated TypeScript types, without modifying the underlying discord_thread_id bigint column.

However, this approach only solves the issue for querying data. We still have challenges with inserting bigint data via the Supabase client without breaking the type safety of the generated types.

It would be great to see better built-in support for bigint in the Supabase client and type generation to handle these common scenarios more elegantly.

[Slyracoon23]

I've been experimenting with a potential workaround that maintains a two-way relationship between the bigint and string columns using PostgreSQL triggers. Check it out:

CREATE OR REPLACE FUNCTION set_reference_id()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.reference_id_str IS NOT NULL AND NEW.reference_id IS NULL THEN
        NEW.reference_id := CAST(NEW.reference_id_str AS bigint);
    ELSIF NEW.reference_id IS NOT NULL AND NEW.reference_id_str IS NULL THEN
        NEW.reference_id_str := CAST(NEW.reference_id AS text);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_reference_id_trigger
BEFORE INSERT OR UPDATE ON public.example_table
FOR EACH ROW
EXECUTE FUNCTION set_reference_id();

Here's the gist:

• If you insert a row with just the reference_id as a bigint, the trigger function automatically fills in the reference_id_str column with the string representation.
• And vice versa - if you provide only the reference_id_str as a string, it gets casted to a bigint and stored in reference_id.
• If you provide both columns, the trigger leaves them as-is.

It's not a perfect solution, but it could help maintain data consistency between the two columns and provide some flexibility in how you insert and retrieve the data.

You can add a CHECK constraint to the table to ensure that the reference_id and reference_id_str columns always contain the same value.

Let me know what you think! Is this something that could be useful in your use case? I'm totally open to suggestions and improvements.