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