Docker initialize database tables and records in SQL Server

Question

How do you specify initialization database script for SQL Server via docker-compose using SQL script file?

docker-compose.yml

database:
    image: microsoft/mssql-server-linux:2017-latest
    container_name: database
    ports:
      - 1433:1433
    volumes:
      - /var/opt/mssql
    environment:
      SA_PASSWORD: "P@55w0rd"
      ACCEPT_EULA: "Y"

schema.sql

CREATE TABLE Department
(
    Id INT PRIMARY KEY IDENTITY (1, 1),
    Name VARCHAR (50) NOT NULL
);

CREATE TABLE Student
(
    Id INT PRIMARY KEY IDENTITY (1, 1),
    Name VARCHAR (50) NOT NULL
);

CREATE TABLE Course 
(
    Id INT PRIMARY KEY IDENTITY (1, 1),
    Name VARCHAR (50) NOT NULL
);

INSERT INTO Student (Id, Name) VALUES(1, "John Doe");
INSERT INTO Student (Id, Name) VALUES(2, "Jane Doe");

Answer 1

If you check the offical documentataion, it suggests to use mssql-docker-demo-app which contain entrypoint script like MySQL container.

import-data.sh

The import-data.sh script is a convenient way to delay the execution of the SQL commands until SQL Server is started. Typically SQL Server takes about 5-10 seconds to start up and be ready for connections and commands.

The next command uses the SQL Server command line utility sqlcmd to execte some SQL commands contained in the setup.sql file.

The setup.sql script will create a new database called DemoData and a table called Products in the default dbo schema.

setup.sql

The setup.sql defines some simple commands to create a database and some simple schema. You could use a .sql file like this for other purposes like creating logins, assigning permissions, creating stored procedures, and much more. When creating a database in production situations, you will probably want to be more specific about where the database files are created so that the database files are stored in persistent storage. This SQL script creates a table with two columns -

ID (integer) and ProductName (nvarchar(max)).

CREATE DATABASE DemoData;
GO
USE DemoData;
GO
CREATE TABLE Products (ID int, ProductName nvarchar(max));
GO

Answer 2

For those who like a little more specifics/examples.

As @Adiii suggested this is how you can initialise the database within a docker-compose file

docker-compose.yml

  test-database:
    build: ./mssql_database
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=<YOUR PASSWORD>
      - TZ=UTC
    volumes:
      - database-data:/var/opt/mssql

volumes:
    database-data:

Directory mssql_database contains

Dockerfile

FROM mcr.microsoft.com/mssql/server

COPY setup.sql setup.sql
COPY setup_database.sh setup_database.sh
COPY entrypoint.sh entrypoint.sh

CMD /bin/bash ./entrypoint.sh

NOTE: If you want to initialise the database during build time instead of run time replace the CMD /bin/bash ./entrypoint.shwith RUN ./opt/mssql/bin/sqlservr & ./setup_database.sh

entrypoint.sh

#!/usr/bin/env bash
set -m
./opt/mssql/bin/sqlservr & ./setup_database.sh
fg

setup_database.sh

NOTE: Instead of using a sleep it would be better to use a healthcheck and once the database is confirmed green run your scripts.

#!/usr/bin/env bash
# Wait for database to startup 
sleep 20
./opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P <YOUR PASSWORD> -i setup.sql

setup.sql

INSERT your sql commands here 

Answer 3

A note if you do use docker-compose and not just bare docker. A snippet from my docker-compose file:

  sql-server:
    image: mcr.microsoft.com/mssql/server:2017-latest-ubuntu
    hostname: sql-server
    container_name: sql-server
    ports:
    - 1433:1433
#    Based on: https://www.softwaredeveloper.blog/initialize-mssql-in-docker-container or similarly: https://github.com/twright-msft/mssql-node-docker-demo-app
    command: bash -c "chmod -R 777 /usr/src/sql/ && /usr/src/sql/entrypoint.sh"
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: Password123
      MSSQL_PID: Express
    volumes:
      - ./sql/:/usr/src/sql/

Now, since I don't use custom Dockerfile I'm obviously not mangling with workdir. And when I run:

docker inspect mcr.microsoft.com/mssql/server:2017-latest-ubuntu | grep -i "DIR"

I get:

    "WorkingDir": "",
    "WorkingDir": "",

Meaning working dir is not set and so you land in the root folder inside the container by default.

This will make scripts like: ./opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P <YOUR PASSWORD> -i setup.sql non-functioning because there's no setup.sql in the root dir. In that case you need to specify absolute path like:

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SA_PASSWORD} -d master -i /usr/src/sql/setup.sql

Answer 4

Another way of doing it, without having to build your own image (no Dockerfile):

# [Create and] Run the MSSQL container
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<sa_password>" --name <container_id> -p 1433:1433 -d mcr.microsoft.com/mssql/server

# Create a "sqlsetup" directory inside the container
docker exec -it <container_id> mkdir /usr/sqlsetup

# Copy your database setup script to the container
docker cp setup.sql <container_id>:/usr/sqlsetup/setup.sql

# Run the script on the database
docker exec -it <container_id> /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <sa_password> -d master -i /usr/sqlsetup/setup.sql

Do not run all the commands at once. SQL Server takes some time to be up and running, so you need some delay to allow it to be running before trying to execute the script.

Answer 5

I had the the same issue, I got inspired by this post https://www.softwaredeveloper.blog/initialize-mssql-in-docker-container

but I made a script that init the database only if not existing: 1- I COPY the sql and sh script in the dockerfile 2- I change the command in the DockeFile: CMD /bin/bash ./entrypoint.sh 3- I create an entrypoint.sh that will call the init script and start the server 4- I create my init script

#!/bin/bash
# Wait to be sure that SQL Server came up
sleep 30s
# DATABSE initialisation
echo "Database initialisation"
# if the table does not exsit it will create the table
# get "1" if the database exist : 'tr' get only the integer, 'cut' only the first integer (the second is the number of row affected)
data=$(/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "SELECT COUNT(*)  FROM master.dbo.sysdatabases WHERE name = N'$DB_NAME'" | tr -dc '0-9'| cut -c1 )
if [ ${data} -eq "0" ]; then
        echo 'create database user'
        /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "CREATE LOGIN $DB_USER WITH PASSWORD='${SA_PASSWORD}', CHECK_POLICY = OFF"

        echo 'create database'
        /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "DROP DATABASE IF EXISTS $DB_NAME"
        /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "CREATE DATABASE $DB_NAME"
        # RUN YOUR SQL FILE HERE

        echo ' give to the user the access to the database'
        /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "CREATE USER $DB_USER FOR LOGIN $DB_USER " -d $DB_NAME
else
        echo "database already existing, nothing to do"
fi

# manual cleaning command
# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "DROP DATABASE $DB_NAME"
# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "DROP  LOGIN  $DB_USER"

I hope it can help someone, you can find the github repo where I did it here : https://github.com/openimis/openimis-db_dkr/blob/release/202204/Dockerfile

I use the same approach for the health check:

data=$(/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "SELECT COUNT(*)  FROM master.dbo.sysdatabases WHERE name = N'$DB_NAME'" | tr -dc '0-9'| cut -c1 )
if [ ${data} -eq "1" ]; then
    exit 0
else
    exit 1
fi