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

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 2

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