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
id | name |
|---|---|
| 1 | United Kingdom |
| 2 | United States |
Cities
id | name | country_id |
|---|---|---|
| 1 | London | 1 |
| 2 | Manchester | 1 |
| 3 | Los Angeles | 2 |
| 4 | New York | 2 |
The APIs will automatically detect relationships based on the foreign keys:
_10const { 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:
_10create table countries (_10 "id" serial primary key,_10 "name" text_10);_10_10create 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:
_15import { QueryResult, QueryData, QueryError } from '@supabase/supabase-js'_15_15const countriesWithCitiesQuery = supabase.from('countries').select(`_15 id,_15 name,_15 cities (_15 id,_15 name_15 )_15`)_15type CountriesWithCities = QueryData<typeof countriesWithCitiesQuery>_15_15const { data, error } = await countriesWithCitiesQuery_15if (error) throw error_15const 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):
_15create table users (_15 "id" serial primary key,_15 "name" text_15);_15_15create table teams (_15 "id" serial primary key,_15 "team_name" text_15);_15_15create 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:
_10const { 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_21create table users (_21 "id" serial primary key,_21 "name" text_21);_21_21-- Badge scans_21create table scans (_21 "id" serial primary key,_21 "user_id" int references users,_21 "badge_scan_time" timestamp_21);_21_21-- Work shifts_21create 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:
_15const { data, error } = await supabase.from('shifts').select(_15 `_15 *,_15 start_scan:scans!scan_id_start (_15 id,_15 user_id,_15 badge_scan_time_15 ),_15 end_scan:scans!scan_id_end (_15 id, _15 user_id,_15 badge_scan_time_15 )_15 `_15)