Database Migrations¶
Migration Basics¶
Migrations là cách version control cho database schema.
┌─────────────────────────────────────────────────────────────┐
│ MIGRATION FLOW │
├─────────────────────────────────────────────────────────────┤
│ │
│ Migration 001 Migration 002 Migration 003 │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ Create │───▶│ Add │───▶│ Create │ │
│ │ users │ │ email │ │ posts │ │
│ └──────────┘ └──────────┘ └──────────┘ │
│ │
│ Applied in order, tracked in schema_migrations table │
└─────────────────────────────────────────────────────────────┘
Creating Migrations¶
Using Supabase CLI¶
# Create new migration
supabase migration new create_users_table
# Output: Created migration supabase/migrations/20240115120000_create_users_table.sql
Migration File Naming¶
supabase/migrations/
├── 20240115100000_create_users_table.sql
├── 20240115110000_add_email_to_users.sql
├── 20240115120000_create_posts_table.sql
└── 20240115130000_add_status_to_posts.sql
Format: YYYYMMDDHHMMSS_description.sql
Writing Migrations¶
Create Table¶
-- supabase/migrations/20240115100000_create_users.sql
CREATE TABLE public.profiles (
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
username TEXT UNIQUE,
full_name TEXT,
avatar_url TEXT,
bio TEXT,
website TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Enable RLS
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
-- Policies
CREATE POLICY "Public profiles are viewable by everyone"
ON public.profiles FOR SELECT
USING (true);
CREATE POLICY "Users can update own profile"
ON public.profiles FOR UPDATE
USING (auth.uid() = id);
-- Trigger for new user
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO public.profiles (id, full_name, avatar_url)
VALUES (
NEW.id,
NEW.raw_user_meta_data->>'full_name',
NEW.raw_user_meta_data->>'avatar_url'
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.handle_new_user();
Alter Table¶
-- supabase/migrations/20240115110000_add_fields_to_profiles.sql
-- Add new columns
ALTER TABLE public.profiles
ADD COLUMN phone TEXT,
ADD COLUMN location TEXT,
ADD COLUMN timezone TEXT DEFAULT 'UTC';
-- Add constraint
ALTER TABLE public.profiles
ADD CONSTRAINT valid_phone CHECK (phone ~ '^\+?[0-9]{10,15}$');
-- Create index
CREATE INDEX idx_profiles_username ON public.profiles(username);
Drop/Rename¶
-- supabase/migrations/20240115120000_refactor_profiles.sql
-- Rename column
ALTER TABLE public.profiles
RENAME COLUMN full_name TO display_name;
-- Drop column
ALTER TABLE public.profiles
DROP COLUMN IF EXISTS bio;
-- Drop table
DROP TABLE IF EXISTS public.old_table CASCADE;
Running Migrations¶
Local Development¶
# Reset database and apply all migrations
supabase db reset
# This will:
# 1. Drop all data
# 2. Apply all migrations in order
# 3. Run seed.sql
Push to Remote¶
# Link to project (one time)
supabase link --project-ref your-project-ref
# Push migrations
supabase db push
# Check what will be applied
supabase db push --dry-run
Check Status¶
Seed Data¶
seed.sql¶
-- supabase/seed.sql
-- Insert test data (only runs on db reset)
INSERT INTO public.categories (id, name, slug) VALUES
('11111111-1111-1111-1111-111111111111', 'Technology', 'technology'),
('22222222-2222-2222-2222-222222222222', 'Design', 'design'),
('33333333-3333-3333-3333-333333333333', 'Business', 'business');
-- Insert test users (with known IDs for testing)
-- Note: Auth users should be created via Auth API, not directly
-- Insert test posts
INSERT INTO public.posts (title, slug, content, category_id, status) VALUES
('Getting Started', 'getting-started', 'Welcome to our blog...', '11111111-1111-1111-1111-111111111111', 'published'),
('Design Principles', 'design-principles', 'Good design is...', '22222222-2222-2222-2222-222222222222', 'published');
Safe Migration Practices¶
✅ Safe Operations¶
-- Add nullable column
ALTER TABLE users ADD COLUMN phone TEXT;
-- Add column with default
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';
-- Create index concurrently (no lock)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Add foreign key (validate later)
ALTER TABLE posts
ADD CONSTRAINT fk_author
FOREIGN KEY (author_id)
REFERENCES users(id)
NOT VALID;
-- Validate separately
ALTER TABLE posts VALIDATE CONSTRAINT fk_author;
❌ Risky Operations¶
-- Adding NOT NULL without default (fails if data exists)
ALTER TABLE users ADD COLUMN phone TEXT NOT NULL;
-- Changing column type (may fail or lose data)
ALTER TABLE users ALTER COLUMN age TYPE TEXT;
-- Dropping column with data
ALTER TABLE users DROP COLUMN important_data;
Migration Patterns¶
Adding NOT NULL Column¶
-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN phone TEXT;
-- Step 2: Backfill data
UPDATE users SET phone = 'unknown' WHERE phone IS NULL;
-- Step 3: Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
Renaming Column (Zero Downtime)¶
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN display_name TEXT;
-- Step 2: Copy data
UPDATE users SET display_name = full_name;
-- Step 3: (Deploy code that uses new column)
-- Step 4: Drop old column (in next migration)
ALTER TABLE users DROP COLUMN full_name;
Adding Enum Value¶
-- Create type if not exists
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'task_status') THEN
CREATE TYPE task_status AS ENUM ('todo', 'in_progress', 'done');
END IF;
END
$$;
-- Add new value to existing enum
ALTER TYPE task_status ADD VALUE IF NOT EXISTS 'archived';
Rollback Strategies¶
Manual Rollback Migration¶
-- supabase/migrations/20240115130000_rollback_changes.sql
-- Reverse the changes from previous migration
DROP INDEX IF EXISTS idx_users_phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;
Backup Before Risky Migration¶
# Backup production data
pg_dump -h db.xxx.supabase.co -U postgres -d postgres > backup_$(date +%Y%m%d).sql
# Restore if needed
psql -h db.xxx.supabase.co -U postgres -d postgres < backup_20240115.sql
Point-in-Time Recovery (Pro Plan)¶
CI/CD Integration¶
GitHub Action¶
# .github/workflows/migrate.yml
name: Database Migration
on:
push:
branches: [main]
paths:
- 'supabase/migrations/**'
jobs:
migrate:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: supabase/setup-cli@v1
with:
version: latest
- name: Link to Production
run: supabase link --project-ref ${{ vars.SUPABASE_PROJECT_REF }}
env:
SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
- name: Push Migrations
run: supabase db push
env:
SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
Debugging Migrations¶
Common Errors¶
| Error | Cause | Solution |
|---|---|---|
relation already exists |
Table/index exists | Use IF NOT EXISTS |
column does not exist |
Wrong column name | Check schema |
violates foreign key |
Invalid reference | Fix data first |
permission denied |
RLS blocking | Use service role |
View Migration History¶
-- Check applied migrations
SELECT * FROM supabase_migrations.schema_migrations
ORDER BY version DESC;
Reset Stuck Migration¶
# Mark migration as applied (dangerous!)
supabase migration repair --status applied 20240115120000
# Mark as reverted
supabase migration repair --status reverted 20240115120000
Tổng kết¶
Migration Commands¶
supabase migration new <name> # Create migration
supabase db reset # Reset + apply all
supabase db push # Push to remote
supabase db diff # Show differences
supabase migration list # List migrations
Best Practices¶
- One concern per migration - Keep focused
- Test locally first -
supabase db reset - Use transactions - Wrap in BEGIN/COMMIT
- Backup before risky changes
- Review in PR - Migrations are code
Q&A¶
- Đã dùng migration tools khác chưa?
- Có concerns về data migration?
- Questions về rollback?