How to create a database automatically and run ef core migration in docker?

Question

This is my Dockerfile

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

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

FROM build AS publish
RUN dotnet publish "MyApi.csproj" -c Release -o /app/publish

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

RUN chmod +x ./entrypoint.sh
CMD /bin/bash ./entrypoint.sh

This is docker-compose.yml

version: "3"
services:
    api:
        build: 
          context: .
          dockerfile: Dockerfile
        ports:
            - "8888:80"
        depends_on:
            - db
    db:
        image: "mcr.microsoft.com/mssql/server:2022-latest"
        environment:
            SA_PASSWORD: "P@55w0rd"
            ACCEPT_EULA: "Y"
        ports:
            - "1433:1433"

This is entrypoint.sh

#!/bin/bash

set -e
run_cmd="dotnet run --server.urls http://*:80"

until dotnet ef database update; do
>&2 echo "SQL Server is starting up"
sleep 1
done

>&2 echo "SQL Server is up - executing command"
exec $run_cmd
sleep 10

>&2 echo "Creating Database"
/opt/mssql-tools/bin/sqlcmd -S db -U sa -P P@55w0rd -i create-database.sql

This is create-database.sql

USE master
GO

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'Demo')
BEGIN
  CREATE DATABASE Demo;
END;
GO

I can run docker compose successfully without error. It creates both containers successfully. I can access to the SQL Server container from SQL Management Studio. The issue is the Demo database is not created automatically as expected. May I know what's wrong?

I notice these errors in the log after running "docker-compose up".

db_1   | /opt/mssql/bin/permissions_check.sh: line 4: [: : integer expression expected
db_1   | SQL Server 2019 will run as non-root by default.
db_1   | This container is running as user mssql.
db_1   | To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
db_1   | /opt/mssql/bin/permissions_check.sh: line 59: [: : integer expression expected
db_1   | 2022-09-21 07:32:01.44 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2022-09-21 07:32:01.49 Server      Did not find an existing master data file /var/opt/mssql/data/master.mdf, copying the missing default master and other system database files. If you have moved the database location, but not moved the database files, startup may fail. To repair: shutdown SQL Server, move the master database to configured location, and restart.

2022-09-21 12:35:04.15 spid27s Recovery is complete. This is an informational message only. No user action is required. 2022-09-21 12:35:04.26 spid36s The default language (LCID 0) has been set for engine and full-text services. 2022-09-21 12:35:04.50 spid36s The tempdb database has 8 data file(s).

and

api_1  | Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server: Could not open a connection to SQL Server)
api_1  |    at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
api_1  |    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
api_1  |    at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
api_1  |    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
api_1  |    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
api_1  |    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
api_1  |    at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
api_1  |    at Microsoft.Data.SqlClient.SqlConnectionFactory.Create

Looks like entrypoint.sh is not executed. Does anyone know why?

Looks like the connection string got issue. This is my connection string is appsettings.json

  "ConnectionStrings": {
    "MyServicesConnection": "Server=localhost,1433;Database=Abc;User Id=sa; Password=P@55w0rd#513;Trusted_Connection=True;MultipleActiveResultSets=true"
  },

As you can see the user id is sa. I manually create this connectionstring based on my docker compose file for my SQL image.

You don't need to create database by hand, `dotne ef update database` creates the database. _"At this point you can have EF create your database and create your schema from the migration"_ — dani herrera, Sep 19 '22 at 07:39
Why do you use mounted cli entity tools instead of using the service itself to check if it needs to creade/miiigrate stuff at its startup, as part of the startup process. Get a hold of the db context and EnsureCreated(), or apply migrations as needed but from wiithin the service, would that work for you? — MKougiouris, Sep 21 '22 at 07:06
@MKougiouris I tried. It works (the database gets created) when I run it locally with IIS. But when I run "docker-compose up" to deploy it to Docker Desktop, it doesn't work (no database was created). — Steve, Sep 21 '22 at 07:36
How do you reference the connection string inside from the project when runniing in docker? How does the internal vm "sees" the db on that same compose file? edit: better put, what is your connection string for the docker build final version — MKougiouris, Sep 21 '22 at 10:56

score 0 · Answer 1 · answered Sep 22 '22 at 16:42

Instead of trying to execute the SQL file in entrypoint.sh, you can pass the file as a volume to the database service.

volumes:
  - <relative-path-to-create-database.sql>:/docker-entrypoint-initdb.d/init.sql

The compose file would look something like

version: "3"
services:
    api:
        build: 
          context: .
          dockerfile: Dockerfile
        ports:
            - "8888:80"
        depends_on:
            - db
    db:
        image: "mcr.microsoft.com/mssql/server:2022-latest"
        environment:
            SA_PASSWORD: "P@55w0rd"
            ACCEPT_EULA: "Y"
        ports:
            - "1433:1433"
        volumes:
          - <relative-path-to-create-database.sql>:/docker-entrypoint-initdb.d/init.sql

This could be your entrypoint.sh, only the last line is changed.

#!/bin/bash

set -e
run_cmd="dotnet run --server.urls http://*:80"

until dotnet ef database update; do
>&2 echo "SQL Server is starting up"
sleep 1
done

>&2 echo "SQL Server is up - executing command"
exec $run_cmd
sleep 10

exec "$@

Try this Dockerfile

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

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

FROM build AS publish
RUN dotnet publish "MyApi.csproj" -c Release -o /app/publish

FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .

COPY entrypoint.sh /usr/bin/
RUN chmod +x /usr/bin/entrypoint.sh

ENTRYPOINT ["entrypoint.sh"]

CMD ["dotnet", "MyApi.dll"]

It doesn't work as expected. The init.sql is a folder. – Steve Sep 23 '22 at 13:20 — Steve, Sep 23 '22 at 13:20

score 0 · Answer 2 · answered Sep 26 '22 at 22:06

Maybe you need to change from depends_on to links.

Some info on SO: link

I have an aplication require a redis cache server.

My Docker-composer.yml looks like:

version: "3.2"
services:
  app:
      image: "app:latest"
      build: .
      ports:
        - "${APP_PORT}:80"
      volumes:
        - ./public:/var/www/html
networks:
        - default
      links:
        - redis4
  redis4:
      image: redis:latest
      ports:
        - '${REDIS_PORT}:6379'

Using volumes free me from using COPY comands to docker and do not require to rebuild it if something is changed.

Docker file is like:

FROM php:8.0-apache

... some dependencies


# Supervisord
RUN apt-get update && apt-get install -y supervisor
RUN mkdir -p /var/log/supervisor && \
    rm -f /etc/supervisord.conf
COPY supervisord.conf /etc/

... others dependencies

COPY run_httpd.sh /run_httpd.sh
RUN chmod -v +x /run_httpd.sh

... some commands

# run background apps
CMD ["/usr/bin/supervisord"]

supervisord.conf:

[supervisord]
nodaemon=true
user=root
logfile=/var/log/supervisor/supervisor.log

[program:httpd]
startsecs=0
autorestart=false
command=/bin/bash -c "/run_httpd.sh"

[program:crond]
startsecs=0
autorestart=true
command=/bin/bash -c "/usr/sbin/cron"

run_https.sh

#!/bin/bash

# Make sure we're not confused by old, incompletely-shutdown httpd
# context after restarting the container.  httpd won't start correctly
# if it thinks it is already running.
rm -rf /run/httpd/* /tmp/httpd*

exec /usr/sbin/apachectl -DFOREGROUND

How to create a database automatically and run ef core migration in docker?

2 Answers2