Relations

We automatically discover relations between database tables by inspecting their foreign keys (and indexes if --no-ignore-indexes or ignoreIndexes: false is set), and use these to build relations into the generated GraphQL schema.

An example of a foreign key constraint when defining a table would be the REFERENCES keyword below:

CREATE TABLE app_public.users (
  -- ...
  organization_id int NOT NULL
    REFERENCES app_public.organizations ON DELETE CASCADE,
  -- ...
);
CREATE INDEX ON app_public.users (organization_id);

Alternatively a foreign key constraint can be added after table creation:

ALTER TABLE users
  ADD CONSTRAINT users_organization_id_fkey
  FOREIGN KEY (organization_id)
  REFERENCES organizations
  ON DELETE CASCADE;
CREATE INDEX ON users (organization_id);

You can read more about defining foreign key constraints, including constraints that utilise multiple columns, in the PostgreSQL documentation.

PostGraphile detects and exposes one-to-one, one-to-many and many-to-one relations automatically. Many-to-many relationships can be handled with the many-to-many relations plugin.

By default, relations are exposed as GraphQL fields using a camelCase combination of the target type and the source fields (inflectors: singleRelationByKeys, singleRelationByKeysBackwards, and manyRelationByKeys). Unique constraints expose a GraphQL table type directly, non-unique constraints expose a connection. The GraphQL connections that these relations expose support pagination, filtering, and ordering.

Examples

Example database schema for one-to-many relation

create schema a;
create schema c;

create table c.person (
  id serial primary key,
  name varchar not null,
  about text,
  email varchar not null unique,
  created_at timestamp default current_timestamp
);

create table a.post (
  id serial primary key,
  headline text not null,
  body text,
  -- `references` 👇  sets up the foreign key relation
  author_id int4 references c.person(id)
);
create index on a.post (author_id);

Example query against the above schema

{
  allPosts {
    nodes {
      headline
      body

      # this relation is automatically exposed
      personByAuthorId {
        id
        name
        about
      }
    }
  }
}

Many-to-many relations

Many-to-many relations can be handled with the many-to-many relations plugin or by using a computed column that returns setof:

create table post (
  id serial primary key,
  headline text,
  body text
);
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)
);

create function "post_authorsByPostId"(p post)
returns setof author as $$
  select author.*
  from author
  inner join post_author
  on (post_author.author_id = author.id)
  where post_author.post_id = p.id;
$$ language sql stable;