`supabase-js`: Selecting computed text field results in SelectQueryError

[stefan-girlich]

Describe the bug

Selecting a computed field on a table results in a TypeScript error:

// SelectQueryError<"column 'name_translated' does not exist on 'category'.">[] | null
const { data: data_computed_field } = await supabase
  .from("category")
  .select("name, name_translated");

Querying a computed relationship works as expected. See Steps to reproduce below and the linked MRE for further details.

Library affected

supabase-js

Reproduction

https://codesandbox.io/p/sandbox/89mt23

Steps to reproduce

1. For a given table, create a Postgres function that accepts the table and returns a scalar, and thus can be used as a computed field in PostgREST; see example below
2. Generate TS types, i.e. via CLI
3. Initialize client via createClient<Database>(/* ... */)
4. Query computed field => SelectQueryError

Computed fields vs computed relationships

Computed relationships work as expected; see the linked MRE. This is the case because they are added explicitly as functions and also in Database['public']['your_table_name']['Row'] whereas there is no entry for computed fields (^= functions returning a scalar).

Adding the missing computed field name during generation (or for now: manually) fixes the issue, but I cannot tell if this is the ideal solution or if anything speaks against it.

Special case

In my specific case, separate name_translated functions exists for two tables, resulting in a generated TS type like this:

name_translated:
        | {
            Args: { category: Database['public']['Tables']['category']['Row'] }
            Returns: string
          }
        | {
            Args: { something_else: Database['public']['Tables']['something_else']['Row'] }
            Returns: string
          }

Using different function/field names does not prevent the issue, but a fix may need to take the possibility of identical function/field names into consideration.

Postgres function example

CREATE OR REPLACE FUNCTION name_translated(category category)
RETURNS text
LANGUAGE sql
STABLE
AS $$
-- ... actual logic omitted ...
$$;

System Info

Reproducible on arbitrary system; tested on Codesandbox, Linux, MacOS

Supabase CLI v2.67.1
supabase-js v2.89.0
TypeScript v5.5.4

Used Package Manager

bun

Logs

No response

Validations

• Follow our Code of Conduct
• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Make sure this is a Supabase JS Library issue and not an issue with the Supabase platform. If it's a Supabase platform related bug, it should likely be reported to supabase/supabase instead.
• Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Discord Chat Server.
• The provided reproduction is a minimal reproducible example of the bug.

[mandarini]

@stefan-girlich thank you for filing this issue. I think this is an issue with how the CLI generates the types. The Row type represents "what fields can be selected from this table", so computed fields are selectable, so they belong in Row. The CLI is the source of truth for generated types as it reads the database schema and should include all selectable fields. postgrest-js already expects computed fields to be in Row (our test fixtures include blurb_message in messages.Row). Since computed relationships are already handled by the CLI, scalar computed fields should follow the same pattern. I think that the CLI should detect functions with signature function_name(table_name) RETURNS <scalar_type> and add them to the corresponding table's Row type. In any case, I will bring this to the attention of the team!

[7ttp]

@mandarini Umm, the CLI invokes type generation,
but the actual schema detection and type generation logic lives in supabase/postgres-meta
(see internal/gen/types/types.go it runs the pg-meta container).
The fix for detecting scalar computed fields needs to be implemented in postgres-meta's type generation code,
not the CLI.
Could you pass this issue there? or should i create a new one 😅💚

[icopp]

@stefan-girlich thank you for filing this issue. I think this is an issue with how the CLI generates the types. The Row type represents "what fields can be selected from this table", so computed fields are selectable, so they belong in Row. The CLI is the source of truth for generated types as it reads the database schema and should include all selectable fields. postgrest-js already expects computed fields to be in Row (our test fixtures include blurb_message in messages.Row). Since computed relationships are already handled by the CLI, scalar computed fields should follow the same pattern. I think that the CLI should detect functions with signature function_name(table_name) RETURNS <scalar_type> and add them to the corresponding table's Row type. In any case, I will bring this to the attention of the team!

The problem with just adding them to Row is that if you select *, those functions aren't included in PostgREST results. The typing needs to be set up in a way that the function is only included in the output type if referenced by name.

[dan-hale]

@stefan-girlich thank you for filing this issue. I think this is an issue with how the CLI generates the types. The Row type represents "what fields can be selected from this table", so computed fields are selectable, so they belong in Row. The CLI is the source of truth for generated types as it reads the database schema and should include all selectable fields. postgrest-js already expects computed fields to be in Row (our test fixtures include blurb_message in messages.Row). Since computed relationships are already handled by the CLI, scalar computed fields should follow the same pattern. I think that the CLI should detect functions with signature function_name(table_name) RETURNS <scalar_type> and add them to the corresponding table's Row type. In any case, I will bring this to the attention of the team!

The problem with just adding them to Row is that if you select *, those functions aren't included in PostgREST results. The typing needs to be set up in a way that the function is only included in the output type if referenced by name.

This already exists for computed relationships, I'm sure that could be used as a reference for implementing this.