Database Migrations Without Breaking Things

Changing Your Database After Launch Is Scary

Adding a column to an empty table during development is easy. Adding a column to a table with 10,000 rows of real user data is stressful. One wrong move and you could lose data or break your app for everyone.

Supabase makes migrations manageable if you follow a few rules.

Rule 1: Never Edit Production Directly

Use the Supabase CLI to create migration files locally, test them, and then apply them to production. This gives you a record of every change and the ability to roll back.

supabase migration new add_avatar_to_profiles

This creates a SQL file in your supabase/migrations folder. Write your changes there.

Rule 2: Make Additive Changes

Adding things is safe. Removing things is risky. When possible:

• Add new columns instead of renaming old ones
• Add new tables instead of restructuring existing ones
• Mark old columns as deprecated rather than dropping them immediately

Rule 3: Use Default Values

When adding a column to an existing table, always include a default value:

ALTER TABLE profiles
ADD COLUMN avatar_url TEXT DEFAULT '';

Without a default, existing rows get NULL, which might break code that does not expect it.

Rule 4: Test on a Branch Database

Supabase supports database branching. Create a branch, apply your migration, check that everything works, then merge to production.

supabase db branch create test-migration
supabase db push --db-url [branch-url]

Common Migration Patterns

Adding a column: Safe. Use ALTER TABLE ADD COLUMN with a default.

Renaming a column: Create the new column, copy data, update code to use the new name, then drop the old column later.

Adding an index: Safe but can be slow on large tables. Use CREATE INDEX CONCURRENTLY in Postgres to avoid locking the table.

Deleting a table: Make sure nothing references it first. Check foreign keys and application code.

Rollback Plan

Before applying any migration to production, write the reverse SQL. If adding a column, the rollback is dropping that column. Keep rollback scripts next to your migration files.