Re-syncing Migrations

[Beeen]

Hello,

I'm having a lot of issues with managing migrations in Supabase. At the beginning of our project we did the big mistake of creating and pushing migrations from our mobile app repository when all of them were done in our back-end repo that contains our edge functions.

To get out of this situation, I tried to "squash" them into a single migration file but I"m still out of sync and can't pull or push without having error messages.

Anyway, the current situation is that we have 4 migration files on the remote instance and the same 4 in our repo.
The Problem: When I start our local db or reset it, it tries to apply the migrations on a empty schema.

Approach 1:
Dump the remote schema in supabase/schema to have declarative schemas which will be executed before applying the migrations when we run supabase locally.
Problem: I get an "unrecognised key" error message when I add this to our config.toml
[db.migrations] schema_paths = [ "./schemas/schema.sql" ]
I'm running the latest version of Supabase CLI

Approach 2:
Try to generate an initial migration file that is basically our current remote schema minus the 4 migrations and have all statements begin conditional so that it doesn't throw errors when pushing it to the remote (because it re-applies existing constraints etc) but it still abble to reproduce our current schema before applying migrations when running our Supabase locally

Questions:

• What's the best way to get out of this situation?
• Is there a way to start fresh with our current schema but with 0 migrations remotely and locally without having to reset our remote database?
• If we have to reset our remote DB what is the correct process to follow?

[Rubenburdin]

Hey Beeen,
This is a frustrating situation but its recoverable. The core problem is that your migration history table on remote doesnt match what your local expects, and your migration files assume an empty database rather than the current schema state.
The cleanest way out is to treat your current remote schema as the new baseline and essentially "restart" your migration history. First, on your remote database run DELETE FROM supabase_migrations.schema_migrations; to clear the migration tracking. Then locally, delete your existing migration files and run supabase db pull to generate a fresh snapshot of your remote schema as a single migration file. This becomes your new starting point. After that, run supabase db push to record this baseline migration in the remote tracking table. From here on your local and remote are in sync and future db diff commands will work properly.
For your Approach 1, that schema_paths config syntax looks correct but make sure the path is relative to your supabase directory and the file actually exists there. Also double check theres no yaml formatting issues in your config.toml, sometimes an extra space or wrong indentation causes those unrecognised key errors.
You dont need to reset your remote database or lose any data. The migration tracking table is just metadata about which migrations have been applied, clearing it doesnt touch your actual tables or records. Your schema stays intact.
One thing to consider going forward, keep migrations in a single repo to avoid this happening again. If your edge functions and mobile app both need schema changes, have them go through the backend repo as the source of truth.
For teams that find themselves constantly fighting sync issues between their database and external systems like HubSpot or Salesforce, thats where tools like Stacksync come in. But for schema migration workflows what I described above should get you unstuck.