$ cat /posts/supabase-queries-select-insert-update-and-delete.md

Supabase Queries: SELECT, INSERT, UPDATE, and DELETE

drwxr-xr-x2026-01-255 min0 views

Mastering CRUD (Create, Read, Update, Delete) operations is essential for building any database-driven application with Supabase. The Supabase JavaScript client provides an intuitive query builder that translates to efficient PostgreSQL queries while maintaining type safety and simplicity. This comprehensive guide covers all fundamental database operations including selecting data with various filters, inserting single and multiple records, updating existing data, deleting records, and handling errors gracefully. Understanding these query patterns enables you to build complete applications with full data management capabilities. Before proceeding, ensure you've completed database table creation and installed the JavaScript client.

SELECT: Reading Data

javascriptselect_queries.js

// Basic SELECT - Get all rows and columns
const { data, error } = await supabase
  .from('posts')
  .select('*')

// Select specific columns
const { data } = await supabase
  .from('posts')
  .select('id, title, created_at')

// Select with simple filter
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('status', 'published')

// Multiple filters (AND condition)
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('status', 'published')
  .gt('view_count', 100)

// OR conditions using .or()
const { data } = await supabase
  .from('posts')
  .select('*')
  .or('status.eq.published,status.eq.featured')

// Get single row
const { data, error } = await supabase
  .from('posts')
  .select('*')
  .eq('id', postId)
  .single()

// Limit results
const { data } = await supabase
  .from('posts')
  .select('*')
  .limit(10)

// Order results
const { data } = await supabase
  .from('posts')
  .select('*')
  .order('created_at', { ascending: false })

// Range/pagination
const { data } = await supabase
  .from('posts')
  .select('*')
  .range(0, 9) // First 10 items (0-9)

SELECT queries retrieve data from your database. Always select only the columns you need rather than using '*' in production for better performance and reduced bandwidth. The query builder chains methods for filtering, ordering, and limiting results. Learn advanced filtering in our filtering techniques guide.

INSERT: Creating Records

javascriptinsert_queries.js

// Insert single record
const { data, error } = await supabase
  .from('posts')
  .insert([
    {
      title: 'My First Post',
      content: 'This is the content',
      status: 'draft'
    }
  ])
  .select()

// Insert multiple records
const { data, error } = await supabase
  .from('posts')
  .insert([
    { title: 'Post 1', content: 'Content 1' },
    { title: 'Post 2', content: 'Content 2' },
    { title: 'Post 3', content: 'Content 3' }
  ])
  .select()

// Insert with specific columns
const { data, error } = await supabase
  .from('posts')
  .insert([{ title: 'New Post', user_id: userId }])
  .select('id, title, created_at')

// Insert and return single row
const { data, error } = await supabase
  .from('posts')
  .insert([{ title: 'Single Post' }])
  .select()
  .single()

// Upsert (insert or update if exists)
const { data, error } = await supabase
  .from('posts')
  .upsert([
    { id: existingId, title: 'Updated Title' }
  ])
  .select()

INSERT operations create new records in your database. Always use .select() after inserting to return the created records with database-generated values like IDs and timestamps. The upsert method updates existing records or inserts new ones based on primary key, useful for sync operations.

UPDATE: Modifying Records

javascriptupdate_queries.js

// Update single record by ID
const { data, error } = await supabase
  .from('posts')
  .update({ status: 'published' })
  .eq('id', postId)
  .select()

// Update multiple records
const { data, error } = await supabase
  .from('posts')
  .update({ status: 'archived' })
  .eq('user_id', userId)
  .select()

// Update with multiple conditions
const { data, error } = await supabase
  .from('posts')
  .update({ featured: true })
  .gt('view_count', 1000)
  .eq('status', 'published')
  .select()

// Increment a value
const { data, error } = await supabase
  .from('posts')
  .update({ view_count: 'view_count + 1' })
  .eq('id', postId)

