How to use a unified connection string to Docker container SQL Server from both local host and another container?

Question

When I run SQL Server in a container to connect the API to it when running from a local host, I need one kind of connection string. However, when I run SQL Server and API in Docker using Docker Compose, I need a different kind of connection string.

Here is my Docker-compose.yaml

version: '3.9'

volumes:
  SqlDataStorage:
  
networks:
  my-network:
    driver: bridge

services:
  weather.api:
    container_name: WeatherForecastApi
    image: maks0s/weather-app:6.1.0
    build:
      context: .
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
      - ASPNETCORE_HTTP_PORTS=8080
      - ASPNETCORE_HTTPS_PORTS=8081
      - ASPNETCORE_Kestrel__Certificates__Default__Password=********
      - ASPNETCORE_Kestrel__Certificates__Default__Path=/https/aspnetapp.pfx
    ports:
      - '5000:8080'
      - '5001:8081'
    volumes:
      - ~/.aspnet/https:/https:ro
    networks:
      - my-network
    depends_on:
      - weather.db

  weather.db:
    container_name: WeatherForecastCloudDb
    image: mcr.microsoft.com/mssql/server:2022-latest
    env_file:
      - sqlconfig.env
      - sapassword.env
    ports:
      - '1439:1433'
    volumes:
      - SqlDataStorage:/var/opt/mssql
    networks:
      - my-network

Running SQL Server in a container, in order to connect my API to it when running from a local host, I need a connection string of the following kind:

"Data Source=host.docker.internal,1439;Initial Catalog=WeatheForecastDb;User ID=sa;Password=**********; Trust Server Certificate=True; MultiSubnetFailover=True"

However, when I run the SQL Server and API in Docker using Docker Compose, the connection string should be:
"Data Source=weather.db;Initial Catalog=WeatheForecastDb;User ID=sa;Password=**********; Trust Server Certificate=True; MultiSubnetFailover=True"

The question arises, is it possible to create some unified connection string, or will it be necessary to use ternary operators with environment variable checking to form a connection string directly at startup time in the local/container?

Answer 1

The question arises, is it possible to create some unified connection string, or will it be necessary to use ternary operators with environment variable checking to form a connection string directly at startup time in the local/container?

While in theory if you run everything in the default network (i.e. removing networks: from the compose) you should be able to use weather.db in both cases (if you are ok with using the same container) but the "correct" answer is to use the configuration to provide the connection string.

Add some key to the configuration (usually it is ConnectionStrings:SomeDbIdentifier) and then for local environment add to the appsettings.Development.json:

"ConnectionStrings": {
  "SomeDbIdentifier": "Data Source=..."
},

In the compose add environment variable:

- ConnectionStrings__SomeDbIdentifier=Data Source=weather.db;...

And in the app:

// get the configuration depending on the app and the setup
Configuration.GetConnectionString("SomeDbIdentifier");

See also:

• Configuration in ASP.NET Core