Supabase Database Patterns
Patterns for working with Supabase databases including Auth, Row Level Security, table relationships, and query best practices.
Overview
- MCP Tools: Query and explore database structure
- Authentication: User management, sessions, auth tables
- Row Level Security: Policy patterns for data access control
- Table Relationships: Foreign keys, joins, nested queries
- Query Patterns: Filtering, pagination, performance
MCP Tools
Available tools for database exploration:
mcp__supabase__list_tables- List all tables in the databasemcp__supabase__get_table_schema- Get schema for a specific tablemcp__supabase__execute_sql- Run read-only SQL queries
Workflow:
- Start with
list_tablesto understand database structure - Use
get_table_schemato inspect columns and types - Use
execute_sqlfor custom queries (read-only)
Best Practices
DO
- ✓ Enable RLS on all public tables
- ✓ Use
(select auth.uid())in RLS policies for performance - ✓ Add indexes on RLS-checked columns
- ✓ Specify roles with
TO authenticatedin policies - ✓ Use
on delete cascadefor foreign keys to auth.users - ✓ Use cursor-based pagination for large datasets
- ✓ Select only needed columns:
.select('id, name')not.select('*')
DON'T
- ✗ Store sensitive data without RLS
- ✗ Use
auth.uid()directly in policies (use(select auth.uid())) - ✗ Create policies without specifying roles
- ✗ Forget indexes on frequently filtered columns
- ✗ Use offset pagination for deep pages (>1000 rows)
- ✗ Expose auth.users directly via API (use public profiles table)
Quick Reference
Common Filters
| Filter | JavaScript | Python |
|---|---|---|
| Equals | .eq('col', val) | .eq("col", val) |
| Not equals | .neq('col', val) | .neq("col", val) |
| Greater than | .gt('col', val) | .gt("col", val) |
| Greater or equal | .gte('col', val) | .gte("col", val) |
| Less than | .lt('col', val) | .lt("col", val) |
| Less or equal | .lte('col', val) | .lte("col", val) |
| Pattern match | .ilike('col', '%val%') | .ilike("col", "%val%") |
| In list | .in('col', [a,b]) | .in_("col", [a,b]) |
| Is null | .is('col', null) | .is_("col", "null") |
| OR | .or('a.eq.1,b.eq.2') | .or_("a.eq.1,b.eq.2") |
Auth Tables Quick Reference
| Table | Key Columns |
|---|---|
auth.users | id, email, phone, created_at, last_sign_in_at, raw_user_meta_data |
auth.sessions | id, user_id, created_at, updated_at |
auth.identities | id, user_id, provider, identity_data |
RLS Policy Template
create policy "policy_name" on table_name
to authenticated -- or anon, or specific role
for select -- select, insert, update, delete, or all
using ( (select auth.uid()) = user_id )
with check ( (select auth.uid()) = user_id ); -- for insert/updateAdditional Resources
For detailed patterns and code examples, consult:
references/auth.md- Authentication with JS/Python SDK, user profilesreferences/rls.md- Row Level Security policies and performance tipsreferences/relationships.md- Table relationships and nested queriesreferences/query-patterns.md- Filtering, pagination, counting, indexes