How do you handle migrations to your database after a long period of time?
I'm using fluent migrator, and after some months or years, the number of migrations could be (and has been in previous projects) in the hundreds.
This is a problem for new SaaS instances that only need to seed from the "latest" version. Currently fluent migrator would start with version 0 and then apply a bunch of migrations to get to the latest. Sometimes a migration from months ago fails for some reason, because nobody is maintaining that. (Yes we always test changes in a prod-copy but it would be good to remove this point of friction).
The closest I've seen is someone doing a sort of "rebase", where they delete the migration version table, and generate new SQL scripts from a database backup tool and call it version "0" from then on. They repeat this every few months. That hardly seems ideal.
I'm looking for a process. I know asking for program recommendations is off-topic, but your process has to be backed up with some stable tool to automate database migrations and updates.
So the criteria for a procedure and related tool(s) would be:
- must handle a large number of migrations over months/years
- must store changes in source code repository (e.g. fluent migrator uses c# code to define the changes, and SQL scripts can be included)
- must be able to seed new databases at any point in the schema lifecycle, preferably by only applying the latest schema
- must be able to apply seed data to new instances
- must integrate with a CI/CD pipeline, as all code changes are reviewed, built, and run by machine (currently using a custom fluent-migrator runner)
- should work in a .net core environment, but this is open to change
- should work with SQL Server or Postgres
I'm already using fluent migrator, so yet another migration tool is not enough, but how you use the same tool in a cloud environment would be interesting to know.
Thanks for the advice (PS first SE question, hai!)