Modern diff tools are designed to provide better guardrails in these situations. For eg, pg-schema-diff [0] tries to generate zero downtime migrations by using lock-free migrations and warns you about potentially hazardous migrations.

I think it's good direction to bake these best practices into the tooling itself, rather than relying purely on the experiences of engineers.

[0] https://github.com/stripe/pg-schema-diff

That was exactly our experience too.

Perhaps we didn't highlight enough in the blog post that schema diff was not meant to replace manual review. It simply provided a good starting point for us to iterate on the migration, which often boosts efficiency.

> Using supabase migration up would mean moving the latest migration out of the migrations folder, running supabase db reset, moving the file back in and then calling supabase migration up.

We can definitely do a better job here. I'm adding support for db reset --version flag [0]. This should allow you run migration up without moving files around directories.

> I wasn't really sure what the actual outcome would look like If I have something like this in a migration script

Agree that we can do a better job with the documentation for squash command. I will add more examples.

The current implementation does a schema only dump from the local database, created by running local migration files. Any insert statements will be excluded from the dump. I believe this is not the correct behaviour so I've filed a bug [1] to fix in the next stable release.

[0] https://github.com/supabase/cli/pull/1369

[1] https://github.com/supabase/cli/issues/1370

We have added supabase migration up [0] command that runs only pending migrations (ie. those that don't exist in local db's migration history table). You can use that to test destructive migration locally with data from seed.sql.

After testing, you want to update your seed.sql with a data-only dump [1] from your local db. That would make CI happy with both the new migration and the new seed file.

> 2. run a preseed script that disables any triggers and removes default data that has been previously seeded in migrations

It sounds like the default data is no longer relevant for your local development. If so, I would suggest running supabase migration squash [2] to remove the default data.

To disable triggers before seeding data, you can add the following line to seed.sql [3]

SET session_replication_role = replica;

[0] https://supabase.com/docs/reference/cli/supabase-migration-u...

[1] https://supabase.com/docs/reference/cli/supabase-db-dump

[2] https://supabase.com/docs/reference/cli/supabase-migration-s...

[3] https://stackoverflow.com/questions/3942258/how-do-i-tempora...

Currently all supabase db and migration commands support --db-url flag [1] which allows you to point the CLI to any Postgres database by a connection string.

If there's any use case I missed, please feel free to open a GitHub issue and I will look into it promptly.

[1] https://supabase.com/docs/reference/cli/supabase-db