Bab 8: Database dan Backend dengan Supabase

Kompetensi Inti Bab 8

• Memahami arsitektur full-stack dan interaksi frontend-backend-database.
• Merancang skema database relasional dengan normalization, constraints, dan indexes.
• Mengimplementasikan Row Level Security (RLS) untuk access control di database level.
• Membangun Full-Stack Task Manager dengan Supabase + Next.js.
• Mengevaluasi output AI menggunakan Schema Review 5 Dimensi.

Dari Frontend-Only ke Full-Stack

Keterbatasan Frontend-Only

• Data tidak accessible dari device lain
• Tidak ada collaborative editing atau multi-user sync
• Tidak ada central authority untuk data consistency
• Security model terbatas—semua data visible di client

Frontend-Only → Full-Stack

Single source of truth. Data accessible dari semua devices. Real-time sync. Secure access control.

Mengapa Supabase?

Mental Model: Database Relasional dan SQL

Sebelum coding, mari bangun pemahaman konseptual tentang relational database dan SQL.

• Dari spreadsheet ke database: evolusi data storage
• Tables, primary keys, dan foreign keys
• Constraints untuk data integrity
• ACID transactions dan consistency guarantees
• SQL sebagai declarative language

Masalah dengan Spreadsheet untuk Data Management

Tables dan Primary Key

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,  -- Auto-increment, unique
  title TEXT NOT NULL,
  completed BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

• Table: Entity type (Tasks, Users). Columns = attributes, Rows = instances.
• Primary Key: Unique identifier untuk setiap row. Guarantee tidak ada duplikat.
• Data Types: BIGSERIAL (auto-increment integer), TEXT, BOOLEAN, TIMESTAMPTZ.
• Constraints: NOT NULL, DEFAULT value.

Foreign Key: Relationships Antar-Tables

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  name TEXT
);

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE
);

• REFERENCES users(id) = foreign key constraint. Nilai di user_id harus exist di users.id.
• ON DELETE CASCADE = jika user dihapus, semua tasks miliknya otomatis dihapus.
• Relationship: One-to-many. Satu user punya banyak tasks, satu task punya satu user.

Constraints: Data Integrity Enforcement

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL CHECK(length(title) > 0),
  completed BOOLEAN DEFAULT false,
  priority INTEGER CHECK(priority BETWEEN 1 AND 5),
  user_id UUID REFERENCES users(id) NOT NULL
);

Database reject insert/update yang violate constraint. Ini mencegah garbage data.

ACID Transactions

BEGIN;
  INSERT INTO tasks (title, user_id) VALUES ('Task A', '123');
  INSERT INTO tasks (title, user_id) VALUES ('Task B', '999');  -- Error!
ROLLBACK;  -- 'Task A' tidak masuk database

SQL: Declarative Language untuk Database

Anda describe apa yang diinginkan; database engine yang tentukan bagaimana mendapatkannya secara optimal.

4 Kategori SQL

CREATE TABLE tasks (...)
ALTER TABLE tasks ADD COLUMN priority INT
DROP TABLE tasks

INSERT INTO tasks VALUES (...)
UPDATE tasks SET completed = true
DELETE FROM tasks WHERE id = 1

SELECT * FROM tasks WHERE completed = false
SELECT users.name, tasks.title
FROM tasks JOIN users ON tasks.user_id = users.id

GRANT SELECT ON tasks TO user_role
CREATE POLICY "..." ON tasks
FOR SELECT USING (auth.uid() = user_id)

Database sebagai API dengan Strong Contract

POST /api/tasks
{ "title": "Buy milk" }

// Response bisa apa saja

INSERT INTO tasks (title)
VALUES ('Buy milk');

-- Guaranteed: types valid,
-- constraints satisfied,
-- transaction atomic

Hands-On: Setup Supabase Project

• Buka supabase.com dan sign up (gratis)
• Klik "New Project" di dashboard
• Name: task-manager, generate strong password, pilih region terdekat
• Pricing Plan: Free (cukup untuk development dan small apps)
• Wait 1-2 menit saat Supabase provision PostgreSQL database Anda di cloud

✓ Anda sekarang punya PostgreSQL database yang running di cloud dengan auto-generated API endpoints.

Explore Dashboard Sections

Create Tasks Table

Navigate ke SQL Editor di sidebar, copy-paste SQL ini, dan klik "Run":

-- Create tasks table
CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL CHECK(length(title) > 0),
  completed BOOLEAN DEFAULT false,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable Row Level Security
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

✓ Success! Navigate ke Table Editor → tasks untuk lihat table kosong Anda.

Insert Data Pertama Anda

