SupabaseS
Supabase•3y ago
cyan

Local <--> Remote Migrations Help

I'm running into some issues with writing migrations.

I want to insert some data into the db to test a few features.

I want this data to be both on production database AND the local database.

So I'm doing this:
  • Writing a miration to insert the data
    ```sql
    -- Insert a new companion and chat for testing purposes
    begin;
with inserted_companion as (
insert into companions (name, bio, avatar_url, created_by)
values ('Rio', 'lorem ispum', '/rio.jpg', '85331156-96c5-4f28-807a-6ab7b347d065')
returning id
)
insert into chats(user_id, companion_id) select '85331156-96c5-4f28-807a-6ab7b347d065', id from inserted_companion;

commit;
- `supabase migration up` to apply the migration instead of `supabase db reset`
`supabase db reset` applies all the migrations first before running seed.sql
However, since the migration is inserting data that depends on seed data (the user uuid),
running `supabase db reset` will result in a contraint error.
I get around this by running supabase migration up instead.

- I then push this migration to the remote db with `supabase db push` 

- I then generate a new seed.sql file from the remote db state with:
sh
echo "SET session_replication_role = replica;" >> supabase/seed.sql
supabase db dump --data-only >> supabase/seed.sql
`` - However, the problem I'm encountering is that the migration that inserts the data will still cause conflicts when running supabase db reset`.

  • Fundementally, it seems like this is because the environments of the remote and local are different.
  • The remote db already has seed data before running the latest migration
  • But the local db has to run all migration first before seeding data.
  • This leads to issues like the one I'm encountering.
Was this page helpful?