Entity Framework DB migration for production Azure App Service (use SqlCmd in Dockerfile?)

Question

I have an Azure App Service "Web App for docker containers" which needs to have Entity-Framework database migrations run before the Web App goes online. The MS Documentation (here too) says that the Web App should not instigate the migration. So I assume the migration must come from a run-time command in the docker image...the same docker image that contains the Web App. I say "run-time" because my Azure ARM deployment leverages github CI/CD "actions", which don't have a migration capability at deployment time. Anyway, the corresponding multi-stage dockerfile correctly generates the migration.sql file that I need, but I'm not sure subsequently how to install and execute the necessary SqlCmd.exe (at run-time) which runs that prior migration.sql file as a parameter.

Below is my current dockerfile (targeting Windows Server, not Linux), which originally had been generated for me by Visual Studio 2022. The principle modification I made was the installation of the "dotnet EF" migration tool, and the command to build the migration script.

#See https://aka.ms/containerfastmode to understand how Visual Studio uses this Dockerfile to build your images for faster debugging.

#Depending on the operating system of the host machines(s) that will build or run the containers, the image specified in the FROM statement may need to be changed.
#For more information, please see https://aka.ms/containercompat

FROM mcr.microsoft.com/dotnet/aspnet:6.0 AS base
WORKDIR /app
EXPOSE 80
EXPOSE 443

FROM mcr.microsoft.com/dotnet/sdk:6.0-windowsservercore-ltsc2022 AS build
RUN dotnet tool install --global dotnet-ef
#ENV PATH="$PATH:/root/.dotnet/tools"
WORKDIR /src
COPY ["Alereem.WebAPI/Alereem.WebAPI.csproj", "Alereem.WebAPI/"]
COPY ["Alereem.Data/Alereem.Data.csproj", "Alereem.Data/"]
RUN dotnet restore "Alereem.WebAPI/Alereem.WebAPI.csproj"
COPY . .
WORKDIR "/src/Alereem.WebAPI"
RUN dotnet build "Alereem.WebAPI.csproj" -c Release -o /app/build
RUN dotnet ef migrations script -p ../Alereem.Data/Alereem.Data.csproj -s ./Alereem.WebAPI.csproj -o /app/publish/migrations.sql

FROM build AS publish
RUN dotnet publish "Alereem.WebAPI.csproj" -c Release -o /app/publish /p:UseAppHost=false

FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
# HOW DO I ADD AN INSTALLATION OF 'SQLCMD.EXE'?
# AND HOW DO I ADD THE INVOCATION OF SQLCMD, ALLOWING
# FOR THE DB CONNECTION STRING TO BE PASSED AT RUN-TIME
# AS AN ENVIRONMENT VARIABLE?
# ...<RUN SQLCMD.EXE MIGRATION HERE>...
# THEN RUN MAIN APPLICATION...
ENTRYPOINT ["dotnet", "Alereem.WebAPI.dll"]

The dockerfile comments I've added at the end represent the problem I have. I've found documentation online suggesting a canned base image I can use that already has sqlcmd installed, but I assume unfortunately I must keep the base images above that Visual Studio created for my Azure App Service app. Also, according to this SO post, I've learned that running two run-time executables isn't "normal" in a dockerfile.

At this point, I need to do one of the following:

• Simply figure out how to make the above dockerfile run a migration. Maybe use tricks from that last link? This is mostly what this question is about.
• Start using Docker Compose in my Azure Web App (a capability that is in preview). One container would run the migration, the other would run the app. This option might be ideal, though I am wary of using a "preview" feature for my production needs.
• Give up, and have the migration run from inside my application (despite documentation warning not to do this).
• Find a way to run the migration that is none of the above. For example, this SO Post essentially attempts to solve the same problem by using a "deployment-time PowerShell" script. I have no idea what platform is supporting this so-called "deployment time" operation - the post doesn't say. So I don't know if I can use this.

Regarding that last point: Again, I am using github "actions" for my CI/CD. I don't know a way for the github build agent to connect to the target database within my Azure tenant. And even if there were a way for that to happen, I have a requirement to have a complete deployment from simply executing my Azure ARM template. This means I don't have the option of hand-editing the github "actions" file to support the deployment. This is why I'm seemingly left only with the option of having the docker image be responsible for causing the migration...once it is launched at run-time.

Guidance would be appreciated.

Answer 1

My question hovered around an assumption that executing the Entity Framework DB migration needed to occur in the docker container entry point.

However, what I'm ultimately attempting to produce...is an Azure Marketplace "Managed App" deployment bundle. That bundle somehow needs to perform the EF migration. In that context, I now have learned that you can have a deployScript in an ARM template perform the migration. Here is an example that was designed as an Azure Marketplace deployment. That example assumes that you've pre-generated an EF migration script.sql that is kept in an artifacts folder of the marketplace app deployment "bundle". The deployScript then simply runs an Invoke-Sqlcmd to run the script.sql file.

Update

While the above approach works, it has the limitation that the version of my Managed App marketplace offering is not kept current with my code base. So I have adjusted my strategy again, so that I use the ARM deployScript to call a MigrateDB endpoint on the Azure App Service which the ARM template is also responsible for deploying. The Azure App Service has a Docker container image that has the script.sql within it. Using this approach, any customer who deploys my Azure Marketplace Managed App always has my latest code, because the ARM template (in regard to the Azure App Service defined within) is defined as pulling my latest docker image from the Azure Container Registry (ACR).

Now my only downside is that I need a way to keep the Azure App Service offline (including if it is one instance among several running in parallel in a server farm) until the MigrateDB endpoint is called. I still haven't figured that one out yet, but this still represents a step forward.