Syncing local and remote DB schemas in supabase

Supabase

Syncing local and remote database schemas in supabase is based around migrations. Migration is a file with a set of SQL operations making changes to database schema.

Migrations are stored by default in supabase/migrations folder. This folder should be committed to git repo. You want to have all the schema updates in migrations files as this allows you fresh start when calling:

 supabase db reset

We are going to go through multiple scenarios and how to handle them.

You can check the state of migrations with:

 supabase migration list

Most of upcoming commands have --linked and --local options, targeting remote and local DB respectively.

Making changes in remote dashboard

We need to add them to migration file:

 supabase db pull

This will generate new migration file in supabase/migrations folder. You will be asked if it should be added to remote migration history table. Select yes if only syncing from remote.

If you are adding some feature, rename the migration file first and then add it to history. The changes from the generated migration file already exists in the database, so we need to update the remote migrations history table (20231018080000 is version number):

supabase migration repair 20231018080000 --status applied

Making changes in local dashboard

Similar to remote instructions but you need flag --local:

 supabase db pull --local

After adding you can just restart the DB and it will apply the changes:

 supabase db restart

Editing migrations in a file

Create new migration file:

 supabase migration new feature_name

This will create new migration file in supabase/migrations folder.

You update local DB with:

 supabase db push --local

or remote DB with:

 supabase db push

Migration exists in remote but not in local

Update git repo. The repo should have the new migrations file.

If for some reason the file is not there, you should be able to create it manually. AFAIK there is no way to do it with command line.

Go to database/migrations in remote dashboard, click View migration SQL of missing migration and copy to the file supabase/migrations/{version}_{name}.sql

Data syncing

Use dump:

 supabase db dump --data-only -f test-dump.sql

then put what you need on a fresh start in seed.sql file.