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

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:templatedatamaster.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.