INSERT INTO tasks (title, user_id)
VALUES 
  ('Setup Supabase project', auth.uid()),
  ('Create database schema', auth.uid()),
  ('Integrate with Next.js', auth.uid());

Catatan: auth.uid() akan return NULL jika belum ada authenticated user. Untuk testing, bisa hardcode UUID atau tunggu implementasi auth di Bab 9.

• id auto-generated oleh BIGSERIAL
• completed default false
• created_at auto-set ke NOW()

Query Data dengan SELECT

-- Get all tasks
SELECT * FROM tasks;

-- Get incomplete tasks only
SELECT id, title, created_at
FROM tasks
WHERE completed = false
ORDER BY created_at DESC;

-- Count completed tasks
SELECT COUNT(*) as total_completed
FROM tasks
WHERE completed = true;

• WHERE untuk filter rows
• ORDER BY untuk sorting
• COUNT(*) untuk aggregate

Modify dan Hapus Data

-- Mark task as completed
UPDATE tasks
SET completed = true
WHERE id = 1;

-- Update multiple columns
UPDATE tasks
SET 
  title = 'Updated title',
  completed = true
WHERE id = 2;

-- Delete task
DELETE FROM tasks
WHERE id = 3;

⚠️ Hati-hati: UPDATE atau DELETE tanpa WHERE akan affect semua rows!

JOIN: Combine Data dari Multiple Tables

-- Get tasks with user information
SELECT 
  users.name,
  users.email,
  tasks.title,
  tasks.completed,
  tasks.created_at
FROM tasks
JOIN auth.users ON tasks.user_id = users.id
WHERE tasks.completed = false
ORDER BY tasks.created_at DESC;

• JOIN menggabungkan rows dari dua tables berdasarkan condition
• ON tasks.user_id = users.id adalah matching condition
• Result: satu row per task dengan data user yang terkait

Row Level Security (RLS)

• RLS adalah PostgreSQL feature untuk restrict akses per-row berdasarkan user identity
• Policies di-define di database; aplikasi tidak bisa bypass
• Lebih aman dari security checks di client atau server code yang bisa di-skip
• Supabase auto-integrate dengan authentication system (auth.uid())

Create Policy: Users Can Only See Their Own Tasks

-- Policy untuk SELECT
CREATE POLICY "Users can view their own tasks"
ON tasks
FOR SELECT
USING (auth.uid() = user_id);

-- Policy untuk INSERT
CREATE POLICY "Users can create their own tasks"
ON tasks
FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Policy untuk UPDATE
CREATE POLICY "Users can update their own tasks"
ON tasks
FOR UPDATE
USING (auth.uid() = user_id);

Bagaimana RLS Bekerja

• Setiap query otomatis di-filter berdasarkan policy
• User tidak bisa "hack" query untuk lihat data orang lain
• Bahkan jika client code compromised, database tetap aman
• auth.uid() return ID dari authenticated user (dari JWT token)

✓ Defense in depth: security di database level, bukan hanya di app logic.

Integrasi Supabase dengan Next.js

Install Supabase client library:

npm install @supabase/supabase-js

Create lib/supabase.js:

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

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
const supabaseKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY;

export const supabase = createClient(supabaseUrl, supabaseKey);

Setup .env.local

Buat .env.local di root project (jangan commit ke git!):

NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key

• URL dan key bisa diambil dari Supabase Dashboard → Settings → API
• NEXT_PUBLIC_ prefix membuat env var accessible di client-side
• anon key safely exposed di client karena protected by RLS
• Restart dev server setelah update .env

Fetch Tasks dari Database

import { supabase } from '@/lib/supabase';
import { useEffect, useState } from 'react';

export default function TaskList() {
  const [tasks, setTasks] = useState([]);

  useEffect(() => {
    async function fetchTasks() {
      const { data, error } = await supabase
        .from('tasks')
        .select('*')
        .order('created_at', { ascending: false });

      if (error) console.error(error);
      else setTasks(data);
    }
    fetchTasks();
  }, []);

  return (
    <ul>
      {tasks.map(task => <li key={task.id}>{task.title}</li>)}
    </ul>
  );
}

Insert, Update, Delete dari React

// INSERT
const { data, error } = await supabase
  .from('tasks')
  .insert({ title: 'New task', user_id: userId });

// UPDATE
await supabase
  .from('tasks')
  .update({ completed: true })
  .eq('id', taskId);

// DELETE
await supabase
  .from('tasks')
  .delete()
  .eq('id', taskId);

• .from('tasks') select table
• .eq('id', value) adalah WHERE condition
• Always check error and handle gracefully

Real-time Subscriptions

