Migration management

[TNieminen]

There seems to be a lack of outline for database migrations management. Where they should be defined, how they should be defined and when they would run.

[HoukasaurusRex]

Will add; open to ideas as well

[TNieminen]

General thoughts, this might take some doing so it can be a separate task later as well that I'm willing to take on (setup the migrations in the past and would like to make that better :D)

• Use Sequelize methods in migrations
• Write migrations such that they are agnostic to an empty database (maybe a testing step against an empty DB should be enforced?)
• Use transactions for migrations for easy rollback https://sequelize.readthedocs.io/en/v3/docs/transactions/
• Fail build at a failed migration so we don't for instance deploy API changes without supporting database changes

[HoukasaurusRex]

Make sure to use the official v5 sequelize docs instead of the readthedocs page, that one is for v3 and many things are completely different from v5. Many migrations need to rely on the fact that it's not a freshly synced DB, so it's probably better to have a policy of removing a migration after running. Since some migrations will always need to be run (e.g. adding DB extensions like fuzzystringmatch or whatever), but others should only be run in a specific DB state (e.g. moving a column of data to another column or something), there might just need to be a "best practices" guide on DB migrations rather than a programmatic solution.

It's important to note as well that an empty DB and a freshly synced DB are not the same, and it might be difficult to tell the difference unless sequelize exposes info in the callback (or promise) to let the migration runner know if it's been synced or not.