Resource Embedding

PostgREST allows including related resources in a single API call. This reduces the need for many API requests.

Foreign Key Joins

The server uses Foreign Keys to determine which database objects can be joined together. It supports joining tables, views and table-valued functions.

  • For tables, it generates a join condition using the foreign keys columns (respecting composite keys).

  • For views, it generates a join condition using the views’ base tables foreign key columns.

  • For table-valued functions, it generates a join condition based on the foreign key columns of the returned table type.

Important

Relationships

For example, consider a database of films and their awards:

../../_images/film.png

Many-to-one relationships

Since films has a foreign key to directors, this establishes a many-to-one relationship. This enables us to request all the films and the director for each film.

GET /films?select=title,directors(id,last_name) HTTP/1.1

[
  { "title": "Workers Leaving The Lumière Factory In Lyon",
    "directors": {
      "id": 2,
      "last_name": "Lumière"
    }
  },
  { "title": "The Dickson Experimental Sound Film",
    "directors": {
      "id": 1,
      "last_name": "Dickson"
    }
  },
  { "title": "The Haunted Castle",
    "directors": {
      "id": 3,
      "last_name": "Méliès"
    }
  }
]

Note that the embedded directors is returned as a JSON object because of the “to-one” end.

Since the table name is plural, we can be more accurate by making it singular with an alias.

GET /films?select=title,director:directors(id,last_name) HTTP/1.1

[
  { "title": "Workers Leaving The Lumière Factory In Lyon",
    "director": {
      "id": 2,
      "last_name": "Lumière"
    }
  },
  ".."
]

One-to-many relationships

The foreign key reference establishes the inverse one-to-many relationship. In this case, films returns as a JSON array because of the “to-many” end.

GET /directors?select=last_name,films(title) HTTP/1.1

[
  { "last_name": "Lumière",
    "films": [
      {"title": "Workers Leaving The Lumière Factory In Lyon"}
    ]
  },
  { "last_name": "Dickson",
    "films": [
      {"title": "The Dickson Experimental Sound Film"}
    ]
  },
  { "last_name": "Méliès",
    "films": [
      {"title": "The Haunted Castle"}
    ]
  }
]

Many-to-many relationships

The join table determines many-to-many relationships. It must contain foreign keys to other two tables and they must be part of its composite key. In the sample film database, roles is taken as a join table.

The join table is also detected if the composite key has additional columns.

create table roles(
  id int generated always as identity,
, film_id int references films(id)
, actor_id int references actors(id)
, character text,
, primary key(id, film_id, actor_id)
);

GET /actors?select=first_name,last_name,films(title) HTTP/1.1

[
  { "first_name": "Willem",
    "last_name": "Dafoe",
    "films": [
      {"title": "The Lighthouse"}
    ]
  },
  ".."
]

One-to-one relationships

One-to-one relationships are detected in two ways.

  • When the foreign key is a primary key as specified in the sample film database.

  • When the foreign key has a unique constraint.

    CREATE TABLE technical_specs(
  film_id INT REFERENCES films UNIQUE,
  runtime TIME,
  camera TEXT,
  sound TEXT
);

GET /films?select=title,technical_specs(camera) HTTP/1.1

[
  {
    "title": "Pulp Fiction",
    "technical_specs": {"camera": "Arriflex 35-III"}
  },
  ".."
]

Computed Relationships

You can manually define relationships by using functions. This is useful for database objects that can’t define foreign keys, like Foreign Data Wrappers.

Assuming there’s a foreign table premieres that we want to relate to films.

create foreign table premieres (
  id integer,
  location text,
  "date" date,
  film_id integer
) server import_csv options ( filename '/tmp/directors.csv', format 'csv');

create function film(premieres) returns setof films rows 1 as $$
  select * from films where id = $1.film_id
$$ stable language sql;

The above function defines a relationship between premieres (the parameter) and films (the return type). Since there’s a rows 1, this defines a many-to-one relationship. The name of the function film is arbitrary and can be used to do the embedding:

GET /premieres?select=location,film(name) HTTP/1.1

[
  {
    "location": "Cannes Film Festival",
    "film": {"name": "Pulp Fiction"}
  },
  ".."
]

Now let’s define the opposite one-to-many relationship.

create function premieres(films) returns setof premieres as $$
  select * from premieres where film_id = $1.id
$$ stable language sql;

In this case there’s an implicit ROWS 1000 defined by PostgreSQL(search “result_rows” on this PostgreSQL doc). We consider any value greater than 1 as “many” so this defines a one-to-many relationship.

GET /films?select=name,premieres(name) HTTP/1.1

[
  {
    "name": "Pulp Ficiton",
    "premieres": [{"location": "Cannes Festival"}]
  },
  ".."
]

Overriding Relationships

Computed relationships also allow you to override the ones that PostgREST auto-detects.

