How to have docker compose init a SQL Server database

I have a docker-compose file that creates a starts SQL Server. This is working fine. I can connect to the database and see the master database.

What I am trying to do is create a new database, and add a table and some data to that table. I have been unable to find an example of doing this using SQL Server. All the examples I have seen are either using PostgreSQL or Mysql.

I have tried to adapt this example Docker Compose MySQL Multiple Database

I have created an init directory with a file called 01.sql and the only thing in it is

CREATE DATABASE `test`;

My docker-compose.yml looks like this

services:
    db:
        image: "mcr.microsoft.com/mssql/server"
        ports:
            - 1433:1433
        volumes:             
            - ./init:/docker-entrypoint-initdb.d
        environment:
            SA_PASSWORD: "password123!"
            ACCEPT_EULA: "Y"

When I run docker-compose up

I'm not seeing anything in the logs that implies it's even trying to load this file. When I check the database I do not see any new database either.

I am at a loss to understand why this isn't working for SQL Server but the tutorial implies that it works for MySql. Is there a different command for SQL Server?

Solution

After quite a bit of Googling and combining four or five very old tutorials, I got this working. Ensuring that you are using Linux line endings is critical with these scripts.

Docker-compose.yml

version: '3'

services:
  db:
    build: ./Db
    ports:
        - 1433:1433

Db/DockerFile

# Choose ubuntu version
FROM mcr.microsoft.com/mssql/server:2019-CU13-ubuntu-20.04

# Create app directory
WORKDIR /usr/src/app

# Copy initialization scripts
COPY . /usr/src/app
             
# Set environment variables, not have to write them with the docker run command
# Note: make sure that your password matches what is in the run-initialization script 
ENV SA_PASSWORD password123!
ENV ACCEPT_EULA Y
ENV MSSQL_PID Express

# Expose port 1433 in case accessing from other container
# Expose port externally from docker-compose.yml
EXPOSE 1433

# Run Microsoft SQL Server and initialization script (at the same time)
CMD /bin/bash ./entrypoint.sh

Db/entrypoint.sh

# Run Microsoft SQl Server and initialization script (at the same time)
/usr/src/app/run-initialization.sh & /opt/mssql/bin/sqlservr

Db/run-initialization.sh

# Wait to be sure that SQL Server came up
sleep 90s

# Run the setup script to create the DB and the schema in the DB
# Note: make sure that your password matches what is in the Dockerfile
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P password123! -d master -i create-database.sql

Db/create-database.sql

CREATE DATABASE [product-db]
GO

USE [product-db];
GO

CREATE TABLE product (
    Id INT NOT NULL IDENTITY,
    Name TEXT NOT NULL,
    Description TEXT NOT NULL,
    PRIMARY KEY (Id)
);
GO

INSERT INTO [product] (Name, Description)
VALUES 
('T-Shirt Blue', 'Its blue'),
('T-Shirt Black', 'Its black'); 
GO

Tip: If you change any of the scripts after running it the first time you need to do a docker-compose up --build to ensure that the container is built again or it will just be using your old scripts.

Connect:

host:  127.0.0.1
Username: SA
Password:  password123!