Introduction
The pipelines bring to us the opportunity to create automatic tasks to execute development operations, like deploys, migrations, tests, etc. Focused in the deploy routine, some applications need other operations to ensure the stability between the application and other resources, like the database.
During the development process, the developers usually customize and improve the application database in the development environment scope, and it can be made easily by using frameworks that contains routines to create source files that have the responsability to keep the history of changes and are used to update the database for determinated version. These files are known as migrations.
In this post, we will see how to apply migrations for the databases that are located in different environments (staging, production, etc.), using the Code-First entity concept, and the Entity Framework Core.
Migrations
To generate database migrations, we can execute the “Add-Migraton <name>” command from the Entity Framework. This command will generate some source files that will contain the code to apply the migration to the database. To execute these migration files, you just need to execute the “Update-Database” command. But, Microsoft recommends to create a SQL file individually, and suggest to execute this file direct to the database, without need to execute an intermediary software (C# + EF) to access the database. To do this, we will have to follow the steps below:
- 1. Create a Pipeline in Azure Devops;
- 2. Install dotnet-ef-tool;
- 3. Generate SQL script;
- 4. Execute SQL script to the database;
- 5. Script File as a Pipeline Artifact.
NOTE: We don’t need to worry about which migration need to be applyed to the database. The dotnet-ef-tool command will generate a script cantaining the needed logic. Check the third step for more details.
1. Create a Pipeline in Azure Devops
We will create a common deployment pipeline in Azure Devops for ASP.NET Core backend applications. Check the end of the post to see the full result of the pipeline.
2. Install dotnet-ef-tool
To execute tool commands of Entity Framework, we have to install a package named dotnet-ef-tool. This needs to be available into the pipeline’s context to be used for the SQL script generation. To install this tool by a pipeline task, we can use the task below:
- task: DotNetCoreCLI@2
displayName: 'Install EF tool'
inputs:
command: 'custom'
custom: 'tool'
arguments: 'install --global dotnet-ef'
3. Generate SQL script
To generate the SQL script containing the logic to apply migrations, we will use the dotnet-ef-tool. By default, the command does not generate the logic to check which migration should be applied, so we have to enable this behavior using the parameter: –idempotent. By using this behavior, we don’t need to worry about the migrations that will be applyed to the database since the generated script already has it. Below, you can see the task that could be used to generate the script file:
- task: DotNetCoreCLI@2
displayName: 'Generate SQL Script'
inputs:
command: 'custom'
custom: 'ef'
arguments: 'migrations script --idempotent --project $(Build.SourcesDirectory)\Data.csproj --output $(System.ArtifactsDirectory)/script.sql'
NOTE: We don’t need to stablish any database connection for the script generation, since we are following the Code First concept for entity creation, so, the command will just look at the entities source code of the project to generate the SQL file. If we don’t use the –idempotent argument, the script will generate the SQL without the conditional logic to determinate which migration should be applied, causing an error if the database already exists.
4. Execute SQL script to the database
Now, with the SQL script available, we just need to execute this file to the database. In this post, we will use a common Azure Database as an example, so for this case, we can use the following task to accomplish this requirement:
- task: SqlAzureDacpacDeployment@1
displayName: 'Update Database'
inputs:
azureSubscription: '<Service Connection Identifier>'
AuthenticationType: 'connectionString' # You can use other method to authenticate to the database if you want
ConnectionString: '<Database Connection String>'
deployType: 'SqlTask'
SqlFile: '$(System.ArtifactsDirectory)/script.sql'
5. Script File as a Pipeline Artifact
Well done. The last task will only be used to make the file available to be accessed by the pipeline runner user. To do this, you can use the task below to add the generated script.sql file to the file bundle (artifacts). To check the artifacts, just access the artifacts option from the pipeline details after the job’s execution.
- task: PublishBuildArtifacts@1
displayName: 'Publish Artifacts'
inputs:
PathtoPublish: '$(System.ArtifactsDirectory)/script.sql'
ArtifactName: '$(artifactName)'
publishLocation: 'Container'
Conclusion
In this post, we could learn an easy way to apply the database migrations to the remote environment by using automation (pipelines) to accomplish it. This method is simple, and does not have any database validation before script execution. So, if you have an active and sensitive environment, be sure that the needed validations are considered before the database update, to ensure that the database will not be update wrongly.
Pipeline Result
trigger:
- none
pool:
vmImage: windows-latest
variables:
solution: '**/*.sln'
buildPlatform: 'AnyCPU'
buildConfiguration: 'Release'
artifactName: 'artifacts'
environment: 'Development'
- task: NuGetToolInstaller@1
displayName: 'NuGet Installation'
- task: NuGetCommand@2
displayName: 'NuGet Restore'
inputs:
restoreSolution: '$(solution)'
- task: VSBuild@1
displayName: 'Build Project'
inputs:
solution: 'backend/WebAPI'
msbuildArgs: '/p:DeployOnBuild=true /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true /p:PackageLocation="$(build.artifactStagingDirectory)"'
platform: '$(buildPlatform)'
configuration: '$(buildConfiguration)'
- task: PublishSymbols@2
displayName: 'Publish Symbols'
inputs:
SearchPattern: '**/bin/**/*.pdb'
SymbolServerType: 'FileShare'
CompressSymbols: false
- task: PublishBuildArtifacts@1
displayName: 'Publish Artifacts'
inputs:
PathtoPublish: '$(Build.ArtifactStagingDirectory)'
ArtifactName: 'artifacts'
publishLocation: 'Container'
- task: DownloadBuildArtifacts@0
displayName: 'Build Artifacts'
inputs:
buildType: 'current'
downloadType: 'single'
artifactName: '$(artifactName)'
downloadPath: '$(System.ArtifactsDirectory)'
- task: DotNetCoreCLI@2
displayName: 'Install EF tool'
inputs:
command: 'custom'
custom: 'tool'
arguments: 'install --global dotnet-ef'
- task: DotNetCoreCLI@2
displayName: 'Generate SQL Script'
inputs:
command: 'custom'
custom: 'ef'
arguments: 'migrations script --idempotent --project $(Build.SourcesDirectory)\Data.csproj --output $(System.ArtifactsDirectory)/script.sql'
- task: SqlAzureDacpacDeployment@1
displayName: 'Update Database'
inputs:
azureSubscription: '<Service Connection Identifier>'
AuthenticationType: 'connectionString'
ConnectionString: '<Connection String>'
deployType: 'SqlTask'
SqlFile: '$(System.ArtifactsDirectory)/script.sql'
- task: AzureRmWebAppDeployment@4
displayName: 'Deploy Application'
inputs:
ConnectionType: 'AzureRM'
azureSubscription: '<Service Connection Identifier>'
appType: 'webApp'
WebAppName: 'armazemlegalapi'
deployToSlotOrASE: true
ResourceGroupName: '<Resource Group Identifier>'
SlotName: 'production'
packageForLinux: '$(System.ArtifactsDirectory)/Project.zip'
- task: PublishBuildArtifacts@1
displayName: 'Publish Artifacts'
inputs:
PathtoPublish: '$(System.ArtifactsDirectory)/script.sql'
ArtifactName: '$(artifactName)'
publishLocation: 'Container'
