Querying Data

Note: This is mock/placeholder content for demonstration purposes.

Efficiently query and filter data using Drizzle ORM's query builder.

Basic Queries

Select All

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

Select Specific Columns

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

Select with Related Data

const { data, error } = await client
  .from('projects')
  .select(`
    id,
    name,
    account:accounts(id, name),
    tasks(id, title, completed)
  `);

Filtering

Equal

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

Not Equal

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

Greater Than / Less Than

const { data } = await client
  .from('projects')
  .select('*')
  .gt('created_at', '2024-01-01')
  .lt('budget', 10000);

In Array

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

Like (Pattern Matching)

const { data } = await client
  .from('projects')
  .select('*')
  .like('name', '%website%');

Full-Text Search

const { data } = await client
  .from('projects')
  .select('*')
  .textSearch('description', 'design & development');

Ordering

Order By

const { data } = await client
  .from('projects')
  .select('*')
  .order('created_at', { ascending: false });

Multiple Order By

const { data } = await client
  .from('projects')
  .select('*')
  .order('status')
  .order('created_at', { ascending: false });

Pagination

Limit

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

Range (Offset)

const page = 2;
const pageSize = 10;
const from = (page - 1) * pageSize;
const to = from + pageSize - 1;

const { data, count } = await client
  .from('projects')
  .select('*', { count: 'exact' })
  .range(from, to);

Aggregations

Count

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

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

function handleDatabaseError(error: Error) {
  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