Typescript gen : invalid SQL Function types

[ngasull]

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

For both input and input types, typescript generation of SQL functions is invalid in some cases :

• Primitive types are generated as non-nullable (type instead of type | null) even though the function may accept NULL as input or output
• Domain types are generated as unknown

I think that this bug is not caused by a PostgreSQL limitation as views produce the correct typing : primitives are nullable, domains generate as their underlying type and when domains are marked NOT NULL they are even non-nullable in typescript.

To Reproduce

CREATE DOMAIN strict_text AS text NOT NULL;

CREATE FUNCTION some_function(arg strict_text)
RETURNS table (nulltext text, stricttext strict_text)
LANGUAGE SQL AS $$
   SELECT NULL::uuid, arg
$$;

Generated type with supabase gen types typescript --local

export interface Database {
  public: {
    Functions: {
      some_function: {
        Args: {
          arg: unknown
        }
        Returns: {
          nulltext: string
          stricttext: unknown
        }[]
      }
    }
  }
}

Expected behavior

Generated types should be

export interface Database {
  public: {
    Functions: {
      some_function: {
        Args: {
          arg: string
        }
        Returns: {
          nulltext: string | null
          stricttext: string
        }[]
      }
    }
  }
}

System information

• OS: [e.g. Linux NixOS]
• Version of supabase CLI: 1.50.8
• Version of Node.js: 18.15.0

Additional context

I :heart: the work being done and the mindset at Supabase. I believe strict typing is crucial to the success of a "backend in the DB" and I could help with a PR if I'm being given some pointers to the code responsible of the TS generation :wave:

[sweatybridge]

Thanks for the detailed bug report. The typescript generation code is in postgres-meta repo if you are keen to take a look.

[ngasull]

Just pushed a PR for this issue, feel free to have a look! :slightly_smiling_face:

[mwoss]

That would be a great addition to the client library tho :3 I would love to see it live in the near future!

[Donnerstagnacht]

Is there an update on this @sweatybridge or @ngasull ? I think https://github.com/supabase/postgres-meta/issues/841 is a duplicate of this issue.

[ngasull]

@Donnerstagnacht PostgreSQL types are always nullable except domains which are NOT NULL.

Thus, I think this issue is broader than #841 : every returned type should be generated as Type | null (including enums) except for domains, which aren't implemented at all.

[Donnerstagnacht]

Right, makes sense. Thanks for the clarification.

May I ask, have you received feedback on your PR #573 or why is that one pending since 2 years?

And would it be a good first step in your opinion to fix #841 first, e.g. fix the type not null generation issue first on a smaller scope for functions e.g. return base types and/or tables/composite types and tackle the general issue in a second step?

And is this a technical (code) issue or a design choice because it is a breaking change? Do you think a flag would be required as proposed in #841 (-rpc-allow-null-results) @ngasull?

[ngasull]

Indeed, because it's such an impacting breaking change the issue falls into the technical design too. However because Supabase tries to push safety to the front of the scene, I really felt that handling nullability correctly was a priority.

Current choice of typing primitives as their NonNullable TS counterpart is a respectable design choice. It's convenient and avoids domains. However it's incorrect and there is no way around unsound types in current design.

I think that changing the null behavior for enums brings the same questions as for other types. Therefore, if I had to decide, I would tackle the issue as a whole in order to commit a single breaking change and to provide simpler and more consistent functionality (why would only enums be generated as nullable?)

No feedback came back to me since PR creation which, to be honest, is sadly the reason why I dropped my interest for Supabase. Unsafe function typing makes it not reliable enough to write business logic in my opinion.