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

Use an example database that stores orchestral_sections and instruments:

Orchestral sections

`id`	`name`
1	strings
2	woodwinds

Instruments

`id`	`name`	`section_id`
1	violin	1
2	viola	1
3	flute	2
4	oboe	2

1
create table orchestral_sections (
2
  "id" serial primary key,
3
  "name" text
4
);
5
6
insert into orchestral_sections
7
  (id, name)
8
values
9
  (1, 'strings'),
10
  (2, 'woodwinds');
11
12
create table instruments (
13
  "id" serial primary key,
14
  "name" text,
15
  "section_id" int references "orchestral_sections"
16
);
17
18
insert into instruments
19
  (name, section_id)
20
values
21
  ('violin', 1),
22
  ('viola', 1),
23
  ('flute', 2),
24
  ('oboe', 2);

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

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

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
create table orchestral_sections (
2
  "id" serial primary key,
3
  "name" text
4
);
5
6
create table instruments (
7
  "id" serial primary key,
8
  "name" text,
9
  "section_id" int references "orchestral_sections"
10
);

We can get the nested SectionsWithInstruments type like this:

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

1
final data = await supabase.from('orchestral_sections').select('id, name, instruments(id, name)');

1
struct OrchestralSection: Codable {
2
  let id: Int
3
  let name: String
4
  let instruments: [Instrument]
5
6
  struct Instrument: Codable {
7
    let id: Int
8
    let name: String
9
  }
10
}
11
12
let orchestralSections: [OrchestralSection] = try await supabase
13
  .from("orchestral_sections")
14
  .select("id, name, instruments(id, name)")
15
  .execute()
16
  .value

1
val data = supabase.from("orchestral_sections").select(Columns.raw("id, name, instruments(id, name)"))

1
data = supabase.from_('orchestral_sections').select('id, name, instruments(id, name)').execute()

1
var result = await supabase
2
  .From<OrchestralSection>()
3
  .Select("id, name, instruments(id, name)")
4
  .Get();

1
const  = `
2
  query {
3
    orchestral_sectionsCollection {
4
      edges {
5
        node {
6
          id
7
          name
8
          instruments {
9
            id,
10
            name
11
          }
12
        }
13
      }
14
    }
15
  }
16
`

1
GET https://[REF].supabase.co/rest/v1/orchestral_sections?select=id,name,instruments(id,name)

Join types and join modifiers

By default, embedded relations use left join semantics from the parent table:

Parent rows are returned even if no related rows match.
The embedded relation is [] for one-to-many joins and null for many-to-one joins when nothing matches.

To filter out parent rows that do not match the related table, use !inner on the embedded relation.

What `:` and `!` mean in join syntax

Syntax	Meaning	Example
`alias:relation(columns)`	Rename the embedded relation in the response.	`start_scan:scans(id, badge_scan_time)`
`relation!inner(columns)`	Use `inner join` behavior for that embedded relation.	`instruments!inner(id, name)`
`relation!foreign_key(columns)`	Choose which foreign key relationship to use when multiple foreign keys match the join.	`scans!scan_id_start(id)`

Example data for join types

Orchestral sections

`id`	`name`
1	strings
2	woodwinds
3	percussion

Instruments

`id`	`name`	`section_id`
1	violin	1
2	viola	1
3	flute	2
4	oboe	2

1
create table orchestral_sections (
2
  "id" serial primary key,
3
  "name" text
4
);
5
6
insert into orchestral_sections
7
  (id, name)
8
values
9
  (1, 'strings'),
10
  (2, 'woodwinds'),
11
  (3, 'percussion');
12
13
create table instruments (
14
  "id" serial primary key,
15
  "name" text,
16
  "section_id" int references orchestral_sections
17
);
18
19
insert into instruments
20
  (id, name, section_id)
21
values
22
  (1, 'violin', 1),
23
  (2, 'viola', 1),
24
  (3, 'flute', 2),
25
  (4, 'oboe', 2);

Left join (default)

This query filters on a joined field (instruments.name) but still returns all parent rows:

1
const { data, error } = await supabase
2
  .from('orchestral_sections')
3
  .select(
4
    `
5
    id,
6
    name,
7
    instruments ( id, name )
8
  `
9
  )
10
  .eq('instruments.name', 'flute')

1
final data = await supabase
2
  .from('orchestral_sections')
3
  .select('''
4
    id,
5
    name,
6
    instruments ( id, name )
7
  ''')
8
  .eq('instruments.name', 'flute');

1
try await supabase
2
  .from("orchestral_sections")
3
  .select(
4
    """
5
      id,
6
      name,
7
      instruments ( id, name )
8
    """
9
  )
10
  .eq("instruments.name", value: "flute")
11
  .execute()

1
val columns = Columns.raw("""
2
    id,
3
    name,
4
    instruments ( id, name )
5
""".trimIndent())
6
7
val data = supabase.from("orchestral_sections").select(
8
  columns = columns
9
) {
10
  filter {
11
    eq("instruments.name", "flute")
12
  }
13
}

