Supabase RPC Typescript Types Nullable

Question

I made a Supabase RPC function and I'm having problems with the typescript definitions generated from:

npx supabase gen types

Below are the generated types, and I want to make current_status nullable, as it can sometimes 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'];
  }[];
};

This is my 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 // How do I 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;
$$;

dshukertjr · Accepted Answer · 2023-11-07 04:47:57Z

2

The generated typescript type should be nullable if the returned function column type is nullable. You can have your function inherit the table column type with the [table].[column]%TYPE notation like this:

CREATE FUNCTION get_bananas(
  _limit INT DEFAULT NULL,
  _offset INT DEFAULT 0
)
RETURNS TABLE (
  id bananas.id%TYPE,
  text bananas.text%TYPE,
  current_status statuses.status_type%TYPE
)
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;
$$;

Once you update your function definition, run the type generation again.

answered Nov 7, 2023 at 4:47

dshukertjr

19.2k11 gold badges71 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Potato Over a year ago

Thanks for the answer! Unfortunately that doesn't seem to work, statuses.status_type is not nullable but it can be null because it's possible to have no status for a banana (no row in the database). Even updating the type of statuses.status_type to allow null and the RPC with current_status statuses.status_type%TYPE, the generated types for my RPC function remain the same ...

dshukertjr Over a year ago

@Potato Huh, okay. I see that you tried current_status: null | Database['public']['Enums']['status_type_enum'];, but did that not work?

Potato Over a year ago

The types are generated so if I add null to current_status, it does work, but it is overwritten everytime I regenerate the types. I thought about adding my own types to override the generated ones but I was looking into getting the right types generated right away - if that is possible.

dshukertjr Over a year ago

@Potato I understand. This might be a technical limitation on the Supabase. It might help opening an issue on the Supabase CLI repo. github.com/supabase/cli

tobias hassebrock Apr 17 at 14:12

Is that a working solution @dshukertjr ? I tried it but could not make it work. There are also 2 related issues in the supabase repo: github.com/supabase/postgres-meta/issues/842 and github.com/supabase/postgres-meta/issues/841

f1yingbanana · Accepted Answer · 2024-12-03 05:02:58Z

0

It looks like this now works out of the box! You still can't mark a parameter as nullable in the create function UI in the Supabase dashboard though, but at least the type generation is working correctly.

answered Dec 3, 2024 at 5:02

f1yingbanana

2,9002 gold badges24 silver badges38 bronze badges

3 Comments

tobias hassebrock Apr 17 at 14:12

How did you make it work? Could you post an example?

f1yingbanana May 18 at 4:24

Instead of creating it in the UI, you can directly create it in SQL! Get your favorite LLM model to do it for you :P

tobias hassebrock May 18 at 8:35

Yeah, ofc I can do that. But the type genration from that sql does not work correctly. It does not generate types as nullable and the proposed notation above with inheriting nullable from a table definition using type% does not work.

Collectives™ on Stack Overflow

Supabase RPC Typescript Types Nullable

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related