Improve generated TS schemas with supabase gen types

[warrenbhw]

I'm using supabase auth, supabase RLS, and the supabase postgREST api. I like the idea of pushing authorization and validation rules all into the db, rather than having them split across the app layer and db.

However, one of the biggest points of friction that I encounter when building this way is reflecting the schema of my supabase tables and functions in my NextJS typescript codebase.

I use the supabase gen types command built into the Supabase CLI, but I end up having to write a lot of additional overrides, because not everything I want is inferred. Here are some examples:

• DB function return types are always unknown - I end up having to write them myself and override the generated type
• JSON types are just generic JSON, even when a better type could be inferred, ex. { id: string, name: string } - should be able to infer these from function definitions or from JSONschema check constraints
• Does not autogenerate schema definitions for zod/yup/valibot, so I end up having to write these manually any time I have a form
• For custom enum types, the type generation works well, but for domain types (aka refinement types), the type is inferred as unknown. Even if the generator doesn't want to generate zod/yup/valibot schemas for these, it could at least generate the base type of the domain instead of unknown.

Given these limitations, I feel that it would've been faster to just use Supabase as a vanilla Postgres and use Prisma - this way, I could define schemas once in typescript and get autogenerated zod, sql, and ts types.

Addressing these limitations would make me significantly more productive using Supabase w/ RLS+auth+postgREST.

Are there any good community projects that offer improved typegen, or are there plans to improve the type generator in supabase cli?
right now, it feels like i either have to get off the "all supabase" stack, or write my own type generator if I want to stay productive

[sweatybridge]

Thank you for your thoughtful feedback. I agree with you that typegen needs more love in some of the advanced use cases.

I will loop in @soedirgo who works on postgres-meta to share more about the alternatives and road map for typescript typegen.

[warrenbhw]

Thanks for your response. @sweatybridge @soedirgo thought on starting with this suggestion?

For custom enum types, the type generation works well, but for domain types (aka refinement types), the type is inferred as unknown. Even if the generator doesn't want to generate zod/yup/valibot schemas for these, it could at least generate the base type of the domain instead of unknown.

Taking a second look, it seems like this behavior is actually already implemented for tables, but it is not correctly implemented for functions. For example, I have this domain type:

-- Our nanoid type is 15 characters long and
-- uses a-z as the alphabet.
CREATE DOMAIN public.snowpilot_nanoid AS text
    CHECK (VALUE ~ '^[a-z]{15}$');

and a function that looks like this:

create or replace function public.current_user_workspace_role(workspace_id snowpilot_nanoid)
    returns jsonb
    language plpgsql
as
$$
BEGIN
 -- function body....
END
$$;

but my generated ts type for the function looks like this:

      current_user_workspace_role: {
        Args: {
          workspace_id: unknown
        }
        Returns: Json
      }

it should be easy to at least infer that workspace_id is a string.

[seanghods]

We had similar issues with unknown return types as well. This function

CREATE OR REPLACE FUNCTION get_profile_with_subscription(user_id UUID)
RETURNS TABLE (
  profile public.profiles,
  subscription public.subscriptions
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    p,
    s
  FROM public.profiles p
  LEFT JOIN public.subscriptions s
    ON p.user_id = s.user_id
  WHERE p.user_id = user_id;
END;
$$ LANGUAGE plpgsql;

returning unknown types:

 get_profile_with_subscription: {
        Args: {
          user_id: string
        }
        Returns: {
          profile: unknown
          subscription: unknown
        }[]
      }

For those who ran into similar issues, our resolution was to build the correct type using types from Supabase to ensure continuity

export type TRpcGetProfileWithSubscription = {
  Args: Database['public']['Functions']['get_profile_with_subscription']['Args']
  Returns: {
    profile: Database['public']['Tables']['profiles']['Row']
    subscription: Database['public']['Tables']['subscriptions']['Row']
  }
}