1
data = (
2
  supabase.from_('orchestral_sections')
3
  .select('id, name, instruments(id, name)')
4
  .eq('instruments.name', 'flute')
5
  .execute()
6
)

1
GET https://[REF].supabase.co/rest/v1/orchestral_sections?select=id,name,instruments(id,name)&instruments.name=eq.flute

Result

1
[
2
  {
3
    "id": 1,
4
    "name": "strings",
5
    "instruments": []
6
  },
7
  {
8
    "id": 2,
9
    "name": "woodwinds",
10
    "instruments": [{ "id": 3, "name": "flute" }]
11
  },
12
  {
13
    "id": 3,
14
    "name": "percussion",
15
    "instruments": []
16
  }
17
]

Inner join (`!inner`)

Adding !inner filters out parent rows that don't match the joined filter:

1
const { data, error } = await supabase
2
  .from('orchestral_sections')
3
  .select(
4
    `
5
    id,
6
    name,
7
    instruments!inner ( id, name )
8
  `
9
  )
10
  .eq('instruments.name', 'flute')

1
final data = await supabase
2
  .from('orchestral_sections')
3
  .select('''
4
    id,
5
    name,
6
    instruments!inner ( id, name )
7
  ''')
8
  .eq('instruments.name', 'flute');

1
try await supabase
2
  .from("orchestral_sections")
3
  .select(
4
    """
5
      id,
6
      name,
7
      instruments!inner ( id, name )
8
    """
9
  )
10
  .eq("instruments.name", value: "flute")
11
  .execute()

1
val columns = Columns.raw("""
2
    id,
3
    name,
4
    instruments!inner ( id, name )
5
""".trimIndent())
6
7
val data = supabase.from("orchestral_sections").select(
8
  columns = columns
9
) {
10
  filter {
11
    eq("instruments.name", "flute")
12
  }
13
}

1
data = (
2
  supabase.from_('orchestral_sections')
3
  .select('id, name, instruments!inner(id, name)')
4
  .eq('instruments.name', 'flute')
5
  .execute()
6
)

1
GET https://[REF].supabase.co/rest/v1/orchestral_sections?select=id,name,instruments!inner(id,name)&instruments.name=eq.flute

Result

1
[
2
  {
3
    "id": 2,
4
    "name": "woodwinds",
5
    "instruments": [{ "id": 3, "name": "flute" }]
6
  }
7
]

Filtering using joined fields

Use joined_table.column in filters (for example eq, neq, and in):

1
const { data, error } = await supabase
2
  .from('instruments')
3
  .select(
4
    `
5
    id,
6
    name,
7
    orchestral_sections!inner ( id, name )
8
  `
9
  )
10
  .eq('orchestral_sections.name', 'woodwinds')

1
final data = await supabase
2
  .from('instruments')
3
  .select('''
4
    id,
5
    name,
6
    orchestral_sections!inner ( id, name )
7
  ''')
8
  .eq('orchestral_sections.name', 'woodwinds');

1
try await supabase
2
  .from("instruments")
3
  .select(
4
    """
5
      id,
6
      name,
7
      orchestral_sections!inner ( id, name )
8
    """
9
  )
10
  .eq("orchestral_sections.name", value: "woodwinds")
11
  .execute()

1
val columns = Columns.raw("""
2
    id,
3
    name,
4
    orchestral_sections!inner ( id, name )
5
""".trimIndent())
6
7
val data = supabase.from("instruments").select(
8
  columns = columns
9
) {
10
  filter {
11
    eq("orchestral_sections.name", "woodwinds")
12
  }
13
}

1
data = (
2
  supabase.from_('instruments')
3
  .select('id, name, orchestral_sections!inner(id, name)')
4
  .eq('orchestral_sections.name', 'woodwinds')
5
  .execute()
6
)

1
GET https://[REF].supabase.co/rest/v1/instruments?select=id,name,orchestral_sections!inner(id,name)&orchestral_sections.name=eq.woodwinds

Result