For example, to override the many-to-one relationship between films and directors.

create function directors(films) returns setof directors rows 1 as $$
  select * from directors where id = $1.director_id
$$ stable language sql;

Thanks to overloaded functions, you can use the same function name for different parameters. Thus define relationships from other tables/views to directors.

create function directors(film_schools) returns setof directors as $$
  select * from directors where film_school_id = $1.id
$$ stable language sql;

Computed relationships have good performance as their intended design enable function inlining.

Warning

  • Always use SETOF when creating computed relationships. Functions can return a table without using SETOF, but bear in mind that PostgreSQL will not inline them.

  • Make sure to correctly label the to-one part of the relationship. When using the ROWS 1 estimation, PostgREST will expect a single row to be returned. If that is not the case, it will unnest the embedding and return repeated values for the top level resource.

Foreign Key Joins on Multiple Foreign Key Relationships

When there are multiple foreign keys between tables, Foreign Key Joins need disambiguation to resolve which foreign key columns to use for the join. To do this, you can specify a foreign key by using the !<fk> syntax.

Multiple Many-To-One

For example, suppose you have the following orders and addresses tables:

../../_images/orders.png

Since the orders table has two foreign keys to the addresses table, a foreign key join is ambiguous and PostgREST will respond with an error:

GET /orders?select=*,addresses(*) HTTP/1.1

HTTP/1.1 300 Multiple Choices

{
  "code": "PGRST201",
  "details": [
    {
      "cardinality": "many-to-one",
      "embedding": "orders with addresses",
      "relationship": "billing using orders(billing_address_id) and addresses(id)"
    },
    {
      "cardinality": "many-to-one",
      "embedding": "orders with addresses",
      "relationship": "shipping using orders(shipping_address_id) and addresses(id)"
    }
  ],
  "hint": "Try changing 'addresses' to one of the following: 'addresses!billing', 'addresses!shipping'. Find the desired relationship in the 'details' key.",
  "message": "Could not embed because more than one relationship was found for 'orders' and 'addresses'"
}

To successfully join orders with addresses, we can follow the error hint which tells us to add the foreign key name as !billing or !shipping. Note that the foreign keys have been named explicitly in the SQL definition above. To make the result clearer we’ll also alias the tables:

GET /orders?select=name,billing_address:addresses!billing(name),shipping_address:addresses!shipping(name) HTTP/1.1

[
  {
    "name": "Personal Water Filter",
    "billing_address": {
      "name": "32 Glenlake Dr.Dearborn, MI 48124"
    },
    "shipping_address": {
      "name": "30 Glenlake Dr.Dearborn, MI 48124"
    }
  }
]

Multiple One-To-Many

Let’s take the tables from Multiple Many-To-One. To get the opposite one-to-many relationship, we can also specify the foreign key name:

GET /addresses?select=name,billing_orders:orders!billing(name),shipping_orders!shipping(name)&id=eq.1 HTTP/1.1

[
  {
    "name": "32 Glenlake Dr.Dearborn, MI 48124",
    "billing_orders": [
      { "name": "Personal Water Filter" },
      { "name": "Coffee Machine" }
    ],
    "shipping_orders": [
      { "name": "Coffee Machine" }
    ]
  }
]

Recursive Relationships

To disambiguate recursive relationships, PostgREST requires Computed Relationships.

Recursive One-To-One

../../_images/presidents.png

To get either side of the Recursive One-To-One relationship, create the functions:

create or replace function predecessor(presidents) returns setof presidents rows 1 as $$
  select * from presidents where id = $1.predecessor_id
$$ stable language sql;

create or replace function successor(presidents) returns setof presidents rows 1 as $$
  select * from presidents where predecessor_id = $1.id
$$ stable language sql;

Now, to query a president with their predecessor and successor:

GET /presidents?select=last_name,predecessor(last_name),successor(last_name)&id=eq.2 HTTP/1.1

[
  {
    "last_name": "Adams",
    "predecessor": {
      "last_name": "Washington"
    },
    "successor": {
      "last_name": "Jefferson"
    }
  }
]

Recursive One-To-Many

../../_images/employees.png

To get the One-To-Many embedding, that is, the supervisors with their supervisees, create a function like this one:

create or replace function supervisees(employees) returns setof employees as $$
  select * from employees where supervisor_id = $1.id
$$ stable language sql;

Now, the query would be:

GET /employees?select=last_name,supervisees(last_name)&id=eq.1 HTTP/1.1

[
  {
    "name": "Taylor",
    "supervisees": [
      { "name": "Johnson" },
      { "name": "Miller" }
    ]
  }
]

Recursive Many-To-One

Let’s take the same employees table from Recursive One-To-Many. To get the Many-To-One relationship, that is, the employees with their respective supervisor, you need to create a function like this one:

