<\/p>\n
This article is a part of a series of articles where we dive deep into one of these Engineering Fundamentals. <\/span><\/p>\n Each article will share the team\u2019s journey, challenges, and learnings to apply each principle alongside the customer\u2019s team. <\/span><\/p>\n Other articles in this series are:<\/span><\/p>\n In this article, we will cover d<\/span>atabase change management using Entity Framework<\/span><\/p>\n .<\/span><\/p>\n <\/p>\n Our team had an opportunity to develop a Web Application (WebApp) intended to leverage an Object-Relational-Mapper (ORM) to define our data models. We also wanted to seamlessly interact with the ORM directly within our codebase. The purpose of this content is to share our journey and how we planned for where an ORM fits within the CICD pipeline.<\/p>\n Throughout this section, we\u2019ll share details on how we accomplished our goals, the challenges that surfaced, and things worth thinking about if you envision your team working on similar efforts. But to start, let\u2019s cover our guiding considerations and motivations for this effort.<\/p>\n This article is not intended to present the advantages\/disadvantages of using ORMs, whether managing DB Schema changes should be part of a CICD flow or not, nor to explore alternatives to manage your DB Schema. I encourage you to perform more focused research on this effort before adopting this design pattern.<\/p>\n With that said, we were already leaning on using an ORM and embedding DB Schema changes as part of our CICD flow. Before jumping to solutioning, as a team we defined the following key motivations and requirements that we wanted to hold ourselves accountable for:<\/p>\n Ease of applying changes to the DB Schema while incorporating versioning. Changes may include applying changes (going to a newer version), or reverting changes (reverting to a previous version).<\/p>\n Our tech stack was based on .NET Core, with a SQL Database (DB). Therefore, we naturally gravitated towards using Entity Framework (EF)<\/strong> for our ORM.<\/p>\n The Microsoft Entity Framework Core documentation<\/a> outlines the two primary ways for keeping the application code in sync with the DB schema:<\/p>\n We recommend considering your organization\u2019s Software Development processes, DevOps and DevSecOps processes to balance the pros and cons before choosing the appropriate option. On our team, we elected to go with the migrations<\/strong> strategy, as this provided the desired behavior of having the DB schema evolve alongside the codebase.<\/p>\n Before describing our approach, I want to paint a very high-level visual of how EF Migrations<\/strong> work.<\/p>\n Key players\/components:<\/strong><\/p>\n Key pieces:<\/strong><\/p>\n The Interaction between the players and the key pieces are as follows:<\/p>\n Note:<\/strong> Having the complete history of all migration Files and the \u201cEF Migrations History\u201d DB table <\/u>enables applying changes on top of the current schema or reverting\/rolling back schema changes to any previous state.<\/p>\n The pattern that we applied was to not use migrations<\/strong> until the team determined that we were close to deploying the first release to production.<\/p>\n This offered the following advantages:<\/p>\n It is important to note how invaluable it was for the team to undergo the manual steps required to create, review, commit, and apply migrations to a database. Namely, they allow for the ability to:<\/p>\n A crucial take-away is that once you start<\/strong> managing your DB schema with migrations<\/u>, you should only<\/strong> use migrations to alter the DB schema.<\/em><\/p>\n<\/blockquote>\n The primary reason for this is that, from the very first migration created, the DB Schema\u2019s \u201cHistory\u201d is now version-controlled as a part of the codebase. This means that, as with any other code versioning solution, altering the history outside of the provided tooling can have drastic implications, resulting in \u201cversion conflicts\u201d between:<\/p>\n What could be the business impact, you ask? Bad deployments that could lead to application outages, failures, errors, or data loss<\/strong><\/p>\n To support creating EF Migrations, the WebApp had to be designed with the intention to influence how the application would start up, given different environment variables\/runtime configurations.<\/p>\n Specifically, we wanted to use the EF Dotnet CLI<\/strong><\/a>, which can only create migrations by having the application startup and successfully connect to the targeted DB on a locally hosted environment. This may pose a challenge if the WebApp has additional external hard dependencies. For example: any external service or component that would prevent the application from starting up if improperly configured.<\/p>\n The design that we chose was to have a specific \u201cDebugEF\u201d runtime that would start the application in a \u201cbarebones\u201d state \u2013 one without any external dependencies besides the DB. Thanks to our adoption of Dependency Injection<\/a> (DI), this approach resulted in no code changes outside of the \u201cstartup\u201d file in .NET Core.<\/p>\n Below is an example of a .NET Core WebApp\u2019s Startup.cs <\/em>file, which is where the application\u2019s startup configurations are defined. Notice how we leveraged .NET preprocessor directives<\/a> in order to avoid starting the application with all the external dependencies when running against an \u201cDebugEF\u201d run configuration.<\/p>\n <\/p>\n Note:<\/em><\/strong> Be careful not to abuse or overuse preprocessor directives, as they can lead to challenges when it comes to code maintainability and readability.<\/em><\/p>\n All that is required by the developer is to set the values for the required environment variables: \u201cSQL-SERVER-FQDN\u201d and \u201cSQL-DB-NAME\u201d<\/p>\n This design would then allow the development team the ability to use the EF DotNet CLI<\/em> to:<\/p>\n Although we have not gone live to production yet, we intentionally decided not to automate the DB Change Management for production environments. This is due to the severe implications of a bad change being introduced and the probability of this occurring.<\/p>\n Instead, we planned on automating the DB change management in lower lifecycle environments (still using migrations) before applying the application changes on top of it. This would allow us the ability to validate that a DB (regardless of the current migration it is on) could be promoted to the latest migration. Upon validating this, the CICD flow would be allowed to proceed with all our application-specific workflow items (linting, building, unit testing, packaging, deploying, integration tests).<\/p>\n The flow can be visualized in the Appendix below. This section will provide a description of each step.<\/p>\n All commands are assumed to be executed in the same path where the \u201cStartup.cs\u201d file is found in.<\/p>\n These steps are all done from a developer\u2019s perspective, using their own unique DB instance<\/u>:<\/p>\n This command will leverage the environment variables, from step #2, to go through all the migration Files in the code. It will then apply them 1-by-1 until the DB Schema reflects the latest desired state. This command will also create and populate the \u201cEF migrations\u201d DB table<\/p>\n Unlike with DEV, where migrations can be applied to the DB using the CLI, the upper-lifecycle environments like User Acceptance Testing (UAT) or PROD should have the DB changes applied using a SQL script. This script can be generated, using the EF Migrations CLI, so that the same operations are performed as in lower-lifecycle environments, like DEV.<\/p>\n <\/p>\n DotNet EF CLI<\/a><\/p>\n EF Core Migrations Overview<\/a><\/p>\n\n
Introduction<\/h2>\n
\n
Objectives<\/h2>\n
\n
\n
\n
\n
\n
Solution<\/h2>\n
\n
\n
\n
\n
\n
.NET Core Web App Design<\/h2>\n
\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/cse\/wp-content\/uploads\/sites\/55\/2022\/03\/ef-migrations-configuration-2-1024x614.png\")
<\/a><\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/cse\/wp-content\/uploads\/sites\/55\/2022\/03\/ef-migrations-configuration-1.png\")
<\/a><\/p>\n\n
dotnet ef migrations add <name-of-migration-to-create> --configuration DebugEF<\/code><\/li>\n<\/ul>\n\n
dotnet ef database update\u00a0 <name-of-migration-to-apply> --configuration DebugEF<\/code><\/li>\n<\/ul>\nOverall Flow<\/h2>\n
Assumptions<\/h3>\n
\n
Steps in the Flow<\/h3>\n
Development \/ PR Creation<\/h4>\n
\n
\n
\n
dotnet ef database update --configuration {configuration_value}<\/code><\/li>\n<\/ol>\n\n
dotnet ef migrations add {name-of-migration} --configuration {configuration_value}<\/code><\/li>\n<\/ol>\n\n
dotnet ef database update {name-of-migration} --configuration {configuration_value}<\/code><\/li>\n<\/ol>\n\n
dotnet ef database update {name-of-previous-migration} --configuration {configuration_value}<\/code><\/li>\n<\/code>Create a PR<\/strong> once the DB Change produces a healthy change and that all tests <\/span>pass. The PR should contain the newly added migration file.<\/span><\/li>\n<\/ol>\nMerging PR and Promoting to DEV<\/h4>\n
\n
\n
dotnet ef database update {name-of-migration} --configuration {configuration_value}<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n
\n
dotnet ef database update {name-of-previous-migration} --configuration {configuration_value}<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n
\n
The team reverts the merge<\/strong> and kicks off a new CICD deployment to revert the deployment.<\/h2>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n
Promoting to UAT and PROD<\/h4>\n
\n
\n
dotnet ef migrations script {name-of-current-migration} {name-of-desired-migration} --configuration {configuration_value}<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n
dotnet ef migrations script {name-of-desired-migration} {name-of-current-migration} --configuration {configuration_value}<\/code><\/li>\n\n
Appendix<\/h2>\n
EF NonProd Migrations Flow<\/h2>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/cse\/wp-content\/uploads\/sites\/55\/2022\/03\/ef-migrations-nonprod-flow.png\")
<\/a><\/p>\nEF Prod Migrations Flow<\/h2>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/cse\/wp-content\/uploads\/sites\/55\/2022\/03\/ef-migrations-prod-flow.png\")
<\/a><\/p>\nResources<\/h2>\n