Database

Querying Joins and Nested tables

The data 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:

Countries

idname
1United Kingdom
2United States

Cities

idnamecountry_id
1London1
2Manchester1
3Los Angeles2
4New York2

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


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

TypeScript types for joins

supabase-js always returns a data object (for success), and an error object (for unsuccessful requests).

These helper types provide the result types from any query, including nested types for database joins.

Given the following schema with a relation between cities and countries:


_10
create table countries (
_10
"id" serial primary key,
_10
"name" text
_10
);
_10
_10
create table cities (
_10
"id" serial primary key,
_10
"name" text,
_10
"country_id" int references "countries"
_10
);

We can get the nested CountriesWithCities type like this:


_15
import { QueryResult, QueryData, QueryError } from '@supabase/supabase-js'
_15
_15
const countriesWithCitiesQuery = supabase.from('countries').select(`
_15
id,
_15
name,
_15
cities (
_15
id,
_15
name
_15
)
_15
`)
_15
type CountriesWithCities = QueryData<typeof countriesWithCitiesQuery>
_15
_15
const { data, error } = await countriesWithCitiesQuery
_15
if (error) throw error
_15
const countriesWithCities: CountriesWithCities = data

Many-to-many joins

The data 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):


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

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:


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

Specifying the ON clause for joins with multiple foreign keys

For example, if you have a project that tracks when employees check in and out of work shifts:


_21
-- Employees
_21
create table users (
_21
"id" serial primary key,
_21
"name" text
_21
);
_21
_21
-- Badge scans
_21
create table scans (
_21
"id" serial primary key,
_21
"user_id" int references users,
_21
"badge_scan_time" timestamp
_21
);
_21
_21
-- Work shifts
_21
create table shifts (
_21
"id" serial primary key,
_21
"user_id" int references users,
_21
"scan_id_start" int references scans, -- clocking in
_21
"scan_id_end" int references scans, -- clocking out
_21
"attendance_status" text
_21
);

In this case, you need to explicitly define the join because the joining column on shifts is ambiguous as they are both referencing the scans table.

To fetch all the shifts with scan_id_start and scan_id_end related to a specific scan, use the following syntax:


_15
const { data, error } = await supabase.from('shifts').select(
_15
`
_15
*,
_15
scans:scan_id_start (
_15
id,
_15
user_id,
_15
badge_scan_time
_15
),
_15
scans:scan_id_end (
_15
id,
_15
user_id,
_15
badge_scan_time
_15
)
_15
`
_15
)