How to Import Data into Supabase

1import { createClient } from '@supabase/supabase-js'
2
3const supabase = createClient(
4  process.env.SUPABASE_URL!,
5  process.env.SUPABASE_SERVICE_ROLE_KEY! // Server-side only
6)
7
8// Single batch insert
9const rows = [
10  { name: 'Alice', email: 'alice@example.com', role: 'admin' },
11  { name: 'Bob', email: 'bob@example.com', role: 'user' },
12  { name: 'Carol', email: 'carol@example.com', role: 'user' },
13]
14
15const { data, error } = await supabase
16  .from('users')
17  .insert(rows)
18  .select()
19
20if (error) console.error('Insert failed:', error.message)
21
22// Batched insert for large datasets
23const BATCH_SIZE = 500
24for (let i = 0; i < largeDataset.length; i += BATCH_SIZE) {
25  const batch = largeDataset.slice(i, i + BATCH_SIZE)
26  const { error } = await supabase.from('users').insert(batch)
27  if (error) {
28    console.error(`Batch ${i / BATCH_SIZE} failed:`, error.message)
29    break
30  }
31}

1# Get your connection string from Dashboard → Settings → Database
2# Format: postgresql://postgres.[ref]:[password]@[host]:5432/postgres
3
4# Import CSV with headers
5psql "postgresql://postgres.[ref]:[password]@db.[ref].supabase.co:5432/postgres" \
6  -c "\COPY public.users (name, email, role) FROM '/path/to/data.csv' WITH (FORMAT csv, HEADER true)"
7
8# For tab-delimited files
9psql "postgresql://postgres.[ref]:[password]@db.[ref].supabase.co:5432/postgres" \
10  -c "\COPY public.users FROM '/path/to/data.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER true)"

1// Upsert: insert or update on conflict
2const { data, error } = await supabase
3  .from('users')
4  .upsert(
5    [
6      { email: 'alice@example.com', name: 'Alice Updated', role: 'admin' },
7      { email: 'dave@example.com', name: 'Dave', role: 'user' },
8    ],
9    { onConflict: 'email' }
10  )
11  .select()
12
13// The email column must have a UNIQUE constraint:
14// ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

1-- Check total rows imported
2SELECT count(*) FROM public.users;
3
4-- Check for NULLs in required columns
5SELECT count(*) FROM public.users WHERE name IS NULL OR email IS NULL;
6
7-- Spot-check first 10 rows
8SELECT * FROM public.users ORDER BY created_at DESC LIMIT 10;