create or replace function supervisor(employees) returns setof employees rows 1 as $$
  select * from employees where id = $1.supervisor_id
$$ stable language sql;

Then, the query would be:

GET /employees?select=last_name,supervisor(last_name)&id=eq.3 HTTP/1.1

[
  {
    "last_name": "Miller",
    "supervisor": {
      "last_name": "Taylor"
    }
  }
]

Recursive Many-To-Many

../../_images/users.png

To get all the subscribers of a user as well as the ones they’re following, define these functions:

create or replace function subscribers(users) returns setof users as $$
  select u.*
  from users u,
       subscriptions s
  where s.subscriber_id = u.id and
        s.subscribed_id = $1.id
$$ stable language sql;

create or replace function following(users) returns setof users as $$
  select u.*
  from users u,
       subscriptions s
  where s.subscribed_id = u.id and
        s.subscriber_id = $1.id
$$ stable language sql;

Then, the request would be:

GET /users?select=username,subscribers(username),following(username)&id=eq.4 HTTP/1.1

[
  {
    "username": "the_top_artist",
    "subscribers": [
      { "username": "patrick109" },
      { "username": "alicia_smith" }
    ],
    "following": [
      { "username": "top_streamer" }
    ]
  }
]

Foreign Key Joins on Partitioned Tables

Foreign Key joins can also be done between partitioned tables and other tables.

For example, let’s create the box_office partitioned table that has the gross daily revenue of a film:

../../_images/boxoffice.png

Since it contains the films_id foreign key, it is possible to join box_office and films:

GET /box_office?select=bo_date,gross_revenue,films(title)&gross_revenue=gte.1000000 HTTP/1.1

Note

Foreign Key Joins on Views

PostgREST will infer the foreign keys of a view using its base tables. Base tables are the ones referenced in the FROM and JOIN clauses of the view definition. The foreign keys’ columns must be present in the top SELECT clause of the view for this to work.

For instance, the following view has nominations, films and competitions as base tables:

CREATE VIEW nominations_view AS
  SELECT
     films.title as film_title
   , competitions.name as competition_name
   , nominations.rank
   , nominations.film_id as nominations_film_id
   , films.id as film_id
  FROM nominations
  JOIN films ON films.id = nominations.film_id
  JOIN competitions ON competitions.id = nominations.competition_id;

Since this view contains nominations.film_id, which has a foreign key relationship to films, then we can join the films table. Similarly, because the view contains films.id, then we can also join the roles and the actors tables (the last one in a many-to-many relationship):

GET /nominations_view?select=film_title,films(language),roles(character),actors(last_name,first_name)&rank=eq.5 HTTP/1.1

It’s also possible to foreign key join Materialized Views.

Important

  • It’s not guaranteed that foreign key joins will work on all kinds of views. In particular, foreign key joins won’t work on views that contain UNIONs.

    • Why? PostgREST detects base table foreign keys in the view by querying and parsing pg_rewrite. This may fail depending on the complexity of the view.

    • As a workaround, you can use Computed Relationships to define manual relationships for views.

  • If view definitions change you must refresh PostgREST’s schema cache for this to work properly. See the section Schema Cache Reloading.

Foreign Key Joins on Chains of Views

Views can also depend on other views, which in turn depend on the actual base table. For PostgREST to pick up those chains recursively to any depth, all the views must be in the search path, so either in the exposed schema (db-schemas) or in one of the schemas set in db-extra-search-path. This does not apply to the base table, which could be in a private schema as well. See Schema Isolation for more details.

Foreign Key Joins on Table-Valued Functions

If you have a Stored Procedure that returns a table type, you can do a Foreign Key join on the result.

Here’s a sample function (notice the RETURNS SETOF films).

CREATE FUNCTION getallfilms() RETURNS SETOF films AS $$
  SELECT * FROM films;
$$ LANGUAGE SQL STABLE;

A request with directors embedded:

GET /rpc/getallfilms?select=title,directors(id,last_name)&title=like.*Workers* HTTP/1.1

[
  { "title": "Workers Leaving The Lumière Factory In Lyon",
    "directors": {
      "id": 2,
      "last_name": "Lumière"
    }
  }
]

Foreign Key Joins on Writes

You can join related database objects after doing Insert, Update or Delete.

Say you want to insert a film and then get some of its attributes plus join its director.

POST /films?select=title,year,director:directors(first_name,last_name) HTTP/1.1
Prefer: return=representation

{
 "id": 100,
 "director_id": 40,
 "title": "127 hours",
 "year": 2010,
 "rating": 7.6,
 "language": "english"
}

Response:

{
 "title": "127 hours",
 "year": 2010,
 "director": {
   "first_name": "Danny",
   "last_name": "Boyle"
 }
}

Nested Embedding

