Add support for `post.image(size: SMALL)`-style singular relation fields based on unique constraints

[dilame]

Feature description

It would be awesome if PostGraphile supported parameterized singular relation fields — basically, being able to fetch a single related row by a unique key directly on a parent type, without having to filter through a connection or write an extendSchema plugin by hand.

This is a really common schema pattern: you have a child table with a unique constraint that involves the parent foreign key, and you want to fetch exactly one related row using that constraint. PostGraphile already has everything it needs to figure this out from the database schema.

The idea is that PostGraphile could automatically add a singular field to the parent type with arguments matching the extra unique key columns. Under the hood it could batch these efficiently (e.g. WHERE (parent_id, key) IN (...)), so there’s no N+1 problem — just clean, expressive GraphQL.

Motivating example

This comes up all the time in real schemas.
For example, imagine you want to fetch a single image of a given size for each post:

create table post (
  id serial primary key,
  headline text,
  body text
);

create table post_image (
  post_id integer references post,
  size text check (size in ('small','big')) not null,
  url text,
  primary key (post_id, size)
);

Desired GraphQL query:

query {
  posts(first: 10) {
    nodes {
      id
      image(size: small) {
        url
      }
    }
  }
}

This is clean and exactly matches how the data is structured in the DB — (post_id, size) uniquely identifies one row.

Another common case is many-to-many or lookup by unique child key:

create table author (
  id serial primary key,
  name text
);

create table post_author (
  post_id   integer references post,
  author_id integer references author,
  primary key (post_id, author_id)
);

Desired GraphQL

post(id: 1) {
  author(id: 42) {
    name
  }
}

And one more example — user settings:

create table user_setting (
  user_id integer references app_user,
  key text not null,
  value text,
  unique (user_id, key)
);

Desired GraphQL

query {
  currentUser {
    setting(key: "dark_mode") {
      value
    }
  }
}

[benjie]

Indeed, this is a planned feature after V5 launches.

[benjie]

Under the hood it could batch these efficiently (e.g. WHERE (parent_id, key) IN (...)), so there’s no N+1 problem — just clean, expressive GraphQL.

Was this your LLM’s suggestion, or your own?

[dilame]

Was this your LLM’s suggestion, or your own?

It was my ChatGPT, i missed this part, sorry:)