Database Migrations

General

We use the Knex library through the node-database library. See https://knexjs.org/guide/migrations.html for more information on writing migrations.

Migrations that are always safe to perform

1. Adding a new table
2. Adding a nullable column

Migrations with breaking changes

Examples

1. Dropping tables, procedures, or columns are always breaking.
2. Renaming an existing table or column is always breaking.
3. Adding a constraint MIGHT be breaking.
4. Changing a columns data type MIGHT be breaking.

How to promote breaking changes to production

Migrations need to be compatible with the current production version of a service before they can be merged and deployed.

Ideas

1. For dropping tables, columns, or adding constraints you should promote the new version of the service first, then follow up with the database migration PR that modifies the database schema to be perfectly aligned with the new version.
   1. Merge & Deploy Service PR that removes DB references
   2. Merge & Deploy migration that removes the DB objects
2. Avoid renaming tables and changing to more restrictive column data types. If this must be done it should be done in a maintenance window to minimize customer impact.

Migrations that may require out of band application

Any schema change that could take a very long time to complete should be applied out of band using a sql script. These would be created and added into banno-data-one-off-scripts. It’s important to understand the schema changes still need to exist in a migration, or you may become unable to recreate the database from the migrations.

1. Create a sql script in banno-data-one-off-scripts that performs the long running schema change.
2. Create a migration that performs the same schema change if it isn’t already applied.

Examples of long running migrations

1. Adding an index to a very large table
2. Adding a non nullable column to a very large table
3. Adding a constraint to a very large table