Supabase迁移管理 #

数据库迁移
├── 版本控制数据库结构
├── 跟踪Schema变化
├── 可重复执行
├── 团队协作
└── 环境同步

supabase/
└── migrations/
    ├── 20240101000000_create_users_table.sql
    ├── 20240102000000_create_posts_table.sql
    └── 20240103000000_add_user_roles.sql

# 创建迁移文件
supabase migration new create_products_table

# 生成的文件
# supabase/migrations/20240115123456_create_products_table.sql

-- supabase/migrations/20240115123456_create_products_table.sql

-- 创建表
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    category TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建索引
CREATE INDEX idx_products_category ON products(category);

-- 启用RLS
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- 创建策略
CREATE POLICY "Public read access"
ON products FOR SELECT
USING (true);

CREATE POLICY "Authenticated users can insert"
ON products FOR INSERT
WITH CHECK (auth.role() = 'authenticated');

迁移编写建议
├── 每个迁移只做一件事
├── 使用幂等操作
├── 添加注释说明
├── 考虑回滚
└── 测试后提交

# 应用所有未应用的迁移
supabase db push

# 输出
# Applying migration 20240115123456_create_products_table.sql...
# Migration complete.

# 应用到链接的远程项目
supabase db push --linked

# 强制应用（跳过确认）
supabase db push --linked --force

# 查看迁移列表
supabase migration list

# 输出
# Local      Remote
# 20240101000000_create_users_table.sql
# 20240102000000_create_posts_table.sql
# 20240103000000_add_user_roles.sql

# 重置本地数据库并重新应用迁移
supabase db reset

# 这将:
# 1. 删除所有数据
# 2. 重新创建数据库
# 3. 应用所有迁移
# 4. 运行种子数据

# 修复迁移版本
supabase migration repair --status reverted 20240115123456

# 比较本地和远程差异
supabase db diff

# 生成迁移文件
supabase db diff --schema public --use-migra -f migration_name

-- supabase/seed.sql

-- 插入测试数据
INSERT INTO products (name, price, category) VALUES
    ('Product 1', 19.99, 'electronics'),
    ('Product 2', 29.99, 'electronics'),
    ('Product 3', 39.99, 'clothing');

INSERT INTO users (email, name) VALUES
    ('test@example.com', 'Test User');

# 重置数据库时自动应用种子数据
supabase db reset

# 或手动执行
supabase db execute -f supabase/seed.sql

-- migrations/20240116000000_add_description_to_products.sql

ALTER TABLE products 
ADD COLUMN description TEXT;

-- 添加注释
COMMENT ON COLUMN products.description IS 'Product description';

-- migrations/20240117000000_create_orders_table.sql

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) NOT NULL,
    total NUMERIC(10, 2) NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT REFERENCES products(id),
    quantity INTEGER NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);

-- migrations/20240118000000_migrate_user_names.sql

-- 添加新列
ALTER TABLE users ADD COLUMN full_name TEXT;

-- 迁移数据
UPDATE users 
SET full_name = CONCAT(first_name, ' ', last_name);

-- 删除旧列
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

-- migrations/20240119000000_create_product_stats_view.sql

CREATE VIEW product_stats AS
SELECT 
    p.id,
    p.name,
    p.category,
    COUNT(oi.id) as order_count,
    SUM(oi.quantity) as total_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.category;

开发流程
├── 1. 拉取最新代码
├── 2. 应用新迁移
│   └── supabase db push
├── 3. 开发新功能
├── 4. 创建迁移
│   └── supabase migration new xxx
├── 5. 测试迁移
│   └── supabase db reset
├── 6. 提交代码
└── 7. CI/CD自动部署

# 如果远程有新迁移
# 1. 拉取最新迁移文件
git pull

# 2. 重置本地数据库
supabase db reset

# 3. 继续开发

# .github/workflows/supabase-migrate.yml
name: Supabase Migrate

on:
  push:
    branches: [main]

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      
      - uses: supabase/setup-cli@v1
        with:
          version: latest
      
      - name: Link project
        run: supabase link --project-ref ${{ secrets.SUPABASE_PROJECT_REF }}
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
      
      - name: Push migrations
        run: supabase db push
        env:
          SUPABASE_DB_PASSWORD: ${{ secrets.SUPABASE_DB_PASSWORD }}

命名规范
├── 使用描述性名称
│   └── create_users_table
│   └── add_email_to_users
│   └── create_orders_index
│
├── 按功能分组
│   └── users/001_create_table.sql
│   └── users/002_add_email.sql
│
└── 避免修改已提交的迁移

# 将迁移文件纳入Git版本控制
git add supabase/migrations/
git commit -m "Add products table migration"
git push