Return types for RPC cannot have nullable properties

[dbuhon]

The supabase gen types appears to lack support for RPC functions returning objects with nullable properties.

Steps to reproduce:

Define an RPC function:

   CREATE TYPE status_type_enum AS ENUM ('rotten', 'ripe'); 
   
   CREATE FUNCTION get_bananas(
     _limit INT DEFAULT NULL,
     _offset INT DEFAULT 0
   )
   RETURNS TABLE (
     id UUID,
     text TEXT,
     current_status status_type_enum -- Is there a way to make this nullable in TS?
   )
   LANGUAGE plpgsql
   AS $$
   BEGIN
     RETURN QUERY
     SELECT
       b.id,
       b.text,
       s.status_type AS current_status
     FROM bananas b
     LEFT JOIN statuses s ON b.id = s.banana_id AND s.user_id = auth.uid()
     LIMIT _limit
     OFFSET _offset;
   END;
   $$;

Run the command:

npx supabase gen types

Examine the generated types:
It doesn't seem possible to make current_status nullable out of the box, even though it can be missing.

   get_bananas: {
     Args: {
       _limit?: number;
       _offset?: number;
     };
     Returns: {
       id: string;
       text: string;
       current_status: Database['public']['Enums']['status_type_enum'];
       // current_status: null | Database['public']['Enums']['status_type_enum'];
     }[];
   };

Workaround

Extend the generated types from another file to overwrite properties.

[Marviel]

One thing that changing this will likely do is affect a lot of people's code.

(i.e. types that were previously non-null are now suddenly going to throw a lot of typescript compile errors)

So it may be wise to have this behind some sort of feature flag, i.e. --rpc-allow-null-results or something like that.

[wmarrujo]

here's my workaround:

type PartialNullBy<T, E extends keyof T> = {[K in keyof T]: K extends E ? (T[K] | null) : T[K]}
type PartialBy<T, K extends keyof T> = Omit<T, K> & Pick<Partial<T>, K>
type ArrayElement<ArrayType extends readonly unknown[]> = ArrayType extends readonly (infer ElementType)[] ? ElementType : never
type Debug<T> = T extends Function ? T : {[K in keyof T]: T[K]}

use the PartialNullBy type for supabase to wrap the object part of what you get back. I've included the ArrayElement type so you can apply it to the object part of the type when you're getting a whole array (table) of objects. I also included a PartialBy just for completeness, which will do the same thing using undefined instead.

let {data} = await locals.supabase.rpc("get_bananas")
let better = data as Array<PartialNullBy<ArrayElement<typeof data>, "current_status" | "another_column_if_you_want">>

now, the resultant type will look gross, but you can wrap the whole thing with Debug to check that it evaluated correctly

[Donnerstagnacht]

Same issue here. There is a stackoverflow question which suggests a solution using a reference to the table type.

https://stackoverflow.com/questions/77427669/supabase-rpc-typescript-types-nullable

Is that still a working solution @dshukertjr ? I was not able to make that solution work.

A simpler test example:

table definition:

create table users.names (
    id int,
    first_name text null
);

postgres function:

create or replace function users.select_names (user_id int) returns table (
    first_name users.names.first_name%type
) language plpgsql security invoker as $$
begin
select
    first_name
FROM 
    users.names
where 
    id = select_names.user_id;
end
$$;

generated types:

      select_names: {
        Args: { user_id: string } | { user_id: number }
        Returns: {
          first_name: string
        }[]

It is also a bit strange that the args object includes { user_id: string } as the postgres argument is int.

The correct type would be identical to the row type:

      select_names: {
        Args: { user_id: number }
        Returns: {
          first_name: string | null
        }[]

compare generated table row type:

    Tables: {
      names: {
        Row: {
          first_name: string | null
          id: number | null
        }

windows 11 docker desktop
command: supabase gen types typescript --local > <file_name>
cli version: supabase -v 2.20.12