Support self-referencing foreign key

Open
#4,752 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
50/100
Issue type
Feature
Clarity
Mostly clear
Activity status
Quiet
Tech stack
haskell, postgresql, sql
Domain
api, databases

Research direction

Start with the existing resource-embedding implementation, foreign-key relationship detection, and the !hint disambiguation path described in the issue. Verify the self-referencing patients example and confirm that the requested embedding works natively without a computed relationship function, including the stated column selection.

Written by the indexing model from the issue text.

Description

docs

Problem

Self-referencing foreign keys cannot be embedded using PostgREST's standard resource embedding. While regular FKs between different tables are auto-detected and embeddable via the !hint disambiguation syntax, self-referencing FKs require manually defining computed relationship functions.

For example, given a table with a self-referencing FK:

  create table patients (
    patient_id int primary key generated always as identity,
    first_name text not null,
    last_name text not null,
    referring_patient_id int references patients(patient_id)
  );

There is no way to query the referring patient via standard embedding. You must define a boilerplate function:

  create or replace function referred_by(patients)
  returns setof patients rows 1 as $$
    select * from patients where patient_id = $1.referring_patient_id
  $$ stable language sql;

This is the only documented approach for recursive relationships. It creates downstream issues — for example, Supabase's type generator now embeds these functions into the table's Row type, forcing workarounds in application code.

Solution

Support self-referencing FK embedding natively using the existing !hint disambiguation syntax:

GET /patients?select=*,referred_by:patients!referring_patient_id(patient_id,first_name,last_name)

PostgREST already detects the FK (referring_patient_id -> patient_id). The !fk_name hint provides enough information to resolve the self-join unambiguously, the same way it resolves ambiguity when multiple FKs point to different tables.

Dominant language
Haskell
Stars
27.7k
Forks
1.2k
Avg merge
1d 9h
Merged PRs (30d)
54

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from PostgREST/postgrest

All issues in PostgREST/postgrest

Similar issues

More Haskell issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.