Feature request

Is your feature request related to a problem? Please describe.

When working in a team that is creating migration scripts, it is common for one dev to have deployed a migration to a remote DB before another. When the migration that has a timestamp that is older than any migrations on the remote DB, npx supabase db push will fail with the error message:

Error: supabase_migrations.schema_migrations table conflicts with the contents of supabase/migrations.; Expected version 20221107155223 but found migration 20221104190409 at index 16.

The workaround options are to:

Truncate the storage.migrations table and run npx supabase db push again. This only works if your migrations have been written in an idempotent way such that they work even if they are running on a DB where they have already been applied.
Copy the contents of your older migration, delete your migration file and regenerate it to give it a newer timestamp. This is problematic because migrations are generally written to work based on the current DB schema at the time the migration was written. If a newer migration changes that schema, you might need to rewrite your migration to work based on that new schema.

Describe the solution you'd like

I want npx supabase db push to roll back migrations up to the point where a linked DB and a remote DB are in sync before progressing with applying the migrations. This could be accomplished by creating two files when running npx supabase migration new. One for applying the migration (up) and one for rolling back the migration (down). For example, npx supabase migration new add_user_table --add-down would create two files in supabase/migrations:

supabase/migrations/
    20221104190409_add_user_table_up.sql
    20221104190409_add_user_table_down.sql

Rather than preventing the up migrations from running, the down scripts would be run up to the point that the local DB storage.migrations table matches the remote table. Then, the up scripts would run in sequence normally.

In order to implement this enhancement as a non-breaking change, if any migrations in the list of migrations that are newer than the migration(s) to be applied do not have a "down" script, the command would error out as normal. It might also be a good idea to include a helpful link to some documentation to instruct the user how to create a down script to avoid this error in the future.

Describe alternatives you've considered

The only solution I've found that already does this is db-migrate. In order to use it, we would have to go around the supabase cli entirely for managing, creating, and applying migrations. Along the aforementioned functionality, this package gives additional functionality such as applying and rolling back migrations individually by name or in succession up to a limit.

As we consider this feature to be critical to our CI/CD workflow, we will be using this package for migrations in the near future. We look forward to using the supabase CLI tool if/when this feature is implemented.

Additional context

The major risk of this functionality is that the down script would be necessarily destructive which could result in unintended data loss. It is only potentially the case that the up script is destructive but that risk is still there in either case. While the migration created data/schema should persist assuming the scripts were written properly, user data could be truncated upon the down script removing any columns, tables, etc. I do not believe this should be any reason not to implement this feature as it would be opt in and any users opting into it would assume that risk.

supabase / cli

Roll back migrations that are out of sync between the local and remote DB #611

Feature request

Is your feature request related to a problem? Please describe.

Describe the solution you'd like

Describe alternatives you've considered

Additional context