Docker Compose Sql server Creating a database and tables automatically upon startup

Question

I created a asp.net core web api with Sql server. By running docker compose file I need to create a database and tables. My solutions is as follows

Dockerfile

FROM microsoft/dotnet:2.2-aspnetcore-runtime AS base
WORKDIR /app
EXPOSE 80

FROM microsoft/dotnet:2.2-sdk AS build
WORKDIR /src
COPY StarterPackMSSQL.StarterPackMSSQL.csproj StarterPackMSSQL/
RUN dotnet restore StarterPackMSSQL/StarterPackMSSQL.csproj
COPY . .
WORKDIR /src/StarterPackMSSQL
RUN dotnet build StarterPackMSSQL.csproj -c Release -o /app

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

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

docker-compose.yml

version: '3.5'

networks : 
  localdev:
    name: localdev

services:
 starterpackmssql:
    image: ${DOCKER_REGISTRY-}mssql
    restart: always
    build:
      context: .
      dockerfile:StarterPackMSSQL/Dockerfile
    ports:
      - "5001:80"
    depends_on:
      - db-server
    networks:
      - localdev

  db-server:
    image: microsoft/mssql-server-linux
    container_name: db-server
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=password123
      - MSSQL_TCP_PORT=1433

    ports:
      - "1400:1433"
    networks:
      - localdev

SQL Script

CREATE DATABASE [StarterPacksDB]
GO

USE [StarterPacksDB]
GO

/****** Object:  Table [dbo].[Users]    Script Date: 10/18/2019 10:42:39 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [handle] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I just need to create database and tables automatically upon when running docker-compose up

And it should work on both mac and windows.

Solution

In case of MySQL and Postgress etc, all DB image comes with out of the box handy entrypoint script that does the DB initialization etc task, but there no entrypoint with MSSQL docker image. So I will recommend to use offical recommended approach as your current script will not work as per your expectation.

But the offical image recommendYou can also use the tools in an entrypoint.sh script to do things like create databases or logins, attach databases, import data, or other setup tasks. See this example of using an entrypoint.sh script to create a database and schema and bcp in some data.

An entrypoint CMD which executes a import-data.sh script at runtime to use sqlcmd to execute a .sql script to create a database and populate initial schema into it.

Now the second thing to make the above entrypoint working, you should use the updated and latest recommended Image by MS. as the current image is deprecated.

microsoft/mssql-server-linux

By microsoft • Updated 10 months ago Deprecated SQL Server on Linux Container Repository. See below for more details.

We are moving to mcr.microsoft.com where you can pull the latest SQL Server 2017 and SQL Server 2019 on Linux containers. We will no longer be updating this container repo with updates, and we will eventually remove all container images from this repo.

Visit our new Docker Hub page at https://hub.docker.com/r/microsoft/mssql-server

https://hub.docker.com/r/microsoft/mssql-server-linux/

Comments

[Janith Widarshana]

As you suggest entrypoint.sh solution is work only for Linux or Mac environment. Do you have any idea about working it on Windows environment?

[Adiii]

I said your entrypoint script will not work, as DB creation happend when MSSQL server is up and able to accept connection, so I will suggest to use github.com/twright-msft/mssql-node-docker-demo-app this entrypoint which will wait for DB up and then create DB etc.

[rStorms]

Is there any solution to use the RUN command in the Dockerfile to populate the database only on first build using sqlcmd? Doing it this way, the sql is tried to be inserted everytime I do docker-compose up, which produces error messages..Thanks!