postgrest-js: Inconsistent Typing for Joined Field in Supabase
Bug report
- I confirm this is a bug with Supabase, not with my own application.
- I confirm I have searched the Docs, GitHub Discussions, and Discord.
Describe the bug
I have added a link to a REPL, HERE along with a screenshot.
The example code expects the created_by field in the query result to be a single object containing employee details (id and full_name). However, the type definition ({ id: any; full_name: any; }[]) suggests it’s an array of employees. This mismatch causes a typing error when working with the data.
Why it happens:
The !report_created_by_fkey syntax likely defines a relationship between tables, but the return type might not be explicitly set to a single object.
To Reproduce
Steps to reproduce the behavior, please provide code snippets or a repository:
- Go to The REPL link
- Enter your Project URL and ANON key into the createClient instance.
- Create the table schemas provided in
$lib/db - When hovering over
datathe return type def is an array of objects.
Expected behavior
The result type def should be a single object for the field created_by.
Screenshots
If applicable, add screenshots to help explain your problem.
System information
- OS: macOS
- Version of supabase-js: 2.42.7
- Version of Node.js: 18.20.3
Additional context
Add any other context about the problem here.
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Reactions: 2
- Comments: 20 (7 by maintainers)
@soedirgo - This is the same issue opened MANY times and still needs to be fixed.
We have 8 different repos all for the same problem. JOINS do not have the correct typing. This needs to be moved up the ladder as a PRIORITY.
J
@soedirgo - Can we please get this looked at? Every single join is effected, there are hundreds of complaints. Please raise this issue to the top of the list!
Thanks,
J
@soedirgo - I have created an example REPO with all the problems I can think of right now.
See repo.
J
Hi @avallete! Sorry for the late reply and thanks for the work on the RC. I’ve tested it in my project for its use-cases and it works like a charm and infers the correct types!
@osilviotti - RPC functions are a different problem and should be in a new post. I don’t want the Supabase staff to get off topic from the issue at hand.
I don’t know your use case, but most MOST join cases do not work as expected. You can scroll through the issues listed above.
J
I’ve also encountered this recently.
In case it’s helpful, querying the table directly gave the correct type for the relation (e.g.
{ id: string; } | null), but calling an RPC function that returned aSETOFthe same table gave the incorrect type for the same relation (e.g.{ id: string }[]).The field types in the Supabase generated
Databaseare identical between the tableRowand the functionReturns, but thePostgrestFilterBuilderisn’t passed theRelationshipsin the case of the RPC function so I suspect that might be why.EDIT: For now, I’m using a type to override what’s being returned from the library. It might only be useful in my specific instance, but thought I’d drop it here in case it helps anyone else.
@jdgamble555 Happy to tell you that we just released v2.47.8 that include the typing inference rework which should fix the inconsistents typing on joined fields.
Hey everyone !
We’ve reworked the result inference in
postgrest-jsto address the typing issues mentioned above. The changes are available in a canary release, and we’re actively seeking your feedback.To test it out, update your
supabase-jsto version2.46.0-rc.1(which includespostgrest-js1.17.0-rc.1):Please let us know if you encounter any issues, especially with retro-compatibility, so we can finalize it for the next release.
Hey there,
I’m currently working on this issue in PR #554. I’ve added more tests to ensure better consistency between runtime behavior and type inference results. Hopefully, this will cover more cases, although I’m not certain it will handle all of them—there are quite a few edge cases to consider.
Additionally, I wonder if it might be worth adding more “hinting” for type inference in the database introspection results. For example, in cases where the result of a foreign key (FK) is nullable when it shouldn’t be, it might be easier to resolve these cases by providing this information directly inside the “relationship” object. This approach could potentially avoid a convoluted type search across relations and table columns to check if the value of the holding column can be null or not.
So far, I still have six divergences between types and runtime values in the test scenarios I’ve set up. Most of these involve relation results being possibly null when they shouldn’t be, and some are due to “relation aliasing.” Depending on whether the user uses a direct foreign key name, a column name, or the destination table (in cases where there is a single relation between
table1andtable2), the parser fails to determine the correct result type.@soedirgo, I believe most of these issues could be addressed by slightly augmenting the data we pull during introspection (e.g., handling the nullable issue). Would this be something worth considering? It would probably require users to rebuild their types and could result in a breaking change between pre- and post-version for the query parser. What do you think?
It’s not ideal, but for the time being, I’m fixing it like so:
Types are inferred correctly if you use the foreign key constraint’s name, which should help solve this.