How to Avoid Duplicated Entries in Supabase

1-- Add UNIQUE when creating the table
2create table public.subscribers (
3  id bigint generated always as identity primary key,
4  email text unique not null,
5  name text,
6  subscribed_at timestamptz default now()
7);
8
9alter table public.subscribers enable row level security;
10
11-- Or add UNIQUE to an existing column
12alter table public.subscribers
13  add constraint subscribers_email_unique unique (email);

1create table public.user_roles (
2  id bigint generated always as identity primary key,
3  user_id uuid not null references auth.users(id) on delete cascade,
4  role text not null,
5  assigned_at timestamptz default now(),
6  unique(user_id, role)
7);
8
9alter table public.user_roles enable row level security;
10
11create policy "Users read own roles" on public.user_roles
12  for select to authenticated
13  using ((select auth.uid()) = user_id);
14
15create policy "Auth users insert roles" on public.user_roles
16  for insert to authenticated
17  with check ((select auth.uid()) = user_id);

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// Upsert: insert new subscriber or update name if email exists
9const { data, error } = await supabase
10  .from('subscribers')
11  .upsert(
12    { email: 'user@example.com', name: 'Updated Name' },
13    { onConflict: 'email' }
14  )
15  .select()
16
17// Bulk upsert: insert or update multiple rows
18const { data: bulk, error: bulkError } = await supabase
19  .from('subscribers')
20  .upsert([
21    { email: 'user1@example.com', name: 'User 1' },
22    { email: 'user2@example.com', name: 'User 2' },
23    { email: 'user3@example.com', name: 'User 3' }
24  ], { onConflict: 'email' })
25  .select()

1-- Only enforce unique emails for active subscribers
2create unique index idx_subscribers_active_email
3on public.subscribers (email)
4where is_active = true;
5
6-- Example: add the is_active column first if it does not exist
7alter table public.subscribers
8  add column is_active boolean default true;

1const { data, error } = await supabase
2  .from('subscribers')
3  .insert({ email: 'user@example.com', name: 'New User' })
4  .select()
5
6if (error) {
7  if (error.code === '23505') {
8    // Duplicate key violation
9    console.error('This email is already registered.')
10    // Show user-friendly message in your UI
11  } else {
12    console.error('Insert failed:', error.message)
13  }
14} else {
15  console.log('Subscriber created:', data)
16}