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

Hi, how the things going? What about the solution shared below? Considering accept one answer which you think it is work for you. — Mengdi Liang, Oct 24 '19 at 10:24

Tomasz Madeyski · Answer 1 · 2020-03-15T19:38:17.013

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.

Worth noting that the build pipeline agent will need your ef connection string in order to generate the scripts, just as Add-Migrations needs it in order to talk to the database. This is fine if you have the connection string hardcoded in your source code (e.g appsettings.json), but can become a problem if you store your credentials on the target server (e.g in Azure). — Patrick Borkowicz, Mar 14 '20 at 03:48
@PatrickBorkowicz this is correct, but Azure KeyVault comes handy in this case - you can store connection string there and access it from your pipeline without need to store it anywhere in code — Tomasz Madeyski, Mar 14 '20 at 11:34
That's an amazing idea; I may give it a try. Add-Migrations is a read-only operation (as far as I know) so it would be acceptable at build time, though it does require the database to exist and be online. You could even limit its scope by using a dedicated database user/connection string with limited access. I have been considering this approach. Currently we deploy migrations at runtime (on startup) with context.Database.Migrate(). This works great, but does require an app restart. — Patrick Borkowicz, Mar 15 '20 at 16:22
@PatrickBorkowicz check my edit as I added some clarifications which might be handy — Tomasz Madeyski, Mar 15 '20 at 19:38
@TomaszMadeyski I'm attempting to implement this but am unsure how to handle the connection string needed to generate the script during the build pipeline. In your example, the implementation of IDesignTimeDbContextFactory contains a hard-coded connection string, but later you also write that Azure KeyVault can be used to "access it from your pipeline without need to store it anywhere in code". Could you please elaborate on how the approach in your example could be avoided in favor of using KeyVault to get the connection string for generating the script? — Boris Layvant, Sep 15 '20 at 16:46
@BorisLayvant thing is that for script generation you don't need proper connection string (nor database at all). It is generated only based on migrations in code. You only need connection string (and database) when you actually want to RUN migrations (script) against your database. In my case it is done in release pipeline (not build pipeline) and connection string is fetched from KeyVault — Tomasz Madeyski, Sep 17 '20 at 11:22

score 4 · Accepted Answer · answered Oct 15 '19 at 09:09

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.

thanks for the answer, is it possible to get these folders (Nuget Packages, Nuget Fallback and EF Dll) programmatically because the server could use different OS and different EF core version — suphero, Nov 08 '19 at 21:46

Entity Framework Migration Azure DevOps Release Pipeline

2 Answers2

Linked