Skip to main content

Database migrations

When changes are required to the schema of our relational database, we must write corresponding migrations to move the database from its current state to the new state. The migrations also ensure that any user can recreate the database in its current state by running the scripts in sequence.

Applying migrations

This section focuses on how to build migrations. For instructions on how to apply them, see the Getting Started section for MSSQL and Entity Framework.

Any database change must be scripted as a migration for both our primary DBMS - MSSQL - as well as for Entity Framework. Follow the instructions below for each provider.

MSSQL migrations​

Recommended reading

Before continuing, be sure you understand:

In accordance with the tenets of Evolutionary Database Design, each change needs to be considered to be split into two parts:

  1. A backwards-compatible transition migration
  2. A non-backwards-compatible final migration

It is possible that a change may not require a non-backwards-compatible end phase (i.e. all changes may be backwards-compatible in their final form). In that case, only one phase of changes is required.

Backwards-compatible transition migration​

  1. Modify the schema definition .sql files in src/Sql/dbo.
  2. Write a migration script, and place it in util/Migrator/DbScripts. Each script must be prefixed with the current date.
Why do we change the schema?

The separate database definitions in src/Sql/dbo serve as a "master" reference for the intended and final state of the database at that time. This is crucial because the state of database definitions at the current moment may differ from when a migration was added in the past. These definitions act as a lint and validation step to ensure that migrations work as expected, and the separation helps maintain clarity and accuracy in database schema management and synchronization processes.

Additionally, a SQL database project is in place; however, instead of using the auto-generated migrations from DAC, we manually write migrations. This approach is chosen to enhance performance and prevent accidental data loss, which is why we have both a sqlproj and standalone migrations.

Non-backwards-compatible final migration​

  1. Copy the relevant .sql files from src/Sql/dbo to src/Sql/dbo_finalization.
  2. Remove the backwards compatibility that is no longer needed.
  3. Write a new Migration and place it in src/Migrator/DbScripts_finalization. Name it YYYY-0M-FinalizationMigration.sql.
    • Typically migrations are designed to be run in sequence. However since the migrations in DbScripts_finalization can be run out of order, care must be taken to ensure they remain compatible with the changes to DbScripts. In order to achieve this we only keep a single migration, which executes all backwards incompatible schema changes.

EF migrations​

If you alter the database schema, you must create an EF migration script to ensure that EF databases keep pace with these changes. Developers must do this and include the migrations with their PR.

To create these scripts, you must first update your data model in Core/Entities as desired. This will be used to generate the migrations for each of our EF targets.

Once the model is updated, navigate to the dev directory in the server repo and execute the ef_migrate.ps1 PowerShell command. You should provide a name for the migration as the only parameter:

pwsh ef_migrate.ps1 [NAME_OF_MIGRATION]

This will generate the migrations, which should then be included in your PR.

[Not Yet Implemented] Manual MSSQL migrations​

There may be a need for a migration to be run outside of our normal update process. These types of migrations should be saved for very exceptional purposes. One such reason could be an Index rebuild.

  1. Write a new Migration with a prefixed current date and place it in src/Migrator/DbScripts_manual
  2. After it has been run against our Cloud environments and we are satisfied with the outcome, create a PR to move it to DbScripts. This will enable it to be run by our Migrator processes in self-host and clean installs of both cloud and self-host environments