// Update specific fields
const { data, error } = await supabase
  .from('posts')
  .update({
    title: newTitle,
    content: newContent,
    updated_at: new Date().toISOString()
  })
  .eq('id', postId)
  .select()

UPDATE operations modify existing records. Always include a filter (like .eq()) to specify which records to update—without filters, all records will be updated! Use .select() to return the updated records. Note that triggers like updated_at are often handled automatically by database triggers.

DELETE: Removing Records

javascriptdelete_queries.js

// Delete single record by ID
const { error } = await supabase
  .from('posts')
  .delete()
  .eq('id', postId)

// Delete multiple records
const { error } = await supabase
  .from('posts')
  .delete()
  .eq('user_id', userId)

// Delete with conditions
const { error } = await supabase
  .from('posts')
  .delete()
  .eq('status', 'draft')
  .lt('created_at', thirtyDaysAgo)

// Delete and return deleted rows
const { data, error } = await supabase
  .from('posts')
  .delete()
  .eq('id', postId)
  .select()

// Soft delete (mark as deleted instead of removing)
const { data, error } = await supabase
  .from('posts')
  .update({ deleted_at: new Date().toISOString() })
  .eq('id', postId)
  .select()

DELETE operations permanently remove records from your database. Always be cautious with deletes and consider soft deletes (marking records as deleted) for important data that may need recovery. CASCADE deletes configured in foreign keys automatically delete related records.

Error Handling

javascripterror_handling.js

// Always check for errors
const { data, error } = await supabase
  .from('posts')
  .select('*')

if (error) {
  console.error('Database error:', error.message)
  // Handle error appropriately
  return { success: false, error: error.message }
}

// TypeScript error handling
try {
  const { data, error } = await supabase
    .from('posts')
    .insert([{ title: 'New Post' }])
    .select()
    .single()
  
  if (error) throw error
  
  console.log('Created post:', data)
  return { success: true, data }
} catch (err) {
  console.error('Failed to create post:', err)
  return { success: false, error: err.message }
}

// Common error types
// - 23505: Unique constraint violation
// - 23503: Foreign key constraint violation
// - 42P01: Table doesn't exist
// - PGRST116: Row not found (single())

Query Best Practices

Select Only Needed Columns: Use .select('id, title') instead of .select('*') for better performance
Always Check Errors: Validate error objects before using data to prevent runtime issues
Use .single() Carefully: Only use when expecting exactly one result, otherwise it throws errors
Implement Pagination: Use .range() or cursor-based pagination for large datasets. Learn pagination techniques
Leverage Indexes: Ensure filtered columns have indexes for fast queries
Use RLS Policies: Don't rely on client-side filters for security. Implement Row Level Security

Counting Records

javascriptcount_queries.js

// Count total records
const { count, error } = await supabase
  .from('posts')
  .select('*', { count: 'exact', head: true })

// Count with filter
const { count } = await supabase
  .from('posts')
  .select('*', { count: 'exact', head: true })
  .eq('status', 'published')

console.log('Total posts:', count)

Security Note: All these queries respect Row Level Security policies. Users can only query, insert, update, or delete data allowed by your RLS policies. Always implement proper RLS policies before deploying to production.

Next Steps

Advanced Filtering: Learn complex query patterns with filtering and ordering techniques
Relationships and Joins: Query related data efficiently with foreign keys and joins
Secure Your Data: Implement Row Level Security policies for production apps
Optimize Performance: Add indexes and implement pagination strategies

Conclusion

Mastering CRUD operations with Supabase's query builder provides the foundation for all database interactions in your applications. The intuitive API makes complex SQL queries accessible while maintaining type safety and performance. Always remember to check for errors, select only necessary columns, implement proper filtering, and secure your data with Row Level Security policies. With these fundamental query patterns mastered, you're equipped to build full-featured applications with complete data management capabilities. Continue enhancing your skills with advanced filtering techniques, relationship queries, and security best practices to build production-ready Supabase applications.