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.