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 database
mcp__supabase__get_table_schema - Get schema for a specific table
mcp__supabase__execute_sql - Run read-only SQL queries

Workflow:

Start with list_tables to understand database structure
Use get_table_schema to inspect columns and types
Use execute_sql for 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 authenticated in policies
✓ Use on delete cascade for 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/update

Additional Resources

For detailed patterns and code examples, consult:

references/auth.md - Authentication with JS/Python SDK, user profiles
references/rls.md - Row Level Security policies and performance tips
references/relationships.md - Table relationships and nested queries
references/query-patterns.md - Filtering, pagination, counting, indexes

Supabase Usage

Skill Content