Supabase known pitfalls

Supabase Known Pitfalls

Overview

The twelve most common Supabase mistakes, ranked by severity: security (service_role exposure, missing RLS, permissive policies, no connection pooling), data integrity (ignoring { data, error }, missing .select() after mutations, .single() on optional results), and performance / maintainability (select('*'), N+1 queries, missing FK indexes, multiple client instances, no generated types). Each pitfall shows the broken code, why it fails, and the correct pattern using createClient from @supabase/supabase-js.

This SKILL.md carries the full pitfall table plus one representative fix per category. The verbatim broken-vs-correct code and detection queries for all twelve live in references/pitfalls.md — drill in there for depth.

Prerequisites

• Access to a Supabase project codebase for review
• @supabase/supabase-js v2+ installed
• Basic understanding of Row Level Security (RLS)

Instructions

Work the pitfalls top-down by severity. Fix every Critical finding before moving on — a single security miss can expose the whole database.

#	Pitfall	Severity	Fix
1	service_role key in client bundle	Critical	anon key on client; service_role server-only, no NEXT_PUBLIC_
2	Table without RLS	Critical	ALTER TABLE … ENABLE ROW LEVEL SECURITY right after CREATE TABLE
3	Overly permissive RLS policy	Critical	scope USING (…) to auth.uid(), never USING (true) for writes
4	No connection pooling in serverless	Critical	pooled string (Supavisor, port 6543), not the direct 5432 URL
5	Ignoring { data, error }	High	destructure both; check error before touching data
6	Missing .select() after mutation	High	chain .select('cols') — mutations return null otherwise
7	.single() on optional result	High	use .maybeSingle() for 0-or-1; .single() only for guaranteed 1
8	select('*') everywhere	Medium	name the columns — smaller payload, typed, no leakage
9	N+1 query loop	Medium	PostgREST embedded join, or batch with .in()
10	FK column without index	Medium	CREATE INDEX on every foreign-key column
11	Multiple client instances	Low	singleton in lib/supabase.ts, imported everywhere
12	Hand-written DB types	Low	supabase gen types typescript --linked

Step 1 — Security (Critical, pitfalls 1-4)

The service_role key bypasses all RLS, so it must never reach a browser bundle. Split the client by trust boundary:

// Client (browser): anon key — respects RLS
const supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!)

// Server only (API routes, server actions): service_role, NO NEXT_PUBLIC_ prefix
const supabaseAdmin = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY!,
  { auth: { autoRefreshToken: false, persistSession: false } })

Then confirm RLS is enabled on every table, tighten any USING (true) policy to auth.uid(), and use the pooled connection string in serverless. Full broken-vs-correct code and the SQL detection queries for pitfalls 1-4 are in the Security section of references/pitfalls.md.

Step 2 — Data Integrity (High, pitfalls 5-7)

Supabase returns { data, error } and mutations return null unless you ask for the row back:

const { data, error } = await supabase
  .from('orders').insert(order)
  .select('id, status')   // without .select(), data is null
  .maybeSingle()          // .single() throws PGRST116 on 0 rows
if (error) throw new Error(`Order failed: ${error.message}`)

See the Data Integrity section of references/pitfalls.md for the .single() vs .maybeSingle() rule of thumb and each failure mode.

Step 3 — Performance & Maintainability (Medium/Low, pitfalls 8-12)

Name your columns, collapse N+1 loops into a single embedded join, index foreign keys, share one client instance, and use generated types:

// One query instead of 1 + N — PostgREST embeds the FK relation
const { data } = await supabase
  .from('projects')
  .select('id, name, tasks (id, title, status)')

The full singleton pattern, the FK-index detection query, and the supabase gen types workflow are in the Performance and Maintainability section of references/pitfalls.md.

Output

• Security pitfalls identified: service_role exposure, missing RLS, permissive policies, no connection pooling
• Data integrity pitfalls fixed: { data, error } handling, .select() after mutations, .maybeSingle() usage
• Performance pitfalls resolved: column-specific selects, JOIN queries, FK indexes
• Maintainability improved: singleton client, generated types
• Detection commands for automated scanning of each pitfall

Error Handling

Issue	Cause	Solution
PGRST116: JSON object requested, multiple (or no) rows returned	Used .single() when 0 or 2+ rows match	Use .maybeSingle() for optional lookups
data is null after insert	Missing .select() chain	Add .select('column1, column2') after .insert()
TypeError: Cannot read property of null	Destructured only data, ignoring error	Always destructure { data, error } and check error first
too many connections for role	Direct connection from serverless	Use pooled connection string (port 6543)
permission denied for table	RLS blocking access, no matching policy	Check RLS policies match the authenticated user's JWT claims
relation does not exist	Table name typo, not caught at compile time	Use generated types for compile-time validation

More operator-facing failure modes (legacy codebases, false positives, fixes that break tests): references/errors.md.

Examples

Quick Security Audit

# Check for the three critical code-level security pitfalls in one pass
echo "=== Pitfall 1: Service role in client code ==="
grep -rn 'SERVICE_ROLE' --include="*.tsx" --include="*.ts" src/ app/ components/ 2>/dev/null || echo "Clean"

echo "=== Pitfall 2: Tables without RLS (run in SQL Editor) ==="
echo "SELECT tablename FROM pg_tables WHERE schemaname='public' AND rowsecurity=false;"

echo "=== Pitfall 3: Overly permissive policies (run in SQL Editor) ==="
echo "SELECT tablename, policyname FROM pg_policies WHERE qual='true' AND cmd!='r';"

Code Review Checklist

### Security
- [ ] No SERVICE_ROLE_KEY in client-side code or NEXT_PUBLIC_* vars
- [ ] RLS enabled on all new tables; policies scope to auth.uid() (no USING(true) writes)
### Data Integrity
- [ ] All calls destructure { data, error } and check error
- [ ] .select() chained after insert/update/upsert; .maybeSingle() for optional lookups
### Performance & Maintainability
- [ ] Columns named in .select() (no select('*')); no N+1; FK columns indexed
- [ ] Single createClient instance; generated types; pooled connection string in serverless

More detection one-liners: references/examples.md. Every pitfall's full before/after code: references/pitfalls.md.

Resources

• Supabase Auth: Securing Your Data
• Row Level Security
• TypeScript Support
• Connection Pooling
• Supabase JavaScript Client Reference
• PostgREST Error Codes

Next Steps

This completes the Supabase pitfalls reference. To start a new project with best practices from day one, see supabase-hello-world.