Add Proper Migration Up and Down

[irainia]

Background

If we look at the latest commit (referring to this), internally we will have migration mechanism being used. The migration up is executed whenever we run the server. However, if we check even further, no down migration is provided. So, even if there's an issue with the current database schema, it's quite tricky to roll back.

Proposal

To address this, this issue proposes to have such functionality being provided in the Optimus. At the high level, more or less, it will be like the following:

optimus server migrate up
# it will execute all migrate `up`

optimus server migrate down
# it will execute all migrate `down`

optimus server migrate {n}
# n is integer number, with positive means `up` n-time and negative means `down` n-time

The command or the mechanism is flexible, but the point is that the up and down are both defined properly.

Additional Context

Since the mentioned commit use golang-migrate/migrate, we can use .Steps(int) method for the n up or down times.

[sbchaos]

We also need to update the version of golang-migrate library to latest version in a patch release on top of current release, so that later if we need to do a rollback, we will not be stuck in incompatible version.

[irainia]

TLDR

• we will have Optimus migration up and rollback only
• migration up is run automatically when server up
• migration rollback is run using client command, for example:

optimus migration rollback

• before rollback, ensure no optimus server is running

Approach

This analysis is based on Optimus v0.3.5 with migration version 27.

Functonalities

The following is the available functionalities for migration:

• migration up: migrate to the latest

  this function is run when we start optimus server. it means whenever there's a new release, we will migrate the latest migration version. for this case, we assume that the current optimus server that does the migration up is the latest version. meaning, the database migration version should be less than or equal to optimus server's migration version.

  the following is the flow:

  1. get the previous optimus version based on the currently active migration version
  2. we migrate all the way to the latest version
  3. create (if not exist) a table to store:
     • current optimus server version
     • optimus server version prior to the current version (used when rollback)
     • current optimus server's migration version
  4. get the current optimus server version
  5. get the current mgiration version
  6. store current and previous (if exist) optimus server versions and the current migration server to the table

  to make it easier, let's name the new table mentioned in the 3rd step above as migration_versions. we can rename later if required. and to make it even easier to imagine, the table is more or less like the following:

  current_optimus_version	previous_optimus_version	current_migration_version
  v0.4.0	-	27

• rollback: rollback database migration into the state before the current one

  for this functionality, we assume that the migration version is comparable, where the higher the version value is the newer the version is.

  for consideration, rollback functionality is done through client command. the approach presented here expects optimus server down time before rollback operation to avoid database getting corrupted. also, the optimus client version that does the rollback should be the same as the current optimus server version.

  the following is the flow:

  1. get optimus client version
  2. get the currently active migration version from schema_migrations table
  3. get the largest previous_optimus_version from migration_versions that has current_optimus_version and current_migration_version the same value as step 1 and 2
  4. if no record, then reject the request and return error
  5. look for migration_versions from table migration_versions that has current_optimus_version the same value as the value of previous_optimus_version from the previous step (this step can be optimized using query)
  6. run migration version based on the result from the previous version

  note: if server down is not expected based on the newer requirement in the future, we can come up with an approach later. for example, commanding optimus server through client to rollback and stop any database transaction before optimus server is updated.

  as mentioned, rollback is done through optimus client command. but, this command is not expected to be run by the general user. the following is how the command is run:

  optimus migration rollback
  

  this command is hidden and requires the same configuration as the related server.

Examples

for more concrete flow, we can take an example based on the two functionalities above. for this example, let's assume that optimus v0.3.5 and migration 27 is the current version and the version with the above functionalities are v0.4.0 with migration 27.

note: we do not support migration down for version older than v0.4.0

• case 1

  v0.4.1 is about to be released, no changes in schema migration. steps:

  1. migration up
  2. store v0.4.1 -- 27 to table migration_versions, table state is presented below

  current_optimus_version	previous_optimus_version	current_migration_version
  v0.4.0	-	27
  v0.4.1	v0.4.0	27

• case 2

  v0.4.1 is about to be released, with changes in schema migration, let's say its version 28. steps:

  1. migration up
  2. store v0.4.1 -- 28 to table migration_versions, table state is presented below

  current_optimus_version	previous_optimus_version	current_migration_version
  v0.4.0	-	27
  v0.4.1	v0.4.0	28

[irainia]

When doing migration up, sometimes we encounter failure, such as from SQL syntax error. Solving this is quite tricky. If we look from the library's point of view (https://github.com/golang-migrate/migrate), we can utilize the Force method to help resolve it. However, its usage needs careful consideration. For the first iteration, we don't implement it, yet. We can implement it later.

[sbchaos]

Use of force parameter should not be the norm, we can only consider force as a param in case of exceptional circumstances, otherwise its use can corrupt the database.

In case of error during error during migration, we should try to leave database in consistent state at the previous migration version (we cannot guarantee in case any destructive operation is present) and exit returing the error.