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
npx supabase migration list
npx supabase db reset
npx supabase gen types typescript --local > lib/database.types.ts
npx supabase link --project-ref "<ref>" && npx supabase db push
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:
ALTER TABLE public.orders ADD COLUMN status text DEFAULT 'pending';
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
| Error | Cause | Solution |
|---|
migration has already been applied | Re-running existing migration | Use supabase migration list to check status; never modify applied migrations |
cannot run inside a transaction block | CREATE INDEX CONCURRENTLY in transaction | Add -- supabase:disable-transaction comment at top of migration file |
column does not exist after migration | Migration not applied or types stale | Run supabase db push then supabase gen types typescript |
deadlock detected during backfill | Concurrent updates on same rows | Reduce batch size; add retry logic with exponential backoff |
statement timeout on large table | Migration takes longer than timeout | Increase statement_timeout in migration: SET statement_timeout = '300s'; |
migration repair failed | Wrong version number | Use exact version from supabase migration list (the timestamp prefix) |
db diff shows unexpected changes | Schema drift from manual SQL Editor changes | Run supabase db pull to capture manual changes as a migration |
| Type mismatch after migration | Generated types don't match new schema | Delete database.types.ts and regenerate from --local or --linked |
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
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