• Supabase broadcast database changes ke subscribed clients via WebSocket
• INSERT, UPDATE, DELETE events bisa di-listen real-time
• Tidak perlu polling atau manual refresh
• Ideal untuk chat apps, collaborative tools, live dashboards

Subscribe ke Database Changes

useEffect(() => {
  const channel = supabase
    .channel('tasks-channel')
    .on(
      'postgres_changes',
      { event: '*', schema: 'public', table: 'tasks' },
      (payload) => {
        console.log('Change received!', payload);
        // Update local state based on event type
        if (payload.eventType === 'INSERT') {
          setTasks(prev => [payload.new, ...prev]);
        }
      }
    )
    .subscribe();

  return () => { supabase.removeChannel(channel); };
}, []);

Optimistic UI Updates

async function toggleTask(taskId) {
  // 1. Optimistically update UI immediately
  setTasks(prev => prev.map(t => 
    t.id === taskId ? { ...t, completed: !t.completed } : t
  ));

  // 2. Send request to database
  const { error } = await supabase
    .from('tasks')
    .update({ completed: true })
    .eq('id', taskId);

  // 3. Rollback if error
  if (error) {
    setTasks(prev => prev.map(t => 
      t.id === taskId ? { ...t, completed: !t.completed } : t
    ));
    alert('Update failed');
  }
}

AI Integration untuk Database Work

• AI boleh generate SQL queries, schema design, dan RLS policies
• WAJIB review dengan Schema Review 5 Dimensi sebelum execute
• Schema mistakes EXPENSIVE di production—migration bisa data loss
• AI sering lupa constraints, indexes, atau cascade implications
• Test queries di SQL Editor dulu sebelum integrate ke code

Checklist Wajib untuk Database Design

Schema Review Format

## Schema Review: tasks table

### Normalization: ✅
User data dipisah ke tabel terpisah (auth.users).
Tidak ada redundant fields.

### Relationships: ✅
Foreign key ke auth.users dengan ON DELETE CASCADE appropriate
(delete user → delete tasks miliknya).

### Data Types: ✅
BIGSERIAL untuk id (support hingga 9 quintillion rows).
TEXT untuk title (unlimited length, appropriate untuk user input).
TIMESTAMPTZ untuk timezone-aware timestamps.

### Constraints: ⚠️
Missing: priority validation jika ada kolom priority.
Consider: ADD CHECK (priority BETWEEN 1 AND 5).

### Indexing: ⚠️
Missing index on user_id untuk fast queries per user.
Recommendation: CREATE INDEX idx_tasks_user_id ON tasks(user_id);

### Keputusan: REQUEST CHANGES (add index)

7 Dimensi untuk API Functions

Database Anti-Patterns yang Sering Muncul dari AI

Full-Stack Task Manager Architecture

• Frontend: Next.js App Router, React hooks, Tailwind CSS
• Backend: Supabase auto-generated API dari schema
• Database: PostgreSQL dengan RLS policies
• Real-time: WebSocket subscriptions untuk live updates

Task Manager Feature Checklist

Strategi Testing Full-Stack App

• Database: Test RLS policies dengan multiple user contexts di SQL Editor
• API: Verify CRUD operations via Supabase client logs
• Frontend: Test UI flows, loading states, error handling
• Real-time: Open app di dua browser windows, verify sync
• Edge cases: Empty states, network errors, concurrent edits

Pertanyaan Reflektif

• Gambar arsitektur full-stack dari memory. Jelaskan setiap layer dan responsibility-nya.
• Mengapa RLS lebih aman dari security checks di application code?
• Kapan menggunakan JOIN vs multiple queries? Trade-off masing-masing?
• Jika user delete account, bagaimana Anda pastikan semua data terkait juga terhapus tanpa orphan records?
• Explain bagaimana real-time subscription work di balik layar. Apa role WebSocket?
• Database schema mistake apa yang paling expensive untuk fix di production? Mengapa?

Eksplorasi & Ekstensi

Ke Bab Selanjutnya

• Anda sekarang punya persistent data layer yang production-ready
• Skill SQL, schema design, dan RLS transferable ke stack manapun
• Pattern CRUD + real-time applicable di banyak jenis aplikasi

Apa yang Berubah Setelah Bab Ini?

• Dari localStorage ke PostgreSQL: data accessible dari anywhere, anytime
• Dari client-side security ke RLS: defense in depth di database level
• Dari static content ke dynamic queries: user-generated content sustainable
• Dari isolated app ke collaborative platform: real-time sync antar users

Anda sekarang seorang full-stack developer. Frontend, backend, dan database—semua dalam satu stack yang cohesive.