[daniel-ash]

Something I'd like to see with local development and the Supabase CLI is timing around inserting seed data, handling triggers, default data. I ran into a bunch of issues getting a nice local dev setup. For example seeding data after migrations is not helpful (and will fail) if your latest migration is destructive - you want to seed data and then run the next migration.

For context, my local dev process is now as follows:

1. supabase db reset with seed.sql empty 2. run a preseed script that disables any triggers and removes default data that has been previously seeded in migrations 3. seed data 4. reenable triggers 5. execute any working migration files that I keep in a separate file

I've written a script that handles all this, so I have mostly solved this for myself - but this was mostly due to running into a bunch of challenges setting up my local env to work well. Very open to general comments on approach too - perhaps there is a simpler way

[sweatybridge]

> if your latest migration is destructive - you want to seed data and then run the next migration.

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...

[daniel-ash]

Thanks, appreciate this. A few comments:

I like being able to call supabase db dump (data only) and not touch code in the file at all - I get that adding SET session_replication_role = replica; is one line, but still my preference is to avoid. But like I said I already disable triggers ahead of the seed script running.

I currently use supabase db reset quite frequently as I make changes in development. 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. Which is not the worst idea, I'd still be looking to automate those steps with my own script atm tho.

Re: squash I have been a little cautious to use it since I first noticed it in the CLI docs as I wasn't really sure what the actual outcome would look like If I have something like this in a migration script:

--set initial permissions INSERT INTO rbac.permissions(name) SELECT unnest(enum_range(NULL::rbac.permission_name)) except SELECT name FROM rbac.permissions;

what would squash do to handle this data?

[sweatybridge]

Thank you for the helpful feedback.

> 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

[kiwicopple]

> I've written a script that handles all this

Would you mind sharing this in a gist? We can add this functionality to the CLI

[daniel-ash]

Not at all: https://gist.github.com/Daniel-Ash/0ddf135cc0d9341d9d13dadba...