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 belong 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 (
  "user_id" int references users,
  "team_id" int references teams,
  primary key (user_id, team_id)
);

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 )
`)