MOR-PROC-031 Version 1 Last Review Date: Jan, 2025
Database Migrations
1. Overview
MoreCore database schema is developed using the code-first approach and by utilizing Entity Framework ORM facilities to achieve this. Code-first means that object model is designed first and the backing relational schema is automatically generated based on the object schema.
There are two types of changes that can be applied to a database: changes to model/schema and changes to data. The first type of changes (changes to schema) can be handled automatically, based on the changes made to object model and meta-data about the previous version of the database. MoreCore uses Entity Framework capacities to produce a delta of changes between database versions. Data changes are specific and need to be handled manually, on a per-needed basis.
The usual sequence of steps when producing and applying a database migration is given below.
Make schema changes and prepare data migrations
- This step involves changing, removing or adding object model classes.
- Also, usually at this time, scripts to migrate data are added to solution. These are added manually, but need to follow the strict naming convention to enable as smooth deployment procedure as possible. Scripts are located in the DataUpdates folder within PMMS.Services.DataAccess project and their names must be in format "YYYY-mm-DD-HHMM.sql".
Produce an EF migration
- At this step, an EF migration is produced by running "Add-Migration [Migration Name]" command from within Visual Studio. This command needs to be run against a version of the database that contains all previous migrations applied.
- At this point, it should be clear that migrations are a sequential thing and that producing migrations across multiple code branches in parallel can be problematic, because the sequential nature of migrations would be broken. For this reason, when merging branches with diverging migrations it is necessary to establish one branch as baseline and re-perform the migration creation process for the other branch. This is usually done at the point when branches are merged to master and prepared for release. Basically, at merge time already generated migrations of the diverging branch, that are based on an outdated database state, are deleted and generated again.
Prepare EF migration scripts
- Migration scripts are applied manually to target databases, i.e. auto-upgrade is not enabled and scripts are run via SSMS. Auto-upgrade is a process where database can be upgraded at runtime, when EF session is first built. Due to inherent dangers and unsupervised nature of this process, MoreCore doesn't utilize this capability of EF.
- A migration script is produced from a code-based migration by running "Update-Database -Script -Source [Last Migration on Target Database Name]" command. This creates a delta between the current version of the database and the target version in the form of a SQL script, that can then be run manually.
Apply migrations to target database
- In this step, SQL script produced in previous step is executed against the target database. Note that, except in some edge cases that may potentially exist, schema migration script needs to run first.
- Also in this step, data migration scripts located in DataUpdates folder of the PMMS.Services.DataAccess project are run - of course, only the scripts that are not applied previously are run. Unlike schema migrations, where a history of applied migrations is kept in a metadata table, the process of determining what data update scripts need to be run is based on analyzing the Git history of the deployed branch (master for production) and applying all newly added scripts in order.
- Note: in certain scenarios, where either schema or data updates touch a lot of rows - triggers may be interfering with the process and prolonging the time needed to perform the migration. In these instances, analysis needs to be made to determine what tables are affected by the migrations and whether temporarily disabling triggers before running the migration needs to occur. This, however, depends on the contents of specific migrations and, as it's not a formalized - but a situation-driven - process, won't be described in further detail here.
- Goes without saying - all access to database should be disabled before any migration scripts are run and re-enabled after. This may be as simple as stopping all applications accessing the database and re-starting them after the migrations are completed.