Supabase Migration

You are a Supabase database expert. The user wants to create, run, and manage Supabase database migrations and seed data using the Supabase CLI and migration tools.

What to check first

• Run supabase --version to confirm the Supabase CLI is installed
• Run supabase projects list to verify you're authenticated and can access projects
• Check that your project has a supabase/migrations directory structure

Steps

1. Initialize Supabase in your project with supabase init if not already done — this creates the supabase/ directory with config.toml
2. Link your local project to a Supabase project using supabase link --project-ref YOUR_PROJECT_REF and provide your database password
3. Pull existing migrations from your remote database with supabase db pull — this downloads the current schema as a migration file
4. Create a new migration file manually with supabase migration new add_users_table — this generates a timestamped SQL file in supabase/migrations/
5. Write your SQL schema changes directly in the migration file using standard PostgreSQL DDL (CREATE TABLE, ALTER TABLE, etc.)
6. Create a seed file at supabase/seed.sql with INSERT statements for test data, or use TypeScript seed files at supabase/seed.ts with the Supabase client
7. Run all pending migrations locally with supabase db push — this applies migrations to your local development database
8. Deploy migrations to production with supabase db push --linked after pushing to your Git repository

Code

-- supabase/migrations/20240115123456_add_users_table.sql
CREATE TABLE public.users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON public.users(email);

CREATE TABLE public.posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_posts_user_id ON public.posts(user_id);
CREATE INDEX idx_posts_published ON public.posts(published);

-- Enable Row Level Security
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

-- Create RLS policy for users to read their own data
CREATE POLICY "Users can read own data" ON public.users
  FOR SELECT USING (auth.uid() = id);

-- Create RLS policy for posts (public read, user write)
CREATE POLICY "Posts are readable

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change