We use a
migrate.ps1 PowerShell script to apply migrations to the local development database. This
script handles the different database providers that we support.
For instructions on how to use
migrate.ps1, see the Getting Started section for
Creating Migrations for New Changes
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.
We recommend reading Evolutionary Database Design and T-SQL Code Style first, since they have a major impact in how we write migrations.
In accordinance with the tenets of Evolutionary Database Design, each change needs to be considered to be split into two parts:
- A backwards-compatible transition migration
- 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 Migration
- Modify the source
- Write a migration script, and place it in
util/Migrator/DbScripts. Each script must be prefixed with the current date.
Non-Backwards Compatible Migration
- Copy the relevant
- Remove the backwards compatibility that is no longer needed.
- Write a new Migration and place it in
src/Migrator/DbScripts_future. Name it
- Typically migrations are designed to be run in sequence. However since the migrations in DbScripts_future 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.
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
pwsh ef_migrate.ps1 [NAME_OF_MIGRATION]
This will generate the migrations, which should then be included in your PR.