Idea: parse select queries to give better types

[bnjmnt4n]

Feature request/Potential idea

Is your feature request related to a problem? Please describe.

When selecting columns to return from a request, if there's any complex select query with specific columns selected and resource embedding, you would need to manually specify the return types if you'd like good TypeScript checking. This can lead to a lot of duplication between the columns you're selecting, and the types you specify.

Example:

type WorkspaceWithTeam =
  definition["workspaces"] & {
    team: {
      user: Pick<definition["users"], "id" | "email">;
    };
  };

const { data, error } = await supabase
  .from<WorkspaceWithTeam>("workspaces")
  .select(`
    *,
    team:members(
      user:users(
        id,
        email
      )
    )
  `);

Describe the solution you'd like

I've been experimenting with TypeScript template literals, and managed to implement a parser for simple PostgREST select queries: https://github.com/bnjmnt4n/postgrest-query. The project is still incomplete, requires a bit of a rewrite and should only be used with TypeScript 4.5+ due to some complexities with template literals, but I wanted to see if there's any interest in integration with postgrest-js.

Here's a screenshot of the LSP type:

The types can parse the query to see which columns are being selected, as well as which resources are being embedded. Then, assuming definitions is a Record<TableName, Record<ColumnName, ColumnType>, it can construct the expected output object type.

After integration of something like #172, here's a potential implementation of a separate type export of a TypedSupabaseClient which would be had would be opt-in:

const supabase = createTypedClient<definitions>(URL, API_KEY);

const { data, error } = await supabase
  .from("workspaces")
  .select(`
    *,
    team:members(
      user:users(
        id,
        email
      )
    )
  `);

// data is properly typed.

Describe alternatives you've considered

I think this has the potential to be useful, but integrating into postgrest-js might also not be required, and I can probably maintain this externally.

[soedirgo]

This looks rad - would be great if this could be made to work with type generation: supabase/postgres-meta#103.

I think the hairy part of implementing this is on the less exercised nooks and crannies of the PostgREST syntax:

• https://postgrest.org/en/v8.0/api.html#computed-cols
• https://postgrest.org/en/v8.0/api.html#hint-disambiguation
• many-to-many to the same table(recursive m2m) PostgREST/postgrest#1907
• Support filtering top-level resource based on embedded resource filter PostgREST/postgrest#1949

I'd look into how PostgREST itself parses the select, but I'd estimate it to be more complex than the rest of postgrest-js (which isn't that complex tbh).

[steve-chavez]

I think the hairy part of implementing this is on the less exercised nooks and crannies of the PostgREST syntax

This is not completed, but here's an ABNF for PostgREST syntax: PostgREST/postgrest-docs#228 (comment)

Some parts are outdated, an up-to-date select syntax should be like

select = [alias] field [cast] / embed / "*" / select "," select
embed = [alias] identifier [hint] "(" select ")"
hint = "!" identifier

[soedirgo]

Nice! A grammar would def make this a lot easier.

[bnjmnt4n]

For the select syntax, I think the only major parts I'm missing are the JSON paths and casts. I think the tough part of making something like this robust is how to handle resource embedding: accurately identifying the kind of relationship and return values (eg. 1-to-1, 1-to-many), and also identifying the correct embedded resource since the target and hint could be a column name, table name or foreign key constraint. If supabase/postgres-meta#103 allows us to export data about this information, I think most of the cases should be able to be handled, and probably the only thing that would need to be tested thoroughly is the TypeScript side.

[bnjmnt4n]

Update: I've made a prototype of what I've discussed in a separate repository here: https://github.com/bnjmnt4n/supabase-client. Perhaps one day (long into the future) this might be upstreamed into a version of supabase-js/postgrest-js, but there is a lot of complexity in the TypeScript code with lots of generic utility types.

[didavid61202]

Update: I've made a prototype of what I've discussed in a separate repository here: https://github.com/bnjmnt4n/supabase-client. Perhaps one day (long into the future) this might be upstreamed into a version of supabase-js/postgrest-js, but there is a lot of complexity in the TypeScript code with lots of generic utility types.

Nice work, thank you @bnjmnt4n, I'll check out your repo and see if I can help.
you can also check out Nuxt3 repo if you have some spare time, might be helpful to see how they generate implement types for serverless functions and composables for some awesome typescript DX.

[soedirgo]

Implemented in #279.