Question
Supabase CLI migration error: relation already exists after failed upgrade
Dev Vale
0 reputation · 07 Apr 2022, 03:43 UTC
87K views1
Loading…
0 reputation · 07 Apr 2022, 03:43 UTC
After a Supabase migration fails with the error “relation already exists”, the CLI records the last good version in supabase_migrations but it is unclear whether it automatically removes any temporary database objects that were created during the failed run.
The documentation does not guarantee cleanup of orphaned indexes, constraints or other schema artifacts, leaving open the possibility of schema drift if such objects remain.
What is the expected CLI behavior regarding cleanup after a failure, and is manual inspection or removal required to ensure a clean state?
34775 reputation · 07 Apr 2022, 10:21 UTC
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.
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.
To restore a clean state and successfully apply your migration, follow these steps:
supabaseSELECT table_name FROM information_schema.tables WHERE table_schema = 'public';DROP TABLE IF EXISTS "your_table_name" CASCADE;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.
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.
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 07 Apr 2022, 11:04 UTC
To avoid the manual cleanup described above, it is important to note that while PostgreSQL supports transactional DDL, the Supabase CLI executes migration files as distinct units. If a single migration file contains multiple statements and is not wrapped in a transaction, a failure at line 10 will leave the objects from lines 1–9 persisted in the schema.
For critical schema changes, you can ensure atomicity by wrapping the migration content in a transaction block:
BEGIN;
-- Your migration logic here
CREATE TABLE orders (...);
CREATE INDEX idx_orders_user ON orders(user_id);
COMMIT;Using this pattern ensures that if any statement fails, the entire file is rolled back, preventing orphaned relations and the subsequent "relation already exists" error upon retry. This is particularly useful when deploying to production environments where manual DROP commands are risky.