1

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

Improve this question
8
  • Focus on a single issue per question. You could create a new question for your second issue.
    – Rik D
    Nov 15, 2021 at 7:52
  • Fair point @RikD, it was getting a bit long winded. I removed reference to the "second" problem.
    – thinkOfaNumber
    Nov 15, 2021 at 14:23
  • 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..?)
    – Ben Cottrell
    Nov 15, 2021 at 14:26
  • @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!
    – thinkOfaNumber
    Nov 15, 2021 at 14:32
  • 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
    – misha130
    Nov 15, 2021 at 16:24

1 Answer 1

Reset to default
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.

Improve this answer
3
  • 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.
    – thinkOfaNumber
    Nov 15, 2021 at 14:28
  • Yeah its PHP but the principle behind it should be applicable to all technologies.
    – Fabian Sievert
    Nov 15, 2021 at 14:29
  • 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.)
    – thinkOfaNumber
    Nov 15, 2021 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.