Flyway + SQL Server ERP Sandbox

Local SQL Server 2022 environment for an ERP-style schema, bootstrapped with Docker Compose and migrated with Flyway.

Project gives you:

• SQL Server 2022 running in Docker
• automatic database creation on first boot
• automatic app login and database user creation
• Flyway-managed schema migrations
• seeded sample ERP data for querying and testing

Stack

• SQL Server image: mcr.microsoft.com/mssql/server:2022-CU24-ubuntu-22.04
• migration runner: flyway/flyway:12.3.0
• orchestration: Docker Compose

What Happens On Startup

1. Compose builds custom SQL Server image from Dockerfile.
2. Container starts SQL Server and runs SqlCmdStartup.sh in background.
3. Startup script waits for SQL Server, creates database if missing, then creates app login and database user with SqlCmdScript.sql.
4. After database becomes healthy, Flyway runs migrations from db/.
5. Seed data loads as part of migration history.

Database files live in named volume mssql-data, so data persists across container restarts.

Prerequisites

• Docker Desktop or Docker Engine with Compose support
• enough local CPU/RAM to run SQL Server container

Configuration

Copy example env file:

cp .env.example .env

Default values:

MSSQL_PORT=1433
MSSQL_DATABASE=ERP
MSSQL_SA_PASSWORD=Passw0rd!123
MSSQL_APP_USER=ERPApp
MSSQL_APP_PASSWORD=ERPApp!123

Important:

• SQL Server password must meet Microsoft complexity rules
• if port 1433 already used on host, change MSSQL_PORT

Quick Start

Start database and run migrations:

docker compose up --build -d

Check service state:

docker compose ps

Inspect migration output:

docker compose logs flyway

Expected result:

• db service stays running
• flyway service exits successfully after migrations finish

Stop services:

docker compose down

Reset everything, including persisted database files:

docker compose down -v

Connect To Database

Host connection:

• server: localhost,<MSSQL_PORT>
• database: value of MSSQL_DATABASE
• app user: value of MSSQL_APP_USER
• app password: value of MSSQL_APP_PASSWORD

Example application connection string:

Server=localhost,1433;Database=ERP;User Id=ERPApp;Password=ERPApp!123;Encrypt=True;TrustServerCertificate=True;

Migration Files

• db/V1__Create_erp_schema.sql: base ERP schema, constraints, indexes
• db/V2__Seed_erp_sample_data.sql: sample master data and transactional records

Run Flyway manually against running database:

docker compose run --rm flyway info
docker compose run --rm flyway migrate

Schema Overview

Seeded schema covers common ERP domains:

• business units and departments
• employees
• suppliers
• warehouses
• items and inventory balances
• purchase orders and purchase order lines
• work orders and work order materials

Sample data includes:

• 2 business units
• 4 departments
• 4 employees
• 3 suppliers
• 2 warehouses
• multiple inventory items and balances
• purchase orders such as PO-2026-001
• work orders such as WO-2026-010

Query Examples

List tables in ERP database:

docker compose exec db /opt/mssql-tools18/bin/sqlcmd \
  -S localhost \
  -U sa \
  -P 'Passw0rd!123' \
  -No \
  -d ERP \
  -Q "SELECT name FROM sys.tables ORDER BY name;"

Check item inventory by warehouse:

docker compose exec db /opt/mssql-tools18/bin/sqlcmd \
  -S localhost \
  -U sa \
  -P 'Passw0rd!123' \
  -No \
  -d ERP \
  -Q "SELECT w.WarehouseCode, i.ItemCode, ib.QuantityOnHand FROM dbo.InventoryBalances ib JOIN dbo.Warehouses w ON w.WarehouseId = ib.WarehouseId JOIN dbo.Items i ON i.ItemId = ib.ItemId ORDER BY w.WarehouseCode, i.ItemCode;"

Review purchase orders:

docker compose exec db /opt/mssql-tools18/bin/sqlcmd \
  -S localhost \
  -U sa \
  -P 'Passw0rd!123' \
  -No \
  -d ERP \
  -Q "SELECT PurchaseOrderNumber, OrderStatus, OrderedAt, TotalAmount FROM dbo.PurchaseOrders ORDER BY OrderedAt DESC;"

If you changed values in .env, update database name, user, password, or port in these examples.

Project Layout

.
|-- Dockerfile
|-- docker-compose.yml
|-- entrypoint.sh
|-- SqlCmdStartup.sh
|-- SqlCmdScript.sql
`-- db/
    |-- V1__Create_erp_schema.sql
    `-- V2__Seed_erp_sample_data.sql

Troubleshooting

flyway fails to connect:

• confirm db container is healthy with docker compose ps
• check SQL Server startup logs with docker compose logs db
• verify passwords in .env meet SQL Server complexity requirements

Port bind fails:

• another process already uses host port from MSSQL_PORT
• change MSSQL_PORT in .env, then rerun docker compose up --build -d

Need clean rebuild:

• run docker compose down -v
• rerun docker compose up --build -d