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 orchestral_sections and instruments:

Orchestral sections

idname
1strings
2woodwinds

Instruments

idnamesection_id
1violin1
2viola1
3flute2
4oboe2

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

1
2
3
4
5
const { data, error } = await supabase.from('orchestral_sections').select(`  id,  name,  instruments ( id, name )`)

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 orchestral sections and instruments:

1
2
3
4
5
6
7
8
9
10
create table orchestral_sections (  "id" serial primary key,  "name" text);create table instruments (  "id" serial primary key,  "name" text,  "section_id" int references "orchestral_sections");

We can get the nested SectionsWithInstruments type like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import { QueryResult, QueryData, QueryError } from '@supabase/supabase-js'const sectionsWithInstrumentsQuery = supabase.from('orchestral_sections').select(`  id,  name,  instruments (    id,    name  )`)type SectionsWithInstruments = QueryData<typeof sectionsWithInstrumentsQuery>const { data, error } = await sectionsWithInstrumentsQueryif (error) throw errorconst sectionsWithInstruments: SectionsWithInstruments = 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):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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:

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

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Employeescreate table users (  "id" serial primary key,  "name" text);-- Badge scanscreate table scans (  "id" serial primary key,  "user_id" int references users,  "badge_scan_time" timestamp);-- Work shiftscreate table shifts (  "id" serial primary key,  "user_id" int references users,  "scan_id_start" int references scans, -- clocking in  "scan_id_end" int references scans, -- clocking out  "attendance_status" text);

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
const { data, error } = await supabase.from('shifts').select(  `    *,    start_scan:scans!scan_id_start (      id,      user_id,      badge_scan_time    ),   end_scan:scans!scan_id_end (     id,     user_id,     badge_scan_time    )  `)