Skip to main content

MSSQL

info

For instructions on how to apply database migrations, please refer to the Getting Started documentation.

tip

We recommend reading T-SQL Code Style since it has a major impact in how we write migrations.

SQL database project​

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.

Modifying the database​

In accordance with the tenets of Evolutionary Database Design every change must be considered as split into two parts:

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

It is likely that a change does not require a non-backwards-compatible end phase e.g. all changes may be backwards-compatible in their final form; in that case, only one phase of changes is required. With the use of beta testing, partial roll-outs, feature flags, etc. the often-chosen path is to spread a change across several major releases with a calculated future state that can perform a "cleanup" migration that is backwards-compatible but still represents an overall-incompatible change beyond the boundaries of what we need for individual release safety.

Backwards compatible migration​

  1. Modify the source .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.

Please take care to ensure:

  • any existing stored procedure accepts the same input parameters and that new parameters have nullable defaults
  • when a column is renamed the existing stored procedures first check (coalesce) the new location before falling back to the old location
  • continued updating of the old data columns since in case of a rollback no data should be lost

Non-backwards compatible migration​

These changes should be written from the perspective of "all data has been migrated" and any old stored procedures that were kept around for backwards compatibility should be removed. Any logic for syncing old and new data should also be removed in this step.

  1. Remove the backwards compatibility that is no longer needed.
  2. 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.

Upon execution any finalization scripts will be automatically moved for proper history.