postgrest-js
postgrest-js copied to clipboard
Database relationships query types not working
Bug report
- [x] I confirm this is a bug with Supabase, not with my own application.
- [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.
Describe the bug
Given a schema types definition for a table like so:
account: {
Row: {
id: string;
name: string | null;
type: string | null;
};
...
},
my_resource: {
Row: {
id: string;
name: string | null;
big_account_id: string | null;
small_account_id: string | null;
};
...
Relationships: [
{
foreignKeyName: 'my_resource_big_account_id_fkey';
columns: ['big_account_id'];
referencedRelation: 'account';
referencedColumns: ['id'];
},
{
foreignKeyName: 'my_resource_small_account_id_fkey';
columns: ['small_account_id'];
referencedRelation: 'account';
referencedColumns: ['id'];
},
];
When querying data from my_resource
, one gets errors or an empty type:
const query = `id,key:id,
name,
state_location_code,
big_account_id(*),
small_account_id(*)`
// resulting type
type MyResource = {
id: string;
key: string;
name: string | null;
big_account_id: {}[];
small_account_id: {}[];
}
const query2 = `id,key:id,
name,
state_location_code,
big_account_id(id,name,type),
small_account_id(id,name,type)`
// result type
type MyResource = {
id: string;
key: string;
name: string | null;
big_account_id: SelectQueryError<"Referencing missing column `id`">[];
small_account_id: SelectQueryError<"Referencing missing column `id`">[];
}
This only happens when one tries to query data using relations in a query; the account
type works fine when querying it directly. It also works on other table types where the foreign key id is called account_id
instead of not matching the foreign table name + _id
.
To Reproduce
- Create any 2 tables, with 1:1 foreign key relations on one of them.
- Name the column something other than
foreign_table
+_id
- Generate the types using the supabase CLI
- Create a query that select fields belonging to the foreign table using
(*)
- The types will be empty or if specifying a field a QueryResult error will appear.
Expected behavior
The correct types are produced based on the relationships generated by the Supabase DB schema.
Screenshots
System information
- Version of supabase-js: 2.33.1
- Version of supabase-cli: 1.91.1
- Version of Node.js: 16.17.0
I think the issue is the column name, once the name does not match the foreign table name, the query breaks down for relations.
ok, it works if I use account(*)
, the query sees the foreign fields, but how can I specify I want to query both big and small accounts ?
Closing, no response for a year, no longer using postgrest, a new issue can be opened if required