Restarting from a given migration

[chrisdone-artificial]

Good afternoon! We're long-time users of sqitch going back to 2020. We've got 429 migrations. And a dozen deployed apps with different databases.

We'd like to delete these migrations and restart from a clean slate, like a git rebase, that essentially the first starting migration would be our complete schema at commit X, and then we'd keep maybe the last n migrations around that are X+1..X+n.

We have our own ideas on how to achieve this, but I wanted to check in whether you had plans for implementing something similar as a sqitch command?

[rotten]

You are thinking something like the django squash migrations feature?

Django uses the current models implementation to figure out a new set of migrations and then pulls in the custom sql and python migrations for you to squash yourself.

Some tools can take a snapshot of the database, assuming everything in the database was created with a migration, and then export new migration files based on what it finds. It won't know about data loading vs. organic data nor will it necessarily know about dead objects that you no longer need.

If you are using postgresql, you can run pg_dump and it will generate a sql file with all of the sql you need to recreate your database (optionally with or without data). You could then edit that sql file into a set of smaller migrations as a good starting point.

Just some ideas, I don't know if sqitch has any tools built-in or on the roadmap.

[theory]

No plans, but the basic pattern has been to dump a database deployed to the point you want, make that the first change in a new plan, then add the subsequent changes to the plan. Essentially the same as starting with an existing database.