# How to Model Relationships in Supabase

- Tool: Supabase
- Difficulty: Beginner
- Time required: 15-20 min
- Compatibility: Supabase (all plans), @supabase/supabase-js v2+
- Last updated: March 2026

## TL;DR

Supabase uses standard PostgreSQL foreign keys to model one-to-one, one-to-many, and many-to-many relationships. Define foreign key columns that reference other tables, create join tables for many-to-many links, and use the Supabase JS client's nested select syntax to fetch related data in a single query without manual joins.

## Modeling One-to-One, One-to-Many, and Many-to-Many Relationships in Supabase

Supabase is built on PostgreSQL, so you model relationships the same way you would in any relational database: with foreign keys. This tutorial walks you through creating each type of relationship, setting up the corresponding RLS policies, and querying related data efficiently using the Supabase JS client's nested select syntax. You will build a simple project with authors, posts, and tags to demonstrate all three relationship types.

## Before you start

- A Supabase project (free tier works)
- Access to the SQL Editor in the Supabase Dashboard
- Basic understanding of SQL tables and columns
- @supabase/supabase-js installed in your frontend project

## Step-by-step guide

### 1. Create the parent tables for authors and tags

Open the SQL Editor in your Supabase Dashboard and create two parent tables: authors and tags. These will be referenced by other tables through foreign keys. Use UUID primary keys for authors so you can link them to auth.users later, and bigint identity keys for tags since they are standalone lookup data.

```
create table public.authors (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  bio text,
  created_at timestamptz default now()
);

create table public.tags (
  id bigint generated always as identity primary key,
  label text unique not null
);

alter table public.authors enable row level security;
alter table public.tags enable row level security;
```

**Expected result:** Both tables appear in the Table Editor. RLS is enabled with no policies, so data is locked down by default.

### 2. Create a one-to-many relationship between authors and posts

A one-to-many relationship means one author can have many posts, but each post belongs to exactly one author. Add a posts table with an author_id column that references the authors table. The foreign key constraint ensures referential integrity — you cannot insert a post pointing to a non-existent author. Adding ON DELETE CASCADE means deleting an author automatically removes all their posts.

```
create table public.posts (
  id bigint generated always as identity primary key,
  title text not null,
  body text,
  author_id uuid not null references public.authors(id) on delete cascade,
  created_at timestamptz default now()
);

alter table public.posts enable row level security;

-- Allow anyone to read posts
create policy "Public read posts" on public.posts
  for select to anon, authenticated
  using (true);

-- Allow authenticated users to insert posts
create policy "Auth users insert posts" on public.posts
  for insert to authenticated
  with check (true);
```

**Expected result:** The posts table is created with a foreign key arrow visible in the Table Editor pointing to authors.

### 3. Create a many-to-many relationship between posts and tags

A many-to-many relationship means one post can have many tags, and one tag can appear on many posts. You model this with a join table (also called a junction or bridge table) that holds two foreign keys. Each row in the join table represents one link between a post and a tag. A composite unique constraint prevents duplicate pairings.

```
create table public.post_tags (
  id bigint generated always as identity primary key,
  post_id bigint not null references public.posts(id) on delete cascade,
  tag_id bigint not null references public.tags(id) on delete cascade,
  unique(post_id, tag_id)
);

alter table public.post_tags enable row level security;

create policy "Public read post_tags" on public.post_tags
  for select to anon, authenticated
  using (true);

create policy "Auth users insert post_tags" on public.post_tags
  for insert to authenticated
  with check (true);
```

**Expected result:** The post_tags join table is created with foreign keys pointing to both posts and tags.

### 4. Query nested relationships with the Supabase JS client

The Supabase JS client uses PostgREST's resource embedding to let you fetch related data in a single request. Pass the related table name inside the select string, and Supabase automatically resolves the foreign key join. For many-to-many relationships, reference the join table and then nest the final target table inside it. This avoids manual SQL joins and keeps your client code clean.

