How to Model Relationships in Supabase

1create table public.authors (
2  id uuid primary key default gen_random_uuid(),
3  name text not null,
4  bio text,
5  created_at timestamptz default now()
6);
7
8create table public.tags (
9  id bigint generated always as identity primary key,
10  label text unique not null
11);
12
13alter table public.authors enable row level security;
14alter table public.tags enable row level security;

1create table public.posts (
2  id bigint generated always as identity primary key,
3  title text not null,
4  body text,
5  author_id uuid not null references public.authors(id) on delete cascade,
6  created_at timestamptz default now()
7);
8
9alter table public.posts enable row level security;
10
11-- Allow anyone to read posts
12create policy "Public read posts" on public.posts
13  for select to anon, authenticated
14  using (true);
15
16-- Allow authenticated users to insert posts
17create policy "Auth users insert posts" on public.posts
18  for insert to authenticated
19  with check (true);

1create table public.post_tags (
2  id bigint generated always as identity primary key,
3  post_id bigint not null references public.posts(id) on delete cascade,
4  tag_id bigint not null references public.tags(id) on delete cascade,
5  unique(post_id, tag_id)
6);
7
8alter table public.post_tags enable row level security;
9
10create policy "Public read post_tags" on public.post_tags
11  for select to anon, authenticated
12  using (true);
13
14create policy "Auth users insert post_tags" on public.post_tags
15  for insert to authenticated
16  with check (true);

1import { createClient } from '@supabase/supabase-js'
2
3const supabase = createClient(
4  process.env.NEXT_PUBLIC_SUPABASE_URL!,
5  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
6)
7
8// One-to-many: fetch posts with their author
9const { data: posts } = await supabase
10  .from('posts')
11  .select('id, title, authors(name, bio)')
12
13// Many-to-many: fetch posts with their tags
14const { data: postsWithTags } = await supabase
15  .from('posts')
16  .select('id, title, post_tags(tags(label))')
17
18// Combined: posts with author AND tags
19const { data: full } = await supabase
20  .from('posts')
21  .select('id, title, body, authors(name), post_tags(tags(label))')

1create table public.profiles (
2  id uuid not null references auth.users(id) on delete cascade,
3  display_name text,
4  avatar_url text,
5  primary key (id)
6);
7
8alter table public.profiles enable row level security;
9
10create policy "Users can view own profile" on public.profiles
11  for select to authenticated
12  using ((select auth.uid()) = id);
13
14create policy "Users can update own profile" on public.profiles
15  for update to authenticated
16  using ((select auth.uid()) = id)
17  with check ((select auth.uid()) = id);
18
19-- Auto-create profile on signup
20create or replace function public.handle_new_user()
21returns trigger language plpgsql security definer set search_path = ''
22as $$
23begin
24  insert into public.profiles (id, display_name)
25  values (new.id, new.raw_user_meta_data ->> 'full_name');
26  return new;
27end;
28$$;
29
30create trigger on_auth_user_created
31  after insert on auth.users
32  for each row execute function public.handle_new_user();

1// Insert an author
2const { data: author } = await supabase
3  .from('authors')
4  .insert({ name: 'Jane Smith', bio: 'Full-stack developer' })
5  .select()
6  .single()
7
8// Insert a post linked to that author
9const { data: post } = await supabase
10  .from('posts')
11  .insert({ title: 'Getting Started with Supabase', body: 'A beginner guide...', author_id: author.id })
12  .select()
13  .single()
14
15// Insert tags
16const { data: tags } = await supabase
17  .from('tags')
18  .insert([{ label: 'supabase' }, { label: 'postgresql' }])
19  .select()
20
21// Link post to tags via join table
22await supabase.from('post_tags').insert([
23  { post_id: post.id, tag_id: tags[0].id },
24  { post_id: post.id, tag_id: tags[1].id }
25])
26
27// Verify with nested select
28const { data } = await supabase
29  .from('posts')
30  .select('title, authors(name), post_tags(tags(label))')
31console.log(JSON.stringify(data, null, 2))