1
[
2
  {
3
    "id": 3,
4
    "name": "flute",
5
    "orchestral_sections": {
6
      "id": 2,
7
      "name": "woodwinds"
8
    }
9
  },
10
  {
11
    "id": 4,
12
    "name": "oboe",
13
    "orchestral_sections": {
14
      "id": 2,
15
      "name": "woodwinds"
16
    }
17
  }
18
]

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
create table users (
2
  "id" serial primary key,
3
  "name" text
4
);
5
6
create table teams (
7
  "id" serial primary key,
8
  "team_name" text
9
);
10
11
create table members (
12
  "user_id" int references users,
13
  "team_id" int references teams,
14
  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:

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

1
final data = await supabase.from('teams').select('id, team_name, users(id, name)');

1
struct Team: Codable {
2
  let id: Int
3
  let name: String
4
  let users: [User]
5
6
  struct User: Codable {
7
    let id: Int
8
    let name: String
9
  }
10
11
  enum CodingKeys: String, CodingKey {
12
    case id, users
13
    case name = "team_name"
14
  }
15
}
16
let teams [Team] = try await supabase
17
  .from("teams")
18
  .select(
19
    """
20
      id,
21
      team_name,
22
      users ( id, name )
23
    """
24
  )
25
  .execute()
26
  .value

1
val data = supabase.from("teams").select(Columns.raw("id, team_name, users(id, name)"));

1
data = supabase.from_('teams').select('id, team_name, users(id, name)').execute()

1
var result = await supabase
2
  .From<Team>()
3
  .Select("id, team_name, users(id, name)")
4
  .Get();

1
const  = `
2
  query {
3
    teamsCollection {
4
      edges {
5
        node {
6
          id
7
          team_name
8
          users {
9
            id,
10
            name
11
          }
12
        }
13
      }
14
    }
15
  }
16
`

1
GET https://[REF].supabase.co/rest/v1/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
-- Employees
2
create table users (
3
  "id" serial primary key,
4
  "name" text
5
);
6
7
-- Badge scans
8
create table scans (
9
  "id" serial primary key,
10
  "user_id" int references users,
11
  "badge_scan_time" timestamp
12
);
13
14
-- Work shifts
15
create table shifts (
16
  "id" serial primary key,
17
  "user_id" int references users,
18
  "scan_id_start" int references scans, -- clocking in
19
  "scan_id_end" int references scans, -- clocking out
20
  "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:

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

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

1
struct Shift: Codable {
2
  let id: Int
3
  let userId: Int
4
  let attendanceStatus: String?
5
6
  let startScan: Scan
7
  let endScan: Scan
8
9
  struct Scan: Codable {
10
    let id: Int
11
    let userId: Int
12
    let badgeScanTime: TimeInterval
13
14
    enum CodingKeys: String, CodingKey {
15
      case id
16
      case userId = "user_id"
17
      case badgeScanTime = "badge_scan_time"
18
    }
19
  }
20
21
  enum CodingKeys: String, CodingKey {
22
    case id
23
    case userId = "user_id"
24
    case attendanceStatus = "attendance_status"
25
    case startScan = "start_scan"
26
    case endScan = "end_scan"
27
  }
28
}
29
30
let shifts: [Shift] = try await supabase
31
  .from("shifts")
32
  .select(
33
    """
34
      *,
35
      start_scan:scans!scan_id_start (
36
        id,
37
        user_id,
38
        badge_scan_time
39
      ),
40
      end_scan:scans!scan_id_end (
41
        id,
42
        user_id,
43
        badge_scan_time
44
      )
45
    """
46
  )
47
  .execute()
48
  .value

1
val data = supabase.from("shifts").select(Columns.raw('''
2
  *,
3
  start_scan:scans!scan_id_start (
4
    id,
5
    user_id,
6
    badge_scan_time
7
  ),
8
end_scan:scans!scan_id_end (
9
    id,
10
    user_id,
11
    badge_scan_time
12
  )
13
'''));

1
data = supabase.from_('shifts').select("""
2
  *,
3
  start_scan:scans!scan_id_start (
4
    id,
5
    user_id,
6
    badge_scan_time
7
  ),
8
  end_scan:scans!scan_id_end (
9
    id,
10
    user_id,
11
    badge_scan_time
12
  )
13
""").execute()

1
var result = await supabase
2
  .From<Shift>()
3
  .Select(@"
4
    *,
5
    start_scan:scans!scan_id_start (
6
      id,
7
      user_id,
8
      badge_scan_time
9
    ),
10
    end_scan:scans!scan_id_end (
11
      id,
12
      user_id,
13
      badge_scan_time
14
    )
15
  ")
16
  .Get();

1
const  = `
2
  query {
3
    shiftsCollection {
4
      edges {
5
        node {
6
          id
7
          user_id
8
          attendance_status
9
          scan_id_start {
10
            id
11
            user_id
12
            badge_scan_time
13
          }
14
          scan_id_end {
15
            id
16
            user_id
17
            badge_scan_time
18
          }
19
        }
20
      }
21
    }
22
  }
23
`

Querying Joins and Nested tables

One-to-many joins

TypeScript types for joins

Join types and join modifiers

What `:` and `!` mean in join syntax

Example data for join types

Orchestral sections

Instruments

Left join (default)

Result

Inner join (`!inner`)

Result

Filtering using joined fields

Result

Many-to-many joins

Specifying the `ON` clause for joins with multiple foreign keys

Is this helpful?

AI tools

One-to-many joins

TypeScript types for joins

Join types and join modifiers

What : and ! mean in join syntax

Example data for join types

Orchestral sections

Instruments

Left join (default)

Result

Inner join (!inner)

Result

Filtering using joined fields

Result

Many-to-many joins

Specifying the ON clause for joins with multiple foreign keys

Is this helpful?

AI tools

What `:` and `!` mean in join syntax

Inner join (`!inner`)

Specifying the `ON` clause for joins with multiple foreign keys