If you want to embed through join tables but need more control on the intermediate resources, you can do nested embedding. For instance, you can request the Actors, their Roles and the Films for those Roles:

GET /actors?select=roles(character,films(title,year)) HTTP/1.1

Embedded Filters

Embedded resources can be shaped similarly to their top-level counterparts. To do so, prefix the query parameters with the name of the embedded resource. For instance, to order the actors in each film:

GET /films?select=*,actors(*)&actors.order=last_name,first_name HTTP/1.1

This sorts the list of actors in each film but does not change the order of the films themselves. To filter the roles returned with each film:

GET /films?select=*,roles(*)&roles.character=in.(Chico,Harpo,Groucho) HTTP/1.1

Once again, this restricts the roles included to certain characters but does not filter the films in any way. Films without any of those characters would be included along with empty character lists.

An or filter can be used for a similar operation:

GET /films?select=*,roles(*)&roles.or=(character.eq.Gummo,character.eq.Zeppo) HTTP/1.1

Limit and offset operations are possible:

GET /films?select=*,actors(*)&actors.limit=10&actors.offset=2 HTTP/1.1

Embedded resources can be aliased and filters can be applied on these aliases:

GET /films?select=*,90_comps:competitions(name),91_comps:competitions(name)&90_comps.year=eq.1990&91_comps.year=eq.1991 HTTP/1.1

Filters can also be applied on nested embedded resources:

GET /films?select=*,roles(*,actors(*))&roles.actors.order=last_name&roles.actors.first_name=like.*Tom* HTTP/1.1

The result will show the nested actors named Tom and order them by last name. Aliases can also be used instead of the resource names to filter the nested tables.

Top-level Filtering

By default, Embedded Filters don’t change the top-level resource(films) rows at all:

GET /films?select=title,actors(first_name,last_name)&actors.first_name=eq.Jehanne HTTP/1.1

[
  {
    "title": "Workers Leaving The Lumière Factory In Lyon",
    "actors": []
  },
  {
    "title": "The Dickson Experimental Sound Film",
    "actors": []
  },
  {
    "title": "The Haunted Castle",
    "actors": [
      {
        "first_name": "Jehanne",
        "last_name": "d'Alcy"
      }
    ]
  }
]

In order to filter the top level rows you need to add !inner to the embedded resource. For instance, to get only the films that have an actor named Jehanne:

GET /films?select=title,actors!inner(first_name,last_name)&actors.first_name=eq.Jehanne HTTP/1.1

[
  {
    "title": "The Haunted Castle",
    "actors": [
      {
        "first_name": "Jehanne",
        "last_name": "d'Alcy"
      }
    ]
  }
]

Null filtering on Embedded Resources

Null filtering on the embedded resources can behave the same as !inner. While providing more flexibility.

For example, doing actors=not.is.null returns the same result as actors!inner(*):

GET /films?select=title,actors(*)&actors=not.is.null HTTP/1.1

The is.null filter can be used in embedded resources to perform an anti-join. To get all the films that do not have any nominations:

GET /films?select=title,nominations()&nominations=is.null HTTP/1.1

Both is.null and not.is.null can be included inside the or operator. For instance, to get the films that have no actors or directors registered yet:

GET /films?select=title,actors(*),directors(*)&or=(actors.is.null,directors.is.null) HTTP/1.1

Empty Embed

You can leave an embedded resource empty, this helps with filtering in some cases.

To filter the films by actors but not include them:

GET /films?select=title,actors()&actors.first_name=eq.Jehanne&actors=not.is.null HTTP/1.1

[
  {
    "title": "The Haunted Castle",
  }
]

Top-level Ordering

On Many-to-One and One-to-One relationships, you can use a column of the “to-one” end to sort the top-level.

For example, to arrange the films in descending order using the director’s last name.

GET /films?select=title,directors(last_name)&order=directors(last_name).desc HTTP/1.1

Spread embedded resource

On many-to-one and one-to-one relationships, you can “spread” the embedded resource. That is, remove the surrounding JSON object for the embedded resource columns.

GET /films?select=title,...directors(director_last_name:last_name)&title=like.*Workers* HTTP/1.1

[
  {
    "title": "Workers Leaving The Lumière Factory In Lyon",
    "director_last_name": "Lumière"
  }
]

Note that there is no "directors" object. Also the embed columns can be aliased normally.

You can use this to get the columns of a join table in a many-to-many relationship. For instance, to get films and its actors, but including the character column from the roles table:

GET /films?select=title,actors:roles(character,...actors(first_name,last_name))&title=like.*Lighthouse* HTTP/1.1

[
  {
    "title": "The Lighthouse",
    "actors": [
       {
         "character": "Thomas Wake",
         "first_name": "Willem",
         "last_name": "Dafoe"
       }
    ]
  }
]

Note

The spread operator ... is borrowed from the Javascript spread syntax.