Snippets

Appearance

Local Database With Scripts (MSSQL) ​

When developing apps locally it can be really useful to have a dockerised database unique to the application.

Often rather than just running a pre-built image, you'll want to run a database with some initial data, tables, or a schema.

For this purpose we can create our own image that extends the base image and adds our own scripts.

Setup ​

For most applications the directory structure will look something like this:

database/
  Dockerfile
  scripts/
    01-create-database.sql
    02-create-tables.sql
    03-seed-data.sql
development/
  compose.yml
src/
  ...
tests/
  ...

Dockerfile ​

Create a dockerfile in the database/ directory:

dockerfile
FROM mcr.microsoft.com/mssql/server:2022-latest

# Set the SQL Server environment variables
ENV ACCEPT_EULA="Y"
ENV SA_PASSWORD="Password123"

# Setup port
EXPOSE 1433

# Create a temp directory
RUN mkdir -p /tmp/init

# Copy all the scripts into the container
COPY ./scripts/ /tmp/init

ENTRYPOINT [ "/tmp/init/entrypoint.sh" ]

DANGER

As this is a local development database, we're using the sa user with a simple password. Do not use this in production.

Scripts ​

Create the scripts in the database/scripts/ directory:

bash
#!/bin/bash

# Run the sql scripts and start sql server
/tmp/init/run-scripts.sh & /opt/mssql/bin/sqlservr
bash
#!/bin/bash

# Wait for the mssql database to be ready
while ! /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -C -Q "SELECT 1" > /dev/null; do
  sleep 1
done

echo "SQL Server is up and running"

# Check if the setup has already been executed
SETUP_DONE=$(/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -C -Q "IF EXISTS (SELECT 1 FROM master.sys.tables WHERE name = 'setup_marker' AND schema_id = SCHEMA_ID('dbo')) SELECT 1 ELSE SELECT 0" -h -1 -W -r 1 | grep -oE '^[0-9]+' | tr -d '[:space:]')

if [[ "$SETUP_DONE" == "1" ]]; then
  echo "Setup has already been completed. Skipping initialization."
  exit 0
else
  echo "Setup has not been completed. Running initialization."
fi

# Run all scripts in the scripts folder
for entry in /tmp/init/*.sql; 
do
  echo "Running script $entry"
  /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -C -i $entry
done

# Create a marker table to indicate setup completion
/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -C -Q "CREATE TABLE master.dbo.setup_marker (id INT PRIMARY KEY IDENTITY, created_at DATETIME DEFAULT GETDATE())"

echo "All scripts have been run"

The above script waits for the database to be ready, then checks if the setup has already run. If not it will run all the scripts in the scripts/ directory and create a marker table to indicate that the setup has been completed.

Create any scripts that you need in the database/scripts/ directory.

TIP

See below for an example of the scripts you might want to run.

sql
CREATE DATABASE MyDatabase
sql
USE MyDatabase

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  name NVARCHAR(50)
)
sql
USE MyDatabase

INSERT INTO MyTable (id, name) VALUES (1, 'Alice')
INSERT INTO MyTable (id, name) VALUES (2, 'Bob')
sql
USE MyDatabase

CREATE LOGIN MyUser WITH PASSWORD = 'MyPassword'
CREATE USER MyUser FOR LOGIN MyUser
ALTER ROLE db_owner ADD MEMBER MyUser;

Compose ​

Lastly we need to create a docker-compose.yml file in the development/ directory:

yaml
services:
  db:
    build:
      context: ../database
      dockerfile: Dockerfile
    volumes:
      - db-data:/var/opt/mssql
    ports:
      - "1433:1433"

volumes:
  db-data: