Supabase Database Migrations: Schema Management Guide

# Install Supabase CLI
npm install -g supabase

# Login to Supabase
supabase login

# Initialize Supabase in project
cd your-project
supabase init

# This creates:
# supabase/
#   config.toml       # Project configuration
#   seed.sql          # Seed data
#   migrations/       # Migration files
#   functions/        # Edge Functions

# Link to cloud project (optional)
supabase link --project-ref your-project-ref

# Check status
supabase status

# Create new migration
supabase migration new create_posts_table

# This creates:
# supabase/migrations/20260125120000_create_posts_table.sql
# Timestamp ensures ordering

# Generate migration from schema changes
supabase db diff -f create_posts_table

# This compares local schema with remote and generates SQL

# Create multiple migrations
supabase migration new add_comments
supabase migration new add_user_profiles
supabase migration new add_rls_policies

# Migrations are applied in chronological order by timestamp

-- supabase/migrations/20260125120000_create_posts_table.sql

-- Create posts table
create table if not exists posts (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade not null,
  title text not null,
  content text,
  published boolean default false,
  created_at timestamp with time zone default now() not null,
  updated_at timestamp with time zone default now() not null
);

-- Create index for faster queries
create index posts_user_id_idx on posts(user_id);
create index posts_created_at_idx on posts(created_at desc);

-- Enable Row Level Security
alter table posts enable row level security;

-- Create RLS policies
create policy "Users can view published posts"
  on posts for select
  using ( published = true or auth.uid() = user_id );

create policy "Users can create their own posts"
  on posts for insert
  with check ( auth.uid() = user_id );

create policy "Users can update their own posts"
  on posts for update
  using ( auth.uid() = user_id );

create policy "Users can delete their own posts"
  on posts for delete
  using ( auth.uid() = user_id );

-- Enable real-time
alter publication supabase_realtime add table posts;

-- supabase/migrations/20260125130000_add_posts_metadata.sql

-- Add new columns
alter table posts
  add column if not exists slug text unique,
  add column if not exists views integer default 0,
  add column if not exists featured boolean default false;

-- Create index for slug lookups
create index if not exists posts_slug_idx on posts(slug);

-- Update existing data
update posts set slug = lower(replace(title, ' ', '-')) where slug is null;

-- Add constraint after data migration
alter table posts alter column slug set not null;

-- supabase/migrations/20260125140000_add_categories.sql

-- Create categories table
create table if not exists categories (
  id uuid default gen_random_uuid() primary key,
  name text unique not null,
  slug text unique not null,
  created_at timestamp with time zone default now()
);

-- Add category reference to posts
alter table posts
  add column if not exists category_id uuid references categories(id);

create index posts_category_id_idx on posts(category_id);

-- Insert default categories
insert into categories (name, slug) values
  ('Technology', 'technology'),
  ('Tutorial', 'tutorial'),
  ('News', 'news')
  on conflict do nothing;

# Start local database
supabase start

# Apply all pending migrations locally
supabase db reset

# This:
# 1. Drops local database
# 2. Recreates from scratch
# 3. Applies all migrations in order
# 4. Runs seed.sql

# Apply migrations to cloud/production
supabase db push

# This applies all local migrations to linked cloud project

# Check migration status
supabase migration list

# Output shows:
# ✓ 20260125120000_create_posts_table.sql
# ✓ 20260125130000_add_posts_metadata.sql
# ✓ 20260125140000_add_categories.sql
# ✗ 20260125150000_add_comments.sql (pending)

# Apply specific migration
supabase db execute --file supabase/migrations/20260125150000_add_comments.sql

-- supabase/seed.sql
-- Runs after migrations on 'supabase db reset'

-- Create test users (for local development only)
insert into auth.users (id, email)
values 
  ('550e8400-e29b-41d4-a716-446655440000', '[email protected]'),
  ('660e8400-e29b-41d4-a716-446655440000', '[email protected]')
  on conflict do nothing;

-- Insert sample categories
insert into categories (name, slug) values
  ('Development', 'development'),
  ('Design', 'design')
  on conflict (name) do nothing;

-- Insert sample posts
insert into posts (user_id, title, content, slug, published, category_id)
select
  '550e8400-e29b-41d4-a716-446655440000',
  'Getting Started with Supabase',
  'This is a sample post about Supabase...',
  'getting-started-with-supabase',
  true,
  (select id from categories where slug = 'development')
where not exists (select 1 from posts where slug = 'getting-started-with-supabase');

-- Seed more sample data
insert into posts (user_id, title, content, slug, published)
select
  '660e8400-e29b-41d4-a716-446655440000',
  'Draft Post',
  'This is a draft...',
  'draft-post',
  false
where not exists (select 1 from posts where slug = 'draft-post');

-- Create down migration for rollback
-- supabase/migrations/20260125150000_add_comments.sql

-- Up migration
create table if not exists comments (
  id uuid default gen_random_uuid() primary key,
  post_id uuid references posts(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  content text not null,
  created_at timestamp with time zone default now()
);

-- To rollback, create reverse migration:
-- supabase/migrations/20260125160000_remove_comments.sql

drop table if exists comments cascade;

-- Alternative: Use conditional logic
-- Check if table exists before creating
-- Use IF EXISTS when dropping

-- Manual rollback via SQL Editor
-- 1. Go to Supabase Dashboard
-- 2. SQL Editor
-- 3. Execute DROP statements
-- 4. Remove migration file locally

# Reset to specific migration (local only)
supabase db reset --version 20260125130000

# Developer A creates migration
supabase migration new add_likes
# Edit migration file
git add supabase/migrations/
git commit -m "Add likes table"
git push

# Developer B pulls changes
git pull
supabase db reset  # Apply new migrations locally

# Handling conflicts:
# If two devs create migrations simultaneously,
# timestamps determine order

# Developer A: 20260125120000_add_feature_a.sql
# Developer B: 20260125120001_add_feature_b.sql
# Both apply in chronological order

# If migrations conflict (e.g., both modify same column):
# 1. Coordinate before creating migrations
# 2. Merge conflicts in Git
# 3. Test combined migrations locally
# 4. Create new migration to resolve conflicts

# Best practices:
# - Pull before creating new migrations
# - Test migrations locally before pushing
# - Keep migrations focused and small
# - Document breaking changes

# .github/workflows/deploy.yml

name: Deploy Database Migrations

on:
  push:
    branches:
      - main

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Setup Supabase CLI
        run: npm install -g supabase

      - name: Link to Supabase 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_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}

# Add secrets in GitHub repo settings:
# - SUPABASE_PROJECT_REF
# - SUPABASE_ACCESS_TOKEN