A simpler solution/workaround would be to extend migrations up to always execute some SQL file (if present) in the migrations directory (matching a filename or a prefix), similar to seed.sql and the start or db reset commands

Great suggestion! 👍 To build on it, a post-migration hook for auto-running r_ files after migration up could keep the definitions updated without duplicating. This would streamline updates to views/functions and keep migrations cleaner!

I think the main benefit of repeatable migration is to reduce code duplication. But after some initial exploration by @JohnBra, the cost involves changes to migration list and db reset commands which seem fairly complex.

So taking a step back, may I suggest using declarative schemas instead? For eg. you can define views and functions in supabase/schemas directory and run supabase stop && supabase db diff to generate a versioned migration. This way your schemas are updated in one place, without worrying about duplications in generated migrations.

.
└── supabase/
    ├── schemas/
    │   ├── employees-table.sql
    │   └── other-view.sql
    └── migrations/
        ├── 20241004112233_employees_table.sql
        ├── 20241005112233_add_employee_age.sql
        └── 20241006112233_add_other_view.sql

To diff the declared schemas, remember to stop the local database first.

I'm not sure the declarative approach is the best way to go:

1. At the end, it's the generated code that gets executed.
2. What about troubleshooting failed migrations? it wouldn't be as straightforward.
3. We'd have to check both dirs to git.

If this would be integrated into other commands (db reset, migration up...), and without users changing their way of working with migrations, or their tooling config, then it's fine, the end result is the same. (though I'm not sure how)

I do see the good side of it though, having one employees-table.sql to maintain is better than having multiple split files.

Shameless plug: I made https://github.com/t1mmen/srtd to simplify this exact problem.

Also comes with quite a few DX benefits, like live/hot loading changes directly to DB (locally).

(I only discovered the CLI supported declarative schemas today; haven't tried that yet, but srtd seems to offer better DX atm)

Supabase crew, feel free to steal the good ideas from my project and bake into the CLI directly :)

We actually supported declarative schemas almost a year ago, but haven't documented or announced it until recently.

It's a bit of a shame that migra is no longer actively maintained. But the good news is there's a new diff tool open sourced by stripe. It has better support for generating zero downtime migrations.

This option is also baked into Supabase CLI via db diff --use-pg-schema flag. Once the diff tool supports views and privileges, it will replace migra as the default.

Nevertheless, I'm impressed by the interest and your work done in this space. I believe the community will benefit a lot from having a perfect schema diff tool for Postgres.