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