Database migrations

4ME database migrations

4ME stores state in a PostgreSQL database. The database schema is bound to change as 4ME evolves. The process of controlling how the schema evolves is called schema migration or database migration.

Forward migrations

Forward migrations are performed by the @4me/graphql.server package automatically when the container starts.

Backward migrations

Backward migrations are usually not necessary since 4ME migrations are written to be backward compatible.

However, practise and theory can sometimes be different and back migrations might be needed in some cases.

Please note that backward migrations require the latest version of the software to be run. Each database migration implements two functions, one to go forward and implement the migration, the other to go backward and revert it. This means that to be able to revert a migration, the software must know about said migration.

For instance, in 4ME v2.0.8, the latest migration was 20180113095656_AddNeutralToWorkload.js. 4ME v2.0.8 introduces a new migration called 20180817081914_AddWhiteToWorkload.js. When upgrading from v2.0.7 to v2.0.8, 20180817081914_AddWhiteToWorkload.js is run on @4me/graphql.server start up and database schema is migrated from version 20180113095656 to 20180817081914. When you revert to v2.0.7, the database schema is still at version 20180817081914 which should be compatible with v2.0.7. However, if issues appear, rolling back the database migration might be a good starting point to solve them. Now, since v2.0.7 is running, this version has no idea how to perform the migration back to 20180113095656. The back migration must be run with the version that performed the forward migration.

Please note that a bug in the implementation prevented an earlier version of 4ME to run against a newer database schema version. More information here. This means that if you wish to downgrade from a version between 2.0.12 and 2.0.9 to a version below 2.0.8, you MUST run the downward migration.

Also, note that backward migrations will lead to data loss. For instance, if a migration adds a new column to a table, rolling back this migration will remove the column and the data it contains.

Backward migration procedure

First you need to identify the database schema version you wish to rollback to. For this, use the following table:

4ME version to rollback to	Migration identifier
2.0.13-2.0.9	No database rollback needed
2.0.8-2.0.3	20180817081914_AddWhiteToWorkload

Now with the newer @4me/graphql.server container running, execute this command :

$ docker-compose exec graphql yarn migrate down --to <Migration identifier>

For instance, if you're running 2.0.12 and with to rollback to 2.0.9, there is nothing to do.

Another example, if you wish to rollback to 2.0.7 and you're running 2.0.9, you need to run the following command:

$ docker-compose exec graphql yarn migrate down --to 20180817081914_AddWhiteToWorkload
yarn run v1.3.2
$ knex-migrate down --to 20180817081914_AddWhiteToWorkload
↓ migrations/20180817081914_AddWhiteToWorkload.js
Done in 0.51s.

You can then proceed and downgrade the running version of @4me/graphql.server.