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.

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

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.