[GUIDE] Sync local and prod schemas when they're out of sync

[techwizzdom]

Hey all 👋🏼,

I noticed that many people have problems when they make changes in remote DB and local DB only by clicking around the Supabase UI - and then, projects go out of sync. As a consequence, you cannot make nice migrations with db diff/push.

I had the same problem, but I finally found a solution I don't see it in the documentation, so I'm sharing it here:

1. Let's say that your remote DB is in a desired state. You need to get rid of all the migrations you previously did. In your remote SQL editor, do:

delete from supabase_migrations.schema_migrations;

2. Now, fetch the latest remote DB schema with:

supabase db pull

This will create a remote schema in supabase/migrations/{{timestamp}}_remote_schema.sql.

3. It's time to see the difference between your local and remote:

supabase db diff -f initial_diff

This will create a difference between local and remote DB schemas in supabase/migrations/{{timestamp}}_initial_diff.sql.

4. Go through *_initial_diff.sql file and see the differences. Keep only those you want to apply to the remote.

5. When you're ready, do:

supabase db push --dry-run

This will show which migrations will be applied to the remote, it will not actually push them. It's just for checking. If you did everything right, you should see only the initial_diff migration:

✗ supabase db push --dry-run
DRY RUN: migrations will *not* be pushed to the database.
Would push migration {{timestamp}}_initial_diff.sql...`
Finished supabase db push.

6. Now, you're ready to push the migrations:

supabase db push

Check your remote - changes in initial_diff should be applied.

7. And from now on, the process is pretty simple. You do some changes in your local DB (through the UI or SQL editor, doesn't really matter). Let's say we added a column new_column to table screenplays via UI. If you then do:

supabase db diff -f add_new_column

A new file supabase/migrations/{{timestamp}}_add_new_column.sql will be created and inside you should see only:

alter table "public"."screenplays" add column "new_column" text;

And changes you kept from the initial diff (if any).

8. If that's the case, it's all synced and ready! 🚀

It would be awesome to add this to the official docs.

[Julian-Tipler]

Thank you so much for this explanation! Follow-up question, do we check the remote_schema.sql into version control?

[stamler]

Yes. It's the source of truth for your schema.