```
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

// One-to-many: fetch posts with their author
const { data: posts } = await supabase
  .from('posts')
  .select('id, title, authors(name, bio)')

// Many-to-many: fetch posts with their tags
const { data: postsWithTags } = await supabase
  .from('posts')
  .select('id, title, post_tags(tags(label))')

// Combined: posts with author AND tags
const { data: full } = await supabase
  .from('posts')
  .select('id, title, body, authors(name), post_tags(tags(label))')
```

**Expected result:** Each query returns an array of posts with nested author and tag objects already resolved — no client-side joining needed.

### 5. Link a profiles table to auth.users for a one-to-one relationship

The most common one-to-one relationship in Supabase links a public profiles table to auth.users. The profiles table uses the same UUID as its primary key, referencing auth.users(id). Create a trigger so that a profile row is automatically created every time a new user signs up. This pattern is used in nearly every Supabase application.

```
create table public.profiles (
  id uuid not null references auth.users(id) on delete cascade,
  display_name text,
  avatar_url text,
  primary key (id)
);

alter table public.profiles enable row level security;

create policy "Users can view own profile" on public.profiles
  for select to authenticated
  using ((select auth.uid()) = id);

create policy "Users can update own profile" on public.profiles
  for update to authenticated
  using ((select auth.uid()) = id)
  with check ((select auth.uid()) = id);

-- Auto-create profile on signup
create or replace function public.handle_new_user()
returns trigger language plpgsql security definer set search_path = ''
as $$
begin
  insert into public.profiles (id, display_name)
  values (new.id, new.raw_user_meta_data ->> 'full_name');
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();
```

**Expected result:** When a new user signs up, a profile row is automatically created. Each user can only read and update their own profile.

### 6. Insert related data and verify the relationships

Test your schema by inserting sample data. Insert an author, then a post linked to that author, then some tags, and finally link them through the join table. Then run the nested select query to verify everything resolves correctly. This confirms that foreign keys, RLS policies, and the nested select syntax all work together.

```
// Insert an author
const { data: author } = await supabase
  .from('authors')
  .insert({ name: 'Jane Smith', bio: 'Full-stack developer' })
  .select()
  .single()

// Insert a post linked to that author
const { data: post } = await supabase
  .from('posts')
  .insert({ title: 'Getting Started with Supabase', body: 'A beginner guide...', author_id: author.id })
  .select()
  .single()

// Insert tags
const { data: tags } = await supabase
  .from('tags')
  .insert([{ label: 'supabase' }, { label: 'postgresql' }])
  .select()

// Link post to tags via join table
await supabase.from('post_tags').insert([
  { post_id: post.id, tag_id: tags[0].id },
  { post_id: post.id, tag_id: tags[1].id }
])

// Verify with nested select
const { data } = await supabase
  .from('posts')
  .select('title, authors(name), post_tags(tags(label))')
console.log(JSON.stringify(data, null, 2))
```

**Expected result:** The console output shows each post with its author name and an array of tag labels nested inside the response object.

## Complete code example

File: `supabase-relationships-schema.sql`

```sql
-- Authors table
create table public.authors (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  bio text,
  created_at timestamptz default now()
);

-- Tags table
create table public.tags (
  id bigint generated always as identity primary key,
  label text unique not null
);

-- Posts table (one-to-many with authors)
create table public.posts (
  id bigint generated always as identity primary key,
  title text not null,
  body text,
  author_id uuid not null references public.authors(id) on delete cascade,
  created_at timestamptz default now()
);

-- Join table for many-to-many (posts <-> tags)
create table public.post_tags (
  id bigint generated always as identity primary key,
  post_id bigint not null references public.posts(id) on delete cascade,
  tag_id bigint not null references public.tags(id) on delete cascade,
  unique(post_id, tag_id)
);

-- Profiles table (one-to-one with auth.users)
create table public.profiles (
  id uuid not null references auth.users(id) on delete cascade,
  display_name text,
  avatar_url text,
  primary key (id)
);

-- Enable RLS on all tables
alter table public.authors enable row level security;
alter table public.tags enable row level security;
alter table public.posts enable row level security;
alter table public.post_tags enable row level security;
alter table public.profiles enable row level security;

-- RLS policies
create policy "Public read authors" on public.authors for select to anon, authenticated using (true);
create policy "Public read tags" on public.tags for select to anon, authenticated using (true);
create policy "Public read posts" on public.posts for select to anon, authenticated using (true);
create policy "Auth insert posts" on public.posts for insert to authenticated with check (true);
create policy "Public read post_tags" on public.post_tags for select to anon, authenticated using (true);
create policy "Auth insert post_tags" on public.post_tags for insert to authenticated with check (true);
create policy "Users read own profile" on public.profiles for select to authenticated using ((select auth.uid()) = id);
create policy "Users update own profile" on public.profiles for update to authenticated using ((select auth.uid()) = id) with check ((select auth.uid()) = id);

-- Auto-create profile on signup
create or replace function public.handle_new_user()
returns trigger language plpgsql security definer set search_path = ''
as $$
begin
  insert into public.profiles (id, display_name)
  values (new.id, new.raw_user_meta_data ->> 'full_name');
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();
```

