How to have docker compose init a SQL Server database

Question

I have a docker compose file which 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, add a table and some data to that table. I have been unable to find an example doing this using SQL SEever. All the example I have found 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 its even trying to load this file. When I check the database I am not seeing any new database either.

I am at a lost 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?

Answer 1

After quite a bit of Googleing 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 to have to write them with 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 in 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!

Answer 2

Docker images for mysql and postgres know about docker-entrypoint-initdb.d directory, and how to process it.

For example, for mysql, here is Dockerfile

It runs script docker-entrypoint.sh:

COPY docker-entrypoint.sh /usr/local/bin/
RUN ln -s usr/local/bin/docker-entrypoint.sh /entrypoint.sh # backwards compat
ENTRYPOINT ["docker-entrypoint.sh"]

And docker-entrypoint.sh runs sql scripts from docker-entrypoint-initdb.d directory:

docker_process_init_files /docker-entrypoint-initdb.d/*

Looks like SQL Server docker image does not have processing for docker-entrypoint-initdb.d, you need to study SQL Server Dockerfile or documentation, probably there is some tools to init DB. If there not - you can create your own Docker image, basing on original:

Dockerfile:

FROM mcr.microsoft.com/mssql/server
# implement init db from docker-entrypoint-initdb.d