Querying Data

Count with Filters

const { count } = await client
  .from('projects')
  .select('*', { count: 'exact', head: true })
  .eq('status', 'active');

Advanced Queries

Multiple Filters

const { data } = await client
  .from('projects')
  .select('*')
  .eq('account_id', accountId)
  .eq('status', 'active')
  .gte('created_at', startDate)
  .lte('created_at', endDate)
  .order('created_at', { ascending: false })
  .limit(20);

OR Conditions

const { data } = await client
  .from('projects')
  .select('*')
  .or('status.eq.active,status.eq.pending');

Nested OR

const { data } = await client
  .from('projects')
  .select('*')
  .or('and(status.eq.active,priority.eq.high),status.eq.urgent');

Joins

Inner Join

const { data } = await client
  .from('projects')
  .select(`
    *,
    account:accounts!inner(
      id,
      name
    )
  `)
  .eq('account.name', 'Acme Corp');

Left Join

const { data } = await client
  .from('projects')
  .select(`
    *,
    tasks(*)
  `);

Null Handling

Is Null

const { data } = await client
  .from('projects')
  .select('*')
  .is('completed_at', null);

Not Null

const { data} = await client
  .from('projects')
  .select('*')
  .not('completed_at', 'is', null);

Insert Data

Single Insert

const { data, error } = await client
  .from('projects')
  .insert({
    name: 'New Project',
    account_id: accountId,
    status: 'active',
  })
  .select()
  .single();

Multiple Insert

const { data, error } = await client
  .from('projects')
  .insert([
    { name: 'Project 1', account_id: accountId },
    { name: 'Project 2', account_id: accountId },
  ])
  .select();

Update Data

Update with Filter

const { data, error } = await client
  .from('projects')
  .update({ status: 'completed' })
  .eq('id', projectId)
  .select()
  .single();

Update Multiple Rows

const { data, error } = await client
  .from('projects')
  .update({ status: 'archived' })
  .eq('account_id', accountId)
  .lt('updated_at', oldDate);

Delete Data

Delete with Filter

const { error } = await client
  .from('projects')
  .delete()
  .eq('id', projectId);

Delete Multiple

const { error } = await client
  .from('projects')
  .delete()
  .in('id', projectIds);

Upsert

Insert or Update

const { data, error } = await client
  .from('projects')
  .upsert({
    id: projectId,
    name: 'Updated Name',
    status: 'active',
  })
  .select()
  .single();

RPC (Stored Procedures)

Call Database Function

const { data, error } = await client
  .rpc('get_user_projects', {
    user_id: userId,
  });

With Complex Parameters

const { data, error } = await client
  .rpc('search_projects', {
    search_term: 'design',
    account_ids: [1, 2, 3],
    min_budget: 5000,
  });

Error Handling

Basic Error Handling

const { data, error } = await client
  .from('projects')
  .select('*');

if (error) {
  console.error('Error fetching projects:', error.message);
  throw error;
}

return data;

Typed Error Handling

import { PostgrestError } from '@supabase/supabase-js';

function handleDatabaseError(error: PostgrestError) {
  switch (error.code) {
    case '23505': // unique_violation
      throw new Error('A project with this name already exists');
    case '23503': // foreign_key_violation
      throw new Error('Invalid account reference');
    default:
      throw new Error('Database error: ' + error.message);
  }
}

TypeScript Types

Generated Types

import { Database } from '~/types/database.types';

type Project = Database['public']['Tables']['projects']['Row'];
type ProjectInsert = Database['public']['Tables']['projects']['Insert'];
type ProjectUpdate = Database['public']['Tables']['projects']['Update'];

Typed Queries

const { data } = await client
  .from('projects')
  .select('*')
  .returns<Project[]>();

Performance Tips

1. Select only needed columns - Don't use select('*') unnecessarily
2. Use indexes - Create indexes on frequently filtered columns
3. Limit results - Always paginate large datasets
4. Avoid N+1 queries - Use joins instead of multiple queries
5. Use RPC for complex queries - Move logic to database
6. Cache when possible - Use React Query or similar
7. Profile queries - Use EXPLAIN ANALYZE in SQL

Best Practices

1. Always handle errors - Check error responses
2. Validate input - Use Zod or similar
3. Use TypeScript - Generate and use types
4. Consistent naming - Follow database naming conventions
5. Document complex queries - Add comments
6. Test queries - Unit test database operations
7. Monitor performance - Track slow queries