## Common mistakes

- **Forgetting to add a SELECT RLS policy alongside INSERT, causing inserts to appear to fail because the returned data is empty** — undefined Fix: Always create a SELECT policy on any table where you insert data via the JS client. The client runs a SELECT after INSERT to return the new row.
- **Using ON DELETE CASCADE when you want to prevent accidental parent deletion** — undefined Fix: Use ON DELETE RESTRICT if child records should block parent deletion. CASCADE silently removes all children when the parent is deleted.
- **Trying to query a many-to-many relationship directly without going through the join table** — undefined Fix: Always reference the join table in your nested select: select('id, post_tags(tags(label))') — not select('id, tags(label)').
- **Not enabling RLS on newly created tables, leaving data exposed to anyone with the anon key** — undefined Fix: Run ALTER TABLE table_name ENABLE ROW LEVEL SECURITY immediately after creating every table. Then add specific policies.

## Best practices

- Always enable RLS on every table and add explicit policies for each operation you need
- Use UUID primary keys for tables that reference auth.users so the foreign key matches the user ID type
- Add ON DELETE CASCADE for child records that should be removed when the parent is deleted
- Add indexes on foreign key columns to speed up joins and nested select queries
- Use composite unique constraints on join tables to prevent duplicate relationships
- Wrap auth.uid() in a select subquery inside RLS policies for per-statement caching: (select auth.uid())
- Use the nested select syntax instead of manual joins to keep client code simple and reduce round trips
- Create a profiles table linked to auth.users as a one-to-one relationship for storing public user data

## Frequently asked questions

### What is the difference between a one-to-many and a many-to-many relationship in Supabase?

A one-to-many relationship uses a single foreign key column on the child table pointing to the parent. A many-to-many relationship requires a separate join table with two foreign key columns, one pointing to each related table.

### Can I query nested relationships in a single request with the Supabase JS client?

Yes. Use the nested select syntax: supabase.from('posts').select('title, authors(name), post_tags(tags(label))'). Supabase resolves foreign key joins automatically through PostgREST resource embedding.

### Do I need to manually write SQL JOIN statements when using Supabase?

No. The Supabase JS client's nested select syntax handles joins automatically based on foreign key relationships. You only need raw SQL joins if you are writing custom database functions or complex queries in the SQL Editor.

### How do I prevent duplicate entries in a many-to-many join table?

Add a composite unique constraint: UNIQUE(post_id, tag_id). This ensures the same combination of foreign keys cannot appear more than once in the join table.

### Should I use UUID or bigint for primary keys in Supabase?

Use UUID for tables that reference auth.users, since Supabase user IDs are UUIDs. Use bigint generated always as identity for standalone tables where sequential IDs are simpler and more storage-efficient.

### Why does my insert return an empty array instead of the new row?

The Supabase client runs a SELECT after INSERT to return the new data. If you have an INSERT RLS policy but no SELECT policy, the insert succeeds but the returned data is blocked. Add a matching SELECT policy to fix this.

### Can RapidDev help me design a relational schema for my Supabase project?

Yes. RapidDev can design your database schema, set up foreign key relationships, write RLS policies, and build the corresponding API queries for your specific application requirements.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-model-relationships-in-supabase
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-model-relationships-in-supabase