Entity Framework Migration Azure DevOps Release Pipeline

Question

I'm trying to run migration on Azure DevOps Release Pipeline. Because I want to run my DB scripts automatically on every release. My release pipeline does not have source code, I just have compiled DLLs.

When I execute this command on my local machine, it runs successfully. How can I convert this command so I can use it with DLLs.

dotnet ef database update --project MyEntityFrameworkProject --context MyDbContext --startup-project MyStartupProject

Answer 1

Another approach is to generate migration script (a regular sql script) during build pipeline and make this script a part of your artifact. To do so run following command:

dotnet ef migrations script --output $(build.artifactstagingdirectory)\sql\migrations.sql -i

Note -i flag which makes this script runnable multiple times on the same database

Once you have this script as a part of your artifact you can run it on database in your release pipeline by using Azure SQL Database Deployment built in task.

Check this link for more info

EDIT: As @PartickBorkowicz pointed out there are some issues related to the fact that database is not available in a regular way from Build/Release Agent perspective. Here are some additional tips how to live without a database and connection string stored anywhere in your code.

1. Build pipeline

If you do nothing, an Build Agent will require database connection in order to run dotnet ef migrations script script. But there's one trick you can do which will allow you to work without database and connection string: IDesignTimeDbContextFactory

It's enough that you create class like this:

public class YourDesignTimeContextFactory : IDesignTimeDbContextFactory<YourDbContext>
{
    public YourDbContext CreateDbContext(string[] args)
    {
        var databaseConnectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;Initial Catalog=LocalDB;Integrated Security=True;Pooling=False";
        var builder = new DbContextOptionsBuilder<YourDbContext>();
        builder.UseSqlServer(databaseConnectionString);

        return new YourDbContext(builder.Options);
    }
}

Once it is present in your project (you don't need to register it anyhow) your Build Agent will be able to generate sql script with migrations logic without access to actual database

2. Release pipeline

Now, you're having sql script generated and being part of artifact from a build pipeline. Now, release pipeline is the time when you want this script to be run on actual database - you'll need a connection string to this database somehow. To do so in secure manner you should not store it anywhere in the code. A good way to do it is to keep password in Azure Key Vault. There's built in task in Azure Release pipelines called Azure Key Vault. This will fetch your secrets which you can use in next step: Azure SQL Database Deployment. You just need to setup options:

AuthenticationType: Connection String
ConnectionString: `$(SqlServer--ConnectionString)` - this value depends on your secret name in Key Vault
Deploy type: SQL Script File
SQL Script: $(System.DefaultWorkingDirectory)/YourProject/drop/migrations/script.sql - this depends how you setup your artifact in build pipeline.

This way you're able to generate migrations without access to database and run migrations sql without storing your connection string anywhere in the code.

Answer 2

If you don't want to include your source code with the artifacts you can use the following script:

set rootDir=$(System.DefaultWorkingDirectory)\WebApp\drop\WebApp.Web
set efPath=C:\Program Files\dotnet\sdk\NuGetFallbackFolder\microsoft.entityframeworkcore.tools\2.1.1\tools\netcoreapp2.0\any\ef.dll
dotnet exec --depsfile "%rootDir%\WebApp.deps.json" --additionalprobingpath %USERPROFILE%\.nuget\packages --additionalprobingpath "C:\Program Files\dotnet\sdk\NuGetFallbackFolder" --runtimeconfig "%rootDir%\WebApp.runtimeconfig.json" "%efpath%" database update --verbose --prefix-output --assembly "%rootDir%\AssemblyContainingDbContext.dll" --startup-assembly "%rootDir%\AssemblyContainingStartup.dll" --working-dir "%rootDir%"

It turns out you can get away with the undocumented dotnet exec command like the following example (assuming the web application is called WebApp):

Note that the Working Directory (hidden under Advanced) of this run command line task must be set to where the artifacts are (rootDir above).

Another option is to install Build & Release Tools extension and use the "Deploy Entity Framework Core Migrations from a DLL" task.

You can read more info here, here and here.