supabase migrations squash not doing what I would expect it to do

[silentworks]

Is your feature request related to a problem? Please describe.

The squash command isn't doing what I was expecting it to do. It seems to just do a db dump of the current database including schemas that are managed by Supabase which then creates conflict when I try to start the db with it.

Describe the solution you'd like

I am going to outline below how I would expect the squash command to work.

• The first thing I would expect it to do is create a db dump with only the schemas I'm managing or those I have included through a flag
• The dump should likely be in a schema.sql file inside of supabase/ directory
• It should then remove all files from my local supabase/migrations directory and TRUNCATE the supabase_migrations.schema_migrations table inside of my local database along with my remove database if it's linked (this step is to prevent migrations being out of sync).
• In my local setup I would now expect that if I should stop my local CLI supabase stop --no-backup and then run supabase start again, it would first check for the supabase/schema.sql file and run it right after the internal migrations have run, then it would go back to the normal supabase/migrations directory and run those along with supabase/seed.sql after.

So to detail this out in bullets points

• supabase migrations squash

  • create supabase/schema.sql
  • remove files from supabase/migrations directory
  • truncate supabase_migrations.schema_migrations - locally
    • if project is linked
      • truncate supabase_migrations.schema_migrations - remote

• supabase start

  • run internal migrations to create auth and the other schemas
  • import supabase/schema.sql
  • run migrations from supabase/migrations directory
  • run seed data from supabase/seed.sql

One concern I could see someone having around this setup is, if remote supabase_migrations.schema_migrations table gets truncated, won't that break the CI pipeline if someone should try and push a new migration? and the answer is yes it would but this is where best practices comes into play as there should be a team wide announcement of such a change and everyone should halt working on migrations when such a change is being made. This isn't something we can automate away, this is something that requires team communication.

Additional context

Prior art:

Laravel migrations https://laravel.com/docs/12.x/migrations#squashing-migrations - I think they have done an excellent job with this as they squash all migrations down to one file called schema/schema.sql and also maintains the migrations table in the database.

[sweatybridge]

The behaviour of migration squash is documented here https://github.com/supabase/cli/blob/develop/docs/supabase/migration/squash.md

The only difference between your proposal is that we don't use schema.sql. Instead, we continue using the migrations directory and repair the remote history table accordingly.