gabrielmoreira 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-jsv2+ 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.