Supabase Migrations & Rollback

Tại sao cần Migrations?

Vấn đề khi không có migrations

❌ Direct changes via Dashboard
   - Không track được history
   - Không sync được giữa environments
   - Không rollback được

❌ Manual SQL execution
   - Dễ quên bước
   - Không consistent
   - Khó reproduce

Migrations giải quyết

✅ Version controlled schema
✅ Consistent across environments
✅ Reviewable in PRs
✅ Rollback capable
✅ Automated deployment

---

Migration Workflow

┌─────────────────────────────────────────────────────────────┐
│                   MIGRATION WORKFLOW                         │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  1. LOCAL DEVELOPMENT                                       │
│     ┌──────────────────────────────────────────────┐        │
│     │  supabase migration new <name>               │        │
│     │  Edit migration SQL                          │        │
│     │  supabase db reset (test locally)            │        │
│     └──────────────────────────────────────────────┘        │
│                           │                                  │
│                           ▼                                  │
│  2. CODE REVIEW                                             │
│     ┌──────────────────────────────────────────────┐        │
│     │  git commit -m "Add users table"             │        │
│     │  Create Pull Request                         │        │
│     │  Review migration SQL                        │        │
│     └──────────────────────────────────────────────┘        │
│                           │                                  │
│                           ▼                                  │
│  3. DEPLOY TO STAGING                                       │
│     ┌──────────────────────────────────────────────┐        │
│     │  supabase db push --linked (staging)         │        │
│     │  Test in staging environment                 │        │
│     └──────────────────────────────────────────────┘        │
│                           │                                  │
│                           ▼                                  │
│  4. DEPLOY TO PRODUCTION                                    │
│     ┌──────────────────────────────────────────────┐        │
│     │  supabase db push --linked (production)      │        │
│     │  Monitor for issues                          │        │
│     └──────────────────────────────────────────────┘        │
│                                                              │
└─────────────────────────────────────────────────────────────┘

---

Creating Migrations

Tạo migration mới

# Create new migration
supabase migration new add_tasks_table

# Creates: supabase/migrations/20240115120000_add_tasks_table.sql

Migration file structure

-- supabase/migrations/20240115120000_add_tasks_table.sql

-- UP Migration (applied)
CREATE TABLE public.tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'pending',
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "Users can manage own tasks"
  ON public.tasks FOR ALL
  USING (auth.uid() = user_id);

-- Create index
CREATE INDEX idx_tasks_user_id ON public.tasks(user_id);
CREATE INDEX idx_tasks_status ON public.tasks(status);

-- Trigger for updated_at
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON public.tasks
  FOR EACH ROW
  EXECUTE FUNCTION extensions.moddatetime(updated_at);

---

Testing Migrations Locally

# Reset database and apply all migrations
supabase db reset

# This will:
# 1. Drop all tables
# 2. Apply all migrations in order
# 3. Run seed.sql

# Verify migration
supabase db diff

# Generate types after migration
supabase gen types typescript --local > types/supabase.ts

---

CI/CD Integration

GitHub Action for Migrations

# .github/workflows/migrations.yml
name: Database Migrations

on:
  push:
    branches: [staging, main]
    paths:
      - 'supabase/migrations/**'

jobs:
  migrate-staging:
    if: github.ref == 'refs/heads/staging'
    runs-on: ubuntu-latest
    environment: staging
    steps:
      - uses: actions/checkout@v4

      - name: Setup Supabase CLI
        uses: supabase/setup-cli@v1
        with:
          version: latest

      - name: Link to Staging
        run: |
          supabase link --project-ref ${{ vars.SUPABASE_PROJECT_REF }}
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}

      - name: Run Migrations
        run: supabase db push
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}

      - name: Verify Migration
        run: supabase db diff
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}

  migrate-production:
    if: github.ref == 'refs/heads/main'
    runs-on: ubuntu-latest
    environment: production
    steps:
      - uses: actions/checkout@v4

      - name: Setup Supabase CLI
        uses: supabase/setup-cli@v1

      - name: Link to Production
        run: supabase link --project-ref ${{ vars.SUPABASE_PROJECT_REF }}
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}

      - name: Run Migrations
        run: supabase db push
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}

---

Multi-environment Setup

Environment-specific configs

# Staging
supabase link --project-ref abc123staging

# Production
supabase link --project-ref xyz789production

Switching between projects

