Auto-generated CRUD API¶

PostgREST là gì?¶

Supabase sử dụng PostgREST để tự động generate REST API từ PostgreSQL schema.

┌─────────────────────────────────────────────────────────────┐
│                    CREATE TABLE users                        │
│                          ↓                                   │
│           PostgREST generates endpoints:                     │
│                                                              │
│  GET    /rest/v1/users          → Select all                │
│  GET    /rest/v1/users?id=eq.1  → Select one                │
│  POST   /rest/v1/users          → Insert                    │
│  PATCH  /rest/v1/users?id=eq.1  → Update                    │
│  DELETE /rest/v1/users?id=eq.1  → Delete                    │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Không cần viết API endpoints!

SELECT - Read Data¶

Select all¶

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

// SQL: SELECT * FROM users

Select specific columns¶

const { data, error } = await supabase
  .from('users')
  .select('id, email, full_name');

// SQL: SELECT id, email, full_name FROM users

Select with alias¶

const { data, error } = await supabase
  .from('users')
  .select('id, email, name:full_name');

// Returns: { id, email, name }

INSERT - Create Data¶

Insert single row¶

const { data, error } = await supabase
  .from('users')
  .insert({
    email: 'user@example.com',
    full_name: 'John Doe',
  })
  .select();  // Return inserted row

// SQL: INSERT INTO users (email, full_name)
//      VALUES ('user@example.com', 'John Doe')
//      RETURNING *

Insert multiple rows¶

const { data, error } = await supabase
  .from('users')
  .insert([
    { email: 'user1@example.com', full_name: 'User 1' },
    { email: 'user2@example.com', full_name: 'User 2' },
    { email: 'user3@example.com', full_name: 'User 3' },
  ])
  .select();

UPDATE - Modify Data¶

Update single row¶

const { data, error } = await supabase
  .from('users')
  .update({ full_name: 'Jane Doe' })
  .eq('id', 'user-id-here')
  .select();

// SQL: UPDATE users SET full_name = 'Jane Doe'
//      WHERE id = 'user-id-here'
//      RETURNING *

Update multiple rows¶

const { data, error } = await supabase
  .from('users')
  .update({ is_active: true })
  .eq('role', 'admin')
  .select();

// Updates all admins

Upsert (Insert or Update)¶

const { data, error } = await supabase
  .from('users')
  .upsert({
    id: 'existing-or-new-id',
    email: 'user@example.com',
    full_name: 'Updated Name',
  })
  .select();

// Insert if not exists, Update if exists

DELETE - Remove Data¶

Delete single row¶

const { error } = await supabase
  .from('users')
  .delete()
  .eq('id', 'user-id-here');

// SQL: DELETE FROM users WHERE id = 'user-id-here'

Delete multiple rows¶

const { error } = await supabase
  .from('users')
  .delete()
  .eq('is_active', false);

// Delete all inactive users

Soft delete pattern¶

// Instead of deleting, update deleted_at
const { error } = await supabase
  .from('users')
  .update({ deleted_at: new Date().toISOString() })
  .eq('id', 'user-id-here');

Error Handling¶

Error structure¶

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

if (error) {
  console.error('Error:', error.message);
  console.error('Code:', error.code);
  console.error('Details:', error.details);
  console.error('Hint:', error.hint);
}

Common errors¶

Code	Meaning
`PGRST116`	No rows returned (single() mode)
`23505`	Unique constraint violation
`23503`	Foreign key violation
`42501`	RLS policy violation
`42P01`	Table doesn't exist

Error handling pattern¶

async function getUser(id: string) {
  const { data, error } = await supabase
    .from('users')
    .select('*')
    .eq('id', id)
    .single();

  if (error) {
    if (error.code === 'PGRST116') {
      return { user: null, error: 'User not found' };
    }
    throw new Error(error.message);
  }

  return { user: data, error: null };
}

Type Safety¶

Với generated types¶

import type { Database } from '@/types/supabase';

type User = Database['public']['Tables']['users']['Row'];
type NewUser = Database['public']['Tables']['users']['Insert'];
type UpdateUser = Database['public']['Tables']['users']['Update'];

// Insert với type safety
const newUser: NewUser = {
  email: 'user@example.com',
  full_name: 'John Doe',
};

const { data, error } = await supabase
  .from('users')
  .insert(newUser)
  .select()
  .single();

// data is typed as User

Count Records¶

Get count only¶

const { count, error } = await supabase
  .from('users')
  .select('*', { count: 'exact', head: true });

// count = number of rows, no data returned

Get count with data¶

const { data, count, error } = await supabase
  .from('users')
  .select('*', { count: 'exact' });

// Both data and count returned

Response Modes¶

Single row¶

const { data, error } = await supabase
  .from('users')
  .select('*')
  .eq('id', 'user-id')
  .single();  // Error if 0 or >1 rows

// data is User (not User[])

Maybe single¶

const { data, error } = await supabase
  .from('users')
  .select('*')
  .eq('id', 'user-id')
  .maybeSingle();  // Returns null if no row

// data is User | null

Hands-on Exercise¶

CRUD operations¶

// 1. Create a task
const { data: newTask } = await supabase
  .from('tasks')
  .insert({
    title: 'Learn Supabase',
    completed: false,
  })
  .select()
  .single();

console.log('Created:', newTask);

// 2. Read all tasks
const { data: allTasks } = await supabase
  .from('tasks')
  .select('*');

console.log('All tasks:', allTasks);

// 3. Update task
const { data: updatedTask } = await supabase
  .from('tasks')
  .update({ completed: true })
  .eq('id', newTask.id)
  .select()
  .single();

console.log('Updated:', updatedTask);

// 4. Delete task
const { error } = await supabase
  .from('tasks')
  .delete()
  .eq('id', newTask.id);

console.log('Deleted:', !error);

Tổng kết¶

CRUD Methods¶

Method	SQL	Usage
`.select()`	SELECT	Read data
`.insert()`	INSERT	Create data
`.update()`	UPDATE	Modify data
`.upsert()`	INSERT ON CONFLICT	Create or update
`.delete()`	DELETE	Remove data

Best Practices¶

Always use .select() after insert/update to get returned data
Use .single() when expecting exactly one row
Use generated types for type safety
Handle errors properly
Use soft delete for important data

Q&A¶

Có use case nào cần raw SQL không?
Error handling như thế nào trong app của bạn?
Có cần count feature không?