Make migrations work for us in water-abstraction-system

[Cruikshanks]

water-abstraction-system is already using the migration engine we want to use (Knex migrations). But we are working in this 'weird-world' where the migrations we've written are focused on creating tables that already exist. This is because we are dealing with a legacy service with an existing DB.

Because of this, we can only run these migrations against a test DB. We are now working on a feature where for the first time we need to create our tables. We'd love to do this in water-abstraction-system but because of how we've set things up have managed to lock ourselves out of doing this. We can't just add our new 'proper' migrations amongst the ones we've created to support unit tests.

Fortunately, there does appear to be a way out. Currently, our knexfile.js configures the migration config inside the default config.

const defaultConfig = {
  client: 'postgres',
  useNullAsDefault: true,
  migrations: {
    tableName: 'knex_migrations',
    directory: './db/migrations'
  },
  seeds: {
    directory: './db/seeds'
  }
}

We then refer to this config when generating the config for each environment (development, test, production).

const development = {
  ...defaultConfig,
  connection: defaultConnection
}

const test = {
  ...defaultConfig,
  connection: {
    ...defaultConnection,
    database: DatabaseConfig.testDatabase
  }
}

const production = {
  ...defaultConfig,
  connection: defaultConnection
}

So, Knex already gives us the ability to do something different for each environment. Reading the docs it tells us that migrations: { directory: '' } can be an array. We can even tell Knex to treat the migrations in each directory separately with sortDirsSeparately:.

So, for development and production we can just run our 'proper' migrations. In test we should be able to configure it to run the current migrations that recreate legacy tables, and then our 'proper' migrations.

[Jozzey]