Skip to main content
Version: 4.12.0

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;