An effective database migration pattern for many instances of the same app over a long period of time

Question

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!)

• Rik D: Focus on a single issue per question. You could create a new question for your second issue.
• thinkOfaNumber: Fair point @RikD, it was getting a bit long winded. I removed reference to the "second" problem.
• Ben Cottrell: How is it possible for previously working migrations to fail unless somebody has modified the production database schema based on that old version? Is there something else changing, or are people diving into production databases and applying custom schema changes to them? (Or, is your schema being branched with new migrations from old versions..?)
• thinkOfaNumber: @BenCottrell they very-rarely fail when creating a new DB instance from scratch, i.e. migration 3 doesn't randomly fail when we're applying migration 55. And yes it's because someone touches an older migration, which is occasionally necessary to fix a bug, but it doesn't get tested properly and we discover the failure later. It's an opportunity for a better test suite but that's another story... NOBODY touches my precious... production database!
• misha130: I don't understand what do you mean fluentmigrator starts with version 0? It always starts from last migration number applied. Also make a rule in your code base to never edit migration files. Also Also consider integration tests in ci that would run your migrations against an empty db

Answer 1

I don't know how fluent migrator handles it but the excellent Laravel documentation suggests "squashing" your migrations to one large SQL-file that takes precedence. Seeing as it is one of the most popular frameworks, I would imagine that they know what they are doing.

So, for a process I would implement a kind of script to dump your SQL, archive the old files, and then commit the new setup. But I would strongly suggest, that you test everything before committing and that this should not be done daily or event monthly. If you feel that you need to do this this often, you should rethink if you need that many migrations in the first place.

• thinkOfaNumber: huh. Never seen that before yet it's exactly the solution... for PHP! That's a bit far from my technologies (maybe the next project) but very interesting, thanks.
• Fabian Sievert: Yeah its PHP but the principle behind it should be applicable to all technologies.
• thinkOfaNumber: Yes except the principle is "execute this command" which I don't have access to in .net. I have run through the manual squash scenario in my question, but it's possible doing it manually is the only way to go. Thanks! (I still +1 your answer though as it's great to see the option in another framework.)