supabase
Support for database fixtures
Is your feature request related to a problem? Please describe. Databases, especially in the supabase context, often need many helper functions, views, etc. That are troublesome to manage with migrations.
Describe the solution you'd like
It would be great if supabase/cli supported, alongside migrations, also fixtures. And by fixtures I mean functions, views, and any other idempotent operations that don't need to be "migrated", as they don't change how data is stored, and can safely be run every time with supabase db push.
A bonus would be if the CLI managed the state of fixtures, and only reran the fixture files if the underlying file hash has changed.
Describe alternatives you've considered
Alternatively, just the ability to run e.g. supabase db run file.sql or with glob supabase db run fixtures/*.sql would be helpful as well. In order to avoid having to set up a parallel config to the CLI to run arbitrary SQL commands in e.g. Github actions.
Additional context N/A
For now, I came up with these scripts in package.json:
"fixtures:local": "cat $(find fixtures -name '*.sql') | psql 'postgresql://postgres:postgres@localhost:54322/postgres'",
"fixtures:push": "doppler run -- git diff --cached --name-only --relative -- fixtures | grep '\\.sql$' | xargs cat | psql $DATABASE_URL"
Still, it's difficult to reuse the same config as I provide to the CLI.
Another issue is that the amount of custom logic we're patching onto the CLI is growing, and we painfully need #501 to avoid making mistakes in the command sequence.
Actually, git diff is too brittle for this. So I just reverted back to find for both push and local implementation. If a github action fails, we will lose it in the history. Hence tracking should ideally happen on the DB level, but for now, we're happy to re-run everything.