Supabase Migrations & CLI

# Install Supabase CLI
npm install -D supabase

# Initialize Supabase in your project
npx supabase init

# Start local Supabase (requires Docker)
npx supabase start

# Link to your remote project
npx supabase link --project-ref your-project-id

# This creates the supabase/ directory:
# supabase/
# ├── config.toml        # Local configuration
# ├── migrations/        # SQL migration files
# └── seed.sql           # Seed data for local dev

# Create a new empty migration file
npx supabase migration new create_profiles_table

-- supabase/migrations/20240101000000_create_profiles_table.sql
CREATE TABLE profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username text UNIQUE NOT NULL,
  avatar_url text,
  bio text,
  created_at timestamptz DEFAULT NOW(),
  updated_at timestamptz DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Allow users to read all profiles
CREATE POLICY "Profiles are viewable by everyone"
  ON profiles FOR SELECT
  USING (true);

-- Allow users to update their own profile
CREATE POLICY "Users can update own profile"
  ON profiles FOR UPDATE
  USING (auth.uid() = id);

# Make changes in the local Supabase Studio (localhost:54323)
# Then generate a migration from the diff
npx supabase db diff -f add_posts_table

# This creates a migration file with the SQL diff
# Review the generated file before committing!

# Apply migrations to local database
npx supabase db reset  # Resets and replays all migrations + seed

# Apply migrations to remote (production/staging)
npx supabase db push

# Check migration status
npx supabase migration list

-- supabase/seed.sql
-- This runs after all migrations on 'supabase db reset'

INSERT INTO profiles (id, username, bio) VALUES
  ('d0fc4c64-a3d4-4d15-8363-a3b4f9a5b80e', 'testuser', 'A test account'),
  ('e1fc4c64-b3d4-5d15-9363-b3b4f9a5b90f', 'admin', 'Admin account');

INSERT INTO posts (title, content, user_id) VALUES
  ('First Post', 'Hello world!', 'd0fc4c64-a3d4-4d15-8363-a3b4f9a5b80e');

# .github/workflows/supabase-migrate.yml
name: Deploy Migrations
on:
  push:
    branches: [main]
    paths:
      - 'supabase/migrations/**'

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v1
      - run: supabase link --project-ref ${{ secrets.SUPABASE_PROJECT_ID }}
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
      - run: supabase db push