Working on a recent project that has a small database to store data for post processing, we turned to ef for dotnet core. We used a
code-first approach and leveraged
migrations. The only last piece we had to figure out is how to have these changes make it to our servers via
Thankfully there is a nice little
cli for ef;
dotnet-ef. The cli made it easy to include new migrations/features into the project and also help us facilitate getting those changes out into the wild. Below are a few of the commands and snippets we used to accomplish this:
Below covers just some of the basic cli commands to get up and running.
Pretty basic, but just getting it installed for use later…
dotnet tool install --global dotnet-ef
Adding a migration
Adding a new migration; could be a new “feature” or a set of significant changes
dotnet ef migrations add FeatureName
This command will actually update the database that is part of the current project, given the current migration files.
dotnet ef database update --project .\src\YourProject.csproj
Assuming you are now at a point where you have your migrations in source and are ready to deploy, below should help guide you thru getting those changes out onto servers.
Create a migration script
After all your standard dotnet core build tasks, add a
powershell task like so:
- task: PowerShell@2 inputs: targetType: 'inline' script: | dotnet tool install --global dotnet-ef dotnet ef migrations script --idempotent --project $(Build.SourcesDirectory)\src\YourProject.csproj --output $(Build.ArtifactStagingDirectory)/db.sql
What the above does is install the
dotnet-ef cli within the context of the hosted agent and then use it to create a single script to encompass all the migration changes. As you can see we specify the
--output folder so it gets picked up by the
publishBuildArtifacts task and a
--idempotent flag. The
--idempotent flag encompasses all changes with a check to the
__EFMigrationsHistory to see what version of migration it is on, thus allowing you to both create from scratch and iterate over an existing database with the same script:
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200923211638_InitialCreate')
Now that we have a single script that can handle all things database schema, let’s make sure the release is capable of executing this.
Deploy database changes in Release
Assuming you are at a point where all your web assets are already being deployed successfully, let’s find the
SQL Server database deploy task. Search for it by name in the task search and add it to your release. This task is able to deploy
dacpac files as well as
sql scripts, we will be using the latter. After adding the task, populate it using the following:
- Deploy SQL using:
Sql Query File
- Sql File:
- Execute within a transaction:
- Server Name:
<the sql server instance, with respect to where your release agent is running>
- Database Name:
- Make sure the database at exists
- If using
Windows Authentication, make sure the user that the release agent runs as has access to this database.
You should now be able to encompass all your database changes in source control, generate a change script that respects the database version and effectively deploy those changes to the database.