How to Query with Filters in Supabase

1import { createClient } from '@supabase/supabase-js';
2
3const supabase = createClient(
4  process.env.NEXT_PUBLIC_SUPABASE_URL!,
5  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
6);
7
8// Exact match
9const { data: activeUsers } = await supabase
10  .from('users')
11  .select('id, name, email')
12  .eq('status', 'active');
13
14// Not equal
15const { data: nonAdmins } = await supabase
16  .from('users')
17  .select('id, name, role')
18  .neq('role', 'admin');
19
20// Multiple equality filters (AND logic)
21const { data: activeAdmins } = await supabase
22  .from('users')
23  .select('id, name')
24  .eq('status', 'active')
25  .eq('role', 'admin');

1// Numbers: products under $50
2const { data: affordable } = await supabase
3  .from('products')
4  .select('name, price')
5  .lt('price', 50);
6
7// Numbers: orders between $10 and $100
8const { data: midRange } = await supabase
9  .from('orders')
10  .select('id, total')
11  .gte('total', 10)
12  .lte('total', 100);
13
14// Dates: posts from the last 7 days
15const weekAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString();
16const { data: recentPosts } = await supabase
17  .from('posts')
18  .select('id, title, created_at')
19  .gte('created_at', weekAgo)
20  .order('created_at', { ascending: false });

1// Case-insensitive search for names containing 'john'
2const { data: johns } = await supabase
3  .from('users')
4  .select('id, name, email')
5  .ilike('name', '%john%');
6
7// Case-sensitive: emails ending with @gmail.com
8const { data: gmailUsers } = await supabase
9  .from('users')
10  .select('id, email')
11  .like('email', '%@gmail.com');
12
13// Names starting with 'A'
14const { data: aNames } = await supabase
15  .from('users')
16  .select('id, name')
17  .ilike('name', 'A%');
18
19// Full-text search (requires tsvector column or function)
20const { data: searchResults } = await supabase
21  .from('articles')
22  .select('id, title, body')
23  .textSearch('title', 'supabase authentication');

1// Match any of multiple values (SQL IN)
2const { data: selectedOrders } = await supabase
3  .from('orders')
4  .select('*')
5  .in('status', ['pending', 'processing', 'shipped']);
6
7// Array column: posts that have the 'supabase' tag
8const { data: taggedPosts } = await supabase
9  .from('posts')
10  .select('id, title, tags')
11  .contains('tags', ['supabase']);
12
13// JSONB containment: products with specific metadata
14const { data: electronics } = await supabase
15  .from('products')
16  .select('id, name, metadata')
17  .contains('metadata', { category: 'electronics' });
18
19// Array is subset of values
20const { data: basicPosts } = await supabase
21  .from('posts')
22  .select('id, title, tags')
23  .containedBy('tags', ['supabase', 'postgres', 'react']);

1// OR: status is 'active' OR role is 'admin'
2const { data: activeOrAdmin } = await supabase
3  .from('users')
4  .select('id, name, status, role')
5  .or('status.eq.active,role.eq.admin');
6
7// Combine AND and OR: active users who are admin OR moderator
8const { data: activeStaff } = await supabase
9  .from('users')
10  .select('id, name, status, role')
11  .eq('status', 'active')
12  .or('role.eq.admin,role.eq.moderator');
13
14// OR with range: price < 10 OR price > 100
15const { data: extremePrices } = await supabase
16  .from('products')
17  .select('id, name, price')
18  .or('price.lt.10,price.gt.100');

1// Fetch orders with their items, only orders with quantity > 5
2const { data: largeOrders } = await supabase
3  .from('orders')
4  .select('id, total, order_items(product_name, quantity)')
5  .gt('order_items.quantity', 5);
6
7// Fetch users with their posts, filter posts by published status
8const { data: usersWithPosts } = await supabase
9  .from('users')
10  .select('id, name, posts(id, title, status)')
11  .eq('posts.status', 'published');
12
13// Combine filters on both parent and child tables
14const { data: activeWithRecent } = await supabase
15  .from('users')
16  .select('id, name, posts(id, title, created_at)')
17  .eq('status', 'active')
18  .gte('posts.created_at', weekAgo);