The Supabase CLI does not automatically roll back or clean up database objects created during a failed migration. If a migration script fails halfway through, any tables, indexes, or constraints created before the error occurred remain in your database, even though the migration is not marked as complete in the supabase_migrations table.
Why This Happens
Supabase migrations are executed as a series of SQL statements. When the CLI encounters an error—such as a syntax mistake or a conflict—it stops execution and records the last successfully completed migration version. However, it does not wrap the entire migration file in a single global transaction that reverts all changes upon failure. Consequently, if your script created a table and then failed on a subsequent index creation, that table persists in the schema.
The "relation already exists" error occurs when you attempt to re-run the migration; the CLI tries to execute the CREATE TABLE statement again, but the object from the previous failed attempt is still present.
Resolution Steps
To restore a clean state and successfully apply your migration, follow these steps:
- Identify Orphaned Objects: Check your database for objects that should have been created by the failed migration. You can query the information schema to find them:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
- Manually Drop Artifacts: Remove the specific relations causing the conflict. Be cautious to only drop objects introduced by the failed migration file.
DROP TABLE IF EXISTS "your_table_name" CASCADE;
- Verify Migration State: Ensure the
supabase_migrations table does not falsely claim the failed version was applied. If it was partially recorded, you may need to remove that specific row to allow the CLI to attempt the file again.
- Re-run Migration: Execute the migration command again via the CLI.
Prevention Tip
To avoid this in the future, use CREATE TABLE IF NOT EXISTS or wrap your migration logic in explicit BEGIN; and COMMIT; blocks if your specific database version and CLI configuration support transactional DDL.
Diagnostic Note: Are you using a local development environment or applying these migrations to a remote staging/production project? The risk of manual DROP commands is significantly higher in remote environments.
supabase