Querying Joins and Nested tables

The Serverless APIs automatically detect relationships between Postgres tables. Since Postgres is a relational database, this is a very common scenario.

One-to-many joins#

Let's use an example database that stores countries and cities:

The APIs will automatically detect relationships based on the foreign keys:

const { data, error } = await supabase.from('countries').select(`
  id, 
  name, 
  cities ( id, name )
`)

Many-to-many joins#

The Serverless APIs will detect many-to-many joins. For example, if you have a database which stored teams of users (where each user could below to many teams):

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

create table
  teams ("id" serial primary key, "team_name" text);

create table
  members (
    "id" serial primary key,
    "user_id" int references users,
    "team_id" int references teams
  );

In these cases you don't need to explicitly define the joining table (members). If we wanted to fetch all the teams and the members in each team:

const { data, error } = await supabase.from('teams').select(`
  id, 
  team_name, 
  users ( id, name )
`)