CODEWITHSUNDEEP
c#sql-serverdockerasp.net-coreentity-framework-core
lordnoob
lordnoob

Reputation: 355

Using Docker to initialize tables in SQL Server container for ASP.NET Core app

I am creating an ASP.NET Core app using Docker with one container for SQL Server and one container for the ASP.NET server. I am also using the Entity Framework Core code-first approach to deal with my tables.

This means that my ASP.NET Core project has a Migrations folder, generated from my models by running dotnet ef migrations add <name>, which is then applied to a database by running dotnet ef database update from the project folder.

I believe that this command uses the connection string and migration code in my source code to connect to the SQL Server database in the Docker container and create the necessary tables.

I have a working Dockerfile and docker-compose.yml to start the SQL server and the ASP.NET Core server in containers, which were mostly auto-generated -- they look like this:

Dockerfile:

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

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

FROM mcr.microsoft.com/dotnet/sdk:6.0 AS build
WORKDIR /src
COPY ["AspNet/AspNet.csproj", "AspNet/"]
RUN dotnet restore "AspNet/AspNet.csproj"
COPY . .
WORKDIR "/src/AspNet"
RUN dotnet build "AspNet.csproj" -c Release -o /app/build

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

FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
ENTRYPOINT ["dotnet", "AspNet.dll"]

docker-compose.yml:

version: '3.4'
name: AspNet

services:
  aspnet:
    container_name: aspnet
    hostname: aspnet
    image: ${DOCKER_REGISTRY-}aspnet
    build:
      context: .
      dockerfile: AspNet/Dockerfile
    ports:
      - "5000:80"
      - "5001:443"
    links:
      - sqlserver

  sqlserver:
    container_name: sqlserver
    hostname: sqlserver
    image: "mcr.microsoft.com/mssql/server"
    environment:
      - "MSSQL_SA_PASSWORD=password"
      - "ACCEPT_EULA=Y"
    ports:
      - "1433:1433"

However, if starting the container from scratch, it is still necessary to run dotnet ef database update if the tables don't yet exist. This only has to be done when the database container is created or when the migration is updated -- for most changes to the app it does not need to be run again. I would like to add this to the process of creating the containers. To do this, I added the following steps to the Dockerfile after the build step:

...
RUN dotnet build "AspNet.csproj" -c Release -o /app/build

FROM build as db
WORKDIR "/src/AspNet"
RUN dotnet ef database update

FROM db AS publish
...

Having done this, and running docker-compose up, the containers are successfully created but the tables are not -- as if I had not added the extra step at all. Additionally, I don't see anything in the output of docker-compose to indicate the database command has run -- when running it locally, I expect to see output such as:

...\AspNet>dotnet ef database update
Build started...
Build succeeded.
Done.

Given my inexperience with Docker, I suspect that I haven't modified the Dockerfile in the correct way.

I would really appreciate it if someone could advise on the best way to do this; I'm not even sure if it's best placed in the build step since it appears the database update command will build the application for a second time, and it only needs to be run if there's a new migration or if the SQL Server container has just been created.

Upvotes: 1

Views: 2183

Answers (1)

lordnoob
lordnoob

Reputation: 355

This was quite the rabbit hole, but I managed to get it working in the end.

The problem with the code in the question as shown is that dotnet ef database update won't work inside your container as the EF Core SDK tools are not installed. This is evident when running docker-compose build. Theoretically you can do dotnet ef database update --verbose to see what the command actually does, and you can recreate that by calling ef.dll, but it was a very long command...

So I changed the setup to do the migration in-code, by adding the following to Program.cs, per this tutorial. I added the step that waits for the DB to be ready first because I found that Docker was sometimes able to run the code in Program.cs before the SQL Server was able to accept a connection.

// ...
var app = builder.Build();

using (var serviceScope = app.Services.GetRequiredService<IServiceScopeFactory>().CreateScope())
{
    var logger = serviceScope.ServiceProvider.GetRequiredService<ILogger<Program>>();
    var db = serviceScope.ServiceProvider.GetRequiredService<ApiContext>().Database;

    logger.LogInformation("Migrating database...");

    while (!db.CanConnect())
    {
        logger.LogInformation("Database not ready yet; waiting...");
        Thread.Sleep(1000);
    }

    try
    {
        serviceScope.ServiceProvider.GetRequiredService<ApiContext>().Database.Migrate();
        logger.LogInformation("Database migrated successfully.");
    }
    catch (Exception ex)
    {
        logger.LogError(ex, "An error occurred while migrating the database.");
    }
}

app.UseHttpsRedirection();
// ...

It now works correctly and can migrate the database from complete scratch.

Upvotes: 5

Related Questions