Supabase Database Migrations

Create, test, and deploy Supabase database migrations safely.

When to Use

• User mentions "migration", "schema change", "database"
• User wants to create/modify tables
• User asks about Supabase database structure
• User needs to add columns, indexes, or constraints
• User mentions RLS or Row Level Security

Migration Workflow

# Create new migration
npx supabase migration new <name>

# Test locally (resets local DB)
npx supabase db reset

# Push to production
npx supabase db push

# Repair if remote is out of sync
npx supabase migration repair --status applied <version>

Migration File Format

Location: supabase/migrations/ Format: YYYYMMDDHHMMSS_description.sql

Example Migration

-- supabase/migrations/20250106120000_add_user_preferences.sql

-- Add new column
ALTER TABLE users
ADD COLUMN preferences JSONB DEFAULT '{}';

-- Create index for faster queries
CREATE INDEX idx_users_preferences ON users USING GIN (preferences);

-- Add RLS policy
CREATE POLICY "Users can update own preferences"
ON users
FOR UPDATE
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

Key Rules

DO:

• Test migrations locally first with npx supabase db reset
• Use npx supabase migration repair if remote is out of sync
• Create new migrations (never modify applied ones)
• Include RLS policies in migrations
• Use descriptive migration names

DON'T:

• Modify already-applied migrations
• Push untested migrations to production
• Forget RLS policies on new tables
• Use destructive operations without backup

Common Patterns

Create Table with RLS

-- Create table
CREATE TABLE items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

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

-- Policy: Users see own items
CREATE POLICY "Users can view own items"
ON items FOR SELECT
USING (auth.uid() = user_id);

-- Policy: Users insert own items
CREATE POLICY "Users can insert own items"
ON items FOR INSERT
WITH CHECK (auth.uid() = user_id);

Add Column Safely

-- Add nullable column first
ALTER TABLE users ADD COLUMN phone TEXT;

-- Then add constraint if needed
ALTER TABLE users
ALTER COLUMN phone SET NOT NULL,
ADD CONSTRAINT phone_format CHECK (phone ~ '^\+[0-9]{10,15}$');

Debugging

# Check migration status
npx supabase migration list

# Query production database
npx supabase db query "SELECT * FROM items LIMIT 10;"

# View table structure
npx supabase db query "\d items"

Environment Setup

Ensure Supabase CLI is linked to your project:

npx supabase link --project-ref <project-id>