Approach to database migrations/versioning

Hello everyone,

on one of the recent technical committees, we have discussed an issue relating to the database migrations and using timestamps as versions for those migrations. As a summary, due to common conflicts in previous versions of OpenLMIS using serial versions, v3 decided to use timestamps to version migrations. A downside to that is that files can be committed “out of order”. This means if I generate my migration with a given timestamp, and someone else does it later but commits their changes earlier, I’ll be committing a migration that has an earlier version. You can also read the summary here:

As I’ve stated on the tech call, using timestamps was a mistake in my eyes. Timestamps indeed do prevent the conflicts with identical versions, but they allow for committing migrations out of order, which we don’t want. Using serial versions would ensure that we always commit in order, even though, we would need to handle the conflicts (which in v3 aren’t that common, since we have each microservice handling their own migrations).

Now, I don’t think this is that a big deal and migration tests give us a little more confidence that migrations work, even if committed out of order. Moreover, special care needs to be applied when committing files early after a release so that no migrations are added with timestamps preceding latest one from the released version. Doing that would result in this migration never getting applied on the production server with the next release.

For now, we have added a warning that is served as a comment in generated migration files, to always make sure that the added file has the latest timestamp. I’ll welcome any comments on whether we should be doing anything else to make sure we don’t introduce migrations with incorrect versions, or if the current approach seems to be enough to mitigate the risk.


Perhaps this is a tabs vs spaces difference: consider RoR moved from sequence numbers to timestamps for the same reason we did. At the end of the day a service could switch to the format it prefers. I might suggest waiting for a greenfield service to try it out.

This is good, thanks for this. I think the key to me is avoiding the situation where a production environment runs into this issue. And that’s only possible if they deploy and try to upgrade from a Release Candidate of a Service. With that in mind I think we could update our guidance to implementers, and at the moment I think the best locations are:

I think the phrasing for that advice to implementers is something such as:

Releases may be a pre-release, called a Release Candidate, that precede the final release of a specific version. These pre-releases are generally suited for testing audiences and user acceptance testing and not production environments. It should not be expected that a Release Candidate is either stable enough for production, nor may be upgraded to/from without a loss of data.


Hi Josh et al.

Thanks for articulating this updated advice- I agree that the toolkit is an excellent place to start in terms of communicating to implementers. In fact, making improvements to this toolkit is one of our goals for the year so any additional guidance, reference documents, etc. that you come across that can strengthen this toolkit- please do send them to me to incorporate in the toolkit!

I don’t have much to comment on the phrasing of the actual advice other than we want to make sure it flows/makes sense with what is already written in the existing text. Will this go in the ‘Maintenance’ subsection of the toolkit? Are there any supporting documents that we should add for folks to download as a reference? @joshzamor perhaps once you get feedback from the committee and finalize the new text we can touch base quickly over skype/slack to make sure this is integrated into the right place and all the relevant details are included.


Yes in section “4-Maintain”, though perhaps as a bullet point one section up in the “OpenLMIS Version Updates” section.

That sounds good. I’ll add it as an agenda item for the next tech committee and followup with you from there.