# Create config for each environment
# supabase/.env.staging
SUPABASE_PROJECT_REF=abc123staging

# supabase/.env.production
SUPABASE_PROJECT_REF=xyz789production

# Use specific environment
supabase db push --linked --project-ref $SUPABASE_PROJECT_REF

---

Rollback Strategies

Strategy 1: Reverse Migration

-- supabase/migrations/20240115130000_rollback_tasks_table.sql

-- Rollback: Drop table and related objects
DROP TRIGGER IF EXISTS set_updated_at ON public.tasks;
DROP INDEX IF EXISTS idx_tasks_user_id;
DROP INDEX IF EXISTS idx_tasks_status;
DROP POLICY IF EXISTS "Users can manage own tasks" ON public.tasks;
DROP TABLE IF EXISTS public.tasks;

# Apply rollback migration
supabase migration new rollback_tasks_table
# Edit the file with DROP statements
supabase db push

Strategy 2: Data-preserving Rollback

-- supabase/migrations/20240115130000_rollback_preserve_data.sql

-- 1. Backup data to temp table
CREATE TABLE public.tasks_backup AS SELECT * FROM public.tasks;

-- 2. Drop new table
DROP TABLE public.tasks;

-- 3. Restore to previous schema
CREATE TABLE public.tasks (
  id UUID PRIMARY KEY,
  title TEXT NOT NULL,
  -- Previous schema without new columns
);

-- 4. Restore data
INSERT INTO public.tasks SELECT id, title FROM public.tasks_backup;

-- 5. Clean up
DROP TABLE public.tasks_backup;

---

Rollback via Dashboard

Point-in-time Recovery (Paid plans)

1. Supabase Dashboard → Database → Backups
2. Select backup point
3. Restore to new project or existing

Limitations:
- Pro plan: 7 days retention
- Only available on paid plans

Manual Backup & Restore

# Backup before risky migration
pg_dump -h db.xxx.supabase.co -U postgres -d postgres > backup.sql

# Restore if needed
psql -h db.xxx.supabase.co -U postgres -d postgres < backup.sql

---

Safe Migration Practices

1. Additive Migrations First

-- ✅ SAFE: Add nullable column
ALTER TABLE users ADD COLUMN phone TEXT;

-- ❌ RISKY: Add non-null column without default
ALTER TABLE users ADD COLUMN phone TEXT NOT NULL;

-- ✅ SAFE: Add with default
ALTER TABLE users ADD COLUMN phone TEXT NOT NULL DEFAULT '';

2. Two-phase Column Removal

-- Phase 1: Deprecate (stop using in code)
-- Update code to not use 'old_column'

-- Phase 2: Remove (after code deployed)
ALTER TABLE users DROP COLUMN old_column;

3. Zero-downtime Renames

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name TEXT;

-- Step 2: Copy data
UPDATE users SET full_name = name;

-- Step 3: Update code to use full_name

-- Step 4: Drop old column
ALTER TABLE users DROP COLUMN name;

---

Migration Checklist

Before Migration

• [ ] Test locally with supabase db reset
• [ ] Review SQL for destructive operations
• [ ] Check RLS policies
• [ ] Backup production (if major change)

After Migration

• [ ] Verify with supabase db diff
• [ ] Generate new types
• [ ] Test application
• [ ] Monitor error rates

Rollback Plan

• [ ] Prepare rollback migration
• [ ] Document rollback steps
• [ ] Test rollback locally

---

Debugging Migration Issues

Check migration status

# List applied migrations
supabase migration list

# Check differences
supabase db diff

# View pending migrations
supabase db diff --use-migra

Common Errors

Error	Cause	Solution
relation already exists	Table exists	Use IF NOT EXISTS
column cannot be cast	Type mismatch	Create new column, migrate data
violates foreign key	Invalid reference	Fix data first
permission denied	RLS blocking	Use service role

---

Tổng kết

Migration Commands

# Create migration
supabase migration new <name>

# Apply locally
supabase db reset

# Push to remote
supabase db push

# Check diff
supabase db diff

# List migrations
supabase migration list

Best Practices

1. Small, focused migrations - One concern per file
2. Test locally first - supabase db reset
3. Review in PR - SQL is code
4. Additive first - Add before remove
5. Always have rollback plan - Prepare reverse migration

---

Q&A

1. Có kinh nghiệm với database migrations chưa?
2. Đã từng cần rollback migration chưa?
3. Challenges với schema changes?