Apply "single()" to foreign keys that only one match can be achieved. Automatically or Manually as option.

[Egnus]

Feature request

When requesting data to a foreign table that is related to the current one by an intermediate table with 2 primary keys, the result should be either an object or nothing, instead of an array.

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

Currently I have a small DB relationship to manage role permissions in a manual way for the users.
Random tables belong to a Project and those projects has several member and roles to manage their projects.
Example for the tables needed here:

Since projectMembers has 2 primary keys, the relation between a projects and a users is unique and so as well with the role between them.
However a query like this:

const { data, error } = await supabase
    .from("users")
    .select("name, project:projects(name, role:roles(name))")
    .eq("id", 1) // id of the user
    .eq("project.id", 2) // id of the project
    .single();

returns:

{
  "name": "John",
  "project": [ // it is a list, but only one could ever match
    { 
       "name": "ACME",
       "role": [ // it is a list, but only one could ever match
         { "name": "project_admin" }
       ] 
    }
  ]
}

A clear and concise description of what you want and what your use case is.

Describe the solution you'd like

If this feature cannot be added automatically for whatever reason, a manual approach should be available to tell that a given foreign table should return single.
something like:

await supabase.from('users').select("name, project:projects(name, role:roles(name))")
.eq("id", 1) // user fixed
.eq("project.id", 1) // project fixed
.single(
    true, // referring to current table
    [{foreignTable: "projects"},{foreignTable: "roles"}] // list of foreign tables that should be single. Could be also a single object for only one foreign table
);

With a response of:

{
  "name": "John",
  "project": { 
    "name": "ACME",
    "role": {
      "name": "project_admin" 
    }
  }
}

Describe alternatives you've considered

Currently I only use a simple JS post script like

data = {
  ...data,
  project: {
    ...data.project[0],
    role: data.project[0].role[0]
  }

But this is a big overhead for the many 2 Primary Key relationships I have between the tables.

I was considering creating a Recursive Function for all responses to always return 1 object when only 1 item is in an array, but this does not apply to all the cases and sometimes I do want to keep the array of a single item.

[steve-chavez]

Related to PostgREST/postgrest#1984

Discussions:

• Return .single() for an inner join? supabase#7610

[madeleineostoja]

Now this is patched in postgrest does supabase just need a version bump?

[soedirgo]

@madeleineostoja we'll upgrade the PostgREST version on the Supabase platform soon - no need to update postgrest-js or supabase-js.

[N00nDay]

Is this a thing yet? Running into this issue currently.

[steve-chavez]

@N00nDay Yes, new projects are coming with PostgREST v10.1.1, which contain this change.

Will close this now.

[gabrielsestrem]

If you had to restart your Supabase server and got this PostgREST v10.1.1 upgrade to your project, you'll have to fix your code that have queries with foreign keys to adapt to this breaking changes.

what I did was searching (ctrl+shift+f) in the entire solution for "[0]" and removing when applicable.

For example:

organisation.rules[0].reasons

now should be:

organisation.rules.reasons

I took 4 hours from midnight until 4am to fix this in 200+ places in my solution.

[N00nDay]

@N00nDay Yes, new projects are coming with PostgREST v10.1.1, which contain this change.

Will close this now.

This may be a silly question but how can I tell which version of PostgREST I am using? I am using app.supabase for hosting.

[madeleineostoja]

Seconding the potentially silly question — how do I restart my postgres instance to get this update on an existing project (again using hosted supabase)

[steve-chavez]

This may be a silly question but how can I tell which version of PostgREST I am using?

You can go to your "API docs", get the sample curl request with the API keys and do:

curl 'https://<your_project_ref>.supabase.co/rest/v1/' \
-H "apikey: SUPABASE_KEY" \
-H "Authorization: Bearer SUPABASE_KEY"

This will give you the OpenAPI which also shows the version:

{"swagger":"2.0","info":{"description":"This is a dynamic API generated by PostgREST","title":"PostgREST API",
"version":"9.0.1.20220717 (pre-release) (d2df289)"}

[steve-chavez]

If you had to restart your Supabase server and got this PostgREST v10.1.1 upgrade to your project

Seconding the potentially silly question — how do I restart my postgres instance to get this update on an existing project (again using hosted supabase)

@madeleineostoja Actually "restart" will not upgrade your postgrest but a "pause" + "restore" will

[edmondso006]

Any idea when this will make it into the CLI? I've using the CLI to run the development of my app & then using the hosted environment for both staging & production. I just updated to CLI version: 1.22.3 and am still getting version 9.0.1.20220717 (pre-release) of PostgREST API

[soedirgo]

@edmondso006 can you create an issue on https://github.com/supabase/cli? I think the version got reverted at some point

[JasonChiu-dev]

Actually "restart" will not upgrade your postgrest but a "pause" + "restore" will

"pause" + "restore" => This is work. Happy.

The Supabase Reference:
https://supabase.com/docs/guides/platform/migrating-and-upgrading-projects#:~:text=Upgrade%20your%20project,restoration%20is%20complete.

[edmondso006]

Just for anyone who is still running into this issue in the future:

• I had the correct PostgREST API version but was still getting an array instead of just an object for what I thought was a 1:1 relationship.
• The problem was that I had not set an unique key constraint for the FK col

[EryouHao]

Just for anyone who is still running into this issue in the future:

• I had the correct PostgREST API version but was still getting an array instead of just an object for what I thought was a 1:1 relationship.
• The problem was that I had not set an unique key constraint for the FK col

@edmondso006 where can config this?

[soedirgo]

@EryouHao this is under https://app.supabase.com/project/_/database/tables > columns > edit button.

[lananelson]

With this change do joins for one-to-many relationships always return array type or null in Typescript definitions? I am still seeing single item | array | null type.

[jmarks-joshua]

I've upgraded my postgrest version. My table is slightly more complicated in that there are multiple joins.
It's an application for working with schools so I've got a class_group table which links to an assessment table
Then there is a class_assessment_relationship table that contains the date the class took that assessment

I want to get the information about a particular class_group. I need to know what assessments they have been assigned and what date they took the assessment (if they have taken it)

My select therefore looks a bit like this

supabaseClient
		.from('class_group')
		.select(
			`id,
			assessments:assessment!class_assessment_relationship(id, name, meta_data:class_assessment_relationship(date_taken))`
		)
		.eq('id', classId)
		.eq('assessments.meta_data.class_group_id', classId)
		.single();

Is there a better/easier way to do this request? Currently it works perfectly except for the fact that postgrest doesn't identify that meta_data is always going to be a single item and therefore returns a list. Is there a way I could do this nicely with Views that would be simpler?

[steve-chavez]

@jmarks-joshua Besides views, you can always override a detected relationship with computed relationships. You have the chance to label the rel as to-one with ROWS 1 on the function.

Also, a new issue would be better for discussing this.

[tomelliot]

I have a reviews table, with a non-null foreign key relationship to a unique primary key in the orders table. If I do a query on the reviews table that joins to the orders table, should I expect to return a single item from the orders table?

Currently it's still returning the single item | array | null type. I was expecting it would just return the single item type.

In the reviews table the foreign key is not listed as unique (because multiple reviews can reference a single order), however there will only ever be one order with a given order_id as it's a unique primary key.

I've updated the PostgREST API to version 10.1.2 (f56bed2).

[steve-chavez]

@tomelliot Typescript types are discussed on #408