Seeding Initial Data Using Docker Compose and SQL Scripts

Introduction

Nothing kills momentum on a new project like cloning the repo, running docker compose up, and landing on an empty database. No roles, no permissions, no login, so nothing works until someone reads the wiki and hand-runs a SQL script. On the Contact Management Application I wanted docker compose up to be the whole setup: containers come up, the schema seeds itself, and you can log in.

This post shows how the SQL Server container seeds its own baseline data from mounted scripts, and it is honest about the one part of that setup that is a hack.

1. Why Data Seeding is Important

Data seeding is the process of pre-populating the database with initial data that is necessary for the application to function, or for testing purposes. Some of the main reasons for using data seeding include:

• Initial configuration data: Populating lookup tables or default values (e.g., roles, permissions).

• Test data: Providing sample data for developers or testers to interact with.

• Consistent environments: The database is in a known state every time the application starts, which makes debugging and test runs repeatable.

2. Using Docker Compose to Seed Data

Docker Compose allows you to orchestrate containers, such as MS SQL Server and your API, and automate the data seeding process. To seed the database with initial data, you can use SQL scripts that run when the MS SQL Server container starts.

2.1 Extending the Docker Compose File for Data Seeding

We will update the docker-compose.yml file to include a volume that will mount the SQL seed scripts to the MS SQL Server container.

services:
  frontend:
    build:
      context: ./frontend
      dockerfile: debug.dockerfile
    command: ["npm", "run", "start:debug"]
    ports:
      - 4200:4200
      - 49153:49153
    volumes:
      - ./frontend:/app
      - /app/node_modules
    stdin_open: true
    tty: true
    depends_on:
      - api
    networks:
      - mssql_network

  api:
    build:
      context: ./backend/src
      dockerfile: Debug.Dockerfile
    command: ["dotnet", "watch", "--project", "Contact.Api/Contact.Api.csproj", "run", "--urls", "http://0.0.0.0:5000"]
    ports:
      - 5000:5000


    environment:
      - ASPNETCORE__ENVIRONMENT=${ENVIRONMENT}
      - DOTNET_SKIP_POLICY_LOADING=false
      - AppSettings__ConnectionStrings__DefaultConnection=Server=${SQL_SERVER};Database=${SQL_DATABASE};User ID=${SQL_USER};Password=${SQL_PASSWORD};Trusted_Connection=False;Encrypt=False;
      - AppSettings__Secret=${JWT_SECRET}
      - AppSettings__Issuer=${JWT_ISSUER}
      - AppSettings__Audience=${JWT_AUDIENCE}
      - AppSettings__PasswordResetUrl=${PASSWORD_RESET_URL}
      - SmtpSettings__SmtpServer=${SMTP_SERVER}
      - SmtpSettings__Port=${SMTP_PORT}
      - SmtpSettings__Username=${SMTP_USERNAME}
      - SmtpSettings__Password=${SMTP_PASSWORD}
      - SmtpSettings__FromEmail=${SMTP_FROM_EMAIL}
      - SmtpSettings__EnableSsl=${SMTP_ENABLE_SSL}
    volumes:
      - ./backend/src:/app
      - ~/.vsdbg:/remote_debugger:rw
    depends_on:
      - mssql
    networks:
      - mssql_network

  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: sqlserver_express
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Express   # Specifies the edition to run as Express
      - MSSQL_SA_PASSWORD=${SQL_PASSWORD}   # Set the SA (System Administrator) password
    ports:
      - "1433:1433"  # Expose SQL Server port 1433
    volumes:
      - mssql_data:/var/opt/mssql  # Persist database data outside of the container
      - ./backend/scripts:/scripts  # Mount for SQL scripts
    entrypoint:
      - /bin/bash
      - -c
      - |
        /opt/mssql/bin/sqlservr & sleep 15s;
        /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SQL_PASSWORD} -d master -i /scripts/seed-data.sql; wait
    networks:
      - mssql_network

volumes:
  mssql_data: # Named volume to persist data

networks:
  mssql_network:
    driver: bridge

Explanation:

• volumes: The mssql service mounts the host ./backend/scripts directory into the container at /scripts. The entrypoint starts SQL Server in the background, waits 15 seconds, then runs sqlcmd against seed-data.sql.

3. Writing SQL Seed Scripts

The SQL seed scripts are responsible for inserting the initial data into your database. For example, we can create scripts to insert data into roles, permissions, and other necessary tables.

Create the directory ./scripts/ and add the SQL script files.

Example seed-data.sql

USE ContactDb;

-- Insert default roles
INSERT INTO Roles (Id, Name, CreatedOn)
VALUES (NEWID(), 'Admin', GETDATE()),
       (NEWID(), 'User', GETDATE());

-- Insert default permissions
INSERT INTO Permissions (Id, Name, CreatedOn)
VALUES (NEWID(), 'Contacts.Create', GETDATE()),
       (NEWID(), 'Contacts.Update', GETDATE()),
       (NEWID(), 'Contacts.Delete', GETDATE()),
       (NEWID(), 'Contacts.Read', GETDATE());

-- Map Admin role to permissions
DECLARE @AdminRoleId UNIQUEIDENTIFIER;
SET @AdminRoleId = (SELECT Id FROM Roles WHERE Name = 'Admin');

INSERT INTO RolePermissions (Id, RoleId, PermissionId, CreatedOn)
SELECT NEWID(), @AdminRoleId, p.Id, GETDATE()
FROM Permissions p;

Explanation:

• The script seeds the Roles and Permissions tables with default data.

• The Admin role is associated with all permissions for contacts (e.g., create, update, delete, read).

For full script for this project please refer seed-data.sql

4. Automating the Seeding Process

When the MS SQL Server container starts, the entrypoint runs the SQL scripts under /scripts/ (mounted via the volume), so the database comes up already seeded.

4.1 Adding Seed Scripts to Docker Compose Workflow

To automate this process:

1. Put all the SQL scripts in ./scripts/.

2. Update the entrypoint command:

 entrypoint:
      - /bin/bash
      - -c
      - |
        /opt/mssql/bin/sqlservr & sleep 15s;
        /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SQL_PASSWORD} -d master -i /scripts/seed-data.sql; wait

Use the docker-compose up command to start both the API and the database containers. The database container runs the seed scripts on startup.

4.2 Running Docker Compose

docker-compose up --build

Once the containers are running, the seed scripts will be executed, and you can access the MS SQL Server database with the pre-populated data.

5. Verifying the Seed Data

You can verify that the data has been correctly seeded by accessing the MS SQL Server container and querying the database.

5.1 Connecting to the MS SQL Server Container

To connect to the MS SQL Server container, use the following command:

docker exec -it <db-container-name> /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P YourPassword

Once connected, you can run queries to verify the data has been seeded:

USE ContactDb;
SELECT * FROM Roles;
SELECT * FROM Permissions;
SELECT * FROM RolePermissions;

These queries will return the data that was inserted by the seed scripts.

6. Why bother wiring this into Docker

• Consistency across environments. Every environment (local, testing, staging, production) starts from the same baseline, which cuts down on “works on my machine” surprises.
• One-command onboarding. The seed runs whenever the database container starts, so a new developer or a CI job needs nothing beyond docker compose up.
• Reproducibility. The seed data lives in version-controlled SQL, so the same starting state reproduces anywhere the scripts run.