supabase-migration-deep-dive

Supabase Migration Deep Dive

Overview

Supabase migrations are timestamped SQL files managed by the CLI that track schema changes across environments. This skill covers the full lifecycle — creating migrations, zero-downtime schema changes, batch backfills, versioning, rollback, and TypeScript type generation — using real CLI commands and createClient from @supabase/supabase-js.

When to use: Creating new database migrations, modifying production schemas without downtime, backfilling existing data after adding columns, managing migration history across dev/staging/production, rolling back failed migrations, or regenerating TypeScript types.

Prerequisites

• Supabase CLI installed: npm install -g supabase or npx supabase --version
• @supabase/supabase-js v2+ installed in your project
• Local Supabase running: npx supabase start
• Understanding of PostgreSQL DDL and transaction behavior

Instructions

Step 1: Create and Manage Migrations

Create each migration as a timestamped SQL file, write the DDL, test it against a local reset, then promote it through environments with db push:

npx supabase migration new add_profiles_table   # create timestamped SQL file
npx supabase migration list                      # show applied/pending status
npx supabase db reset                            # apply + seed locally (destructive)
npx supabase gen types typescript --local > lib/database.types.ts
npx supabase link --project-ref "<ref>" && npx supabase db push   # promote to remote

Write DDL that enables RLS, adds policies, indexes, and triggers in the same file so the schema is complete when applied. For the full worked migration (a profiles table with RLS policies, an email index, a signup trigger, and an updated_at trigger) plus the local-test and staging/production promotion commands, see creating migrations.

Step 2: Zero-Downtime Migration Patterns

Production schema changes must avoid locking tables. The core rules: adding a nullable column with a default is lock-free on Postgres 11+; build indexes with CREATE INDEX CONCURRENTLY in a -- supabase:disable-transaction migration; rename or retype a column in two phases (add new column, backfill, sync trigger, then drop the old one) rather than an in-place ALTER COLUMN. Example of the safe column-add:

-- Nullable column with a default does NOT lock the table in Postgres 11+
ALTER TABLE public.orders ADD COLUMN status text DEFAULT 'pending';
-- Then, in a separate -- supabase:disable-transaction migration:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON public.orders(status);

For the complete set — two-phase column rename with a sync trigger, safe type change via add-backfill-swap, and an SDK health-check that samples query latency during the migration — see zero-downtime patterns.

Step 3: Data Backfill, Versioning, and Rollback

See data backfill, versioning, and rollback for batch backfill patterns with the SDK, schema versioning across environments, three rollback strategies (compensating migration, repair, feature flags), and type regeneration after migrations.

Output

This skill produces:

• Migration creation workflow — npx supabase migration new with descriptive SQL files and local testing
• Zero-downtime patterns — safe column additions, two-phase renames, concurrent index creation
• Batch backfill — SDK-based row-by-row updates with progress logging and rate limiting
• Schema versioning — supabase migration list and db diff for comparing environments
• Rollback strategies — compensating migrations, migration repair, and feature-flagged schema changes
• Type regeneration — supabase gen types typescript after every schema change
• Migration promotion — db push workflow from local to staging to production

Error Handling

Examples

The create-write-test-push workflow for a real column addition:

npx supabase migration new add_tags_to_projects
cat > supabase/migrations/20260322150000_add_tags_to_projects.sql << 'SQL'
ALTER TABLE public.projects ADD COLUMN tags text[] DEFAULT '{}';
CREATE INDEX idx_projects_tags ON public.projects USING GIN(tags);
SQL
npx supabase db reset
npx supabase gen types typescript --local > lib/database.types.ts
npx supabase link --project-ref <staging-ref> && npx supabase db push

See examples for three full worked examples: the complete local-to-production migration workflow, a batch backfill with progress tracking from the SDK, and a safe enum migration using a CHECK constraint instead of ALTER TYPE.

Resources

• Database Migrations — Supabase Docs
• Supabase CLI Migration Commands
• supabase db push — Supabase Docs
• supabase gen types — Supabase Docs
• Zero-Downtime PostgreSQL Migrations
• supabase db diff — Supabase Docs

Next Steps

• For advanced troubleshooting after migrations, see supabase-advanced-troubleshooting
• For multi-environment migration promotion, see supabase-multi-env-setup
• For performance tuning after schema changes, see supabase-performance-tuning