Accountable changes: Allow migration sets outside this repo

[mrsarm]

We have in this project sets of migrations defined under the libs/**/migrations/ folders, and each one only contains SQL scripts, there are no "business logic" behind the source code to perform complex migrations, just simple SQL scripts that after being executed Postgrator takes care of keep track of the migrations executed so they are executed in the right order.

If we allow partners to create outside this projects a repo with the migrations they want to run, the changes will be accountable, and handled by the same tool: medic-couch2pg.

If this change sounds reasonable, I can create a draft with the structure and the usage changes needed.

CC @garethbowen @kennsippell

[kennsippell]

What is the usecase you're thinking of here for migrations? What is an example of a migration that you think partners would want to run?

[mrsarm]

@kennsippell yes when I say "partners" I also wanted to say "us". I think that for the success of this feature is key to also implement #92 (transactions) , and also is key to change the way we write migrations script as I highlight in this comment in the same ticket:

... not perform any more CASCADE migrations, and instead we DROP first the elements one by one that we control in the migrations ...

The use case is to avoid errors that happened to us recently, and detect these errors before production in some cases, and because we cannot add into the medic-couch2pg codebase all the SQL scripts needed by each project, we need to allow a way of centralize these changes by medic-couch2pg .

So we need:

• Accountability (that is way this ticket)
• Transactional changes (Migrations along with first batch of data should be performed in a single transaction #92)
• Keep in sync changes made by medic-couch2pg and changes needed in a project. Keeping "custom" changes in a repo that are executed by the same tool (this tool) will allows us to achieve this goal, because right now if you create a repo to keep track of custom made changes but you execute them with a 3th party tool like a SQL client, how you keep track of these changes in the time? There will be a point were changes in that repo will be created and changes in medic-couch2pg will be created and will be hard to figure out the order of execution, and a mess to execute them. Executing the script with the same tool following the same versioning pattern that offers Postgrator allow to achieve that.
• Transactional execution will allows us to test these custom scripts and upcoming changes in medic-couch2pg in dev environments and detects errors earlier.
• Avoid the CASCADE modifier in SQL scripts, and we can easily check that in medic-couch2pg adding a linter that is run automatically before any script is executed.

The last point along with transactions is key, here is an example:

An script drops a view "reports_aa". Instead of use the CASCADE modifier in the DROP statement, all the dependent views are dropped first, then the view "reports_aa" is deleted. Because cascade was not used, if another view that was not taken into account by the developer also depends of the views , the drop of "reports_aa" or any of the other drops will cause the operation to fail, and because transactions is used, all the drop instructions will be cancelled in production or the dev environment used.

The only way to warranty this is:

• Changes will be executed withing a single transaction and a single tool (medic-couch2pg).
• No CASCADE operations are executed as mentioned, also warranted by the tool.
• Changes are aligned with changes in medic-couch2pg scripts, and all the script are executed in the right order whether they come from the medic-couch2pg source or the custom repo source.

Moreover, if all the changes are managed by medic-couch2pg, we can detect errors earlier in dev environments, because it's easier to reproduce the schema and the result of schema changes.